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:~$
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; ------- ------- ----+ ------- ------- ----+ SUBSTITUTION | ------- ------- ----+
+------
| @@global.sql_mode |
+------
| NO_ENGINE_
+------
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; ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ----+ ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ----+ 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 | ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ----+
+------
| @@global.sql_mode |
+------
| STRICT_
+------
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; desktop: ~$ pt-online- schema- change --alter "ADD COLUMN c1 INT" D=test,t=nil_test --execute foreign_ keys, 10, 1 `nil_test` ... desktop: ~$
ERROR 1067 (42000): Invalid default value for 'bdata'
mysql>
mysql> quit
Bye
nilnandan@
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_
Altering `test`.
Creating new table...
`test`.`nil_test` was not altered.
Killed
nilnandan@