4

In a project of mine, I had to get the last inserted ID from the database so that I can sync newly inserted data with in memory data without doing a SELECT * FROM my_db; query i.e. I am doing a SELECT * FROM my_db WHERE id=new_index.

Now, I know that MySQL, which I'm using, supports the query select last_insert_id();, so I figured I would just make that command and execute it. But upon digging into C# (the programming language that I'm using for the project) I've discovered that the MySqlCommand has a LastInsertedId field.

Now what puzzles me is that I expected the getter of the field to just do the same select last_insert_id(); query, but upon looking at the logs from MySQL, I see no such query being made at all.

So my question is, how does MySqlCommand.LastInsertedId know what ID was assigned to the tuple that I'm inserting when the ID is auto generated by the MySQL server?

ILA
  • 83
  • 9
  • Have you enabled logging **all** the queries executed on that server? – e4c5 Dec 29 '16 at 14:56
  • @e4c5 I executed the `SET GLOBAL general_log = 'ON';` command so yes. Anyway, I see that I have my question answered. Thanks for the interest in the subject anyway. – ILA Dec 30 '16 at 16:36

2 Answers2

5

When the MySQL server responds to the client software (in your case it's Connector/Net) after an UPDATE query, it sends a so-called OK_Packet over the tcp (or socket) connection. That packet contains the last_insert_id value.

On the server it's available incidental to any INSERT query without another query, and so the wire-protocol handler just throws it in to the response packet.

The client software extracts that value from the packet and presents it to your application software on the LastInsertedId property.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
3

If MySQL successfully executes a query requested by a client, then MySQL send an OK_Packet as a response.

In the payload of the OK_Packet MySQL includes the last inserted id (see documentation linked above):

Type        | Name           | Description
------------|----------------|-----------------------------------
int<1>      | header         | [00] or [fe] the OK packet header 
int<lenenc> | affected_rows  | affected rows 
int<lenenc> | last_insert_id | last insert-id
...

MySQL bug #65452 indicates that LastInsertId method reads the data out of a packet:

public int GetResult(ref int affectedRow, ref int insertedId)
{
...
insertedId = (int)packet.ReadFieldLength();
...
}

This indicates that the LastInsertId gets the value from the OK_Packet. On the server no select last_insert_id() is executed to populate this value into the OK_packet, this is why you do not see any such query in the query log.

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • Thank You very much for a detailed reply. You've really explained everything I wanted to know. The only reason why I chose @O. Jones answer as accepted is because he answered quicker and said basically the same thing. – ILA Dec 30 '16 at 16:44