Description:
In a stored procedure, if a result set is selected from a temporary table and then deleted, a MySQLSyntaxErrorException occurs from the driver trying to perform SHOW FULL COLUMNS on the non-existent temporary table.
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'tmp_table' doesn't exist
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2536)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2465)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1383)
at com.mysql.jdbc.Field.getCollation(Field.java:465)
at com.mysql.jdbc.ResultSetMetaData.isCaseSensitive(ResultSetMetaData.java:558)
at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:701)
at com.sun.rowset.CachedRowSetImpl.populate(CachedRowSetImpl.java:621)
-- Snip --
How to repeat:
Connection Settings:
-noAccessToProcedureBodies=true
-autoReconnect=true
-connectTimeout=20000
-maxReconnects=5
-initialTimeout=1
-emulateUnsupportedPstmts=true
-useServerPrepStmts=true
-jdbcCompliantTruncation=false
-cacheCallableStmts=false
-elideSetAutoCommits=true
-cachePrepStmts=false
-cacheResultSetMetadata=false
-zeroDateTimeBehavior=convertToNull
-profileSQL=true
In a stored procedure:
1. Create a temporary table.
2. Insert some values into the temporary table.
3. Select the values back as a result set.
4. Delete the temporary table.
The exception is actually thrown when populating a CachedRowSet using CachedRowSetImpl.populate() and passing the ResultSet from the CallableStatement to it.
If you were to remove the deletion of the temporary table at the end of the procedure, then the resulting SQL Profile looks something like this (notice how it does the SHOW FULL COLUMNS call on a temporary table). Of course this works fine because the temporary table still exists.
Profiler Event: [QUERY] at com.epoch.core.dao.SQLAdapter.exec(SQLAdapter.java:811) duration: 95 ms, connection-id: 1687, statement-id: 1930, resultset-id: 1930, message: CALL mySP()
Profiler Event: [FETCH] at com.epoch.core.dao.SQLAdapter.exec(SQLAdapter.java:811) duration: 1 ms, connection-id: 1687, statement-id: 1930, resultset-id: 1930
Profiler Event: [QUERY] at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:701) duration: 96 ms, connection-id: 1687, statement-id: 1932, resultset-id: 1931, message: SHOW FULL COLUMNS FROM `tmp_table`
Profiler Event: [FETCH] at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:701) duration: 1 ms, connection-id: 1687, statement-id: 1932, resultset-id: 1931
Without being able to clean up the temporary table at the end of the procedure, it will stay in memory until the connection is closed (which is very bad in a connection pooling scheme).
Description: In a stored procedure, if a result set is selected from a temporary table and then deleted, a MySQLSyntaxErrorException occurs from the driver trying to perform SHOW FULL COLUMNS on the non-existent temporary table. com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'tmp_table' doesn't exist at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) at java.lang.reflect.Constructor.newInstance(Constructor.java:513) at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) at com.mysql.jdbc.Util.getInstance(Util.java:381) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2536) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2465) at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1383) at com.mysql.jdbc.Field.getCollation(Field.java:465) at com.mysql.jdbc.ResultSetMetaData.isCaseSensitive(ResultSetMetaData.java:558) at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:701) at com.sun.rowset.CachedRowSetImpl.populate(CachedRowSetImpl.java:621) -- Snip -- How to repeat: Connection Settings: -noAccessToProcedureBodies=true -autoReconnect=true -connectTimeout=20000 -maxReconnects=5 -initialTimeout=1 -emulateUnsupportedPstmts=true -useServerPrepStmts=true -jdbcCompliantTruncation=false -cacheCallableStmts=false -elideSetAutoCommits=true -cachePrepStmts=false -cacheResultSetMetadata=false -zeroDateTimeBehavior=convertToNull -profileSQL=true In a stored procedure: 1. Create a temporary table. 2. Insert some values into the temporary table. 3. Select the values back as a result set. 4. Delete the temporary table. The exception is actually thrown when populating a CachedRowSet using CachedRowSetImpl.populate() and passing the ResultSet from the CallableStatement to it. If you were to remove the deletion of the temporary table at the end of the procedure, then the resulting SQL Profile looks something like this (notice how it does the SHOW FULL COLUMNS call on a temporary table). Of course this works fine because the temporary table still exists. Profiler Event: [QUERY] at com.epoch.core.dao.SQLAdapter.exec(SQLAdapter.java:811) duration: 95 ms, connection-id: 1687, statement-id: 1930, resultset-id: 1930, message: CALL mySP() Profiler Event: [FETCH] at com.epoch.core.dao.SQLAdapter.exec(SQLAdapter.java:811) duration: 1 ms, connection-id: 1687, statement-id: 1930, resultset-id: 1930 Profiler Event: [QUERY] at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:701) duration: 96 ms, connection-id: 1687, statement-id: 1932, resultset-id: 1931, message: SHOW FULL COLUMNS FROM `tmp_table` Profiler Event: [FETCH] at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:701) duration: 1 ms, connection-id: 1687, statement-id: 1932, resultset-id: 1931 Without being able to clean up the temporary table at the end of the procedure, it will stay in memory until the connection is closed (which is very bad in a connection pooling scheme).