Uploaded image for project: 'Percona Toolkit'
  1. Percona Toolkit
  2. PT-1528

pt-online-schema-change "Invalid utf8mb4 character string: 'A111E8'"

Details

    • Bug
    • Status: Done
    • Medium
    • Resolution: Fixed
    • 3.0.8
    • 3.3.0
    • 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.

      Attachments

        Activity

          People

            Unassigned Unassigned
            nickolay.ihalainen Nickolay Ihalainen
            Votes:
            1 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Smart Checklist