Undocumented support of LAST_INSERT_ID in OK Packet

Help
1

My project just ran into a case where we needed the resulting ID returned from an insert/update query. There is a page on your docs for LAST_INSERT_ID but the described usage is as a function. We found that the insert/update queries we actually returning the id of the just created/updated row, without any invocation of LAST_INSERT_ID(). After doing some research it was found that this behavior is actually a part of the MySQL protocol, specifically the OK_PACKET. We were wondering if we could get some clarification on this behavior, specifically if we shouldn’t rely on MemSQL always supporting this behavior. Any information you can provide would be very helpful, thank you!

For some extra insight into the project we are using Knexjs with MySQL as a driver.

2

Yes, since we are MySQL wire protocol compatible, you can rely on LAST_INSERT_ID being there inside the packet (You can refer to MySQL documentation for more on their wire protocol though I don’t remember on top of my head which MySQL wire protocol do we support. This link might be helpful Client and Driver Downloads · SingleStore Documentation) Just make sure to take note of all Remarks in this page SingleStoreDB Cloud · SingleStore Documentation such as this:

LAST_INSERT_ID works with UPDATE/INSERT statements, but it may not work as expected with SELECT statements because of the distributed nature of MemSQL.

and others.

1 Like