Bug #8051

getAfterID raises "could not get last insert ID"

Details

Submitted	2006-06-28 10:53 UTC
From	LiveFreeAndRoam at gmail dot com
Assigned	lsmith
Status	Closed
Package	MDB2
PHP Version	5.1.2
OS	WXP Pro
Roadmaps	(Not assigned)

Comments

[2006-06-28 10:53 UTC] LiveFreeAndRoam at gmail dot com

Description:
------------
>php --version
PHP 5.1.2 (cli) (built: Jan 11 2006 16:40:00)
Copyright (c) 1997-2006 The PHP Group
Zend Engine v2.1.0, Copyright (c) 1998-2006 Zend Technologies

I have also tested with PHP V4.4.2.

>pear list
MDB2 2.0.3 stable
MDB2_Driver_mysql 1.0.3 stable

Cannot retrieve the LastInsertId from MySQL database when using MDB2 autoexecute. This works if I use native mysql functions.

Just so you can see the simple table I am using and that it has an auto-increment field, I executed the query:

SQL> DESCRIBE SPECIES

Then exported the resultset to XML:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE ROOT SYSTEM "species.dtd">
<ROOT>
<row>
<field name="Field">id</field>
<field name="Type">tinyint(4)</field>
<field name="Null">NO</field>
<field name="Key">PRI</field>
<Default/>
<field name="Extra">auto_increment</field>
</row>
<row>
<field name="Field">name</field>
<field name="Type">varchar(50)</field>
<field name="Null">NO</field>
<field name="Key"></field>
<field name="Default"></field>
<field name="Extra"></field>
</row>
</ROOT>

Test script:
---------------
function _iud($post=null, $autoquery, $where=null) {
echo '<pre>_iud($post, $autoquery, $where): (';
print_r($post); print_r($autoquery); print_r($where); echo ')</pre>';

// use of autoExecute requires MDB2::loadModule('Extended')
// $this->dao->db->loadModule('Extended'); // use $mdb2->extended->xxx()
// echo '<pre>Table Class _iud:'; print_r($this); echo '</pre>';

$affected = $this->dao->db->extended->autoExecute(
$this->table_name, $post, $autoquery, $where, null);

if(PEAR::isError($affected)) {
trigger_error($affected->getMessage(), E_USER_WARNING);
}

if($autoquery == MDB2_AUTOQUERY_INSERT) {
$id = $this->dao->db->lastInsertID();
if(PEAR::isError($id)) {
trigger_error($id->getMessage(), E_USER_WARNING);
echo '<pre>'; print_r($id); echo '</pre>';
} else echo 'Newly created ID: ' . $id;
}
return $affected;
}

Expected result:
----------------
I expected to have this routine display the newly created ID that was resulted from the insert operation.

Actual result:
--------------
POST:Array
(
[name] => House
[submit] => Create
)

_iud($post, $autoquery, $where): (Array
(
[name] => House
)
1)

Warning: MDB2 Error: Array in C:\Documents and Settings\LFaR\My Documents\My Websites\qgw-0.11\private\app\php\lib\table_class.php on line 288

MDB2_Error Object
(
[error_message_prefix] =>
[mode] => 1
[level] => 1024
[code] =>
[message] => MDB2 Error: Array
[userinfo] => lastInsertID: (0) Could not get last insert ID[Native code: 0]
[Native message: ]

[backtrace] => Array
(
[0] => Array
(
[file] => c:\php-4.4.2-Win32\PEAR\MDB2.php
[line] => 951
[function] => PEAR_Error
[class] => PEAR_Error
[object] => MDB2_Error Object
*RECURSION*
[type] => ->
[args] => Array
(
[0] => MDB2 Error: Array
[1] =>
[2] => 1
[3] => 1024
[4] => lastInsertID: (0) Could not get last insert ID[Native code: 0]
[Native message: ]

)

[1] => Array
(
[file] => c:\php-4.4.2-Win32\PEAR\PEAR.php
[line] => 540
[function] => MDB2_Error
[class] => MDB2_Error
[object] => MDB2_Error Object
*RECURSION*
[type] => ->
[args] => Array
(
[0] =>
[1] => 1
[2] => 1024
[3] => lastInsertID: (0) Could not get last insert ID[Native code: 0]
[Native message: ]

)

[2] => Array
(
[file] => c:\php-4.4.2-Win32\PEAR\MDB2.php
[line] => 1357
[function] => raiseError
[class] => PEAR
[object] => MDB2_Driver_mysql Object
(
[escape_quotes] => \
[db_index] => 1
[dsn] => Array
(
[phptype] => mysql
[dbsyntax] => mysql
[username] => root
[password] => secret
[protocol] => tcp
[hostspec] => localhost
[port] =>
[socket] =>
[database] =>
[mode] =>
)

[connected_dsn] => Array
(
[phptype] => mysql
[dbsyntax] => mysql
[username] => root
[password] => secret
[protocol] => tcp
[hostspec] => localhost
[port] =>
[socket] =>
[database] =>
[mode] =>
)

[connection] => Resource id #18
[opened_persistent] =>
[database_name] => qgw
[connected_database_name] => qgw
[connected_server_info] => 5.0.18-nt-log
[supported] => Array
(
[sequences] => emulated
[indexes] => 1
[affected_rows] => 1
[summary_functions] => 1
[order_by_text] => 1
[transactions] =>
[current_id] => emulated
[limit_queries] => 1
[LOBs] => 1
[replace] => 1
[sub_selects] => 1
[auto_increment] => 1
[primary_key] => 1
[result_introspection] => 1
[prepared_statements] => 1
)

[options] => Array
(
[ssl] =>
[field_case] => 0
[disable_query] =>
[result_class] => MDB2_Result_%s
[buffered_result_class] => MDB2_BufferedResult_%s
[result_wrap_class] =>
[result_buffering] => 1
[fetch_class] => stdClass
[persistent] =>
[debug] => 0
[debug_handler] => MDB2_defaultDebugOutput
[default_text_field_length] => 4096
[lob_buffer_length] => 8192
[log_line_break] =>

[idxname_format] => %s_idx
[seqname_format] => %s_seq
[seqcol_name] => sequence
[quote_identifier] =>
[use_transactions] =>
[decimal_places] => 2
[portability] => 127
[modules] => Array
(
[ex] => Extended
[dt] => Datatype
[mg] => Manager
[rv] => Reverse
[na] => Native
[fc] => Function
)

[emulate_prepared] =>
[default_table_type] =>
)

[warnings] => Array
(
)

[debug_output] =>
[in_transaction] =>
[offset] => 0
[limit] => 0
[phptype] => mysql
[dbsyntax] => mysql
[last_query] => DEALLOCATE PREPARE MDB2_Statement_mysqlacbaedcfaa4c94c68e11ea4c06feb82c
[fetchmode] => 2
[modules] => Array
(
[Extended] => MDB2_Extended Object
(
[db_index] => 1
)

[Manager] => MDB2_Driver_Manager_mysql Object
(
[verified_table_types] => Array
(
)

[db_index] => 1
)

[Reverse] => MDB2_Driver_Reverse_mysql Object
(
[db_index] => 1
)

[Datatype] => MDB2_Driver_Datatype_mysql Object
(
[valid_types] => Array
(
[text] =>
[boolean] => 1
[integer] => 0
[decimal] => 0
[float] => 0
[timestamp] => 1970-01-01 00:00:00
[time] => 00:00:00
[date] => 1970-01-01
[clob] =>
[blob] =>
)

[lobs] => Array
(
)

[db_index] => 1
)

)

[destructor_registered] => 1
[_debug] =>
[_default_error_mode] =>
[_default_error_options] =>
[_default_error_handler] =>
[_error_class] => PEAR_Error
[_expected_errors] => Array
(
)

[extended] => MDB2_Extended Object
(
[db_index] => 1
)

[manager] => MDB2_Driver_Manager_mysql Object
(
[verified_table_types] => Array
(
)

[db_index] => 1
)

[loaded_version_modules] => Array
(
[0] => manager
[1] => reverse
[2] => datatype
)

[reverse] => MDB2_Driver_Reverse_mysql Object
(
[db_index] => 1
)

[datatype] => MDB2_Driver_Datatype_mysql Object
(
[valid_types] => Array
(
[text] =>
[boolean] => 1
[integer] => 0
[decimal] => 0
[float] => 0
[timestamp] => 1970-01-01 00:00:00
[time] => 00:00:00
[date] => 1970-01-01
[clob] =>
[blob] =>
)

[lobs] => Array
(
)

[db_index] => 1
)

)

[type] => ->
[args] => Array
(
[0] =>
[1] =>
[2] =>
[3] =>
[4] => lastInsertID: (0) Could not get last insert ID[Native code: 0]
[Native message: ]

[5] => MDB2_Error
[6] => 1
)

)

[3] => Array
(
[file] => c:\php-4.4.2-Win32\PEAR\MDB2\Driver\mysql.php
[line] => 917
[function] => raiseError
[class] => MDB2_Driver_Common
[object] => MDB2_Driver_mysql Object
(
[escape_quotes] => \
[db_index] => 1
[dsn] => Array
(
[phptype] => mysql
[dbsyntax] => mysql
[username] => root
[password] => secret
[protocol] => tcp
[hostspec] => localhost
[port] =>
[socket] =>
[database] =>
[mode] =>
)

[connected_dsn] => Array
(
[phptype] => mysql
[dbsyntax] => mysql
[username] => root
[password] => secret
[protocol] => tcp
[hostspec] => localhost
[port] =>
[socket] =>
[database] =>
[mode] =>
)

[emulate_prepared] =>
[default_table_type] =>
)

[warnings] => Array
(
)

[Manager] => MDB2_Driver_Manager_mysql Object
(
[verified_table_types] => Array
(
)

[db_index] => 1
)

[Reverse] => MDB2_Driver_Reverse_mysql Object
(
[db_index] => 1
)

[lobs] => Array
(
)

[db_index] => 1
)

)

[destructor_registered] => 1
[_debug] =>
[_default_error_mode] =>
[_default_error_options] =>
[_default_error_handler] =>
[_error_class] => PEAR_Error
[_expected_errors] => Array
(
)

[extended] => MDB2_Extended Object
(
[db_index] => 1
)

[manager] => MDB2_Driver_Manager_mysql Object
(
[verified_table_types] => Array
(
)

[db_index] => 1
)

[loaded_version_modules] => Array
(
[0] => manager
[1] => reverse
[2] => datatype
)

[reverse] => MDB2_Driver_Reverse_mysql Object
(
[db_index] => 1
)

[lobs] => Array
(
)

[db_index] => 1
)

)

[type] => ->
[args] => Array
(
[0] =>
[1] =>
[2] =>
[3] => lastInsertID: (0) Could not get last insert ID
)

)

[4] => Array
(
[file] => C:\Documents and Settings\LFaR\My Documents\My Websites\qgw-0.11\private\app\php\lib\table_class.php
[line] => 286
[function] => lastInsertID
[class] => MDB2_Driver_mysql
[object] => MDB2_Driver_mysql Object
(
[escape_quotes] => \
[db_index] => 1
[dsn] => Array
(
[phptype] => mysql
[dbsyntax] => mysql
[username] => root
[password] => secret
[protocol] => tcp
[hostspec] => localhost
[port] =>
[socket] =>
[database] =>
[mode] =>
)

[connected_dsn] => Array
(
[phptype] => mysql
[dbsyntax] => mysql
[username] => root
[password] => secret
[protocol] => tcp
[hostspec] => localhost
[port] =>
[socket] =>
[database] =>
[mode] =>
)

[emulate_prepared] =>
[default_table_type] =>
)

[warnings] => Array
(
)

[Manager] => MDB2_Driver_Manager_mysql Object
(
[verified_table_types] => Array
(
)

[db_index] => 1
)

[Reverse] => MDB2_Driver_Reverse_mysql Object
(
[db_index] => 1
)

[lobs] => Array
(
)

[db_index] => 1
)

)

[destructor_registered] => 1
[_debug] =>
[_default_error_mode] =>
[_default_error_options] =>
[_default_error_handler] =>
[_error_class] => PEAR_Error
[_expected_errors] => Array
(
)

[extended] => MDB2_Extended Object
(
[db_index] => 1
)

[manager] => MDB2_Driver_Manager_mysql Object
(
[verified_table_types] => Array
(
)

[db_index] => 1
)

[loaded_version_modules] => Array
(
[0] => manager
[1] => reverse
[2] => datatype
)

[reverse] => MDB2_Driver_Reverse_mysql Object
(
[db_index] => 1
)

[lobs] => Array
(
)

[db_index] => 1
)

)

[type] => ->
[args] => Array
(
)

)

[5] => Array
(
[file] => C:\Documents and Settings\LFaR\My Documents\My Websites\qgw-0.11\private\app\php\lib\table_class.php
[line] => 307
[function] => _iud
[class] => Table
[object] => Species Object
(
[dao] => DataAccess Object
(
[dbname] => qgw
[username] => root
[password] => secret
[hostname] => localhost
[link] => 0
[db_type] => mysql
[dsn] => mysql://root:secret@localhost/qgw
[db] => MDB2_Driver_mysql Object
(
[escape_quotes] => \
[db_index] => 1
[dsn] => Array
(
[phptype] => mysql
[dbsyntax] => mysql
[username] => root
[password] => secret
[protocol] => tcp
[hostspec] => localhost
[port] =>
[socket] =>
[database] =>
[mode] =>
)

[connected_dsn] => Array
(
[phptype] => mysql
[dbsyntax] => mysql
[username] => root
[password] => secret
[protocol] => tcp
[hostspec] => localhost
[port] =>
[socket] =>
[database] =>
[mode] =>
)

[emulate_prepared] =>
[default_table_type] =>
)

[warnings] => Array
(
)

[Manager] => MDB2_Driver_Manager_mysql Object
(
[verified_table_types] => Array
(
)

[db_index] => 1
)

[Reverse] => MDB2_Driver_Reverse_mysql Object
(
[db_index] => 1
)

[lobs] => Array
(
)

[db_index] => 1
)

)

[destructor_registered] => 1
[_debug] =>
[_default_error_mode] =>
[_default_error_options] =>
[_default_error_handler] =>
[_error_class] => PEAR_Error
[_expected_errors] => Array
(
)

[extended] => MDB2_Extended Object
(
[db_index] => 1
)

[manager] => MDB2_Driver_Manager_mysql Object
(
[verified_table_types] => Array
(
)

[db_index] => 1
)

[loaded_version_modules] => Array
(
[0] => manager
[1] => reverse
[2] => datatype
)

[reverse] => MDB2_Driver_Reverse_mysql Object
(
[db_index] => 1
)

[lobs] => Array
(
)

[db_index] => 1
)

)

[table_name] => Species
[fieldspecs] => Array
(
[id] => Array
(
[table] => species
[name] => id
[type] => int
[length] => 4
[flags] => not_null primary_key auto_increment
[mdb2type] => integer
[desc] => Id
[form] =>
)

[name] => Array
(
[table] => species
[name] => name
[type] => char
[length] => 50
[flags] => not_null
[mdb2type] => text
[desc] => Name
[form] => 1
)

)

[field_values] => Array
(
[name] => House
)

[errors] => Array
(
)

[form] =>
)

[type] => ->
[args] => Array
(
[0] => Array
(
[name] => House
)

[1] => 1
)

)

[6] => Array
(
[file] => C:\Documents and Settings\LFaR\My Documents\My Websites\qgw-0.11\test\controller.php
[line] => 47
[function] => insert
[class] => Table
[object] => Species Object
(
[dao] => DataAccess Object
(
[dbname] => qgw
[username] => root
[password] => secret
[hostname] => localhost
[link] => 0
[db_type] => mysql
[dsn] => mysql://root:secret@localhost/qgw
[db] => MDB2_Driver_mysql Object
(
[escape_quotes] => \
[db_index] => 1
[dsn] => Array
(
[phptype] => mysql
[dbsyntax] => mysql
[username] => root
[password] => secret
[protocol] => tcp
[hostspec] => localhost
[port] =>
[socket] =>
[database] =>
[mode] =>
)

[connected_dsn] => Array
(
[phptype] => mysql
[dbsyntax] => mysql
[username] => root
[password] => secret
[protocol] => tcp
[hostspec] => localhost
[port] =>
[socket] =>
[database] =>
[mode] =>
)

[emulate_prepared] =>
[default_table_type] =>
)

[warnings] => Array
(
)

[Manager] => MDB2_Driver_Manager_mysql Object
(
[verified_table_types] => Array
(
)

[db_index] => 1
)

[Reverse] => MDB2_Driver_Reverse_mysql Object
(
[db_index] => 1
)

[lobs] => Array
(
)

[db_index] => 1
)

)

[destructor_registered] => 1
[_debug] =>
[_default_error_mode] =>
[_default_error_options] =>
[_default_error_handler] =>
[_error_class] => PEAR_Error
[_expected_errors] => Array
(
)

[extended] => MDB2_Extended Object
(
[db_index] => 1
)

[manager] => MDB2_Driver_Manager_mysql Object
(
[verified_table_types] => Array
(
)

[db_index] => 1
)

[loaded_ver

[2006-07-16 23:25 UTC] LiveFreeAndRoam at gmail dot com

I will try to make time for this again tonight. I've been going flat out in other areas. It is still an important issue for me, so I will try to pull together a full reproducer tonight.

Regards,
LFaR.

[2006-07-17 11:09 UTC] LiveFreeAndRoam at gmail dot com

Hi Lukas,

Thanks again for prodding me on this. I hope I am doing everything correctly here. I am still quite the novice, so I haven't ruled out a silly error on my part.

The simple script below creates a database table called 'animal'. It then demonstrates that MDB2's lastInsertID() succeeds if the new row was inserted with the query() function, though it fails if the new row as inserted with the autoExecute() function.

Let me know if I can be of more help.

Best regards,
LFaR.

Reproducer script:

-- animal.sql -----------------------------------------
CREATE TABLE animal (
idanimal INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NULL,
PRIMARY KEY(idanimal)
);
INSERT INTO ANIMAL (name) values ('cat');
INSERT INTO ANIMAL (name) values ('dog');
INSERT INTO ANIMAL (name) values ('mouse');
select * from animal;
---------------------------------------------------------

-- lastInsertId.php -------------------------------------
<?php
require_once('MDB2.php'); // PEAR MDB2

class DataAccess extends MDB2 {
var $dsn = array(
'phptype' => 'mysql',
'username' => 'test',
'password' => 'test',
'hostspec' => 'localhost',
'database' => 'test');
var $db;

function DataAccess() {
$this->__construct();
register_shutdown_function(array($this, '__destruct'));
}

function __construct() {
// establish a permanent connection
$this->db =& MDB2::factory($this->dsn);

if(PEAR::isError($this->db)) {
trigger_error($this->db->getMessage(), E_USER_ERROR);
}

// fetch objects by default
$this->db->setFetchMode(MDB2_FETCHMODE_ASSOC);

// enable autoPrepare()
$this->db->loadModule('Extended'); // use $mdb2->extended->xxx()
$this->db->loadModule('Manager'); // use $mdb2->manager->xxx()
$this->db->loadModule('Reverse');
}
function __destruct() {
}
} // class DataAccess

function _list_table($db) {
$query = 'select * from animal';
$result =& $db->query($query);
if(PEAR::isError($result)) {
trigger_error($result->getMessage().' Query:'.$query, E_USER_WARNING);
}

while($row = $result->fetchRow()) {
echo $row['idanimal'] . '. ' . $row['name'] . ' ';
}
echo ' ';
}

$dao = new DataAccess();
_list_table($dao->db); // show all is well

if(!empty($_GET['name'])) $name = $_GET['name'];
else $name = 'rat';

echo '<pre>Using query() followed by lastInsertID() succeeds</pre> ';
$query = "insert into animal (name) values ('$name')";
$result =& $dao->db->query($query); // simple insert
if(PEAR::isError($result)) {
trigger_error($result->getMessage().' Query:'.$query, E_USER_ERROR);
}
$newId = $dao->db->lastInsertID(); // retrieve the Id of last inserted row
if(PEAR::isError($newId)) {
trigger_error($newId->getMessage(), E_USER_ERROR);
}
echo 'Last Insert ID: ' . $newId . ' ';
_list_table($dao->db);

// autoExecute to insert
echo '<pre>Using autoExecute() followed by lastInsertID() fails!<pre> ';
$post['name'] = $name;
$affected = $dao->db->extended->autoExecute(
'animal', $post, MDB2_AUTOQUERY_INSERT, null, null);
if(PEAR::isError($affected)) {
trigger_error($affected->getMessage(), E_USER_ERROR);
}

$newId = $dao->db->lastInsertID(); // this fails
if(PEAR::isError($newId)) {
trigger_error($newId->getMessage(), E_USER_WARNING);
echo '<pre>Error Object'.print_r($newId, 1).'</pre>';
die("Walkin' Away");
}
echo 'Last Insert ID: ' . $newId . ' ';
_list_table($dao->db);

?>
---------------------------------------------------------

-- sample output ----------------------------------------
Using URL: http://localhost/bug8051/lastinsertid.php?name=rat

The browser output is as follows:

1. cat
2. dog
3. mouse

Using query() followed by lastInsertID() succeeds

Last Insert ID: 33
1. cat
2. dog
3. mouse
33. rat

Using autoExecute() followed by lastInsertID() fails!
Warning: MDB2 Error: Array in C:\Documents and Settings\LFaR\My Documents\My Websites\bug8051\lastInsertID.php on line 81
Error Objectmdb2_error Object
(
[error_message_prefix] =>
[mode] => 1
[level] => 1024
[code] =>
[message] => MDB2 Error: Array
[userinfo] => lastInsertID: Could not get last insert ID[Native code: 0]
[Native message: ]

[backtrace] => Array
(
[0] => Array
(
[file] => C:\php\pear\MDB2.php
[line] => 951
[function] => pear_error
[class] => mdb2_error
[type] => ->
[args] => Array
(
[0] => MDB2 Error: Array
[1] =>
[2] => 1
[3] => 1024
[4] => lastInsertID: Could not get last insert ID[Native code: 0]
[Native message: ]

)

[1] => Array
(
[file] => C:\php\pear\PEAR.php
[line] => 563
[function] => mdb2_error
[class] => mdb2_error
[type] => ->
[args] => Array
(
[0] =>
[1] => 1
[2] => 1024
[3] => lastInsertID: Could not get last insert ID[Native code: 0]
[Native message: ]

)

[2] => Array
(
[file] => C:\php\pear\MDB2.php
[line] => 1368
[function] => raiseerror
[class] => pear
[type] => ::
[args] => Array
(
[0] =>
[1] =>
[2] =>
[3] =>
[4] => lastInsertID: Could not get last insert ID[Native code: 0]
[Native message: ]

[5] => MDB2_Error
[6] => 1
)

)

[3] => Array
(
[file] => C:\php\pear\MDB2\Driver\mysql.php
[line] => 931
[function] => raiseerror
[class] => mdb2_driver_mysql
[type] => ->
[args] => Array
(
[0] =>
[1] =>
[2] =>
[3] => lastInsertID: Could not get last insert ID
)

)

[4] => Array
(
[file] => C:\Documents and Settings\LFaR\My Documents\My Websites\bug8051\lastInsertID.php
[line] => 79
[function] => lastinsertid
[class] => mdb2_driver_mysql
[type] => ->
[args] => Array
(
)

)

[callback] =>
)
Walkin' Away
-- end --------------------------------------------------

[2006-07-18 00:53 UTC] LiveFreeAndRoam at gmail dot com

I have verified this fix with your updated package from CVS.

Much appreciated for the fix.

All the best to you!
LFaR.