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

Description

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.

Comments

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'.

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

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

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

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


/**
 * 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) {
    unset($data[$pkIdentity]);
 }

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..

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.


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

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


<?php

set_include_path('/var/www/weby/library/');
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'     => '127.0.0.1',
    'port'     => '3306',
    'username' => 'root',
    'password' => '',
    'dbname'   => 'test'
));

Zend_Db_Table::setDefaultAdapter($dbAdapter);

class customer extends Zend_Db_Table_Abstract {
    protected $_sequence = true; // default behavior
    protected $_primary = 'id';
}
$data=array();
$data['title']='xxx';
$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";

?>

That should be discovered by


$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

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: http://dev.mysql.com/doc/refman/…

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.

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.

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.

Duplicate of ZF-2953

Fix in trunk r23878

Fixed in release branch 1.11 in r23949

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.


$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?