I started to have the following error running

# drush mi xxxx --limit=1

exception 'PDOException' with message 'SQLSTATE[22007]: Invalid datetime format: 1292 Truncated incorrect DOUBLE value: [error]
''' in includes/database/database.inc:2171
Stack trace:
#0 includes/database/database.inc(2171): PDOStatement->execute(Array)
#1 includes/database/database.inc(683): DatabaseStatementBase->execute(Array, Array)
#2 includes/database/query.inc(1177): DatabaseConnection->query('UPDATE {migrate...', Array,
Array)
#3 sites/all/modules/migrate/includes/base.inc(820): UpdateQuery->execute()
#4 sites/all/modules/migrate/includes/migration.inc(758):
MigrationBase->saveHighwater('1268764286')
#5 sites/all/modules/migrate/includes/base.inc(1073): Migration->import()
#6 sites/all/modules/migrate/migrate.drush.inc(1234): MigrationBase->processImport(Array)
#7 [internal function]: drush_migrate_import('Blog')
#8 /usr/local/src/drush/includes/command.inc(359): call_user_func_array('drush_migrate_i...', Array)
#9 /usr/local/src/drush/includes/command.inc(210): _drush_invoke_hooks(Array, Array)
#10 [internal function]: drush_command('Blog')
#11 /usr/local/src/drush/includes/command.inc(178): call_user_func_array('drush_command', Array)
#12 /usr/local/src/drush/lib/Drush/Boot/BaseBoot.php(62): drush_dispatch(Array)
#13 /usr/local/src/drush/drush.php(70): Drush\Boot\BaseBoot->bootstrap_and_dispatch()
#14 /usr/local/src/drush/drush.php(11): drush_main()

I tracked this down to the following:

default:
  // CAST(highwater AS INTEGER) would be ideal, but won't           // work in MySQL. This hack is thought to be 
   $query->where('(highwater+0) < :highwater', array(':highwater' => $highwater));

It may be local to my current setup, but nonetheless worrisome. I have Maria DB 10.1.8 installed and PHP Version 5.4.45 with PDO Driver for MySQL enabled
Client API version mysqlnd 5.0.10 - 20111026.

For some reason the following SQL

UPDATE migrate_status SET highwater='1268764286'
WHERE  (machine_name = 'Blog') AND ((highwater+0) < 1268764286)

Works on the mysqlclient, but as soon as I try to run it on a drupal site with db_query() I get the same error as with drush. This only happens when highwater is empty, not when there's an actual value in it, even if it's just 0.

This was on migrate for quite a bit. It says there in the comment that CAST is not available in mysql, I am not sure that's the case.

CommentFileSizeAuthor
#13 migrate-cast-2635792-13.patch882 bytesnedjo
#11 migrate-cast-2635792-11.patch1.14 KBnedjo
#9 migrate-cast-2635792-9.patch1.08 KBnedjo
#2 migrate-2635792-2-sql-error-saving-highwater.patch619 byteshanoii

Comments

hanoii created an issue. See original summary.

hanoii’s picture

hanoii
Primary language Spanish
Location 🇦🇷UTC-3
commented
Status: Active » Needs review
Related issues: +#2184641: SQL error on saveHighwater running a migration with PostgreSQL
StatusFileSize
new migrate-2635792-2-sql-error-saving-highwater.patch619 bytes

Attached is a patch that does the same think with a different approach that seems not to through any error and also seems even more flexible than the hack already in place.

glass.dimly’s picture

glass.dimly commented

Same error. Patch tested and working to fix against migrate 7.x-2.8. Thanks for the patch!

eneko1907’s picture

eneko1907 commented

Same error observed on a clean 7.50 install, migrating content from D6.

Patch worked nicely, many thanks.

ElleKitty’s picture

ElleKitty commented
Status: Needs review » Reviewed & tested by the community

+1 "patch works for me", also D6 to 7, also old MySQL to MariaDb 10.1. Seems worth pushing for the patch to be accepted.

immanuel.paul’s picture

immanuel.paul commented

+1"patch works for me", D7 to D7. Patch can be accepted ASAP.

nedjo’s picture

nedjo
he/him/his
Primary language English
commented

Closed #2713563: Truncated incorrect DOUBLE value message with highwater as a duplicate.

Mike Ryan had commented there (#2):

The problem here is with

// CAST(highwater AS INTEGER) would be ideal, but won't
// work in MySQL. This hack is thought to be portable.
$query->where('(highwater+0) < :highwater', array(':highwater' => $highwater));

The hack is not so portable after all - the highwater column is a varchar, and it seems that MySQL has become pickier about automatically casting varchars to ints than when this was initially implemented. So, we probably need to do the same sort of CASE WHEN highwater='' THEN 0 thing the pgsql case is doing. I don't have time to test that out at this time, but for those facing this problem you can do:

  1. UPDATE migrate_status SET highwater=0 WHERE highwater='' AND machine_name='migration_machine_name' (preferred), or
  2. Remove the +0 from the above line (should work where your only highwaters are timestamps since (Mike checks http://www.epochconverter.com/) Sun, 09 Sep 2001 01:46:40 GMT
nedjo’s picture

nedjo
he/him/his
Primary language English
commented
Status: Reviewed & tested by the community » Needs work

The current patch avoids the error only in the case that the existing highmark is NULL or an empty string. Because the highwater+0 fallback is retained, any other value will still trigger the error.

nedjo’s picture

nedjo
he/him/his
Primary language English
commented
Status: Needs work » Needs review
StatusFileSize
new migrate-cast-2635792-9.patch1.08 KB

Actually, it looks like we can simply remove this hack. It's left over from the 6.x-2.x version and no longer needed.

CAST support has been in MySQL since at least 5.0 (see the MySQL 5.0 manual). Drupal 6 required MySQL 4.1.1. or higher. But Drupal 7 requires MySQL 5.0.15 or higher, so we can assume CAST support.

Patch attached.

Status: Needs review » Needs work

The last submitted patch, 9: migrate-cast-2635792-9.patch, failed testing.

nedjo’s picture

nedjo
he/him/his
Primary language English
commented
Status: Needs work » Needs review
StatusFileSize
new migrate-cast-2635792-11.patch1.14 KB

Forgot to delete a line.

Status: Needs review » Needs work

The last submitted patch, 11: migrate-cast-2635792-11.patch, failed testing.

nedjo’s picture

nedjo
he/him/his
Primary language English
commented
Status: Needs work » Needs review
StatusFileSize
new migrate-cast-2635792-13.patch882 bytes

MySQL casts and converts as SIGNED or UNSIGNED rather than INTEGER (docs).

joelpittet’s picture

joelpittet
he/him
Primary language English
Location Vancouver
commented
Status: Needs review » Reviewed & tested by the community

This worked for me, thank you @nedjo

wrg20’s picture

wrg20 commented

#13 worked for me. Thank you #nedjo

mrP’s picture

mrP commented

+1 RTBC @nedjo #13

web247’s picture

web247 commented

#13 worked for me nicely, thanks.

khiminrm’s picture

khiminrm commented

#13 works for me. Thanks!

zythyr’s picture

zythyr commented

#13 patch worked for me. Thanks!

Grayside’s picture

Grayside commented

#13 worked for me.

ShutterFreak’s picture

ShutterFreak commented

#13 worked for me, while migrating from D6.38 on MariaDB to D7.56 on MariaDB. Both Drupal instances shared the same DB.

yurg’s picture

yurg commented

#13 works for me too. +1 for commit.

  • pifagor committed 91f601b on 7.x-2.x 
    Issue #2635792 by nedjo, hanoii: SQLSTATE[22007]: Invalid datetime...
pifagor’s picture

pifagor commented
Status: Reviewed & tested by the community » Fixed

Done

pifagor’s picture

pifagor commented
Status: Fixed » Closed (fixed)