Details
-
Bug
-
Status: Done
-
Medium
-
Resolution: Fixed
-
3.0.8
-
None
-
None
-
Yes
-
1
Description
Hi,
pt-online-schema-change could emit utf8 errors for binary PK:
Load t.sql.xz to test database
docker run -it --name p57 -e MYSQL_ALLOW_EMPTY_PASSWORD=1 percona:latest --character-set-server=utf8mb4 docker cp t.sql.xz p57:/root/ docker exec -it p57 bash apt-get update ; apt-get install -y percona-toolkit less mysqladmin create test; xzcat t.sql.xz |perl -pe 's/latin1/utf8mb4/g'| mysql test pt-online-schema-change --execute --charset=utf8 --chunk-size 2 --alter 'engine=innodb' D=test,t=brokenutf8alter
The output:
`test`.`brokenutf8alter` was not altered. 2018-04-07T03:50:02 Error copying rows from `test`.`brokenutf8alter` to `test`.`_brokenutf8alter_new`: 2018-04-07T03:50:02 Copying rows caused a MySQL error 1300: Level: Warning Code: 1300 Message: Invalid utf8 character string: 'F53A16' Query: INSERT LOW_PRIORITY IGNORE INTO `test`.`_brokenutf8alter_new` (`id`) SELECT `id` FROM `test`.`brokenutf8alter` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 609 copy nibble*/
Workaround specify latin1 charset for pt-o-s-c:
pt-online-schema-change --execute --charset=latin1 --chunk-size 2 --alter 'engine=innodb' D=test,t=brokenutf8alter
pt-o-s-c still processes the table correctly (utf8mb4 symbols stored in additional varchar field are valid after pt-o-s-c run). 4-byte changes also processed correctly by pt-o-s-c triggers.
Possible fix: use binary or hex literals instead of '?' substitution.