Error 1292: Incorrect datetime value: '0000-00-00' for column 'date_insert'

[babinomec]

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)

[shlomi-noach]

If I'm reading this correctly, you have NO_ZERO_IN_DATE,NO_ZERO_DATE, in your sql_mode ; you also happen to have zero-dates in your data -- presumably from a time when your sql_mode did not have NO_ZERO_IN_DATE,NO_ZERO_DATE,

So gh-ost attempts to copy your zero dates data onto the ghost table, and is unable to, because sql_mode forbids it.

Either convert all your existing zero dates to some non-zero dates, or temporarily remove NO_ZERO_IN_DATE,NO_ZERO_DATE, from your sql_mode.
Both operations could be super risky in production and I'd advise you to give this considerable thought.

[babinomec]

Either convert all your existing zero dates to some non-zero dates, or temporarily remove NO_ZERO_IN_DATE,NO_ZERO_DATE, from your sql_mode.
Both operations could be super risky in production and I'd advise you to give this considerable thought.

Yep definitely not going to do that :)

I know it's super counter intuitive (I had to read the documentation a few times), but bear with me for a second.

The doc https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date says the following :

The NO_ZERO_DATE mode affects whether the server permits '0000-00-00' as a valid date. Its effect also depends on whether strict SQL mode is enabled.

• If this mode is not enabled, '0000-00-00' is permitted and inserts produce no warning.
• If this mode is enabled, '0000-00-00' is permitted and inserts produce a warning.
• If this mode and strict mode are enabled, '0000-00-00' is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning.

We are in the second case here (NO_ZERO_DATE enabled but no strict mode) so it should be permitted but produce a warning.

It's indeed what happens :
(please disregard the absurdly high number of warnings due to fields not having default values)

MySQL [<database>]> insert into <table> set email='notanemail', date_insert='0000-00-00';
Query OK, 1 row affected, 230 warnings (0.01 sec)

MySQL [<database>]> show warnings;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1264 | Out of range value for column 'date_insert' at row 1    |
| Warning | 1364 | Field 'is_blacklisted' doesn't have a default value     |
(...)
+---------+------+---------------------------------------------------------+
64 rows in set (0.00 sec)

MySQL [<database>]> select id, email, date_insert from <table> where email='notanemail';
+--------+------------+-------------+
| id     | email      | date_insert |
+--------+------------+-------------+
| 845449 | notanemail | 0000-00-00  |
+--------+------------+-------------+
1 row in set (0.01 sec)

MySQL [<database>]> update <table> set date_insert='0000-00-00' where id=845449;
Query OK, 0 rows affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 1

MySQL [<database>]> show warnings;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1264 | Out of range value for column 'date_insert' at row 1 |
+---------+------+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL [<database>]> select id, email, date_insert from <table> where email='notanemail';
+--------+------------+-------------+
| id     | email      | date_insert |
+--------+------------+-------------+
| 845449 | notanemail | 0000-00-00  |
+--------+------------+-------------+
1 row in set (0.11 sec)

Also on the ghost table :

MySQL [<database>]> insert into _<table>_gho set email='notanemail', date_insert='0000-00-00';
Query OK, 1 row affected, 230 warnings (0.03 sec)

MySQL [<database>]> select id, email, date_insert from _<table>_gho where email='notanemail';
+--------+------------+---------------------+
| id     | email      | date_insert         |
+--------+------------+---------------------+
| 671001 | notanemail | 0000-00-00 00:00:00 |
+--------+------------+---------------------+
1 row in set (0.00 sec)

MySQL [<database>]> update _<table>_gho set date_insert='0000-00-00' where email='notanemail';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1

MySQL [<database>]> select id, email, date_insert from _<table>_gho where email='notanemail';
+--------+------------+---------------------+
| id     | email      | date_insert         |
+--------+------------+---------------------+
| 671001 | notanemail | 0000-00-00 00:00:00 |
+--------+------------+---------------------+
1 row in set (0.01 sec)

So even if the sql_mode says NO_ZERO stuff, as strict mode is not enabled, it only produces a warning and still works.

Basically what happens here :

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]

Would not happen if run "manually" on the server (you'd have a warning though)

[shlomi-noach]

Right. And so gh-ost also adds STRICT_ALL_TABLES for safety measure, which has proved to do the right thing in multiple cases I'm familiar with and which saved the day. But in your case it prevents you from copying the zero-date because it's an out-of-bounds value.

I'm not so happy to support non-strict mode. How happy are you with cloning the repo, editing this line:

gh-ost/go/logic/applier.go

Line 487 in 4ff5d6a

sql_mode = CONCAT(@@session.sql_mode, ',STRICT_ALL_TABLES')

and building your personal STRICT-free version?

[babinomec]

I'm quite happy with it if it solves the problem :) I'll give it a try and let you know how it turns out.

If it does indeed work maybe we could have a "--skip-strict-mode" option ?

[shlomi-noach]

hence my:

I'm not so happy to support non-strict mode.

But please let me know how it went.

[babinomec]

It worked ok 👍

Finished with Copy: 845448/845448 100.0%; Applied: 32; Backlog: 0/1000; Time: 2m13s(total), 2m10s(copy); streamer: bin-log.142449:15248198; State: migrating; ETA: due

("Applied" is the numbers of events from the binlog applied to the ghost table, right ?)

I had to comment the strict mode in more than one place though, just the one you mentionned was not enough, I changed it in two other places after that in the same script (maybe only once in the "correct" place would have been enough, I didn't test that)

[dmbsites]

só colocar is null na query:

exemplo:

select * from USA_TABELA where data_expirar = '0000-00-00 00:00:00' OR data_expirar is null