Bug #38900 mysql_insert_id() and possibly last_insert_id() incorrect
Submitted: 19 Aug 2008 20:14 Modified: 21 Aug 2008 5:30
Reporter: Matthew Lord Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.66a OS:Any
Assigned to: CPU Architecture:Any

[19 Aug 2008 20:14] Matthew Lord 
Description:
Either we need to make this more clear in the documentation here:
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id
AND
http://dev.mysql.com/doc/refman/5.0/en/mysql-insert-id.html

OR there is a bug we need to fix.  The information appears not to be correct.  Please let me know if I'm mistaken.  Here are the results of the test:
mysql test

mysql> source /tmp/lastidtest.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

--------------
mysql  Ver 14.12 Distrib 5.0.66a, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id:          3
Current database:       test
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.66a-enterprise-gpl-log MySQL Enterprise Server (GPL)
Protocol version:       10
Connection:             127.0.0.1 via TCP/IP
Insert id:              3
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 3 min 55 sec

Threads: 1  Questions: 28  Slow queries: 0  Opens: 26  Flush tables: 1  Open tables: 18  Queries per second avg: 0.119
--------------

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 | 
+------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

--------------
mysql  Ver 14.12 Distrib 5.0.66a, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id:          3
Current database:       test
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:		5.0.66a-enterprise-gpl-log MySQL Enterprise Server (GPL)
Protocol version:	10
Connection:		127.0.0.1 via TCP/IP
Insert id:		3
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	latin1
Conn.  characterset:	latin1
TCP port:		3306
Uptime:			3 min 55 sec

Threads: 1  Questions: 32  Slow queries: 0  Opens: 26  Flush tables: 1  Open tables: 18  Queries per second avg: 0.136
--------------

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 | 
+------------------+
1 row in set (0.00 sec)

+---------+--------+------------+
| primary | unique | tochange   |
+---------+--------+------------+
|       1 |      1 | itschanged | 
|       2 |      2 | itschanged | 
+---------+--------+------------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

--------------
mysql  Ver 14.12 Distrib 5.0.66a, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id:		3
Current database:	test
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.0.66a-enterprise-gpl-log MySQL Enterprise Server (GPL)
Protocol version:	10
Connection:		127.0.0.1 via TCP/IP
Insert id:		3
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	latin1
Conn.  characterset:	latin1
TCP port:		3306
Uptime:			3 min 55 sec

Threads: 1  Questions: 37  Slow queries: 0  Opens: 26  Flush tables: 1  Open tables: 18  Queries per second avg: 0.157
--------------

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 | 
+------------------+
1 row in set (0.00 sec)

+---------+--------+------------+
| primary | unique | tochange   |
+---------+--------+------------+
|       1 |      1 | itschanged | 
|       2 |      2 | itschanged | 
+---------+--------+------------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

--------------
mysql  Ver 14.12 Distrib 5.0.66a, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id:		3
Current database:	test
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.0.66a-enterprise-gpl-log MySQL Enterprise Server (GPL)
Protocol version:	10
Connection:		127.0.0.1 via TCP/IP
Insert id:		3
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	latin1
Conn.  characterset:	latin1
TCP port:		3306
Uptime:			3 min 55 sec

Threads: 1  Questions: 42  Slow queries: 0  Opens: 26  Flush tables: 1  Open tables: 18  Queries per second avg: 0.179
--------------

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 | 
+------------------+
1 row in set (0.00 sec)

The LAST_INSERT_ID() is 3 which I can explain as being that it was the last
one allocated although not used.  The value of mysql_insert_id(), that is used
by STATUS is not correct though as it should only have the last value that was
successfully _USED_.

How to repeat:
mysql test

source lastidtest.sql

Suggested fix:
mysql_insert_id() should not return 3 as it was never actually used.
[19 Aug 2008 20:43] Matthew Lord 
More example output as I just realized that the original description was off:

mysql> CREATE TABLE `test` (
    -> `primary` int(10) unsigned NOT NULL auto_increment,
    -> `unique` int(10) NOT NULL default 0,
    -> `tochange` varchar(255) NOT NULL default '',
    -> PRIMARY KEY  (`primary`),
    -> UNIQUE KEY `unique` (`unique`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> INSERT INTO test SET `unique`=1, `tochange`='iwillchangethis'
    -> ON DUPLICATE KEY UPDATE `tochange`='itschanged';
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> STATUS;
--------------

Connection id:		2
Current database:	test
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		'/tmp/dupupdate5.sql'
Using delimiter:	;
Server version:		5.0.66-br26089-enterprise-gpl-log MySQL Enterprise Server (GPL)
Protocol version:	10
Connection:		Localhost via UNIX socket
Insert id:		1
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	latin1
Conn.  characterset:	latin1
UNIX socket:		/tmp/mysql.sock
Uptime:			1 hour 11 min 12 sec

Threads: 1  Questions: 1080  Slow queries: 0  Opens: 17  Flush tables: 1  Open tables: 9  Queries per second avg: 0.253
--------------

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 | 
+------------------+
1 row in set (0.00 sec)

mysql> 
mysql> INSERT INTO test SET `unique`=2, `tochange`='justasecondrow'
    -> ON DUPLICATE KEY UPDATE `tochange`='itschanged';
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> STATUS;
--------------

Connection id:		2
Current database:	test
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		'/tmp/dupupdate5.sql'
Using delimiter:	;
Server version:		5.0.66-br26089-enterprise-gpl-log MySQL Enterprise Server (GPL)
Protocol version:	10
Connection:		Localhost via UNIX socket
Insert id:		2
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	latin1
Conn.  characterset:	latin1
UNIX socket:		/tmp/mysql.sock
Uptime:			1 hour 11 min 12 sec

Threads: 1  Questions: 1084  Slow queries: 0  Opens: 17  Flush tables: 1  Open tables: 9  Queries per second avg: 0.254
--------------

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 | 
+------------------+
1 row in set (0.00 sec)

mysql> 
mysql> SELECT * FROM test;
+---------+--------+-----------------+
| primary | unique | tochange        |
+---------+--------+-----------------+
|       1 |      1 | iwillchangethis | 
|       2 |      2 | justasecondrow  | 
+---------+--------+-----------------+
2 rows in set (0.00 sec)

mysql> 
mysql> INSERT INTO test SET `unique`=1, `tochange`='iwillchangethis'
    -> ON DUPLICATE KEY UPDATE `tochange`='itschanged';
Query OK, 2 rows affected (0.00 sec)

mysql> 
mysql> STATUS;
--------------

Connection id:		2
Current database:	test
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		'/tmp/dupupdate5.sql'
Using delimiter:	;
Server version:		5.0.66-br26089-enterprise-gpl-log MySQL Enterprise Server (GPL)
Protocol version:	10
Connection:		Localhost via UNIX socket
Insert id:		1
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	latin1
Conn.  characterset:	latin1
UNIX socket:		/tmp/mysql.sock
Uptime:			1 hour 11 min 12 sec

Threads: 1  Questions: 1089  Slow queries: 0  Opens: 17  Flush tables: 1  Open tables: 9  Queries per second avg: 0.255
--------------

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 | 
+------------------+
1 row in set (0.00 sec)

mysql> 
mysql> SELECT * FROM test;
+---------+--------+----------------+
| primary | unique | tochange       |
+---------+--------+----------------+
|       1 |      1 | itschanged     | 
|       2 |      2 | justasecondrow | 
+---------+--------+----------------+
2 rows in set (0.00 sec)

mysql> 
mysql> INSERT INTO test SET `unique`=1, `tochange`='iwillchangethis'
    -> ON DUPLICATE KEY UPDATE `tochange`='itschanged';
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> STATUS;
--------------

Connection id:		2
Current database:	test
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		'/tmp/dupupdate5.sql'
Using delimiter:	;
Server version:		5.0.66-br26089-enterprise-gpl-log MySQL Enterprise Server (GPL)
Protocol version:	10
Connection:		Localhost via UNIX socket
Insert id:		3
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	latin1
Conn.  characterset:	latin1
UNIX socket:		/tmp/mysql.sock
Uptime:			1 hour 11 min 12 sec

Threads: 1  Questions: 1094  Slow queries: 0  Opens: 17  Flush tables: 1  Open tables: 9  Queries per second avg: 0.256
--------------

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 | 
+------------------+
1 row in set (0.00 sec)
[19 Aug 2008 20:49] Matthew Lord 
test case

Attachment: lastidtest.sql (text/x-sql), 971 bytes.
[20 Aug 2008 12:47] Kenny Gryp 
We were able to workaround this by first looking if it will be a duplicate and fetching the id.

But still, the documentation is very unclear about what it should output.
[21 Aug 2008 5:30] Sveta Smirnova 
Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Duplicate of bug #19243 which was fixed in version 5.1