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.
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).
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.)