Connection pools and releasing temporary tables on return

Question

I'm using MySQL's temporary tables with connection pooling.

Normally, temporary tables created in a session stay in that session and are dropped when the connection is closed. However, since I'm using connection pooling and physical connections aren't actually closed when java.sql.Connection.close() is called, I'm finding temporary tables staying around and affecting the next session.

This causes resource leakage on the DB since temporary tables aren't immediately freed, as well as name clashes due to colliding table names.

Intuitively, I expected a clean slate when I borrow a connection. How does one achieve this?

• There doesn't seem to be an equivalent SQL Server's sp_reset_connection for MySQL.
• Even if there was, I can't see how dbcp2 can be configured to call it when the connection is returned.
• Always closing the physical connection when returning to pool could work, but I can't see how dbcp2 can be configured to do that either.

Aside from temporary tables, this problem also affects:

• User-Defined Variables (i.e. SET @foo = 1) scoping
• Releasing of User level locks (i.e. GET_LOCK())

Answer 1

use MySqlConnection.ClearPool(connection); can work. I have similar issues to this for GET_LOCK(), MySqlConnection.ClearPool(connection) can resolve this issue.