MySQL Connector/NET, connection pooling and LAST_INSERT_ID()

Question

User-1896913385 posted

In my .NET web application I use the following query to insert a new record into a table and get its auto-increment record ID back: "insert into mytable (mycolumn) values ('myvalue');select LAST_INSERT_ID();"

This works fine on my local development computer and in a shared hosting environment I tested this in.

This week, I installed the web application on a server of another shared hosting provider, and there that query doesn't work correctly. The record is inserted, bus unless I explicitly specify "pooling=false" in the connection string, I always get value 0 (zero) back from "select LAST_INSERT_ID()".

The hosting provider states that this is normal pooling behavior. They say my query will always be broken up in 2 parts, "insert into mytable (mycolumn) values ('myvalue');" and "select LAST_INSERT_ID();", and MySQL will use 2 different connections from the pool to execute them. And according to them, that's why "select LAST_INSERT_ID();" always returns 0.

I find that hard to believe: 1) it always worked correctly for me in my test environments, but it doesn't work on this last server, and 2) if connection pooling was causing this, then noone could ever reliably use "select LAST_INSERT_ID();"

Can anyone here shed some light? Thanks in advance.

Answer 1

User1824551582 posted

MySQL server has maximum number of connection pool that it can allow.

Therefore when you make a connection from .NET connector, it opens a pool which is stored on the server. Then setting pool=false will opt out the pool.

Since you have specified not to use pooling, each query would be executed as distinct parameters. I think the better way to run multiple query in an application pool is to put them in a transaction, which could be rolled back if any of the queries fails.

But to use this, your database configuration must be set to Innodb