Is LAST_INSERT_ID Pool Connection Safe?

Hi everyone and a happy new year!I am working with the plain old mysql package in a node-express environment, and I am using mysql.createPool({...}) to create a connection (ensuring it is done only once in the runtime of the application). Previous empirical studies done by me through jMeter have proved that running multiple statements in the same query (constructing a query string that has multiple statements separated by semicolon) and using one query per connection (doing a pool.getConnection() for every query) is the most economical (time-wise) and safe.Now, my use case is that I want to insert a record in a table, with only one column populated based on request data and the rest initialized to defaults. The table id is an auto-increment primary key, and the same data can be inserted again and again, each time a new id will be generated, meaning there is no way to identify a record except by its auto-generated Primary Key id. I would also like to return to the client the entire newly inserted row, meaning I need to query the table again and get the last inserted record.Since each query is going to contain one insert and one select statement and both are running on the same unique connection, the most logical thing would be to use last_insert_id() in my select statement, and since it is documented to be thread safe, there shouldn't be a problem. But according to a comment on this thread,"... Connection pools will behind-the-scenes hold on to connections without closing them so they can be re-used by other components to save performance. While the last_insert_ID() will work for you if you do not use a connection pool and manage your own connections on a per transaction basis, it is not a solution for those utilizing connection pooling. "Is the above point really valid? Wouldn't the last_insert_id() be generated afresh even if a connection from the pool is reused? Are there alternatives ways to what I am trying to achieve (except switching to PostgreSQL)Thanks a lot in advance!

Submitted January 01, 2019 at 07:32AM by enkayjee