I am having a very unusual problem after upgrading from iBatis 2.0.4 to
2.1.5.
It has to do with a particular table and a few particularly simple maps that
interact with that table.
The table is in Sybase, and it has a clustered index (which in Sybase
doesn't allow duplicate ROWS, ie. two rows having the exact same content).
Essentially, the flow looks like this:
1) start transaction.
2) Load all rows from this table by an id number.
3) Delete all rows in table for this id number. (clearing them out)
4) Insert all the rows back into the table.
5) commit transaction.
With the older iBatis, it works fine; queries for the existing rows;
deletes them, and then re-inserts them. But the new iBatis SOMETIMES fails
doing the first INSERT. But Only SOMETIMES!!!
Here is what happens in each version of iBatis:
Now for this one table, 2.0.4 works just as you'd expect:
(it does a select for all the values with id "1", then the delete, then the
inserts for each row.)
DEBUG JakartaCommonsLoggingImpl: {conn-100000} Connection
DEBUG JakartaCommonsLoggingImpl: {pstm-100001} PreparedStatement: SELECT
mvn_name_num as nameNum, mvn_movie_num as movieNum,
mvn_name_code as nameCode, mvn_role_code as roleCode FROM
movie_db..movie_name_t WHERE mvn_movie_num = ?
DEBUG JakartaCommonsLoggingImpl: {pstm-100001} Parameters: [1]
DEBUG JakartaCommonsLoggingImpl: {pstm-100001} Types: [java.lang.Long]
DEBUG JakartaCommonsLoggingImpl: {rset-100002} ResultSet
DEBUG JakartaCommonsLoggingImpl: {rset-100002} Header: [nameNum, movieNum,
nameCode, roleCode]
DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C001, 1, 1, 1]
DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C002, 1, 2, 1]
DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C003, 1, 5990, 1]
DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C004, 1, 114781, 1]
DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C005, 1, 114782, 1]
DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C006, 1, 114783, 1]
DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C007, 1, 6559, 1]
DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C008, 1, 7635, 1]
DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C009, 1, 9047, 1]
DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C010, 1, 7273, 1]
DEBUG JakartaCommonsLoggingImpl: {pstm-100003} PreparedStatement: DELETE
FROM movie_db..movie_name_t WHERE movie_db..movie_name_t.mvn_movie_num = ?
DEBUG JakartaCommonsLoggingImpl: {pstm-100003} Parameters: [1]
DEBUG JakartaCommonsLoggingImpl: {pstm-100003} Types: [java.lang.Long]
DEBUG JakartaCommonsLoggingImpl: {pstm-100004} PreparedStatement: INSERT
INTO movie_db..movie_name_t (mvn_name_num, mvn_movie_num, mvn_name_code,
mvn_role_code) VALUES (?, ?, ?, ?)
DEBUG JakartaCommonsLoggingImpl: {pstm-100004} Parameters: [C001, 1, 1, 1]
DEBUG JakartaCommonsLoggingImpl: {pstm-100004} Types: [java.lang.String,
java.lang.Long, java.lang.Long, java.lang.Long]
DEBUG JakartaCommonsLoggingImpl: {pstm-100005} PreparedStatement: INSERT
INTO movie_db..movie_name_t (mvn_name_num, mvn_movie_num, mvn_name_code,
mvn_role_code) VALUES (?, ?, ?, ?)
DEBUG JakartaCommonsLoggingImpl: {pstm-100005} Parameters: [C002, 1, 2, 1]
DEBUG JakartaCommonsLoggingImpl: {pstm-100005} Types: [java.lang.String,
java.lang.Long, java.lang.Long, java.lang.Long]
DEBUG JakartaCommonsLoggingImpl: {pstm-100006} PreparedStatement: INSERT
INTO movie_db..movie_name_t (mvn_name_num, mvn_movie_num, mvn_name_code,
mvn_role_code) VALUES (?, ?, ?, ?)
DEBUG JakartaCommonsLoggingImpl: {pstm-100006} Parameters: [C003, 1, 5990,
1]
DEBUG JakartaCommonsLoggingImpl: {pstm-100006} Types: [java.lang.String,
java.lang.Long, java.lang.Long, java.lang.Long]
DEBUG JakartaCommonsLoggingImpl: {pstm-100007} PreparedStatement: INSERT
INTO movie_db..movie_name_t (mvn_name_num, mvn_movie_num, mvn_name_code,
mvn_role_code) VALUES (?, ?, ?, ?)
DEBUG JakartaCommonsLoggingImpl: {pstm-100007} Parameters: [C004, 1, 114781,
1]
DEBUG JakartaCommonsLoggingImpl: {pstm-100007} Types: [java.lang.String,
java.lang.Long, java.lang.Long, java.lang.Long]
DEBUG JakartaCommonsLoggingImpl: {pstm-100008} PreparedStatement: INSERT
INTO movie_db..movie_name_t (mvn_name_num, mvn_movie_num, mvn_name_code,
mvn_role_code) VALUES (?, ?, ?, ?)
DEBUG JakartaCommonsLoggingImpl: {pstm-100008} Parameters: [C005, 1, 114782,
1]
DEBUG JakartaCommonsLoggingImpl: {pstm-100008} Types: [java.lang.String,
java.lang.Long, java.lang.Long, java.lang.Long]
DEBUG JakartaCommonsLoggingImpl: {pstm-100009} PreparedStatement: INSERT
INTO movie_db..movie_name_t (mvn_name_num, mvn_movie_num, mvn_name_code,
mvn_role_code) VALUES (?, ?, ?, ?)
DEBUG JakartaCommonsLoggingImpl: {pstm-100009} Parameters: [C006, 1, 114783,
1]
DEBUG JakartaCommonsLoggingImpl: {pstm-100009} Types: [java.lang.String,
java.lang.Long, java.lang.Long, java.lang.Long]
DEBUG JakartaCommonsLoggingImpl: {pstm-100010} PreparedStatement: INSERT
INTO movie_db..movie_name_t (mvn_name_num, mvn_movie_num, mvn_name_code,
mvn_role_code) VALUES (?, ?, ?, ?)
DEBUG JakartaCommonsLoggingImpl: {pstm-100010} Parameters: [C007, 1, 6559,
1]
DEBUG JakartaCommonsLoggingImpl: {pstm-100010} Types: [java.lang.String,
java.lang.Long, java.lang.Long, java.lang.Long]
DEBUG JakartaCommonsLoggingImpl: {pstm-100011} PreparedStatement: INSERT
INTO movie_db..movie_name_t (mvn_name_num, mvn_movie_num, mvn_name_code,
mvn_role_code) VALUES (?, ?, ?, ?)
DEBUG JakartaCommonsLoggingImpl: {pstm-100011} Parameters: [C008, 1, 7635,
1]
DEBUG JakartaCommonsLoggingImpl: {pstm-100011} Types: [java.lang.String,
java.lang.Long, java.lang.Long, java.lang.Long]
DEBUG JakartaCommonsLoggingImpl: {pstm-100012} PreparedStatement: INSERT
INTO movie_db..movie_name_t (mvn_name_num, mvn_movie_num, mvn_name_code,
mvn_role_code) VALUES (?, ?, ?, ?)
DEBUG JakartaCommonsLoggingImpl: {pstm-100012} Parameters: [C009, 1, 9047,
1]
DEBUG JakartaCommonsLoggingImpl: {pstm-100012} Types: [java.lang.String,
java.lang.Long, java.lang.Long, java.lang.Long]
DEBUG JakartaCommonsLoggingImpl: {pstm-100013} PreparedStatement: INSERT
INTO movie_db..movie_name_t (mvn_name_num, mvn_movie_num, mvn_name_code,
mvn_role_code) VALUES (?, ?, ?, ?)
DEBUG JakartaCommonsLoggingImpl: {pstm-100013} Parameters: [C010, 1, 7273,
1]
DEBUG JakartaCommonsLoggingImpl: {pstm-100013} Types: [java.lang.String,
java.lang.Long, java.lang.Long, java.lang.Long]
---------------------------------------------------------------
However, when I use 2.1.5 for this SAME EXACT logic, *sometimes* it fails
immediately for the first INSERT statement after the DELETE, causing an
Exception as below:
DEBUG JakartaCommonsLoggingImpl: {conn-100000} Connection
DEBUG JakartaCommonsLoggingImpl: {pstm-100001} PreparedStatement: SELECT
mvn_name_num as nameNum, mvn_movie_num as movieNum,
mvn_name_code as nameCode, mvn_role_code as roleCode FROM
movie_db..movie_name_t WHERE mvn_movie_num = ?
DEBUG JakartaCommonsLoggingImpl: {pstm-100001} Parameters: [1]
DEBUG JakartaCommonsLoggingImpl: {pstm-100001} Types: [java.lang.Long]
DEBUG JakartaCommonsLoggingImpl: {rset-100002} ResultSet
DEBUG JakartaCommonsLoggingImpl: {rset-100002} Header: [nameNum, movieNum,
nameCode, roleCode]
DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C001, 1, 1, 1]
DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C002, 1, 2, 1]
DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C003, 1, 5990, 1]
DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C004, 1, 114781, 1]
DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C005, 1, 114782, 1]
DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C006, 1, 114783, 1]
DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C007, 1, 6559, 1]
DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C008, 1, 7635, 1]
DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C009, 1, 9047, 1]
DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C010, 1, 7273, 1]
DEBUG JakartaCommonsLoggingImpl: {pstm-100003} PreparedStatement: DELETE
FROM movie_db..movie_name_t WHERE movie_db..movie_name_t.mvn_movie_num = ?
DEBUG JakartaCommonsLoggingImpl: {pstm-100003} Parameters: [1]
DEBUG JakartaCommonsLoggingImpl: {pstm-100003} Types: [java.lang.Long]
DEBUG JakartaCommonsLoggingImpl: {pstm-100049} PreparedStatement: INSERT
INTO movie_db..movie_name_t (mvn_name_num, mvn_movie_num, mvn_name_code,
mvn_role_code) VALUES (?, ?, ?, ?)
DEBUG JakartaCommonsLoggingImpl: {pstm-100049} Parameters: [C001, 1, 1, 1]
DEBUG JakartaCommonsLoggingImpl: {pstm-100049} Types: [java.lang.String,
java.lang.Long, java.lang.Long, java.lang.Long]
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in TestMap.xml.
--- The error occurred while applying a parameter map.
--- Check the doInsert-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: com.sybase.jdbc3.jdbc.SybSQLException: Attempt to insert
duplicate row in table 'movie_name_t' with index 'name_indx' in database
'movie_db'. Could drop and recreate index with ignore duprow or allow
duprow.
Caused by: com.sybase.jdbc3.jdbc.SybSQLException: Attempt to insert
duplicate row in table 'movie_name_t' with index 'name_indx' in database
'movie_db'. Could drop and recreate index with ignore duprow or allow
duprow.
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(Ge
neralStatement.java:91)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert(SqlMapExecutorDe
legate.java:442)
at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.insert(SqlMapSessionImpl.jav
a:81)
at
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.insert(SqlMapClientImpl.java:
58)
at sqlmaptest.SqlMapTest.runTest(SqlMapTest.java:54)
at sqlmaptest.SqlMapTest.<init>(SqlMapTest.java:32)
at sqlmaptest.SqlMapTest.main(SqlMapTest.java:61)
Caused by: com.sybase.jdbc3.jdbc.SybSQLException: Attempt to insert
duplicate row in table 'movie_name_t' with index 'name_indx' in database
'movie_db'. Could drop and recreate index with ignore duprow or allow
duprow.
at com.sybase.jdbc3.tds.Tds.processEed(Tds.java:3069)
at com.sybase.jdbc3.tds.Tds.nextResult(Tds.java:2373)
at
com.sybase.jdbc3.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
at
com.sybase.jdbc3.jdbc.SybStatement.nextResult(SybStatement.java:220)
at
com.sybase.jdbc3.jdbc.SybStatement.nextResult(SybStatement.java:203)
at
com.sybase.jdbc3.jdbc.SybStatement.executeLoop(SybStatement.java:1875)
at
com.sybase.jdbc3.jdbc.SybStatement.execute(SybStatement.java:1867)
at
com.sybase.jdbc3.jdbc.SybPreparedStatement.execute(SybPreparedStatement.java
:640)
at
org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPrepar
edStatement.java:256)
at sun.reflect.GeneratedMethodAccessor11.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at
com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(PreparedStat
ementLogProxy.java:62)
at $Proxy1.execute(Unknown Source)
at
com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.jav
a:84)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate
(GeneralStatement.java:200)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(Ge
neralStatement.java:78)
... 6 more
Now, If I rerun this, it might throw the same exact error message, or... it
might work exactly like it should! I am positive that the dataset is
exactly the same each run.
The symptoms are as if 2.1.5 didn't ACTUALLY do the DELETE statement; this
error message about the duplicate row only happens when that row isn't
deleted. But it doesn't throw the error every time!
When I use 2.0.4, it NEVER fails, even though I use the same exact Sybase
Driver, same SQL map config file, same exact everything, except for the new
ibatis jars.
Here are the map statements:
<statement id="doDelete" parameterClass="long">
DELETE FROM movie_db..movie_name_t WHERE
movie_db..movie_name_t.mvn_movie_num = #value#
</statement>
<statement id="doInsert" parameterClass="sqlmaptest.TestPerson">
INSERT INTO movie_db..movie_name_t (mvn_name_num, mvn_movie_num,
mvn_name_code, mvn_role_code)
VALUES (#nameNum#, #movieNum#, #nameCode#, #roleCode#)
</statement>
<statement id="getPeople" parameterClass="long"
resultClass="sqlmaptest.TestPerson">
SELECT mvn_name_num as nameNum,
mvn_movie_num as movieNum,
mvn_name_code as nameCode,
mvn_role_code as roleCode
FROM movie_db..movie_name_t
WHERE mvn_movie_num = #value#
</statement>
Here is the snippet of java that uses the maps (TestPerson is a simple
bean):
try {
sqlMap.startTransaction();
List people = sqlMap.queryForList("getPeople", new Long(movieNum));
sqlMap.delete("doDelete", new Long(movieNum));
if(people != null) {
for (Iterator iter = people.iterator(); iter.hasNext(); ) {
TestPerson person = (TestPerson) iter.next();
sqlMap.insert("doInsert", person);
}
}
sqlMap.commitTransaction();
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlMap.endTransaction();
try {
} catch (Exception e) {
e.printStackTrace();
}
}
Any help would be GREATLY appreciated! I've tried to come up with any
possible reason why this works or fails SOMETIMES but not predictably.
Thank you!
-Bryan Shannon
