Related to:
http://drupal.org/node/1176002

When trying to run a migration it tries to save starttime field with a float (1321958004087.1) into a bigint field. MySQL let's you do this (truncates the decimal) but Postgres fails. I've seen this is a common problem with the drupal database abstraction layer.

Error:
An AJAX HTTP error occurred. HTTP Result Code: 500 Debugging information follows. Path: /batch?id=7&op=do StatusText: Service unavailable (with message) ResponseText: PDOException: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "1321958004087.1" LINE 1: ...initialHighwater) VALUES ('CatalogKlubNode', '1', '132195800... ^: INSERT INTO si.migrate_log (machine_name, process_type, starttime, initialHighwater) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3); Array ( ) in MigrationBase->beginProcess() (line 738 of /var/www/petrol/repo/d7/branches/theme.multilanguage/webdir/sites/all/modules/migrate/includes/base.inc).PDOException: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "1321958004087.1" LINE 1: ...initialHighwater) VALUES ('CatalogKlubNode', '1', '132195800... ^: INSERT INTO si.migrate_log (machine_name, process_type, starttime, initialHighwater) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3); Array ( ) in MigrationBase->beginProcess() (line 738 of /var/www/petrol/repo/d7/branches/theme.multilanguage/webdir/sites/all/modules/migrate/includes/base.inc). ( ! ) Fatal error: Exception thrown without a stack frame in Unknown on line 0 Call Stack #TimeMemoryFunctionLocation 10.857828331964_drupal_exception_handler( )../bootstrap.inc:0 20.858628333800_drupal_log_error( )../bootstrap.inc:2158

CommentFileSizeAuthor
#9 postgres_error_trying-1349246-9.patch620 bytesmikeryan
#6 postgres_error_trying-1349246-6.patch1 KBmikeryan

Comments

Putr’s picture

Putr commented

Found a fix:

base.inc line 764

if ($this->logHistory) {
        db_merge('migrate_log')
          ->key(array('mlid' => $this->logID))
          ->fields(array(
            'endtime' => round(microtime(TRUE) * 1000),
            'finalhighwater' => $this->getHighwater(),
            'numprocessed' => $this->total_processed,
          ))
          ->execute();
      }

base.inc line 730

if ($this->logHistory) {
      $this->logID = db_insert('migrate_log')
                     ->fields(array(
                       'machine_name' => $this->machineName,
                       'process_type' => $newStatus,
                       'starttime' => round(microtime(TRUE) * 1000),
                       'initialHighwater' => $this->getHighwater(),
                       ))
                     ->execute();
    }

Seeing how you're operating with really large numbers i dont think you need the decimal point anyway.

mikeryan’s picture

mikeryan
he/him
Primary language English
Location Pittsfield, MA, USA
commented
Title: Runing migrate fial: Trying to insert float into bigint with Postgres » Postgres error: Trying to insert float into bigint
Version: 7.x-2.2 » 7.x-2.x-dev
Assigned: Unassigned » mikeryan
Status: Active » Fixed

Committed, thanks!

For future reference, please read http://drupal.org/patch/submit on how to submit patches.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

TheMentat’s picture

TheMentat commented

I get the following error:

PDOException: SQLSTATE[22P02]: Invalid text representation: 7 ERROR:[error]
invalid input syntax for integer: "1.34426532893E+12"
LINE 1: ...time, initialHighwater) VALUES ('Candidate', '1', '1.3442653...
^: INSERT INTO
migrate_log (machine_name, process_type, starttime, initialHighwater) VALUES
(:db_insert_placeholder_0, :db_insert_placeholder_1,
:db_insert_placeholder_2, :db_insert_placeholder_3); Array

Would sprintf() be the best way to solve this problem?

<?php
if ($this->logHistory) {
      $this->logID = db_insert('migrate_log')
                     ->fields(array(
                       'machine_name' => $this->machineName,
                       'process_type' => $newStatus,
                       'starttime' => sprintf('%d', round(microtime(TRUE) * 1000)),
                       'initialHighwater' => $this->getHighwater(),
                       ))
                     ->execute();
    }
?>
TheMentat’s picture

TheMentat commented
Issue summary: View changes

Grammar

sebcorbin’s picture

sebcorbin commented
Status: Closed (fixed) » Active

This is still active for me (PHP 5.4, PostGres 9.4). Comment #4 fixes it

mikeryan’s picture

mikeryan
he/him
Primary language English
Location Pittsfield, MA, USA
commented
Status: Active » Needs review
StatusFileSize
new postgres_error_trying-1349246-6.patch1 KB

I think casting to int is cleaner than sprintf. @SebCorbin, does the attached patch work for you?

sebcorbin’s picture

sebcorbin commented

Sorry it doesn't as the integer is bigger than PHP_INT, thus overflowing and giving a negative integer

mikeryan’s picture

mikeryan
he/him
Primary language English
Location Pittsfield, MA, USA
commented
Status: Needs review » Needs work
mikeryan’s picture

mikeryan
he/him
Primary language English
Location Pittsfield, MA, USA
commented
Status: Needs work » Needs review
Issue tags: +Migrate 2.9
StatusFileSize
new postgres_error_trying-1349246-9.patch620 bytes

%d didn't work, nor any other integer type specifier (all truncate to a 32-bit integer), but %s seems to work for me. @SebCorbin, can you try the attached patch with Postgres?

Thanks.

sebcorbin’s picture

sebcorbin commented

We actually used sprintf('%u', round(microtime(TRUE) * 1000)) because somehow the query was trying to insert 1.1234567+e19

mikeryan’s picture

mikeryan
he/him
Primary language English
Location Pittsfield, MA, USA
commented

%u does not work for me on MySQL, PHP 5.5.23 on Mac OS X - while the existing code, or the %s, produces correct timestamps like 1438383410969, using %u produces the likes of 3864369549.

Does %s work for you?

sebcorbin’s picture

sebcorbin commented

Unfortunately, no:

$ php -v
PHP 5.4.10 (cli) (built: Jan 21 2013 15:12:32)
Copyright (c) 1997-2012 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2012 Zend Technologies
    with Xdebug v2.2.1, Copyright (c) 2002-2012, by Derick Rethans
$ php -r "echo sprintf('%s', round(microtime(TRUE) * 1000));"
1.43877693974E+12
$ php -r "echo sprintf('%u', round(microtime(TRUE) * 1000));"
1438776944970
sebcorbin’s picture

sebcorbin commented

With php 5.5.24: 

$ /usr/bin/php -v
PHP 5.5.24 (cli) (built: May 19 2015 10:10:05)
Copyright (c) 1997-2015 The PHP Group
Zend Engine v2.5.0, Copyright (c) 1998-2015 Zend Technologies
$ /usr/bin/php -r "echo sprintf('%s', round(microtime(TRUE) * 1000));"
1438777123643
$ /usr/bin/php -r "echo sprintf('%u', round(microtime(TRUE) * 1000));"
1438777125210

A friend of mine has PHP 5.4.41 (32bits):

>php -r "echo sprintf('%s', round(microtime(1) * 1000));"
1438777320974
>php -r "echo sprintf('%u', round(microtime(1) * 1000));"
4258252022

This said, we should not use any of these functions, we should rely on the database, but Mysql doesn't support sub-second timestamps.

mikeryan’s picture

mikeryan
he/him
Primary language English
Location Pittsfield, MA, USA
commented
Status: Needs review » Needs work
Issue tags: -Migrate 2.9

Resetting to "Needs work" - we do not yet have a solution that works across different PHP versions and database engines.