ZF-3837: insert() don't return last_insert_id when empty key exists in inserted data

Issue Type: Bug Created: 2008-08-03T07:57:05.000+0000 Last Updated: 2011-12-20T12:17:51.000+0000 Status: Closed Fix version(s): - 1.11.6 (05/May/11)

Reporter: Pavel Vrany (derk) Assignee: Adam Lundrigan (adamlundrigan) Tags: - Zend_Db_Table

Related issues: - ZF-4183

Attachments: - ZF-3837.patch


example with MySQL:

CREATE TABLE customer ( id int(11) NOT NULL auto_increment, title text NOT NULL, PRIMARY KEY (id) )

class Customer extends Zend_Db_Table_Abstract { protected _sequence = true; // defaul behavior }

$data=('id'=>null, 'title'=>'xxx');

$table = new Customer(); $table->insert($data);

Mysql is ignoring empty 'id' and using auto value, framework (Zend/Db/Table/Abstract.php) don't expects it:

    if ($this->_sequence === true && !isset($data[$pkIdentity])) {
        $data[$pkIdentity] = $this->_db->lastInsertId();

Maybe, empty() test will be better.


Posted by Dolf Schimmel (Freeaqingme) (freak) on 2009-07-22T14:47:33.000+0000

1.6.1 1.7.3 1.7.5 1.7.6 1.7.7 1.7.8 1.8.1 1.8.2 1.8.3 1.8.4 Next mini relase (again)

Could anybody please explain what's going on here? This issue has been updated so many times that in that same timeframe it could have been fixed. Please either fix it, close it as wont-fix, or set a realistic 'fix version'.

Posted by Fabio Almeida (fabius) on 2009-08-18T02:27:32.000+0000

@Dolf Schimmel: I totally second that. And it got bumped again, to version 1.9.1. And guess what... It's not fixed. Again.

Posted by Ralph Schindler (ralph) on 2009-08-18T09:46:35.000+0000

Updating to "Should Have" in "Next Mini Release". Changing to "Major" priority.

Posted by Alin Gherman (ha11owed) on 2009-09-10T02:52:17.000+0000

this happens also in 1.9.2, i think the only workaround is to create a new array with no empty elements

Posted by Menno Luiten (mluiten) on 2009-12-19T03:01:38.000+0000

Looking into this, and strange thing is that 'id' should be emptied by the following code (above the actual insertion)

<pre class="highlight">
 * If the primary key can be generated automatically, and no value was
 * specified in the user-supplied data, then omit it from the tuple.
 if (array_key_exists($pkIdentity, $data) && $data[$pkIdentity] === null) {

So the isset($data[$pkIdentity]) as mentioned above should fail anyway, even when given null, since it's being unset in that case. Trying to replicate the behavior..

Posted by Menno Luiten (mluiten) on 2009-12-19T04:51:25.000+0000

There is a actually test case named testTableInsert() inside Zend/Db/Table/TestCommon.php which uses the exact scenario described, and it succeeds. And I can't seem to get it to fail under any condition; anyone else with more 'luck'? Otherwise, this might not be an issue.

<pre class="highlight">
public function testTableInsert()
    $table = $this->_table['bugs'];
    $row = array (
        'bug_id'          => null,
        'bug_description' => 'New bug',
        'bug_status'      => 'NEW',
        'created_on'      => '2007-04-02',
        'updated_on'      => '2007-04-02',
        'reported_by'     => 'micky',
        'assigned_to'     => 'goofy',
        'verified_by'     => 'dduck'
    $insertResult = $table->insert($row);
    $lastInsertId = $this->_db->lastInsertId();
    $this->assertEquals($insertResult, $lastInsertId);
    $this->assertEquals(5, $lastInsertId);

Tried scenarios (using mysqli driver): * changing bug_id column from IDENTIFIER to 'INT(11) NOT NULL auto_increment' * adding $_sequence = true to My_ZendDbTable_TableBugs * commenting out "protected $_primary = 'bug_id';" in My_ZendDbTable_TableBugs * replacing $_primary with "array('bug_id')" * changing table type from InnoDB to MyISAM

Posted by Pavel Vrany (derk) on 2009-12-19T06:24:51.000+0000

The function dbAdapter->lastInsertId() is OK, the problem is with the return value of the function dbtable->insert()

$ php test.php returnedKey is 0, but lastInsertId is 55

$ cat test.php

<pre class="highlight">

require_once 'Zend/Db/Table.php';
require_once 'Zend/Db/Table/Abstract.php';
require_once 'Zend/Db.php';

$dbAdapter = Zend_Db::factory('mysqli',array(
    'host'     => '',
    'port'     => '3306',
    'username' => 'root',
    'password' => '',
    'dbname'   => 'test'


class customer extends Zend_Db_Table_Abstract {
    protected $_sequence = true; // default behavior
    protected $_primary = 'id';
$data['id']=0; //mysql documentation: It is not even changed if you update another AUTO_INCREMENT column with a nonmagic value (that is, a value that is not NULL and not 0). 
$table = new customer;
$primary_key=$table->insert($data); //@return mixed The primary key of the row inserted.  

$lastInsertId = $dbAdapter->lastInsertId();
echo "returnedKey is $primary_key, but lastInsertId is $lastInsertId\n";


Posted by Menno Luiten (mluiten) on 2009-12-19T07:21:37.000+0000

That should be discovered by

<pre class="highlight">
$this->assertEquals($insertResult, $lastInsertId);

In your code, you set $data['id'] to 0 (zero), not null. This would not be unset and would explain the behavior (if the mysqli adapter does convert 0 -> null at some point). Could you test with $data['id'] = null and confirm if that works?

If I insert 0 as row key directly into MySQL, it is true that is considers it as NULL and thus creates an auto incremented key. This could be easily patched by checking for $data[$pkIdentity] == null instead of === null

Posted by Pavel Vrany (derk) on 2009-12-19T10:19:00.000+0000

You're right, null value works correctly now. But other empty values returns a bad result: string '', integer 0, array(). Also everything what isn't number: string 'fsdfdsfs' etc.

It's not only "feature" of php adapter, I get the same results from the terminal client.

The behavior can be also changed:…

Solution could be to call the function last_insert_id() before and after the insert(), compare the values and in case of change use the new value as the primary_key.

Posted by Menno Luiten (mluiten) on 2009-12-19T13:25:01.000+0000

Actually, empty values like string "" and array() would be detected when using the == operator instead of the ==== operator. I don't think an input of "fsdfsdgsf" would be wanted anyway when using sequence = true. Maybe should give some Exception, but I'm not sure. I will talk to Ralph or Matthew to decide if loosening the check is wanted tomorrow.

Posted by Adam Lundrigan (adamlundrigan) on 2011-04-28T15:19:55.000+0000

I've attached a test suite patch which, in my mind anyway, proves that this bug no longer exists.

In the test, I set 'bug_id' to an empty string then perform the insert. The return value of insert() is the correct auto value.

This test passed when using both sqlite in-memory and pdo_mysql adapters.

Posted by Adam Lundrigan (adamlundrigan) on 2011-04-28T17:58:35.000+0000

Duplicate of ZF-2953

Posted by Adam Lundrigan (adamlundrigan) on 2011-04-28T22:36:07.000+0000

Fix in trunk r23878

Posted by Ralph Schindler (ralph) on 2011-05-03T03:26:48.000+0000

Fixed in release branch 1.11 in r23949

Posted by Ketil Stadskleiv (stadskle) on 2011-12-20T12:17:51.000+0000

I am receiving the same issue now when the data field is a Zend_Db_Expr('NULL'). This should not stop the db_table to return the new primary key.

<pre class="highlight">
$row = array (
        'bug_id'          => Zend_Db_Expr('NULL'),
        'bug_description' => 'New bug',
        'bug_status'      => 'NEW',
        'created_on'      => '2007-04-02',
        'updated_on'      => '2007-04-02',
        'reported_by'     => 'micky',
        'assigned_to'     => 'goofy',
        'verified_by'     => 'dduck'

This causes insert() to return a Zend_Db_Expr('NULL') object, that cannot be the desired behaviour?

Have you found an issue?

See the Overview section for more details.


© 2006-2018 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.