I got it now, and I can confirm the bug. If column is all lowercase the alter will not fail.
percona:~/sandboxes/msb_5_7_18$ ./pt-online-schema-change --alter 'change column `Last_referenced` `cl1` datetime not null' --ask-pass --no-swap-tables --no-drop-triggers --no-drop-new-table --no-check-alter --execute D=test,t=t1,h=127.0.0.1,u=root,P=5718,S=/tmp/mysql_sandbox5718.sock
Enter MySQL password:
No slaves found. See --recursion-method if host percona has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `test`.`t1`...
Renaming columns:
Last_referenced to cl1
Creating new table...
Created new table test._t1_new OK.
Altering new table...
Altered `test`.`_t1_new` OK.
2017-08-03T14:49:09 Creating triggers...
2017-08-03T14:49:09 Created triggers OK.
2017-08-03T14:49:09 Copying approximately 100 rows...
Not dropping triggers because --no-drop-triggers was specified. To drop the triggers, execute:
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_t1_del`;
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_t1_upd`;
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_t1_ins`;
Not dropping the new table `test`.`_t1_new` because --no-drop-new-table was specified. To drop the new table, execute:
DROP TABLE IF EXISTS `test`.`_t1_new`;
`test`.`t1` was not altered.
2017-08-03T14:49:09 Error copying rows from `test`.`t1` to `test`.`_t1_new`: 2017-08-03T14:49:09 Copying rows caused a MySQL error 1364:
Level: Warning
Code: 1364
Message: Field 'cl1' doesn't have a default value
Query: INSERT LOW_PRIORITY IGNORE INTO `test`.`_t1_new` (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`, `c10`) SELECT `c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`, `c10` FROM `test`.`t1` LOCK IN SHARE MODE /*pt-online-schema-change 6462 copy table*/
percona:~/sandboxes/msb_5_7_18$ ./use test -e "show create table t1\G"
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` int(10) unsigned NOT NULL,
`c2` varchar(255) NOT NULL,
`Last_referenced` datetime NOT NULL,
`c3` int(10) unsigned NOT NULL,
`c4` int(10) unsigned NOT NULL DEFAULT '0',
`c5` varchar(255) NOT NULL DEFAULT '',
`c6` varchar(255) NOT NULL DEFAULT '',
`c7` varchar(255) NOT NULL DEFAULT '',
`c8` varchar(255) DEFAULT '',
`c9` varchar(255) DEFAULT '',
`c10` int(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`c1`,`c2`),
KEY `Last_Referenced_c6_Index` (`Last_referenced`,`c6`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
percona:~/sandboxes/msb_5_7_18$ ./use test -e "show create table _t1_new\G"
*************************** 1. row ***************************
Table: _t1_new
Create Table: CREATE TABLE `_t1_new` (
`c1` int(10) unsigned NOT NULL,
`c2` varchar(255) NOT NULL,
`cl1` datetime NOT NULL,
`c3` int(10) unsigned NOT NULL,
`c4` int(10) unsigned NOT NULL DEFAULT '0',
`c5` varchar(255) NOT NULL DEFAULT '',
`c6` varchar(255) NOT NULL DEFAULT '',
`c7` varchar(255) NOT NULL DEFAULT '',
`c8` varchar(255) DEFAULT '',
`c9` varchar(255) DEFAULT '',
`c10` int(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`c1`,`c2`),
KEY `Last_Referenced_c6_Index` (`cl1`,`c6`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
I got it now, and I can confirm the bug. If column is all lowercase the alter will not fail.
percona: ~/sandboxes/ msb_5_7_ 18$ ./pt-online- schema- change --alter 'change column `Last_referenced` `cl1` datetime not null' --ask-pass --no-swap-tables --no-drop-triggers --no-drop-new-table --no-check-alter --execute D=test, t=t1,h= 127.0.0. 1,u=root, P=5718, S=/tmp/ mysql_sandbox57 18.sock foreign_ keys, 10, 1 `pt_osc_ test_t1_ del`; `pt_osc_ test_t1_ upd`; `pt_osc_ test_t1_ ins`; schema- change 6462 copy table*/
Enter MySQL password:
No slaves found. See --recursion-method if host percona has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_
Altering `test`.`t1`...
Renaming columns:
Last_referenced to cl1
Creating new table...
Created new table test._t1_new OK.
Altering new table...
Altered `test`.`_t1_new` OK.
2017-08-03T14:49:09 Creating triggers...
2017-08-03T14:49:09 Created triggers OK.
2017-08-03T14:49:09 Copying approximately 100 rows...
Not dropping triggers because --no-drop-triggers was specified. To drop the triggers, execute:
DROP TRIGGER IF EXISTS `test`.
DROP TRIGGER IF EXISTS `test`.
DROP TRIGGER IF EXISTS `test`.
Not dropping the new table `test`.`_t1_new` because --no-drop-new-table was specified. To drop the new table, execute:
DROP TABLE IF EXISTS `test`.`_t1_new`;
`test`.`t1` was not altered.
2017-08-03T14:49:09 Error copying rows from `test`.`t1` to `test`.`_t1_new`: 2017-08-03T14:49:09 Copying rows caused a MySQL error 1364:
Level: Warning
Code: 1364
Message: Field 'cl1' doesn't have a default value
Query: INSERT LOW_PRIORITY IGNORE INTO `test`.`_t1_new` (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`, `c10`) SELECT `c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`, `c10` FROM `test`.`t1` LOCK IN SHARE MODE /*pt-online-
percona: ~/sandboxes/ msb_5_7_ 18$ ./use test -e "show create table t1\G" ******* ******* ****** 1. row ******* ******* ******* ****** d_c6_Index` (`Last_ referenced` ,`c6`)
*******
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` int(10) unsigned NOT NULL,
`c2` varchar(255) NOT NULL,
`Last_referenced` datetime NOT NULL,
`c3` int(10) unsigned NOT NULL,
`c4` int(10) unsigned NOT NULL DEFAULT '0',
`c5` varchar(255) NOT NULL DEFAULT '',
`c6` varchar(255) NOT NULL DEFAULT '',
`c7` varchar(255) NOT NULL DEFAULT '',
`c8` varchar(255) DEFAULT '',
`c9` varchar(255) DEFAULT '',
`c10` int(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`c1`,`c2`),
KEY `Last_Reference
) ENGINE=InnoDB DEFAULT CHARSET=latin1
percona: ~/sandboxes/ msb_5_7_ 18$ ./use test -e "show create table _t1_new\G" ******* ******* ****** 1. row ******* ******* ******* ****** d_c6_Index` (`cl1`,`c6`)
*******
Table: _t1_new
Create Table: CREATE TABLE `_t1_new` (
`c1` int(10) unsigned NOT NULL,
`c2` varchar(255) NOT NULL,
`cl1` datetime NOT NULL,
`c3` int(10) unsigned NOT NULL,
`c4` int(10) unsigned NOT NULL DEFAULT '0',
`c5` varchar(255) NOT NULL DEFAULT '',
`c6` varchar(255) NOT NULL DEFAULT '',
`c7` varchar(255) NOT NULL DEFAULT '',
`c8` varchar(255) DEFAULT '',
`c9` varchar(255) DEFAULT '',
`c10` int(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`c1`,`c2`),
KEY `Last_Reference
) ENGINE=InnoDB DEFAULT CHARSET=latin1