pt-online-schema-change should clear sql_mode before CREATE and ALTER TABLE

[Maciej Dobrzanski]

In environments where sql_mode was changed after database schema was deployed, some tables may no longer compatible with the current sql_mode settings. In such case any CREATE TABLE or ALTER TABLE statements fail for reasons that are beyond pt-online-schema-change's control. I think the tool should still be able to apply the requested changes regardless of any pre-existing issues, because one of the reasons to use pt-osc is to fix such issues.

pt-online-schema-change temporarily resets sql_mode when it reads a table definition, but then it restores the original setting. It does not, however, reset it again for CREATE TABLE or ALTER TABLE statements, so they may fail under the circumstances I described above.

Here's an example:

mysql> select @@global.sql_mode;
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------------------------------------+

Error creating new table: DBD::mysql::db do failed: Invalid default value for 'updated_at' [for Statement "CREATE TABLE `test`.`_test_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  ...
  `updated_at` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  `created_at` timestamp NOT NULL,
  ...
) ENGINE=InnoDB AUTO_INCREMENT=26175140 DEFAULT CHARSET=utf8mb4"] at ./pt-online-schema-change line 9899.

[Maciej Dobrzanski (mushu)]

A quick patch to fix the problem. The diff comes from a modified 2.2.14, but it should apply on a vanilla 2.2.14 too.

[Nilnandan Joshi (nilnandan-joshi)]

Confirmed as a feature request.

nilnandan@desktop:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 45
Server version: 5.6.24-72.2-log Percona Server (GPL), Release 72.2, Revision 8d0f85b

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@global.sql_mode;
+------------------------+
| @@global.sql_mode |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

mysql>
mysql> CREATE TABLE nil_test (id int, name varchar(10), bdata timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.01 sec)

mysql> SET GLOBAL sql_mode = "STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected, 3 warnings (0.00 sec)

mysql> select @@global.sql_mode;
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table nil_test \G
*************************** 1. row ***************************
       Table: nil_test
Create Table: CREATE TABLE `nil_test` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  `bdata` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> ALTER TABLE nil_test ADD COLUMN phone int;
ERROR 1067 (42000): Invalid default value for 'bdata'
mysql>
mysql> quit
Bye
nilnandan@desktop:~$ pt-online-schema-change --alter "ADD COLUMN c1 INT" D=test,t=nil_test --execute
No slaves found. See --recursion-method if host desktop has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test`.`nil_test`...
Creating new table...
`test`.`nil_test` was not altered.
Killed
nilnandan@desktop:~$

[Shahriyar Rzayev (rzayev-sehriyar)]

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