[NAS-2229] SQL Error During harvestDatabaseUpdate (TEST7) Created: 08/Aug/13 Updated: 11/Apr/18 Resolved: 12/Aug/13 |
|
| Status: | Closed |
| Project: | NetarchiveSuite |
| Component/s: | PostgreSQL, Test |
| Affects Version/s: | None |
| Fix Version/s: | 4.2 |
| Type: | Bug | Priority: | Major |
| Reporter: | Colin Rosenthal | Assignee: | Colin Rosenthal |
| Resolution: | Fixed | ||
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Verification: | This can be verified by running an update file and checksum information job for a checksum-replica. The GUIApplication log should no longer show an SQL error and stacktrace when the job is completed. |
| Description |
|
At https://sbforge.org/display/NAS/TEST+7#TEST7-Upgradethedatabase in TEST7, the log has several errors like: [test@kb-test-adm-001 TEST7]$ cat start_harvestdatabaseUpdateApplication.log Beginning database upgrade at Thu Aug 08 11:01:00 CEST 2013 log4j:WARN No appenders could be found for logger (com.mchange.v2.log.MLog). log4j:WARN Please initialize the log4j system properly. Aug 8, 2013 11:02:01 AM dk.netarkivet.common.utils.DBUtils rollbackIfNeeded WARNING: SQL error doing rollback after updating table with SQL: ALTER TABLE domains ALTER COLUMN crawlertraps type text;UPDATE schemaversions SET version = 3 WHERE tablename = 'domains''. SQLException trace: SQL State:25P01 Error Code:0 org.postgresql.util.PSQLException: Cannot rollback when autoCommit is enabled. at org.postgresql.jdbc2.AbstractJdbc2Connection.rollback(AbstractJdbc2Connection.java:842) at com.mchange.v2.c3p0.impl.NewProxyConnection.rollback(NewProxyConnection.java:855) at dk.netarkivet.common.utils.DBUtils.rollbackIfNeeded(DBUtils.java:734) I'm slighty worried about the "Cannot rollback when autoCommit is enabled" but more worried as to why it's trying to roll back at all. |
| Comments |
| Comment by Colin Rosenthal [ 12/Aug/13 ] |
|
There is a very minimal fix in svn r2771. It just checks if autoCommit was true and only attempts to rollback if it was false. |
| Comment by Søren Vejrup Carlsen (Inactive) [ 12/Aug/13 ] |
|
The rollbackIfNeeeded code goes back to revision 1041 |
| Comment by Søren Vejrup Carlsen (Inactive) [ 12/Aug/13 ] |
|
I don't know. Could be that the problem arose when we changed the way we managed our connections? The problem doesn't seem to occur with derby. But I've seen this problem before with MySQL. Would it help to put the rollbackIfNeeded inside the catch for the SQLException ? |
| Comment by Colin Rosenthal [ 12/Aug/13 ] |
|
It's clearly wrong in DBUtils.executeSQL() to set autoCommit true immediately before one attempts a rollback. This will always fail. Is the problem that we are using "connection.setAutoCommit(true);" both to reset the state of the connection and to commit the transaction? Why not just call the conn.commit() method? The standard logic (e.g. http://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html ) seems to be that one calls all statements and commit() in a try{} block and rollback in a catch() block. Why do we attempt rollback in a finally{} block? |
| Comment by Colin Rosenthal [ 12/Aug/13 ] |
|
This bug is also manifest in the checksum job step of TEST7:
INFO: UpdateChecksumStatus operation commencing
Aug 9, 2013 7:58:05 PM dk.netarkivet.common.utils.DBUtils executeSQL
FINE: Executing SQL-statement: UPDATE replicafileinfo SET checksum_status = 2 WHERE checksum_status != 2 AND file_id IN ( SELECT file_id FR
OM ( SELECT file_id, COUNT(file_id) AS checksums, SUM(replicas) replicas FROM ( SELECT file_id, COUNT(checksum) AS replicas, chec
ksum FROM replicafileinfo WHERE filelist_status != 1 AND checksum IS NOT NULL GROUP BY file_id, checksum ) AS ss1 GRO
UP BY file_id ) AS ss2 WHERE checksums = 1 )
Aug 9, 2013 8:09:16 PM dk.netarkivet.common.utils.DBUtils executeSQL
FINE: Updated database using updates 'UPDATE replicafileinfo SET checksum_status = 2 WHERE checksum_status != 2 AND file_id IN ( SELECT file_
id FROM ( SELECT file_id, COUNT(file_id) AS checksums, SUM(replicas) replicas FROM ( SELECT file_id, COUNT(checksum) AS replica
s, checksum FROM replicafileinfo WHERE filelist_status != 1 AND checksum IS NOT NULL GROUP BY file_id, checksum ) AS ss1
GROUP BY file_id ) AS ss2 WHERE checksums = 1 )'.
Aug 9, 2013 8:09:17 PM dk.netarkivet.common.utils.DBUtils rollbackIfNeeded
WARNING: SQL error doing rollback after updating table with SQL: UPDATE replicafileinfo SET checksum_status = 2 WHERE checksum_status != 2 AND file_id IN ( SELECT file_id FROM ( SELECT file_id, COUNT(file_id) AS checksums, SUM(replicas) replicas FROM ( SELECT file_id, COUNT(checksum) AS replicas, checksum FROM replicafileinfo WHERE filelist_status != 1 AND checksum IS NOT NULL GROUP BY file_id, checksum ) AS ss1 GROUP BY file_id ) AS ss2 WHERE checksums = 1 )'.
SQLException trace:
SQL State:25P01
Error Code:0
org.postgresql.util.PSQLException: Cannot rollback when autoCommit is enabled.
|
| Comment by Colin Rosenthal [ 08/Aug/13 ] |
|
"rollbackIfNeeded" only actually rolls back if there are uncommitted commands. In this case there are no uncommitted commands so no rollback is necessary. However it attempts to rollback which generates an error because autoCommit has been set to "true" in DBUtils.executeSQL(). So why don't we see this error all the time? |