MySQL kļūda 1366, "Incorrect string value", nozīmē, ka serveris saņēma baitus, kurus nevar saglabāt mērķa kolonnas rakstzīmju kopā. Praksē tas gandrīz vienmēr ir viens no trim gadījumiem: emocijzīme nonāk utf8mb3 kolonnā, ne-Rietumeiropas burts nonāk latin1 kolonnā, vai klienta savienojums deklarē vienu rakstzīmju kopu, bet sūta citu.
Kļūda ir kaitinoša, taču serveris vienkārši dara savu darbu. Bez tās tā pati rinda tiktu saglabāta ar jautājuma zīmēm vai nesakarīgiem simboliem, un jūs to atklātu pēc mēnešiem. Tālāk: kā nolasīt ziņojumu, kā ar dažiem vaicājumiem atrast īsto cēloni, kā to salabot un kas mainās, ja tabulā ir simtiem miljonu rindu.
Divas kļūdas aiz viena numura
Kļūdai 1366 ir simbols ER_TRUNCATED_WRONG_VALUE_FOR_FIELD, SQLSTATE HY000 un ziņojuma veidne Incorrect %s value: '%s' for column '%s' at row %ld (MySQL servera kļūdu saraksts). %s ir tips, tāpēc redzēsiet divas ļoti atšķirīgas grupas:
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x80' for column 'comment' at row 1
ERROR 1366 (HY000): Incorrect integer value: '' for column 'qty' at row 1
- "Incorrect string value" ir rakstzīmju kopas problēma. Lielākā daļa raksta ir par to.
- "Incorrect integer value" (vai decimal, vai datetime) ir strict mode problēma: lietotne nosūtīja vērtību, kas nav derīga kolonnas tipam. Tai ir sava sadaļa raksta beigās.
Vispirms nolasiet heksadecimālos baitus
Ziņojumā citētā vērtība ir neapstrādāti baiti, un tie parasti pasaka cēloni, pirms palaižat kaut vienu vaicājumu.
- Sākas ar
\xF0(piemēram,\xF0\x9F\x98\x80, kas ir emocijzīme 😀): četru baitu UTF-8 rakstzīme. Kolonna gandrīz noteikti irutf8mb3(bieži rādīta kāutf8), kas izmanto ne vairāk kā trīs baitus rakstzīmei un atbalsta tikai Basic Multilingual Plane, tāpēc emocijzīmes un citas papildu rakstzīmes tajā vispār nevar saglabāt (utf8mb3 rakstzīmju kopa). - Divu baitu virknes, piemēram,
\xC4\x81(latviešu ā) vai kirilica: kolonna, visticamāk, irlatin1, kurā šādu burtu nav. Franču un vācu diakritiskās zīmes bieži ietilpstlatin1, tāpēc šādas tabulas izdzīvo gadiem, līdz pierakstās pirmais klients no Rīgas vai Varšavas. - Baiti, kas vispār nav derīgs UTF-8: mūsu pieredzē tie ir binārie dati (hash, šifrēts marķieris, fails), ko raksta teksta kolonnā, vai klients, kas sūta vienu kodējumu, deklarējot
utf8mb4. Tad risinājums irVARBINARY/BLOBkolonna vai salabots klients, nevis jauna rakstzīmju kopa.
1. solis: pārbaudiet kolonnu
Apskatiet tabulu ar SHOW CREATE TABLE vai uzskaitiet visas kolonnas visā serverī, kas joprojām izmanto vecu rakstzīmju kopu:
SELECT table_schema, table_name, column_name,
character_set_name, collation_name, column_type
FROM information_schema.columns
WHERE character_set_name IN ('utf8mb3', 'latin1')
AND table_schema NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema')
ORDER BY table_schema, table_name, ordinal_position;
Kopš MySQL 8.0 servera noklusējumi ir utf8mb4 un utf8mb4_0900_ai_ci, tāpēc jaunām datubāzēm viss ir kārtībā. Vecās rakstzīmju kopas nāk no shēmām, kas izveidotas 5.x versijās un pārnestas ar dump failiem un jauninājumiem. MySQL rāda utf8 kā utf8mb3 izvadē SHOW CREATE TABLE un INFORMATION_SCHEMA, un utf8mb3 ir novecojusi, tās izņemšana gaidāma kādā nākamajā lielajā versijā. Konvertēt tagad ir lētāk nekā vēlāk zem spiediena.
2. solis: pārbaudiet savienojumu
Ja kolonna jau ir utf8mb4, bet kļūda joprojām parādās, problēma ir savienojumā. Serveris interpretē ienākošos vaicājumus character_set_client kopā, un SET NAMES vienlaikus iestata character_set_client, character_set_connection un character_set_results (savienojuma rakstzīmju kopas):
SHOW SESSION VARIABLES LIKE 'character\_set\_%';
SHOW SESSION VARIABLES LIKE 'collation\_%';
-- Fix for the current session (sets client, connection and results)
SET NAMES utf8mb4;
Veiciet šo pārbaudi no paša lietotnes savienojuma, nevis no sava termināļa: mysql klients parasti pēc noklusējuma izmanto utf8mb4, tāpēc jūsu manuālais tests izdosies, kamēr lietotne kļūdīsies. Ilgtspējīgs risinājums ir draivera rakstzīmju kopas opcija (piemēram, charset=utf8mb4 PHP PDO DSN), nevis SET NAMES, kas izkaisīts pa kodu. Mūsu pieredzē savienojums ar utf8mb3 noraida emocijzīmes pat tad, ja visas kolonnas ir utf8mb4.
3. solis: konvertējiet kolonnu uz utf8mb4
Ir divi veidi, un atšķirība ir svarīga (ALTER TABLE):
-- Per column: keeps the data type you chose
ALTER TABLE reviews
MODIFY comment TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
MODIFY title VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL;
-- Whole table: simpler, but may turn TEXT/VARCHAR into MEDIUMTEXT
ALTER TABLE reviews CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- Default for tables created later (existing tables are not changed)
ALTER DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
MODIFYkatrai kolonnai saglabā tieši to tipu, ko uzrakstāt. Produkcijas shēmās dodiet priekšroku tam.CONVERT TO CHARACTER SETkonvertē visas rakstzīmju kolonnas, un rokasgrāmata brīdina, ka tas pēc vajadzības maina tipus, lai saglabātu to pašu rakstzīmju skaitu:latin1TEXTkolonna kļūst parMEDIUMTEXT, un garšVARCHARarī var kļūt parMEDIUMTEXT. ORM un shēmu salīdzināšanas rīki to vēlāk pamana.- Norādiet salīdzināšanas kārtību (collation) skaidri. Tā avots, replikas un nākamā migrācija paliek saskaņā.
Pirms konvertēšanas pārbaudiet baitos mērītos limitus. Indeksa atslēgas ir ierobežotas līdz 3072 baitiem DYNAMIC un COMPRESSED tabulās un 767 baitiem COMPACT un REDUNDANT, kas ir 768 vai 191 utf8mb4 rakstzīme (pāreja uz utf8mb4). Ja indekss ir par garu, konvertēšana neizdodas ar kļūdu, kas aprakstīta mūsu rokasgrāmatā par MySQL kļūdu 1071.
latin1 kolonnā jau ir UTF-8 baiti (lietotne gadiem rakstīja UTF-8 pa latin1 savienojumu, un citos rīkos é vietā redzat é), CONVERT TO šos baitus konvertēs vēlreiz un padarīs haosu pastāvīgu. Rokasgrāmatas risinājums ir iet caur bināro tipu, jo konvertēšana uz BLOB un no tā nenotiek:
-- Column is labelled latin1, but the bytes are really UTF-8
ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8mb4;
Pārbaudiet to uz kopijas un salīdziniet rindu paraugu pirms un pēc.Variants "Incorrect integer value"
Ja ziņojumā ir integer, decimal vai datetime, rakstzīmju kopa nav iesaistīta. Strict mode režīmā nederīga vērtība INSERT vai UPDATE vaicājumā transakciju tabulai pārtrauc vaicājumu un atceļ to. Bez strict mode MySQL saglabā pielāgotu vērtību un izdod tikai brīdinājumu (SQL režīmi). Noklusējuma sql_mode MySQL 8.4 ietver STRICT_TRANS_TABLES, tāpēc šīs kļūdas bieži parādās tūlīt pēc jaunināšanas no servera, kas darbojās ar tukšu sql_mode.
- Parastais vaininieks ir formas lauks, kas nosūtīts kā tukša virkne
''skaitliskā kolonnā. Sūtiet no lietotnesNULLvai īstu skaitli. - Neizlabojiet to, globāli izslēdzot strict mode. Tas kļūdas atkal pārvērš klusās datu izmaiņās, piemēram, nullēs tur, kur nekas netika ievadīts.
INSERT IGNOREarī pazemina kļūdu: rokasgrāmata saka, ka rindas ar konvertēšanas kļūdām saņem tuvāko derīgo vērtību. Tas ir pieņemami vienreizējam, pārskatītam importam, nevis lietotnes rakstīšanai.
Lielās datubāzēs
Dažu mazu tabulu konvertēšana aizņem sekundes. Shēmā ar terabaitiem datu tas ir projekts, un šīs ir vietas, kur tas sāp:
- Konvertēšanas laikā rakstīšana ir bloķēta. Tiešsaistes DDL tabulā rakstzīmju kopas konvertēšana norādīta kā tabulas pārbūve bez vienlaicīga DML (tiešsaistes DDL operācijas). 300 GB tabulā tas nozīmē stundas bez rakstīšanas. Izmantojiet gh-ost vai pt-online-schema-change; mūsu rokasgrāmata par ALTER TABLE bez dīkstāves apraksta, kā tie darbojas un kā kļūdās.
- Diska vieta. Pārbūve vai tiešsaistes rīks uzraksta pilnu jaunu tabulas kopiju ar indeksiem, pirms vecā tiek dzēsta. Pārbaudiet brīvo vietu katrai tabulai, nevis serverim kopumā, un atcerieties bināros žurnālus, ko kopēšana rada.
- Repliku aizture. Tiešs
ALTERizpildās katrā replikā pēc tam, kad tas pabeigts avotā. Mūsu pieredzē aizture ir aptuveni tikpat ilga kā patsALTER, tāpēc divu stundu konvertēšana nozīmē replikas divas stundas iepakaļ. Tiešsaistes rīki kopē pa daļām un var bremzēt aiztures dēļ. - Rindas izmērs. 65 535 baitu rindas limits skaita deklarētos baitus (rindas izmēra limiti). Plata tabula ar vairākām garām
VARCHARkolonnām var ietilptutf8mb3un neizdoties ar kļūdu 1118utf8mb4; vienaNOT NULLutf8mb4VARCHARkolonna sasniedz griestus ap 16 383 rakstzīmēm. - Salīdzināšanas kārtība maina uzvedību, ne tikai glabāšanu.
utf8mb4_0900_*kārtības irNO PAD, tāpēc'a'un'a 'ir dažādas virknes, bet vecākāsutf8mb4_general_ciunutf8mb4_unicode_ciirPAD SPACE(Unicode salīdzināšanas kārtības). Dažādas kārtības arī atšķirīgi apstrādā dažas diakritiskās un speciālās rakstzīmes, tāpēc unikālā atslēga var noraidīt rindas, kas agrāk bija atšķirīgas. Pārbaudiet pirms konvertēšanas:-- Would a unique key collide under the new collation? SELECT CONVERT(email USING utf8mb4) COLLATE utf8mb4_0900_ai_ci AS k, COUNT(*) FROM users GROUP BY k HAVING COUNT(*) > 1; - Jauktas rakstzīmju kopas migrācijas laikā. Kamēr dažas tabulas ir konvertētas un citas nav, savienojumi (join) starp tām salīdzina dažādas rakstzīmju kopas. Mūsu pieredzē tas var neļaut optimizētājam izmantot indeksu savienojuma kolonnā, un daži vaicājumi neizdodas ar kļūdu 1267 "Illegal mix of collations". Konvertējiet savstarpēji savienotās tabulas vienā logā.
- Replikām jābūt saskaņā. Rokasgrāmata brīdina, ka atšķirīgas definīcijas avotā un replikā noved pie negaidītiem rezultātiem un ka katrai replikai jāsaprot jaunā rakstzīmju kopa. Neatstājiet replikas uz
utf8mb3, kamēr avots raksta emocijzīmes. - Vispirms izmēriet uz vienas tabulas. Konvertējiet vidēju tabulu, pierakstiet GB stundā uz jūsu aparatūras un tad plānojiet lielās tabulas pēc šī skaitļa.
Galvenās atziņas
- "Incorrect string value" ir rakstzīmju kopu neatbilstība; "Incorrect integer value" ir strict mode, kas noraida sliktu vērtību.
- Baiti, kas sākas ar
\xF0, nozīmē četru baitu rakstzīmi, parasti emocijzīmi, kas nonākutf8mb3kolonnā. - Pārbaudiet kolonnu, tad lietotnes savienojumu; abiem jābūt
utf8mb4. - Dodiet priekšroku
MODIFYkatrai kolonnai, nevisCONVERT TO, un norādiet salīdzināšanas kārtību. - Divreiz kodētiem datiem konvertējiet caur
BLOB, nevis arCONVERT TO. - Lielās tabulās izmantojiet tiešsaistes shēmas maiņas rīku un plānojiet disku, repliku aizturi, indeksa garumu, rindas izmēru un kārtības uzvedību.