Scott,
The bottom line is that LAST_INSERT_ID() isn't guaranteed to be unchanged by
an INSERT IGNORE that doesn't insert anything, so you have to do something
else.
You need to test that a row was, in fact, inserted by the first INSERT
IGNORE and, if not, do not execute the second INSERT.
If that's difficult, and you really want to use just SQL statements, you
could have the second insert test that there is a row in the first table
with id=LAST_INSERT_ID(), perhaps using INSERT ... SELECT. I'd worry,
however, that you don't really have a guarantee of the value of
LAST_INSERT_ID().
Here's what's probably happening. (I haven't looked at the internals, but
it's a close enough model.) On the second time you do the first INSERT
IGNORE,
- It reserves id 129 and remembers that as the last insert id
- It does the INSERT
- The insert fails, but the error is ignored
However, the last insert id remains 129. (You have inserted zero records,
the first of which has a key of 129.)
All you are guaranteed is that
(a) right after you insert something, and before you try to insert
anything else, LAST_INSERT_ID() returns the first id inserted by that INSERT
statement, if any.
(b) for any thread, the id's increase
(c) no two threads ever get the same id
However, LAST_INSERT_ID() is not quite transactional, even if you are using
transactional tables. Once an id has been reserved, it's never given back,
even if the SQL statement is rolled back--so you can get gaps. The reason
for this is so that two threads can independently insert records with an
auto_increment key without either one having to wait for the other. This
can make a substantial difference to the concurrency of transactions.
HTH
Bill
--------- original message follows ----------
Date: Thu, 22 Jul 2004 22:56:12 -0700
Subject: Last insert id problem/bug
From: Scott Haneda <[EMAIL PROTECTED]>
I am not sure this is just how it is, my issue, or a bug, I searched the
bugs database, but don't really know what to call this in order to properly
look up the info:
MySQL 4.0.18-standard
CREATE TABLE `addresses` (
`id` int(11) NOT NULL auto_increment,
`user_id` int(11) NOT NULL default '0',
`first_name` varchar(48) NOT NULL default '',
`last_name` varchar(48) NOT NULL default '',
`email_address` varchar(48) NOT NULL default '',
`updated` timestamp(14) NOT NULL,
`added` timestamp(14) NOT NULL default '00000000000000',
PRIMARY KEY (`id`),
UNIQUE KEY `emailuid` (`email_address`,`user_id`)
) TYPE=InnoDB PACK_KEYS=0 COMMENT='foo';
CREATE TABLE addresses_incampaign (
id int(11) NOT NULL auto_increment,
user_id int(11) NOT NULL default '0',
address_id int(11) NOT NULL default '0',
campaign_id int(11) NOT NULL default '0',
updated timestamp(14) NOT NULL,
added timestamp(14) NOT NULL default '00000000000000',
PRIMARY KEY (id),
UNIQUE KEY address_id (address_id)
) TYPE=InnoDB PACK_KEYS=0 COMMENT='bar';
As you can see, I have a unique key on addresses.email_address and
addresses.user_id, note the unique key on address_id in the second table.
I insert the following data TWICE:
INSERT IGNORE INTO `addresses`
(`user_id`,`first_name`,`last_name`,`email_address`) VALUES (1, 'Claire',
'Altman', '[EMAIL PROTECTED]');
INSERT IGNORE INTO `addresses_incampaign` (`user_id`, `address_id`,
`campaign_id`) VALUES (1,LAST_INSERT_ID(), 2);
Notice I am picking up the last_insert_id in the second insert, I then
insert that data again, since I am using insert ignore and since there are
unique keys on this, I should only still have one records in each table.
Results are as follows:
mysql> select * from addresses;
+-----+---------+------------+-----------+---------------+
| id | user_id | first_name | last_name | email_address |
+-----+---------+------------+-----------+---------------+
| 148 | 1 | Claire | Altman | [EMAIL PROTECTED] |
+-----+---------+------------+-----------+---------------+
1 row in set (0.00 sec)
mysql> select * from addresses_incampaign ;
+-----+---------+------------+-------------+
| id | user_id | address_id | campaign_id |
+-----+---------+------------+-------------+
| 128 | 1 | 148 | 2 |
| 129 | 1 | 149 | 2 | <- SOULD NOT HAVE HAPPENED
+-----+---------+------------+-------------+
2 rows in set (0.00 sec)
Record 129 should not have been inserted, it should not have ever gotten a
insert ID back from mysql, mysql said back 149, which is in fact the next
record, but no new record was added to addresses so it really is not valid.
Workarounds and suggestions?
--
-------------------------------------------------------------
Scott Haneda Tel: 415.898.2602
http://www.newgeo.com Fax: 313.557.5052
[EMAIL PROTECTED] Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
