MySQL Bugs: #27598: LAST_INSERT_ID returns wrong values

Bug #27598	LAST_INSERT_ID returns wrong values
Submitted:	2 Apr 2007 22:14	Modified:	5 Apr 2007 23:32
Reporter:	Mike Robinson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.37	OS:	Windows (Windows)
Assigned to:	Sveta Smirnova	CPU Architecture:	Any
Tags:	LAST_INSERT_ID

Description:
I believe that under these circumstances LAST_INSERT_ID returns spurious values.

One thread is inserting several rows into three related tables, all with auto-increment primary keys. After each insert a SELECT LAST_INSERT_ID is performed.

At the same time, another thread is doing a SELECT from the same tables.

If the thread doing the SELECT is not running, the LAST_INSERT_IDs are always correct. If the thread is running, the LAST_INSERT_IDs will invariable get invalid result, on one or more of the IDs.

FYI, I'm using JConnect 3.1.14

How to repeat:
I would like to provide some simple code to test this, but I have no idea how to simulate different threads from the command line. I hope that the description provided is clear enough for someone there to be able to do a test case easily.

Thank you for the report.

Please provide output of SHOW CREATE TABLE for table you use.

Table definitions of the three tables in use:

| mail_message | CREATE TABLE `mail_message` (
  `mail_message_id` int(11) NOT NULL auto_increment,
  `subject` varchar(100) NOT NULL,
  `from_person` varchar(50) NOT NULL,
  `content` varchar(500) default NULL,
  `tries` int(11) unsigned NOT NULL default '0',
  `initial_try` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `last_try` timestamp NOT NULL default '0000-00-00 00:00:00',
  `sent` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`mail_message_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 59392 kB; (`notification_id`) REFER `metalsmith' |

| mail_attachment | CREATE TABLE `mail_attachment` (
  `mail_attachment_id` int(11) NOT NULL auto_increment,
  `mail_message_id` int(11) NOT NULL,
  `attachment` varchar(200) NOT NULL,
  `attachment_mime` varchar(50) NOT NULL,
  PRIMARY KEY  (`mail_attachment_id`),
  KEY `FK_attachment_message` (`mail_message_id`),
  CONSTRAINT `FK_mail_attachment_1` FOREIGN KEY (`mail_message_id`) REFERENCES `mail_message` (`mail_message_id`) ON DEL
ETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

| recipient | CREATE TABLE `recipient` (
  `recipient_id` int(11) NOT NULL auto_increment,
  `mail_message_id` int(11) NOT NULL,
  `email_address` varchar(50) NOT NULL,
  PRIMARY KEY  (`recipient_id`),
  KEY `FK_recipient_mail_message` (`mail_message_id`),
  CONSTRAINT `FK_recipient_1` FOREIGN KEY (`mail_message_id`) REFERENCES `mail_message` (`mail_message_id`) ON DELETE CA
SCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 59392 kB' |

This might be a bug in the MySQL server or in the client library. InnoDB does not do much with LAST_INSERT_ID. A SELECT should not touch it at all.

Please also check that you are not mixing connection handles in your application.

Are you using prepared statements?

Regards,

Heikki

I am using Spring's jdbcTemplate for all interactions with the database.

The connection handles are handled by the jdbcTemplate.

Honestly, I thought I was submitting this to the mySQLServer bug database. I did not realize that I was somehow limiting it to InnoDb (or am I?). Do I need to submit it again w/o reference to InnoDb?

Mike, thank you for the feedback.

>Do I need to submit it again w/o reference to InnoDb?

Not. You (and we) can edit this bug report.

We can not repeat with 2 threads.

Please check that you are not mixing connection handles as Heikki Tuuri asked already and if you don't, provide more detailed instruction how to repeat described behaviour.

After some research into the use of Spring's jdbcTemplate, I think that the issue is there rather than the mySQL server. I believe I have unwittingly been using my DAO classes in a thread-unsafe manner. So, I'm closing this bug.