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]
