Connection pools and free temporary tables upon return

Question

I use MySQL temporary tables with pooling .

Typically, temporary tables created in a session remain in that session and are deleted when the connection is closed. However, since I use the connection pool, and the physical connections do not actually close when java.sql.Connection.close() called, I find temporary tables that remain around and affect the next session.

This leads to a leak of resources in the database, since temporary tables are not immediately freed, as well as name conflicts due to collisions with tables.

Intuitively, I expected a clean slate when I take the connection. How to achieve this?

• It seems that for MySQL is not the equivalent of SQL Server sp_reset_connection .
• Even if it were, I don’t see how dbcp2 can be configured to call it when the connection returns.
• Always closing a physical connection when returning to the pool may work, but I don’t see how dbcp2 can be configured for this.

In addition to temporary tables, this problem also affects:

• Custom variables (i.e. SET @foo = 1 ) scoping
• Release user level locks (i.e. GET_LOCK() )

Answer 1

use MySqlConnection.ClearPool (connection); can work. I have similar problems for GET_LOCK (), MySqlConnection.ClearPool (connection) can solve this problem.