Error 1292: Incorrect datetime value: '0000-00-00' for column 'date_insert' #711
Description
I'm trying to alter a column from "DATE" into "DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP" and I'm getting the following error : Error 1292: Incorrect datetime value: '0000-00-00' for column 'date_insert'
It seems that the copy from the old table goes well (there are lines in the new table) but maybe it does not work as well when processing updates from the binlogs.
ghost command :
GHOST \
--user="ghost" \
--password="<password>" \
--host="localhost" \
--allow-master-master \
--assume-master-host="<master>" \
--database="<database>" \
--table="<table>" \
--alter="MODIFY date_insert DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP" \
--verbose \
--switch-to-rbr \
--skip-foreign-key-checks \
--initially-drop-ghost-table \
--initially-drop-old-table \
--exact-rowcount \
--execute
gh-ost version is e48844d (a few commits ahead of latest release)
Servers are percona 5.7.19 (master) and 5.7.22 (slave) ; sql_mode on both servers is :
MySQL> show variables like 'sql_mode';
+---------------+----------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+----------------------------------------------------------------------------------------------------+
| sql_mode | NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Create for original table :
CREATE TABLE `mensup` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`email` varchar(120) NOT NULL,
`is_blacklisted` tinyint(3) unsigned NOT NULL,
`date` datetime NOT NULL,
`date_insert` date NOT NULL,
(other columns here)
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`),
(other keys here)
) ENGINE=InnoDB AUTO_INCREMENT=844009 DEFAULT CHARSET=utf8
Some data from both the original table and ghost tables :
(In this case all the date_insert values are '0000-00-00' but it may be set in other tables that we will migrate later)
MySQL [<database>]> select count(*), sum(date_insert='0000-00-00') from <table>;
+----------+-------------------------------+
| count(*) | sum(date_insert='0000-00-00') |
+----------+-------------------------------+
| 844008 | 844008 |
+----------+-------------------------------+
1 row in set, 1 warning (0.38 sec)
MySQL [<database>]> select count(*), sum(date_insert='0000-00-00 00:00:00') from _<table>_gho;
+----------+----------------------------------------+
| count(*) | sum(date_insert='0000-00-00 00:00:00') |
+----------+----------------------------------------+
| 671000 | 671000 |
+----------+----------------------------------------+
1 row in set, 1 warning (0.42 sec)
MySQL [<database>]> select * from _<table>_ghc order by id desc limit 1\G
*************************** 1. row ***************************
id: 410
last_update: 2019-02-13 17:52:14
hint: copy iteration 671 at 1550076734
value: Copy: 671000/844008 79.5%; Applied: 2; Backlog: 2/1000; Time: 2m42s(total), 2m41s(copy); streamer: bin-log.142063:258888295; State: migrating; ETA: 41s
1 row in set (0.01 sec)
Here is the log (I anonymized it a little bit):
2019-02-13 17:49:31 INFO starting gh-ost e48844de0bee9a8db611a06cd6080cac4dab25cb
2019-02-13 17:49:31 INFO Migrating `<database>`.`<table>`
2019-02-13 17:49:31 INFO connection validated on localhost:3306
2019-02-13 17:49:31 INFO User has SUPER, REPLICATION SLAVE privileges, and has ALL privileges on `<database>`.*
2019-02-13 17:49:31 INFO binary logs validated on localhost:3306
2019-02-13 17:49:31 INFO Restarting replication on localhost:3306 to make sure binlog settings apply to replication thread
2019-02-13 17:49:32 INFO Inspector initiated on <slave>:3306, version 5.7.22-22-log
2019-02-13 17:49:32 INFO Table found. Engine=InnoDB
2019-02-13 17:49:32 WARNING --skip-foreign-key-checks provided: will not check for foreign keys
2019-02-13 17:49:32 INFO Estimated number of rows via EXPLAIN: 844330
2019-02-13 17:49:32 INFO Master forced to be <master>:3306
2019-02-13 17:49:32 INFO log_slave_updates validated on localhost:3306
2019-02-13 17:49:32 INFO connection validated on localhost:3306
2019-02-13 17:49:32 INFO Connecting binlog streamer at bin-log.142062:78229422
2019-02-13 17:49:32 INFO rotate to next log from bin-log.142062:0 to bin-log.142062
2019-02-13 17:49:32 INFO connection validated on <master>:3306
2019-02-13 17:49:32 INFO connection validated on <master>:3306
2019-02-13 17:49:32 INFO will use time_zone='SYSTEM' on applier
2019-02-13 17:49:32 INFO Examining table structure on applier
2019-02-13 17:49:32 INFO Applier initiated on <master>:3306, version 5.7.19-17-log
2019-02-13 17:49:32 INFO Dropping table `<database>`.`_<table>_gho`
2019-02-13 17:49:32 INFO Table dropped
2019-02-13 17:49:32 INFO Dropping table `<database>`.`_<table>_del`
2019-02-13 17:49:32 INFO Table dropped
2019-02-13 17:49:32 INFO Dropping table `<database>`.`_<table>_ghc`
2019-02-13 17:49:32 INFO Table dropped
2019-02-13 17:49:32 INFO Creating changelog table `<database>`.`_<table>_ghc`
2019-02-13 17:49:32 INFO Changelog table created
2019-02-13 17:49:32 INFO Creating ghost table `<database>`.`_<table>_gho`
2019-02-13 17:49:32 INFO Ghost table created
2019-02-13 17:49:32 INFO Altering ghost table `<database>`.`_<table>_gho`
2019-02-13 17:49:32 INFO Ghost table altered
2019-02-13 17:49:32 INFO Waiting for ghost table to be migrated. Current lag is 0s
2019-02-13 17:49:33 INFO Intercepted changelog state GhostTableMigrated
2019-02-13 17:49:33 INFO Handled changelog state GhostTableMigrated
2019-02-13 17:49:33 INFO Chosen shared unique key is PRIMARY
2019-02-13 17:49:33 INFO Shared columns are id,email,is_blacklisted,date,date_insert,(other columns...)
2019-02-13 17:49:33 INFO Listening on unix socket file: /tmp/gh-ost.<database>.<table>.sock
2019-02-13 17:49:33 INFO As instructed, counting rows in the background; meanwhile I will use an estimated count, and will update it later on
2019-02-13 17:49:33 INFO As instructed, I'm issuing a SELECT COUNT(*) on the table. This may take a while
2019-02-13 17:49:33 INFO Migration min values: [1]
2019-02-13 17:49:33 INFO Migration max values: [844008]
2019-02-13 17:49:33 INFO Waiting for first throttle metrics to be collected
2019-02-13 17:49:33 INFO First throttle metrics collected
2019-02-13 17:49:33 INFO Exact number of rows via COUNT: 844008
2019-02-13 17:51:08 INFO rotate to next log from bin-log.142063:1073937075 to bin-log.142063
2019-02-13 17:51:08 INFO rotate to next log from bin-log.142063:0 to bin-log.142063
2019-02-13 17:51:14 ERROR Error 1292: Incorrect datetime value: '0000-00-00' for column 'date_insert' at row 1; query=
update /* gh-ost `<database>`.`_<table>_gho` */
`<database>`.`_<table>_gho`
set
`id`=?, `email`=?, `is_blacklisted`=?, `date`=?, `date_insert`=?, (other columns...)
where
((`id` = ?))
; args=[137149 <email address> 0 2019-01-25 22:11:50 0000-00-00 (other columns...) 137149]
(the error repeats a few times and exits eventually)