Zend_Db last_insert_id returns super big number

Question

Hello,

I have a MySql table whose primary key is a 64bit BigInt

I'm using Zend_Db (Zend Framework 1.8.4) to insert a new row, then call lastInsertId() to retreive the new row's id, what I get back is a super large number such as 18446744072633694008, and this number changes from time to time, but always this large. the auto increment index is set to 0, and in the database the record actually got inserted with correct primary id, (0, 1, 2...), it's just that the id return from lastInsertId() gives weird number. Is it a known issue for Zend_db, which doesn't deal with 64 bit number?

environment: Zend Framework 1.8.4 Apache2 on 32bit box, Ubuntu OS MySQL5.1 PHP5.2.4 MySQL adapter: mysqli

Thanks

Answer 1

I developed a lot of Zend_Db code through ZF 1.0.

PHP database extensions return PHP strings, not integers. The reason for this is that the RDBMS is likely to be using numeric data types that exceed the range of integers supported in the client PHP binary.

Zend_Db just returns the values it fetches from the PHP database extension, so if you see strange values, then it's probably due to PHP, not Zend_Db.

You might want to run the unit tests to test inserts:

$ cd <zf>/tests
$ vi TestConfiguration.php   # enable the MySQL adapter you're using
$ phpunit --verbose --filter testAdapterInsert Zend/Db/AllTests.php

One of the assertions made in these tests is that the return value from lastInsertId() is a PHP string type.

Answer 2

This seems to be a bug in the OO implementation of PHP's Mysqli adapter. See this note on PHP's website.

For a temporary, stop-gap solution, try using the PDO_Mysql Zend_Db adapter.

I'm currently creating an issue and working on a solution to see if I can work around this problem in Zend_Db_Adapter_Mysqli. I will keep this answer up to date with my progress.

You can follow my progress on a workaround at http://framework.zend.com/issues/browse/ZF-7590