Hi Björn,
I tested what you did on one of my systems, running 4.0.20:
mysql> create table parent (
-> ID int unsigned not null auto_increment,
-> value varchar(50),
-> primary key (ID)
-> ) engine=InnoDB;
create table child (
ID int unsigned not null auto_increment,
parent_ID int unsigned not null,
value varchar(50),
primary key (ID),
key (parent_ID),
foreign key (parent_ID) references parent (ID)
) engine=InnoDB;
insert into parent (value) values ('a');Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> create table child (
-> ID int unsigned not null auto_increment,
-> parent_ID int unsigned not null,
-> value varchar(50),
-> primary key (ID),
-> key (parent_ID),
-> foreign key (parent_ID) references parent (ID)
-> ) engine=InnoDB;
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> insert into parent (value) values ('a');
Query OK, 1 row affected (0.02 sec)
mysql> select last_insert_ID();
+------------------+
| last_insert_id() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
*********************************this value is as
expected*********************************
mysql> insert into child (parent_ID, value) values (last_insert_ID(),
'b1'), (last_insert_ID(), 'b2'), (last_insert_ID(), 'b3');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select last_insert_ID();
+------------------+
| last_insert_id() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
*********************************
this value is interesting -- we've inserted 3 items into the child
table, and the last_insert_ID() is still 1. So I ran the command
again:
*********************************
mysql> insert into child (parent_ID, value) values (last_insert_ID(),
'b1'), (last_insert_ID(), 'b2'), (last_insert_ID(), 'b3');
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select last_insert_ID();
+------------------+
| last_insert_id() |
+------------------+
| 4 |
+------------------+
1 row in set (0.00 sec)
*********************************
this value is interesting also -- given these 2 results, MySQL only
finds the value for last_insert_ID once per statement, instead of once
per actual new row. I have 6 rows in my table, and the last_insert_id
is 4.
*********************************
mysql> insert into child (parent_ID, value) values (last_insert_ID(),
'b1'), (last_insert_ID(), 'b2'), (last_insert_ID(), 'b3');
ERROR 1216: Cannot add or update a child row: a foreign key constraint fails
mysql> select last_insert_ID();
+------------------+
| last_insert_id() |
+------------------+
| 7 |
+------------------+
1 row in set (0.00 sec)
mysql> select * from child;
+----+-----------+-------+
| ID | parent_ID | value |
+----+-----------+-------+
| 1 | 1 | b1 |
| 2 | 1 | b2 |
| 3 | 1 | b3 |
| 4 | 1 | b1 |
| 5 | 1 | b2 |
| 6 | 1 | b3 |
+----+-----------+-------+
6 rows in set (0.00 sec)
*********************************
This makes sense, because it's trying to insert a parent_ID of 4,
which is the current value of last_insert_id. Nothing was inserted,
and yet the last_insert_ID() changed to 7, even though it didn't even
insert anything with the value of 7 into the child table.
Granted, I tested this on MySQL 4.0.20, but it seems consistent with
what you get. As well, if you're inserting multiple parent rows, the
last_insert_ID is the first ID that is inserted:
mysql> insert into parent (value) VALUES ('b'),('c'),('d');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select last_insert_ID();
+------------------+
| last_insert_id() |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec)
mysql> select * from parent;
+----+-------+
| ID | value |
+----+-------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+----+-------+
4 rows in set (0.00 sec)
We would expect the value of last_insert_ID() to be 4, not 2.
I had to dig into the documentation to find this, but it's in there:
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html
"LAST_INSERT_ID() LAST_INSERT_ID(expr)
Returns the first automatically generated value that was set for an
AUTO_INCREMENT column by the last INSERT or UPDATE query to affect
such a column."
-Sheeri
On 11/4/05, Björn Persson <[EMAIL PROTECTED]> wrote:
> Hi everybody!
>
> It seems like different instances of MySQL do things in different order, with
> rather unfortunate effects on the last_insert_ID function.
>
> Consider the following tables and insertions:
>
> create table parent (
> ID int unsigned not null auto_increment,
> value varchar(50),
> primary key (ID)
> ) engine=InnoDB;
>
> create table child (
> ID int unsigned not null auto_increment,
> parent_ID int unsigned not null,
> value varchar(50),
> primary key (ID),
> key (parent_ID),
> foreign key (parent_ID) references parent (ID)
> ) engine=InnoDB;
>
> insert into parent (value) values ('a');
>
> insert into child (parent_ID, value) values
> (last_insert_ID(), 'b1'), (last_insert_ID(), 'b2'), (last_insert_ID(), 'b3');
>
> The intention is of course that all three child rows should reference the
> parent row that was inserted. This will work if the server first evaluates
> all the calls to last_insert_ID and then starts inserting the rows. I have
> one MySQL 4.0 server where this appears to work consistently.
>
> On some MySQL 4.1 servers I sometimes get the error "a foreign key constraint
> fails". On one server it sometimes works and sometimes not. What I think
> happens is that the server evaluates the first call to last_insert_ID and
> inserts the first row (b1), and then starts processing the b2 row, evaluates
> last_insert_ID again, and gets the ID of the b1 row.
>
> I thought I had read somewhere in the documentation that functions are
> evaluated before data is inserted, and as this never failed on my development
> server I relied on it. Then it failed on the production server. Ouch!
>
> So my questions are:
> 1: Is this expected behaviour? That is, should I expect some function calls to
> be evaluated after some rows have already been inserted and auto_increment
> counters have been incremented, or are functions supposed to be evaluated
> before insertion starts?
> 2: Whichever order a query is processed in, wouldn't it be better to always do
> things in the same order?
>
> Björn Persson
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
