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

[DC]

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*/

[DC (darren.cassar)]

identified as related to https://bugs.mysql.com/bug.php?id=79317 and a solution is to set --charset=binary. If you think you would rather make pt-osc automatically switch to that charset for a binary pkey that'd be great but if not and you think the user should set charset to binary then feel free to close.

[Sveta Smirnova (svetasmirnova)]

Thank you for the report and analysis. I believe such options, limited by server, should not be set by pt-online-schema-change, thus closing report as "Invalid".

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-1388