Reset/clear MySQL user variables

Question

I have bunch of MySQL queries that use temporary tables to split complex/expensive queries into small pieces.

create temporary table product_stats (
     product_id int 
    ,count_vendors int 
    ,count_categories int
    ,...
);

-- Populate initial values.
insert into product_stats(product_id) select product_id from product;

-- Incrementally collect stats info.
update product_stats ... join vendor ... set count_vendors = count(vendor_id);
update product_stats ... join category... set count_categories = count(category_id);
....

-- Consume the resulting temporary table.
select * from product_stats;

The problem is that, as I use connection pool, these tables are not cleared even if I close the java.sql.Connection.

I can manually remove them (drop temporary table x;) one by one before executing the needed queries, but that may take place for mistakes.

Is there a way (JDBC/MySQL , API/configuration) to reset all the temporary tables created within the current session without closing the database connection (as you know, I'm not reffering to java.sql.Connection.close()), so that I can still use the advantages that provides connection pool?

Edited:

It seems that only from MySQL version 5.7.3 they started imlpementing the "reset connection" feature. (Release note: https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-3.html) However, I will not use it for the moment because version 5.7 is still on a development release.

either you let mysql do its own cleanup by closing the connection, or you clean up every single variable/table you created yourself. there's no "reset the environment to how it was at the beginning" functionality in mysql. — Marc B, Jun 23 '15 at 20:47
Nice find on the **`mysql_reset_connection`** function introduced in MySQL 5.7.3. — spencer7593, Jun 24 '15 at 01:08

spencer7593 · Accepted Answer · 2015-06-24T01:07:44.243

Q: Is there a way (JDBC/MySQL , API/configuration) to reset all the temporary tables created within the current session without closing the database connection.

A: No. There's no "reset" available. You can issue DROP TEMPORARY TABLE foo statements within the session, but you have to provide the name of the temporary table you want to drop.

The normative pattern is for the process that created the temporary table to drop it, before the connection is returned to the pool. (I typically handle this in the finally block.)

If we are expecting other processes may leave temporary tables in the session (and to be defensive, that's what we expect), we typically do a DROP TEMPORARY TABLE IF EXISTS foo before we attempt to create a temporary table.

EDIT

The answer above is correct for MySQL up through version 5.6.

@mr.Kame (OP) points out the new mysql_reset_connection function (introduced in MySQL 5.7.3).

Reference: 22.8.7.60 mysql_reset_connection() http://dev.mysql.com/doc/refman/5.7/en/mysql-reset-connection.html

Looks like this new function achieves nearly the same result as we'd get by disconnecting from and reconnecting to MySQL, but with less overhead.

(Now I'm wondering if MariaDB has introduced a similar feature.)

I was wondering about this feature because of these pages: https://github.com/felixge/... that talks about a new "reset connection" packet, and http://dev.mysql.com/doc/... that talks about "ConnectionReset" pooling option. — mr.Kame, Jun 23 '15 at 21:39
I will do a little more research, and then mark this as an answer if nothing comes up. — mr.Kame, Jun 23 '15 at 21:49

Reset/clear MySQL user variables

1 Answers1