| Bug #15876 | ArrayIndexOutOfBoundsException processing multiple-statement query | ||
|---|---|---|---|
| Submitted: | 20 Dec 2005 4:06 | Modified: | 5 Jan 2006 21:48 |
| Reporter: | Stuart Friedberg | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / J | Severity: | S3 (Non-critical) |
| Version: | 3.1.12 | OS: | Windows (Windows 2000 w/SP 4) |
| Assigned to: | Vasily Kishkin | CPU Architecture: | Any |
[20 Dec 2005 4:37]
Stuart Friedberg
Problem occurs with Connector/J 3.2.0-alpha, as well. Line numbers change
slightly, but it's essentially the same stack trace.
D:\Work\Java\net\saf3\biblio>java net.saf3.biblio.Test
Exception: Error during query: Unexpected Exception: java.lang.ArrayIndexOutOfBo
undsException message given: 8
Nested Stack Trace:
** BEGIN NESTED EXCEPTION **
java.lang.ArrayIndexOutOfBoundsException
MESSAGE: 8
STACKTRACE:
java.lang.ArrayIndexOutOfBoundsException: 8
at com.mysql.jdbc.ByteArrayBuffer.readInt(ByteArrayBuffer.java:224)
at com.mysql.jdbc.MysqlIO.unpackField(MysqlIO.java:607)
at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:414)
at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:1962)
at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1419)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1728)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2988)
at com.mysql.jdbc.Connection.commit(Connection.java:2161)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.ibatis.common.jdbc.SimpleDataSource$SimplePooledConnection.invoke
(SimpleDataSource.java:946)
at $Proxy0.commit(Unknown Source)
at com.ibatis.sqlmap.engine.transaction.jdbc.JdbcTransaction.commit(Jdbc
Transaction.java:66)
at com.ibatis.sqlmap.engine.transaction.TransactionManager.commit(Transa
ctionManager.java:83)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.commitTransactio
n(SqlMapExecutorDelegate.java:759)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.commitTransaction(Sql
MapSessionImpl.java:133)
at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.commitTransaction(SqlM
apClientImpl.java:110)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.autoCommitTransa
ction(SqlMapExecutorDelegate.java:866)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert(SqlMapExe
cutorDelegate.java:451)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.insert(SqlMapSessionI
mpl.java:81)
at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.insert(SqlMapClientImp
l.java:58)
at net.saf3.biblio.Test.main(Test.java:265)
** END NESTED EXCEPTION **
[20 Dec 2005 6:43]
Stuart Friedberg
OK, I figured out what's causing the problem. The two problematic sub-statements were generating SQL errors. The first one produced "Every derived table must have its own alias", and the second one produced "Duplicate entry '2-1' for key 1". When I cleaned up the SQL issues, I stopped getting the bad-index exception. I reproduced the query manually (not using iBATIS) and fed it through Statement.executeUpdate(). The SQL errors neither threw an SQLException nor the bad-index exception. Instead, the errors were returned through Statement.getWarnings(). Because this problem has to do with error-handling, I am downgrading severity from S2 to S3. It looks like there are a couple of manifestations here. First, as called by iBATIS, SQL errors in a multi-statement query throw the bad-index exception. Second, as called manually, SQL errors in a multi-statement query do _not_ throw an SQLException, but show up as SQLWarnings.
[20 Dec 2005 6:44]
Stuart Friedberg
Correction: the second sub-statement was generating "Table 'biblio.TGN_delta' doesn't exist"
[31 Dec 2005 1:39]
Stuart Friedberg
I finally got iBATIS to dump out its query as submitted. It was using a prepared statement, while I used a simple statement. In later investigations, I was getting erratic, spurious syntax errors in my compound statements (still using iBATIS). These would come or go as I re-ordered simple SQL statements within the compound statement. To gain reliability, I finally gave in and broke my compound statements into a sequence of single statements. Trawling through the bug database, I see bug 9121 which emphasizes mySQL prepared statements do not support multiple SQL statements. iBATIS always generates prepared statements, apparently. So, this must be considered an invalid bug report or, at best, a feature request.
[5 Jan 2006 21:48]
Vasily Kishkin
Yes. mySQL prepared statements do not support multiple SQL statements. But anyway thanks for the bug report.
Description: Found with Connector/J 3.1.12. Connecting to Linux mySQL 5.0.15. Was using iBATIS 2.1.5.582, but don't think that's relevant. allowMultipleQueries was turned on. D:\Work\Java\net\saf3\biblio>java net.saf3.biblio.Test Exception: Error during query: Unexpected Exception: java.lang.ArrayIndexOutOfBo undsException message given: 8 Nested Stack Trace: ** BEGIN NESTED EXCEPTION ** java.lang.ArrayIndexOutOfBoundsException MESSAGE: 8 STACKTRACE: java.lang.ArrayIndexOutOfBoundsException: 8 at com.mysql.jdbc.ByteArrayBuffer.readInt(ByteArrayBuffer.java:224) at com.mysql.jdbc.MysqlIO.unpackField(MysqlIO.java:607) at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:414) at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:1962) at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1419) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1728) at com.mysql.jdbc.Connection.execSQL(Connection.java:2988) at com.mysql.jdbc.Connection.commit(Connection.java:2161) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at com.ibatis.common.jdbc.SimpleDataSource$SimplePooledConnection.invoke (SimpleDataSource.java:946) at $Proxy0.commit(Unknown Source) at com.ibatis.sqlmap.engine.transaction.jdbc.JdbcTransaction.commit(Jdbc Transaction.java:66) at com.ibatis.sqlmap.engine.transaction.TransactionManager.commit(Transa ctionManager.java:83) at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.commitTransactio n(SqlMapExecutorDelegate.java:759) at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.commitTransaction(Sql MapSessionImpl.java:133) at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.commitTransaction(SqlM apClientImpl.java:110) at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.autoCommitTransa ction(SqlMapExecutorDelegate.java:866) at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert(SqlMapExe cutorDelegate.java:451) at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.insert(SqlMapSessionI mpl.java:81) at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.insert(SqlMapClientImp l.java:58) at net.saf3.biblio.Test.main(Test.java:265) ** END NESTED EXCEPTION ** How to repeat: mysql> describe TagGroupNesting; +---------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+---------+-------+ | GroupID | int(11) | NO | PRI | | | | ChildID | int(11) | NO | PRI | | | +---------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> select * from TagGroupNesting; Empty set (0.00 sec) mysql> describe TagGroupClosure; +--------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------+------+-----+---------+-------+ | GroupID | int(11) | NO | PRI | | | | DescendantID | int(11) | NO | PRI | | | +--------------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> select * from TagGroupClosure; +---------+--------------+ | GroupID | DescendantID | +---------+--------------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | +---------+--------------+ 4 rows in set (0.00 sec) iBATIS takes the following mapped statement and does trivial parameter substitution on it: groupId=2 and childId=1 . I've commented out two sub-statements that individually cause the reported ArrayIndexOutOfBoundsException. The other statements do not cause any trouble, and the first statement definitely succeeds by inspection of table TagGroupNesting. <insert id="insert" parameterClass="TagGroupNesting"> insert into `TagGroupNesting` ( `GroupID`, `ChildID` ) values ( #groupId#, #childId# ) ; create temporary table TGC_temp like `TagGroupClosure` ; create temporary table TGC_delta like `TagGroupClosure` ; <!-- insert into TGC_temp ( `GroupID`, `DescendantID` ) select * from ( select `GroupID`, #childId# from `TagGroupClosure` where `DescendantID` = #groupId# union select #childId#, `DescendantID` from `TagGroupClosure` where `GroupID` = #childId# union select TGC1.`GroupID`, TGC2.`DescendantID` from `TagGroupClosure` as TGC1, `TagGroupClosure` as TGC2 where TGC1.`DescendantID` = #groupId# and TGC2.`GroupID` = #childId# ) --> insert into TGC_temp ( `GroupID`, `DescendantID` ) values ( #groupId#, #childId# ) ; insert into TGC_delta ( `GroupID`, `DescendantID` ) select * from TGC_temp where not exists ( select * from `TagGroupClosure` as TGC where TGC.`GroupID` = TGC_temp.`GroupID` and TGC.`DescendantID` = TGC_temp.`DescendantID` ) ; <!-- insert into `TagGroupClosure` select * from TGN_delta ; --> drop temporary table if exists TGC_temp, TGC_delta </insert>