INSERT doesn't work when followed by SELECT LAST_INSERT_ID

Question

There are a lot of questions about LAST_INSERT_ID()

In my case, issue is:

When INSERT is followed by SELECT LAST_INSERT_ID() there are no records being inserted

INSERT INTO sequences (state)  VALUES (0);
select LAST_INSERT_ID();
>>> 80  // nothing is added to DB

INSERT on it's own works OK

INSERT INTO sequences (state)  VALUES (0);
>>>
select LAST_INSERT_ID();
>>> 81 // row is inserted

For testing I am using SequelPro, DB is Amazon's RDS MySQL. Same issue happens when I use Python's MySQLdb module.

Ideally I want to insert row, get back ID of it for future identification and use.

score 3 · Accepted Answer · answered Mar 07 '19 at 22:34

3

You should run one query at a time. Most SQL interfaces don't allow multiple queries.

MySQL allows a single query to be terminated by ; but if there's any words following the ; (except for a comment), it's be a syntax error, which will make the whole request fail. So the INSERT won't run either.

MySQL does have a connection option to allow multi-query, but it's not active by default. See https://dev.mysql.com/doc/refman/8.0/en/c-api-multiple-queries.html

There's really no reason to use multi-query. Just run the SELECT LAST_INSERT_ID() as a separate query following the INSERT. As long as you use the same connection, it'll give you the right answer.

answered Mar 07 '19 at 22:34

Bill Karwin

538,548
86
673
828

Will that create race conditions? I am running 400+ nodes in parallel, what if one node INSERT's right after the other – aaaaa says reinstate Monica Mar 07 '19 at 22:36
@aaaaaa why are you still in doubt? `LAST_INSERT_ID()` is thread safe it's on a connection base .. The [manual](https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id) says "This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions. " it will be okay when all nodes use the same connection or separated connections and not to forgot **race conditions free** – Raymond Nijland Mar 07 '19 at 22:45
1

Yes, LAST_INSERT_ID() is scoped to your own session. It wouldn't be much use to anyone if it weren't! Besides, if it weren't thread-safe, then putting it together with the INSERT in one multi-query wouldn't help. It would still be subject to race conditions. – Bill Karwin Mar 07 '19 at 23:03

INSERT doesn't work when followed by SELECT LAST_INSERT_ID

1 Answers1