5

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:

Community
  • 1
  • 1
antak
  • 19,481
  • 9
  • 72
  • 80
  • *"Always closing the physical connection when returning to pool could work"* ...that's a contradition in terms, isn't it? If the connection is closed, it's not going back to the pool. You could try to force the pool to release the db connection when you're done by issuing the query `ROLLBACK RELEASE;` That almost certainly will do what you're trying to do, but the side effects will be interesting and pooling becomes a bit pointless. Fundamentally, with pooling, your code has to be sure it does its own initialization, `DROP TEMPORARY TABLE IF EXISTS...` before creating the table, etc. – Michael - sqlbot Dec 28 '16 at 12:08

1 Answers1

-1

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

Jeff
  • 1