pt-osc is not run with column that default value is 0000-00-00 00:00:00

[almaz]

mysql version : 5.6.25
tool version : percona-toolkit-2.2.16

- i want add column with pt-osc.

- command : ./bin/pt-online-schema-change --dry-run --statistics --nodrop-old-table --critical-load="Threads_running=500" --nocheck-replication-filters --charset=utf8mb4 --alter-foreign-keys-method=auto --set-vars SQL_MODE=\'\' --alter "ADD COLUMN PT_TEMP varchar(50) DEFAULT NULL" D=idb,t=err_msg

But error is occur like that :
Error creating new table: DBD::mysql::db do failed: Invalid default value for 'CREATE_DT' [for Statement "CREATE TABLE `idb`.`_err_msg_new` (
  `ERR_CD` varchar(5) COLLATE utf8mb4_bin NOT NULL,
  `ERR_MSG` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
  `USE_FG` varchar(1) COLLATE utf8mb4_bin DEFAULT 'N',
  `START_DT` timestamp NULL DEFAULT NULL,
  `END_DT` timestamp NULL DEFAULT NULL,
  `CREATE_DT` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `MODIFY_DT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ERR_CD`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8"] at ./bin/pt-online-schema-change line 10035.

- the default value of 'CREATE_DT' is '0000-00-00 00:00:00'. is it problem for using pt-osc?
 please check and answer.

thanks

[Frank Cizmich (frank-cizmich)]

Hi almaz,

It's a problem with MySQL sql_mode variable.

Check the modes like this:

show variables like 'sql_mode';

You probably have a combination of STRICT_ALL_TABLES, NO_ZERO_DATE,NO_ZERO_IN_DATE or something of the sort.

You have to either remove those modes (set global sql_mode = ... ) , or use a valid date for timestamp.

See: http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html

[Frank Cizmich (frank-cizmich)]

Almaz,

I'm setting this issue as "incomplete" as a reminder. Any feedback is appreciated since those sql_mode's are tricky.

Regards.

[almaz (miel313)]

Hi, Frank

thank you for your answer.

but
i add the option like that "--set-vars SQL_MODE=\'\' "

why the option is not working??

[almaz (miel313)]

+
as you mention that
i checked the global sql_mode. that includes NO_ZERO_DATE.

[almaz (miel313)]

Hi,

could i change the SQL_MODE with using the --set-vars option?

[Launchpad Janitor (janitor)]

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

[Shahriyar Rzayev (rzayev-sehriyar)]

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