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

LP #1634649: pt-online-schema-change errors out on a binary primary key utf8 starting with 5.5.46.5 and later versions (even 5.6)

    XMLWordPrintable

    Details

      Description

      **Reported in Launchpad by DC last update 12-12-2016 19:17:04

      A regular alter on the table works fine and I even tried copying and pasting the queries generated by pt-online-schema-change logged via general log on percona server 5.5.43 (as long as server version was < 5.5.46.5 pt-online-schema-change below worked). Upon adding an auto inc unsigned int as pkey instead of the binary column the pt-osc command worked on all versions.

      $ pt-online-schema-change --version
      pt-online-schema-change 2.2.19

      show global variables like 'char%';
      ------------------------------------------------------------------------------------+

      Variable_name Value

      ------------------------------------------------------------------------------------+

      character_set_client utf8
      character_set_connection utf8
      character_set_database utf8
      character_set_filesystem binary
      character_set_results utf8
      character_set_server utf8
      character_set_system utf8
      character_sets_dir /home/dcassar/repos/sandbox/archive/5.5.43/share/charsets/

      ------------------------------------------------------------------------------------+

      Table:

      CREATE TABLE `TABLE_NAME` (
      `id` binary(16) NOT NULL,
      `refCount` int(11) NOT NULL DEFAULT '0',
      `createdDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `createdBy` char(20) DEFAULT NULL,
      `comment` varchar(1024) DEFAULT NULL,
      `title` varchar(128) DEFAULT NULL,
      `tbSupported` tinyint(1) NOT NULL DEFAULT '1',
      `creationStatus` enum('OPEN','FROZEN') NOT NULL DEFAULT 'FROZEN',
      `cd` tinyint(1) NOT NULL DEFAULT '0',
      `ad` tinyint(1) NOT NULL DEFAULT '0',
      `url` varchar(1024) DEFAULT NULL,
      `supportedHt` int(11) DEFAULT '0',
      `verificationStatus` enum('NEW','UNVERIFIED','VERIFIED') NOT NULL DEFAULT 'NEW',
      `usmmi` char(20) DEFAULT NULL,
      `nextColumnIndex` int(11) NOT NULL DEFAULT '1',
      `nextuserColumnIndex` int(11) NOT NULL DEFAULT '1',
      `static` varchar(1024) DEFAULT NULL,
      `statictwo` varchar(1024) DEFAULT NULL,
      `website` char(255) DEFAULT NULL,
      `ai` binary(16) NOT NULL,
      `verid` binary(16) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_id_ai` (`ai`),
      KEY `idx_refCount_usmmi` (`refCount`,`usmmi`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

      pt-online-schema-change D=DATABASE_NAME,t=TABLE_NAME \
      --user user \
      --password password \
      --host localhost \
      --port 3306 \
      --nocheck-replication-filters \
      --chunk-time=0.5 \
      --chunk-size-limit=0 \
      --recursion-method=none \
      --max-load=Threads_running=70 \
      --critical-load=Threads_running=500 \
      --set-vars=lock_wait_timeout=1 \
      --tries=create_triggers:15:5,drop_triggers:100:5,copy_rows:100:1,swap_tables:100:5,update_foreign_keys:100:5 \
      --alter-foreign-keys-method=drop_swap \
      --alter 'ADD COLUMN test3 VARCHAR(255) NOT NULL DEFAULT ""' \
      --charset=utf8 \
      --no-drop-old-table \
      --no-swap-tables \
      --execute

      No slaves found. See --recursion-method if host <hostname> has slaves.
      Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
      *******************************************************************
      Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
      is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
      together with SSL_ca_file|SSL_ca_path for verification.
      If you really don't want to verify the certificate and keep the
      connection open to Man-In-The-Middle attacks please set
      SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
      *******************************************************************
      at /usr/bin/pt-online-schema-change line 6819.
      *******************************************************************
      Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
      is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
      together with SSL_ca_file|SSL_ca_path for verification.
      If you really don't want to verify the certificate and keep the
      connection open to Man-In-The-Middle attacks please set
      SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
      *******************************************************************
      at /usr/bin/pt-online-schema-change line 6819.
      Operation, tries, wait:
      analyze_table, 10, 1
      copy_rows, 100, 1
      create_triggers, 15, 5
      drop_triggers, 100, 5
      swap_tables, 100, 5
      update_foreign_keys, 100, 5
      No foreign keys reference `DATABASE_NAME`.`TABLE_NAME`; ignoring --alter-foreign-keys-method.
      Altering `DATABASE_NAME`.`TABLE_NAME`...
      Creating new table...
      Created new table DATABASE_NAME._TABLE_NAME_new OK.
      Altering new table...
      Altered `DATABASE_NAME`.`_TABLE_NAME_new` OK.
      2016-10-14T22:03:11 Creating triggers...
      2016-10-14T22:03:11 Created triggers OK.
      2016-10-14T22:03:11 Copying approximately 102632 rows...
      2016-10-14T22:03:11 Dropping triggers...
      2016-10-14T22:03:11 Dropped triggers OK.
      2016-10-14T22:03:11 Dropping new table...
      2016-10-14T22:03:11 Dropped new table OK.
      `DATABASE_NAME`.`TABLE_NAME` was not altered.
      2016-10-14T22:03:11 Error copying rows from `DATABASE_NAME`.`TABLE_NAME` to `DATABASE_NAME`.`_TABLE_NAME_new`: 2016-10-14T22:03:11 Copying rows caused a MySQL error 1300:
      Level: Warning
      Code: 1300
      Message: Invalid utf8 character string: '8D5381'
      Query: INSERT LOW_PRIORITY IGNORE INTO `DATABASE_NAME`.`_TABLE_NAME_new` (`refcount`, `createddate`, `createdby`, `comment`, `title`, `tbsupported`, `creationstatus`, `cd`, `ad`, `url`, `supportedht`, `verificationstatus`, `usmmi`, `nextcolumnindex`, `nextusercolumnindex`, `static`, `statictwo`, `website`, `ai`, `id`, `verid`, `test2`) SELECT `refcount`, `createddate`, `createdby`, `comment`, `title`, `tbsupported`, `creationstatus`, `cd`, `ad`, `url`, `supportedht`, `verificationstatus`, `usmmi`, `nextcolumnindex`, `nextusercolumnindex`, `static`, `statictwo`, `website`, `ai`, `id`, `verid`, `test2` FROM `DATABASE_NAME`.`TABLE_NAME` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /pt-online-schema-change 22000 copy nibble/

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            lpjirasync lpjirasync (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Smart Checklist