pt-online-schema-change 2.2.14 can not create new table with --set-vars SQL_MODE option

[almaz]

I'm using pt-online-schema-change 2.2.14 for altering a table on mysql 5.6 on ubuntu 14.04.

i want alter some table but pt-online-schema-change not proceed any more.

pt-online-schema-change is not create the new table
i can't not find at out put "Created new table" after "Creating new table..."
and process killed after a while.

the table's SQL_MODE is ''(NULL) so i add the --set-vars option at command.
(System SQL_MODE is not ''(NULL), @@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)

COMMAMD :
$ PTDEBUG=1 ./pt-online-schema-change --execute --no-drop-old-table --nocheck-replication-filters --charset=utf8mb4 --set-vars SQL_MODE=\'\' --alter "ADD COLUMN test2015 VARCHAR(1) COLLATE utf8mb4_bin DEFAULT NULL" D=idb,t=iseller 2>&1

at output, i find some evidence related with SQL_MODE
i wonder why set @@SQL_MODE := @OLD_SQL_MODE (line number 631) prior to create new table.
i think that using @@SQL_MODE('';) when create a new table is correct.

------------------------------------------------------------------------------------------------------------
621 Altering `idb`.`iseller`...
622 # CleanupTask:6170 94022 Created cleanup task CODE(0x3476198)
623 # pt_online_schema_change:9739 94022 Renamed columns (old => new): $VAR1 = {};
624 #
625 # Percona::XtraDB::Cluster:7621 94022 ip-10-41-232-151 SHOW VARIABLES LIKE 'wsrep_on'
626 # Percona::XtraDB::Cluster:7623 94022 $VAR1 = undef;
627 #
628 # TableParser:3119 94022 /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := 621 @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
629 # TableParser:3124 94022 DBI::db=HASH(0x3415a10) USE `idb`
630 # TableParser:3128 94022 SHOW CREATE TABLE `idb`.`iseller`
631 # TableParser:3138 94022 /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
632 Creating new table...
633 # pt_online_schema_change:9868 94022 CREATE TABLE `idb`.`_iseller_new` (
634 # `USER_ID` varchar(10) COLLATE utf8mb4_bin NOT NULL,
635 # `PNUM_TXT` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
636 # PRIMARY KEY (`USER_ID`)
637 # ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
638 # CleanupTask:6178 94022 Calling cleanup task CODE(0x3476198)
639 # pt_online_schema_change:8589 94022 Clean up done, report if orig table was altered
640 `idb`.`iseller` was not altered.
------------------------------------------------------------------------------------------------------------
no more output.

pt-online-schema-change not proceed any more.
and process killed after a while.

[Nilnandan Joshi (nilnandan-joshi)]

Enable to reproduce this case with 2.2.15. Can you try with new version? Also can you provide the table structure where you want to add columns? Valid test case will be better to understand this bug.

root@desktop:~# pt-online-schema-change --execute --no-drop-old-table --nocheck-replication-filters --charset=utf8mb4 --set-vars SQL_MODE=\'\' --alter "ADD COLUMN test2015 VARCHAR(1) COLLATE utf8mb4_bin DEFAULT NULL" D=test,t=nil 2>&1
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`...
Creating new table...
Created new table test._nil_new OK.
Altering new table...
Altered `test`.`_nil_new` OK.
2015-10-20T11:55:33 Creating triggers...
2015-10-20T11:55:33 Created triggers OK.
2015-10-20T11:55:33 Copying approximately 2 rows...
2015-10-20T11:55:33 Copied rows OK.
2015-10-20T11:55:33 Swapping tables...
2015-10-20T11:55:33 Swapped original and new tables OK.
Not dropping old table because --no-drop-old-table was specified.
2015-10-20T11:55:33 Dropping triggers...
2015-10-20T11:55:33 Dropped triggers OK.
Successfully altered `test`.`nil`.
root@desktop:~#

[almaz (miel313)]

at 2.2.15 version : i can't alter the table. the pt-online-shcema change is ended with error.
at 2.2.14 version : the pt-online-shcema change is not ended normally. the process is killed after a while.

you can find table structure below.
i want add the new column at the end.
as i mentioned,
global sql mode is not null. (@@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)
but this DB's sql mode is null.

schema :
+------------------+--------------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------------------+-----------------------------+
| USER_ID | varchar(10) | NO | PRI | NULL | |
| PNUM_TXT | varchar(50) | YES | | NULL | |
| COUNTRY_ID | varchar(3) | YES | | NULL | |
| MEDAL_TYPE | varchar(10) | YES | | NULL | |
| BIRTH_DATE | date | YES | | NULL | |
| SETTLEMENT_RATE | double | YES | | NULL | |
| LAST_LOGIN_TIME | timestamp | YES | | NULL | |
| SELLER_TYPE | varchar(9) | YES | | NULL | |
| GENDER_CODE | varchar(6) | YES | | NULL | |
| FIRST_NAME | varchar(150) | YES | | NULL | |
| LAST_NAME | varchar(150) | YES | | NULL | |
| MODIFY_DT | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| CREATE_DT | timestamp | NO | | 0000-00-00 00:00:00 | |
| ETL_LD_DT | timestamp | YES | | NULL | |
| USER_NM | varchar(100) | YES | | NULL | |
| CORP_REG_NUM_TXT | varchar(11) | YES | | NULL | |
+------------------+--------------+------+-----+---------------------+-----------------------------+
16 rows in set (0.00 sec)

[Launchpad Janitor (janitor)]

[Expired for Percona Toolkit because there has been no activity for 60 days.]

[almaz (miel313)]

is this issue expired??
please check this issue.
i can't alter the table with --set-vars SQL_MODE option at 2.2.14 and 2.2.15.

[Frank Cizmich (frank-cizmich)]

Hi almaz,

I can't seem to reproduce this.

What is the error you get when running with 2.2.15 ? Is the PTDEBUG output in the report from 2.2.15?

Can you provide MySQL version and OS ?

[almaz (miel313)]

Hi, frank. Thank you your comment.

MySQL : 5.6.25
OS : ubuntu 14.04.

( i add the option --set-vars SQL_MODE=\'\' at command because global sql mode is not null)
Command :
PTDEBUG=1 ./pt-online-schema-change --execute --no-drop-old-table --nocheck-replication-filters --charset=utf8mb4 --set-vars SQL_MODE=\'\' --alter "ADD COLUMN test2015 VARCHAR(1) COLLATE utf8mb4_bin DEFAULT NULL" D=idb,t=iseller 2>&1

Error at PTDEBUG output with 2.2.15 version :
Error creating new table: DBD::mysql::db do failed: Invalid default value for 'CREATE_DT' [for Statement "CREATE TABLE `idb`.`_iseller_new` (
  `USER_ID` varchar(10) COLLATE utf8mb4_bin NOT NULL,
  `PNUM_TXT` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
  `COUNTRY_ID` varchar(3) COLLATE utf8mb4_bin DEFAULT NULL,
  `MEDAL_TYPE` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
  `BIRTH_DATE` date DEFAULT NULL,
  `SETTLEMENT_RATE` double DEFAULT NULL,
  `LAST_LOGIN_TIME` timestamp NULL DEFAULT NULL,
  `SELLER_TYPE` varchar(9) COLLATE utf8mb4_bin DEFAULT NULL,
  `GENDER_CODE` varchar(6) COLLATE utf8mb4_bin DEFAULT NULL,
  `FIRST_NAME` varchar(150) COLLATE utf8mb4_bin DEFAULT NULL,
  `LAST_NAME` varchar(150) COLLATE utf8mb4_bin DEFAULT NULL,
  `MODIFY_DT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `CREATE_DT` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `ETL_LD_DT` timestamp NULL DEFAULT NULL,
  `USER_NM` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
  `CORP_REG_NUM_TXT` varchar(11) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`USER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8"] at ./pt-online-schema-change line 9893.

[Frank Cizmich (frank-cizmich)]

Yup. It's a bug. :-)

sql_mode gets clobbered (with original values) after being set. Subtle. Will fix soon.

Thanks almaz!

[Frank Cizmich (frank-cizmich)]

Notice that if you wish to set more than one mode, you will need to double escape the commas.

Random example: --set-vars sql_mode=\'STRICT_ALL_TABLES\\,ALLOW_INVALID_DATES\'

[Frank Cizmich (frank-cizmich)]

This will be in the docs.

[almaz (miel313)]

thanks :)

[Shahriyar Rzayev (rzayev-sehriyar)]

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