Comment 6 for bug 1705998

Revision history for this message
Jericho Rivera (jericho-rivera) wrote :

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