MySQL Error Code 1292 – Causes and Solutions

'2013-08-25T17:00:00+00:00'

This is a valid iso-8601 datetime value, but it is not a valid MySQL datetime literal. On that point, the developer is incorrect.

The documentation explains what ALLOW_INVALID_DATES does:

Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31.

In other words, 2013-02-31 would be a permissible date if allow_invalid_dates is set. This option does not do anything when the date or datetime isn't even in a valid format for MySQL.

The +00:00 is the timezone offset from UTC. In this case, the time expressed is in UTC, so the offset is zero hours, zero minutes.

Your workaround would be to remove the STRICT_TRANS_TABLES from the sql_mode that is a default in the config file created during the MySQL 5.6 installation process... you need to carefully consider the implications of changing this, but it does allow the data to go in.

mysql> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into datetimetest(dt) values ('2013-08-26T12:00:00+00:00');
ERROR 1292 (22007): Incorrect datetime value: '2013-08-26T12:00:00+00:00' for column 'dt' at row 1

-- remove STRICT_TRANS_TABLES -- note that executing this only removes it for your
-- current session -- it does not make a server-wide config change

mysql> set @@sql_mode='no_engine_substitution';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

-- now MySQL will accept the invalid value, with a warning

mysql> insert into datetimetest(dt) values ('2013-08-26T12:00:00+00:00');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 'dt' at row 1 |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

-- the value did get inserted, but the time zone information was lost:

mysql> select * from datetimetest;
+----+---------------------+
| id | dt                  |
+----+---------------------+
|  1 | 2013-08-26 12:00:00 |
+----+---------------------+
1 row in set (0.00 sec)

From your steps, you are most likely backing up and starting replication from a different position:

Get the GTID of the master by checking the variable "gtid_binlog_position". Save this value.

Do not use that value, between that time and the time the backup was taken (even if seconds), more rows could have been changed, which means you will not get an accurate representation of the start position- being the most probable cause of the duplicate key errors. You are taking the backup with:

--single-transaction --gtid --master-data

You must be using InnoDB for single transaction to work properly, otherwise, your other tables will not be consistent. Then, use the binary log coordinates or the gtid position written near the start of the dump .sql file to kickstart your slave.

The mistake may be because on the official guide it mentions the SELECT of the variable, but that would only work on a running replication if the master is not being written or all the tables are locked. The same guide mentions that using the feature integrated into mysqldump or xtrabackup is a better option.

Here it is a full guide for GTIDs on MySQL (some options and features change, but it may be a better overview for the overal procedure): https://www.percona.com/blog/2013/02/08/how-to-createrestore-a-slave-using-gtid-replication-in-mysql-5-6/