Inserting >PHP_INT_MAX into BIGINT fails on PostgreSQL

It looks to me like this is sorted in Php 6.0. I trawled around in the code to find what had been changed, and have what I Think is the relevant diff attached to:

http://bugs.php.net/bug.php?id=48924

This seems to fix the issue cleanly. Its worth noting that Mysql users will experience the same sort of error as Postgres if STRICT_ALL_TABLES or similar is switched on (Actually they won't.... I was looking at the 64-bit case by mistake).

The PDO developers have fixed this issue and committed a patch to 5.2, 5.3 and 6.0 branches. For this interested, this bug was introduced in a fix for another similar issue (loss of decimal data in prepared statements http://bugs.php.net/bug.php?id=41698). I could not exhibit our bug on Php6 because it was not patched for 41698! Presumably the fix for our bug will be in the next point release of php.

We really should find some way to code around this bug. It's not enough that some future PHP will work for D7...

#349508 is closed, is there anything left to do here?

Looks like it was fixed here: http://bugs.php.net/bug.php?id=48924. I can't find that bugfix in the php changelog at http://www.php.net/ChangeLog-5.php, but the commit message is at http://svn.php.net/viewvc?view=revision&revision=284208. Looking at the date (July 16 2009), the fix must have been included in PHP 5.2.11 and 5.3.1.

Josh, there is a small mistake in the patch, in the warning message it says "5.3.11" where it should say "5.2.11".

Here is a new patch, fixing the mistake from #10 and another minor typo. Note that I didn't actually test the patch (I don't have PGSQL running here) so I don't feel qualified to say this is RTBC.

Any reason this is not using the _requirements() hook?

Let's move driver checks to another issue. Also even the code comments in the patch state this isn't critical.

Could we add a @Todo: remove in D8 comment?

This is a driver-specific requirement so adding it in the install class for that driver is correct. hook_requirements() is for Drupal-specific requirements. Database drivers are specifically designed to allow for driver-specific requirements for exactly this reason. :-)

If we include a todo, it should say to remove when we start requiring 5.2.12 / 5.3.1. We don't know yet if Drupal 8 will do so. A todo to that effect is probably unnecessary since the comment here clearly specifies which versions are safe, but I suppose it can't hurt.

That said, the error message should probably mention both of the possible data loss issues, this and #316095: Raise PHP requirement to 5.2.12 for PostgreSQL only. Setting back to CNW. I leave it up to fiasco to decide how to merge these issues, but I'm +1 on the approach here.

#11: 515310-11-PHP_INT_MAX.patch queued for re-testing.

Crell, given that we didn't get an answer from fiasco, would you want me to commit this?

Josh Waihi == fiasco.

In concept I agree with this approach. My only issues with the most recent patch would be that it doesn't declare a visibility for the new test method (it defaults to public, but really we should specify that explicitly) and that it uses db_query() from within a DB class, but the installer is, I believe, an exception to that at the moment. (Maybe it needn't be, but for now it is.)

And as catch said, Josh == fiasco so for Postgres intricacies I defer to him.

Not sure how I forgot that Josh is fiasco. Either way, I committed this patch to CVS HEAD. Thanks! :)

Hello All!

On a new install of 7.0-alpha6, on PostgreSQL (v9-beta2, if this matters), we are still seeing a seemingly? erroneous PHP version check:

The version of PHP you are using has known issues with PostgreSQL. You can see more at http://drupal.org/node/515310. We suggest you upgrade PHP to 5.2.11, 5.3.1 or greater. Failing to do so may result in serious data corruption later.

Error messageNotice: unserialize(): Error at offset 0 of 59 bytes in install_verify_completed_task() (line 784 of /media/htdocs/drupal-7.0-alpha6/includes/install.core.inc).

We are currently running php 5.2.11...

I am getting this warning with PHP 5.3.2-1ubuntu4.2 on 64-bit linux, even though I shouldn't according to the message. The reason is that the SQL insert fails with:

ERROR:  invalid input syntax for integer: "9.2233720368548E+18" at character 51
STATEMENT:  INSERT INTO test_php_version ( test_int ) VALUES ('9.2233720368548E+18')

This is because PHP_INT_MAX is float(9.2233720368548E+18) on 64-bit systems (see http://php.net/manual/en/language.types.integer.php#language.types.integ...) and is erroneously quoted as a string when PDO uses it.

So either this problem exists on 64-bit systems and the warning should be displayed but its text corrected, or it does not and the testing should be fixed.

I am getting this warning on a 32 bit Ubuntu Lucid.

% php -v
PHP 5.3.2-1ubuntu4.2 with Suhosin-Patch (cli) (built: May 13 2010 20:01:00) 
Copyright (c) 1997-2009 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2010 Zend Technologies
    with Xdebug v2.1.0, Copyright (c) 2002-2010, by Derick Rethans
    with Suhosin v0.9.29, Copyright (c) 2007, by SektionEins GmbH
% psql --version
psql (PostgreSQL) 8.4.4
contains support for command-line editing

@Yorirou (#30), does your PostgreSQL log tell you anything (/var/log/postgresql/postgresql-8.4-main.log) when the check is performed?

2010-07-15 22:38:16 CEST ERROR:  value "2147483648" is out of range for type integer at character 51
2010-07-15 22:38:16 CEST STATEMENT:  INSERT INTO test_php_version ( test_int ) VALUES ('2147483648')

I think this is the relevant part of the log.

So the check fails when a too big value is inserted into a regular INT field.

      db_query("CREATE TABLE test_php_version ( test_int INT NOT NULL )");

But the bug report is about inserts into BIGINT fields, so something is wrong here.

I ran into this too with MAMP 1.9 using PHP 5.3.2 and PostgreSQL 8.4.

I applied the patch, and visually confirmed that the error message did not come up anymore. I tried with both PHP 5.3.2 and PHP 5.2.13 in MAMP 1.9.

The code behind the patch looks good to me, and is fairly simple. I don't recall anything different with BIGINT in other versions of pg.

I think this can be RTBC once there's a review that still displays the message in a prior version of PHP.

sneak edit: somehow tag just got changed on my edit?

#34, For 32-bit systems this should work, but it will still fail with 64-bit systems (see #29). From what I understand PostgreSQL won't recognize the number expressed with E notation (PHP_INT_MAX + 1 = 9.2233720368548E+18) as a numeric value when quoted as a string like PDO does. Unfortunately, even if it was expressed correctly, without quotes, it would fail as the maximum value for BIGINT is less than PHP_INT_MAX + 1 on 64-bit systems. (9223372036854775807 < 9.2233720368548E+18).

With PHP on 64-bit system:

$> php -r 'echo PHP_INT_MAX + 1;'
9.2233720368548E+18

And in PostgreSQL:

drupal=# CREATE TABLE test_php_version ( test_int BIGINT NOT NULL );
drupal=# INSERT INTO test_php_version ( test_int ) VALUES (9.2233720368548E+18);
 ERROR:  bigint out of range

:(

Per #38.

Made the check conditional with PHP_INT_MAX ≥ max allowed value for BIGINT to address #38. Also smuggled in some quoting style changes — should be more in line with the coding standards I assume.

Let's simply check the PHP version. We need to do it anyway, because there are other bugs in PHP < 5.2.7 that can prevent installation on PostgreSQL. See #862078: PDO coerceing empty strings to NULL when target is a Postgres bytea.

This is now a duplicate of the other issue.

I have PHP 5.3.2 installed:

localhost:/opt/local/apache2 admin$ php -v
PHP 5.3.2 (cli) (built: Aug  5 2010 21:45:16) 
Copyright (c) 1997-2010 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2010 Zend Technologies
    with Xdebug v2.0.5, Copyright (c) 2002-2008, by Derick Rethans

But I am still getting this error:

The version of PHP you are using has known issues with PostgreSQL. You can see more at http://drupal.org/node/515310. We suggest you upgrade PHP to 5.2.11, 5.3.1 or greater. Failing to do so may result in serious data corruption later.

But I have upgraded to PHP 5.3.1 or greater. So the expected behavior is either no error, or a more informative one.

OS X Server 10.4.11, PostGres 8.4.4, 7.0-alpha6.

I ran #38's check:

localhost:/opt/local/lib/postgresql84/bin postgres$ ./psql -U drupal -d drupal7
psql (8.4.4)
Type "help" for help.

drupal7=> CREATE TABLE test_php_version ( test_int BIGINT NOT NULL );
CREATE TABLE
drupal7=> INSERT INTO test_php_version ( test_int ) VALUES (9.2233720368548E+18);
ERROR:  bigint out of range
drupal7=> 

7.0-alpha6, on a 64-bit machine:

  Model Name:	Mac Pro
  Model Identifier:	MacPro1,1
  Processor Name:	Dual-Core Intel Xeon
  Processor Speed:	3 GHz
  Number Of Processors:	2
  Total Number Of Cores:	4
  L2 Cache (per processor):	4 MB
  Memory:	4 GB
  Bus Speed:	1.33 GHz
  Boot ROM Version:	MP11.005C.B08
  SMC Version:	1.7f10

And:

localhost:/Library/WebServer/Documents/drupal7 admin$ arch
i386

Then, using this script, I discovered that my PHP_INT_MAX is 2147483647 (32-bit), which is a little short of the patch's 9223372036854775807 (64-bit). It seems that my macports install has an issue....

Continued issues with installing a 64 bit PHP on OS X Server 10.4.11. A clean macports with +universal kept failing. MAMP and XAMPP are 32 bit. Compiling PostGres from source worked great, via here:

http://community.invisionpower.com/topic/292849-installing-postgresql-se...

However, compiling PHP 5.3.3 from source has not, via:

http://drewish.com/node/128

I run up against this bug, which has been marked "bogus" (not PHP) by the PHP developers, "Make fails dns.c:305":

http://bugs.php.net/bug.php?id=51706

If we need a 64 bit PHP to run PostGres, and that won't compile under some versions OS X, then that could be a problem?

@shunting: no, you don't need a 64bit PHP to run Drupal on PostgreSQL. The test here is bogus, and we are removing it in #862078: PDO coerceing empty strings to NULL when target is a Postgres bytea. You should help review this other issue.

Well, that's a relief. Thanks for much for responding. Having torn my hair out, I was about to revert to MySQL, and abandon all the plpgsl functions I'd written... Anyhow, I learned a lot about compiler configuration!

(I should also say that whoever was monitoring the thread from http://bugs.php.net/bug.php?id=51706 didn't seem aware that OS X usage was very heavy in the Drupal community...

This is fixed; I left a note at the other issue.