| 2016-10-18 21:37:08 |
DC |
description |
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).
$ 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*/ |
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*/ |
|
