MySQL connection pooling and creating temporary table.

Question

I already set up MySQL connection pool in Glassfish using JNDI. I only execute one query at a time but use the same sql instance. Everything seems to work fine except creating temporary tables and use them. In short, even though after I create a temporary table, insert query does not work since the table does not exist. Are there any configurations I might look into? Is it possible to create temp table and use it within multiple statements. Thank you in advance!

Answer 1

Temporary tables are only available within the connection/session you created them in. You can use them within multiple statements, but you can't switch to another connection in the pool and expect the temporary table to be there.