ERROR 1366 (22007): Incorrect string value
I am trying to migrate a database from MySQL 5.0 to MariaDB 10.3 and I am getting the follow fatal error.
ERROR 1366 (22007) at line 20: Incorrect string value: '\xC2 > at...' for column `rt34`.`Attachments`.`Headers` at row 42626
The character set on the old MySQL database is Latin1 but I need to change the character set to UTF-8 on MariaDB. Here is how I have tried this upgrade and migration.
On the old MySQL database, dump the database with the follow options and compress it.
old$ mysqldump -u root -p --opt --quote-names --skip-set-charset --default-character-set=latin1 rt34 > /tmp/just-rt34.sql
old$ gzip -9 /tmp/just-rt34.sql
On the new MariaDB, restore the database.
new$ zcat just-rt34.sql.gz | mysql -h 127.0.0.1 -u root -p
Convert the rt34.Attachments table to convert from Latin1 to UTF-8
MariaDB [rt34]> alter table Attachments change Headers Headers LONGTEXT CHARACTER SET latin1;
Query OK, 832329 rows affected (6 min 40.358 sec) Records: 832329 Duplicates: 0 Warnings: 0
MariaDB [rt34]> alter table Attachments change Headers Headers LONGBLOB;
Query OK, 832329 rows affected (6 min 47.586 sec) Records: 832329 Duplicates: 0 Warnings: 0
MariaDB [rt34]> alter table Attachments change Headers Headers LONGTEXT CHARACTER SET utf8;
ERROR 1366 (22007): Incorrect string value: '\xCD\xF8\xC2\xE7\xBF\xC9...' for column `rt34`.`Attachments`.`Headers` at row 1229
Another (more detailed) attempt to convert rt34.Attachments (sql below)
MariaDB [rt34]> ALTER TABLE Attachments DEFAULT CHARACTER SET utf8, MODIFY MessageId VARBINARY(160) NULL DEFAULT NULL, MODIFY Subject VARBINARY(255) NULL DEFAULT NULL, MODIFY Filename VARBINARY(255) NULL DEFAULT NULL, MODIFY ContentType VARBINARY(80) NULL DEFAULT NULL, MODIFY ContentEncoding VARBINARY(80) NULL DEFAULT NULL, MODIFY Content LONGBLOB NULL DEFAULT NULL, MODIFY Headers LONGBLOB NULL DEFAULT NULL;
Query OK, 832329 rows affected (6 min 52.094 sec) Records: 832329 Duplicates: 0 Warnings: 0
MariaDB [rt34]> ALTER TABLE Attachments MODIFY MessageId VARCHAR(160) CHARACTER SET ascii NULL DEFAULT NULL, MODIFY Subject VARCHAR(255) CHARACTER SET utf8 NULL DEFAULT NULL, MODIFY Filename VARCHAR(255) CHARACTER SET utf8 NULL DEFAULT NULL, MODIFY ContentType VARCHAR(80) CHARACTER SET ascii NULL DEFAULT NULL, MODIFY ContentEncoding VARCHAR(80) CHARACTER SET ascii NULL DEFAULT NULL, MODIFY Headers LONGTEXT CHARACTER SET utf8 NULL DEFAULT NULL;
ERROR 1366 (22007): Incorrect string value: '\xCD\xF8\xC2\xE7\xBF\xC9...' for column `rt34`.`Attachments`.`Headers` at row 1229
I originally posted this on the request-tracker forums and I was told this is a character set issue and/or database issue not a request-tracker. But I do not know how to troubleshoot the problem. Any advise would be appreciated. Thanks.
Answer Answered by Daniel Black in this comment.
What happens if you use utf8mb4 rather than utf8?
Any reason you chose MariaDB-10.3 rather than more recent 10.5/10.6 version?