Out of range value datetime when insert from select

[Sam]

Stack trace:
PerconaMigrator::Error: No foreign keys reference `bingo`.`contracts`; ignoring --alter-foreign-keys-method.
2017-02-17T05:38:42 Error copying rows from `bingo`.`contracts` to `bingo`.`_contracts_new`: 2017-02-17T05:38:42 Copying rows caused a MySQL error 1264:
Level: Warning
Code: 1264
Message: Out of range value for column 'end_date' at row 2127
Query: INSERT LOW_PRIORITY IGNORE INTO `bingo`.`_contracts_new` (`id`, `name`, `number`, `version`, `supplier_id`, `start_date`, `end_date`) SELECT `id`, `name`, `number`, `version`, `supplier_id`, `start_date`, `end_date` FROM `bingo`.`contracts` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /pt-online-schema-change 15890 copy nibble/

OK, lets understand whats happens - firstly, a little schema:
(ruby app) -> (percona migrator gem) -> (pt-online-schema-change)
All queries from app percona migrator gem converting to pt-online-schema-change format.
First source query looks like ALTER TABLE ..... converting to console command like $ pt-online-schema-change --'alter ...'.
This query works good with pt-online-schema-change tool too. But we do ONLINE MIGRATION, so, after alter query pt-online-schema-change execute helper query to move data from old to new table, wich we can see in failure (like INSERT LOW_PRIORITY IGNORE INTO ...). This command GENERATED only by pt-online-schema-change. This command is right, but date (time, datetime) fields in this variant select like a string, not like a date format. After in insert command this string value parses to date format again. This step gives to us current failure. We can avoid it only if we will select date field with a cast - this way date selects as a date and inserts as a date too.
Examples:
==> Query with fail:
INSERT LOW_PRIORITY IGNORE INTO `table`.`_name_new` (`id`, `name`, `number`, `version`, `supplier_id`, `start_date` ... ) SELECT `id`, `name`, `number`, `version`, `supplier_id`, `start_date` ... FROM `table`.`name` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE
==> Query without fail:
INSERT LOW_PRIORITY IGNORE INTO `table`.`_name_new` (`id`, `name`, `number`, `version`, `supplier_id`, `start_date` ... ) SELECT `id`, `name`, `number`, `version`, `supplier_id`, CAST(`start_date` AS DATE) ... FROM `table`.`name` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE

But we can't do it because of this query generates by pt-online-schema-change tool, not with ruby gem. So, for any changes of pt-online-schema-change we need sources.

Related link - http://stackoverflow.com/questions/11731288/strange-mysql-warning-1264-for-valid-datetime-value

[Sam (sammerset-h)]

Possible patch to oesolve this issue

[Nickolay Ihalainen (ihanick)]

Hi Sam,

Could you provide table structure, sample for data minimally causing the issue (e.g. mysql dump file with single table and several rows)?

As I understand from your description, datetime value blindly converted just to date and time part lost, thus mysql emits error (no error for '2012-07-26 00:00:00'). Cast hides this error, but data loss is still in place.

It could be useful to change/override queries in plugins for both triggers and table copy procedure (e.g. for automatic data conversion during modify column alter table ), but it looks like a feature request.

mysql> create table test.t2(id int(11) NOT NULL AUTO_INCREMENT, d date default null, primary key(id)) engine=innodb;
Query OK, 0 rows affected (0.34 sec)

mysql> insert into test.t2 (d) values('2012-07-26 14:27:00');
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> show warnings;
+-------+------+---------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------+
| Note | 1292 | Incorrect date value: '2012-07-26 14:27:00' for column 'd' at row 1 |

If you have a problem caused by datetime to date conversion, logically correct to fix the data first (make sure that time part is always zero) and start migration.

If it's actually something different, please provide full command line for pt-o-s-c, table structure and several sample rows.

Best regards,
Nickolay

[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-1415