ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Shannon, Bryan" <BShan...@Tribune.com>
Subject RE: Problem upgrading 2.0.4 to 2.1.5
Date Mon, 26 Sep 2005 17:09:49 GMT
Actually, I've whittled the problem down to the basics to reproduce the
problem in as few steps as possible.

The data that gets re-inserted doesn't come from the table initially in my
real program; it is generated and just happens to be the same in these
conditions.

The sybase drivers I use are exactly the same; everything except the ibatis
jars (common and sqlmap).  (JConnect 6, btw).

I'm not sure about a transaction timing issue;  If you mean internal to
iBatis, then perhaps, but I'm the only user in this database.  Run once...
Works... Run again.. works... Run again... fails.  Run again.. works.  Like
the bad flashlight in your kitchen drawer that you have to bang against the
counter to get working.  :-)

The error message coming from Sybase is pretty straightforward; you can't
insert the row with the same contents of a row that exists, but the DELETE
always succeeds... And you would assume that the insert should as well. 



-----Original Message-----
From: Michael Campbell [mailto:michael.campbell@gmail.com]
Sent: Monday, September 26, 2005 1:02 PM
To: user-java@ibatis.apache.org
Subject: Re: Problem upgrading 2.0.4 to 2.1.5


Guessing here, but it almost smells like a transaction timing issue. 
Are the sybase drivers the same for both iBATIS versions?  (Or, I
wonder if some code in 2.0.4 is synchronized and 2.1.5 is not?)

As an aside, is there a reason you're not just doing an update vs.
select/delete/insert?

On 9/26/05, Shannon, Bryan <BShannon@tribune.com> wrote:
> 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
>


--
I tend to view "truly flexible" by another term: "Make everything
equally hard". -- DHH

Mime
View raw message