2

According to mysql doc: https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-usagenotes-last-insert-id.html

At times, it can be tricky to use the SELECT LAST_INSERT_ID() query, as that function's value is scoped to a connection. So, if some other query happens on the same connection, the value is overwritten. On the other hand, the getGeneratedKeys() method is scoped by the Statement instance, so it can be used even if other queries happen on the same connection, but not on the same Statement instance.

First, I consider LAST_INSERT_ID(). The SQL function LAST_INSERT_ID() is connection safe, but not session/transaction/statement safe. It can't be used in production, because in real environment multiple session/transaction/statement in one connection is very common.

Then getGeneratedKeys() using JDBC. When I'm using getGeneratedKeys() in Java. I want to see what it does in database. I try to track the SQL statement with the following statements after a simple insert into a demo table with auto increase primary key using JDBC:

SET GLOBAL log_output = 'TABLE'; 
SET GLOBAL general_log = 'ON';
SELECT * FROM mysql.general_log;

I'm sure the new row is correctly inserted and getGeneratedKeys() brings the auto-incremented id back. However, I find nothing but just an insert statement which JDBC executed before, and some static data like "SELECT database(),version()...". Now, conclusion is, getGeneratedKeys() doesn't execute any SQL statement to get auto-incremented id. Then I find another possibility, I debug into call stacks, see JDBC get auto-incremented id from an object called OkPacket. It has a property called last_insert_id. Here I find it finally.

My questions are:

  1. Is there really no way to get a STATEMENT SAFE (at least transaction safe) auto-incremented id using pure SQL statement (without JDBC)?
  2. How does OkPacket work under hood? How does it get a statement safe auto increased id? Maybe it calls some low level C function in MySQL driver or MySQL server/client protocol?
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Jayesslee
  • 67
  • 6

1 Answers1

2

MySQL has an API that clients use to communicate commands and get results.

Actually, it has two forms of this API. One is called the "SQL protocol" in which statements are sent as strings like SELECT * FROM mytable, etc. The other form is called the "binary protocol" where commands are sent using some byte that the server recognizes, even though they are not human-readable strings.

Some commands can be executed by either the SQL protocol or the binary protocol. For example, START TRANSACTION, COMMIT, PREPARE... there are textual SQL statements for these commands, but there are also non-textual ways for the API to invoke these commands.

You can certainly query SELECT LAST_INSERT_ID(); and get the most recent generated id, but only the most recent. Another INSERT statement will overwrite this value, as you read.

The OkPacket is filled in by the binary protocol. That is, the MySQL Server returns an OkPacket with several pieces of metadata about any statement execution.

See https://github.com/mysql/mysql-connector-j/blob/release/8.0/src/main/protocol-impl/java/com/mysql/cj/protocol/a/result/OkPacket.java#L55

The Ok Packet includes the following:

  • Affected row count (if any)
  • Last insert id (if any)
  • Status flags
  • Warning count (if any)
  • String with error message (if any)

The MySQL Server code that documents the OK Packet is unusually thorough with examples:

https://github.com/mysql/mysql-server/blob/8.0/sql/protocol_classic.cc#L665-L838

There's no way to fetch the OK Packet for earlier SQL statements. The client must save the result immediately after a statement execution. In object-oriented code such as the JDBC driver, it makes sense to store that in the NativeResultset object: https://github.com/mysql/mysql-connector-j/blob/release/8.0/src/main/protocol-impl/java/com/mysql/cj/protocol/a/result/NativeResultset.java#L77-L82

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828