#1449 DROP CONSTRAINT causes SQLIntegrityConstraintViolationException
We use hsqldb (2.3.4) inmemory mode for testing and all test uses the same db.
When we start a full test at first we load data from xml to db as we drop constraints -> load data -> add constraints.
It works properly but some tests load some data again and one of them (and following tests) failed when tried to drop constraints.
It doesn't make sense because of a constraint (foreign key) dropping cannot cause "SQLIntegrityConstraintViolationException: integrity constraint violation: unique constraint or index violation"!
I know whcih test methods cause the problem just I don't know why.
Theese tests cointain failed transactions but other ones too and they don't cause any problem.
I attached the log and in debug I found that "olddata" (RowStoreAVL.java:540) contains primary key twice but obviously I found just once in db (with select).
HSQL version: 2.3.4
url='jdbc:hsqldb:mem:xxx'
connectionProperties.put(url_create, "true");
connectionProperties.put(sql_nulls_first, "false");
connectionProperties.put(sql_nulls_order, "false");
connectionProperties.put(hsqldb_tx, "mvcc");
log:
org.apache.ddlutils.DatabaseOperationException: Error while executing SQL ALTER TABLE PROCESS
DROP CONSTRAINT PROCESS_FK_CURRENTACTIVITYID_ACTIVITYDEFINITION
at org.apache.ddlutils.platform.PlatformImplBase.evaluateBatch(PlatformImplBase.java:344)
at org.apache.ddlutils.platform.PlatformImplBase.evaluateBatch(PlatformImplBase.java:279)
at com.ericsson.cas.db.dbunit.DbLoader.executeDataXMLOperation(DbLoader.java:134)
at com.ericsson.cas.componenttest.dbinitializer.PerCaseDbInitializerSpringImpl.initPerCaseDb(PerCaseDbInitializerSpringImpl.java:45)
at com.ericsson.cas.functest.core.db.DbInitalizer.initPerCaseDb(DbInitalizer.java:57)
at com.ericsson.cas.functest.core.CasSystemTest.initDb(CasSystemTest.java:90)
at com.ericsson.cas.functest.core.CasSystemTest.init(CasSystemTest.java:74)
at com.ericsson.cas.inttest.awm.RemoteUserResetPasswordListPresenterIT.init(RemoteUserResetPasswordListPresenterIT.java:83)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
at mockit.integration.junit4.internal.JUnit4TestRunnerDecorator.invokeExplosively(JUnit4TestRunnerDecorator.java:44)
at mockit.integration.junit4.internal.MockFrameworkMethod.invokeExplosively(MockFrameworkMethod.java:37)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:24)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
at org.junit.internal.runners.statements.FailOnTimeout$StatementThread.run(FailOnTimeout.java:74)
Caused by: java.sql.SQLIntegrityConstraintViolationException: integrity constraint violation: unique constraint or index violation; SYS_PK_11827 table: PROCESS in statement [ALTER TABLE PROCESS
DROP CONSTRAINT PROCESS_FK_CURRENTACTIVITYID_ACTIVITYDEFINITION]
at org.hsqldb.jdbc.JDBCUtil.sqlException(JDBCUtil.java:324)
at org.hsqldb.jdbc.JDBCUtil.sqlException(JDBCUtil.java:247)
at org.hsqldb.jdbc.JDBCStatement.fetchResult(JDBCStatement.java:1797)
at org.hsqldb.jdbc.JDBCStatement.executeUpdate(JDBCStatement.java:208)
at org.apache.ddlutils.platform.PlatformImplBase.evaluateBatch(PlatformImplBase.java:323)
... 22 more
Caused by: org.hsqldb.HsqlException: integrity constraint violation: unique constraint or index violation; SYS_PK_11827 table: PROCESS
at org.hsqldb.error.Error.error(Error.java:137)
at org.hsqldb.Constraint.getException(Constraint.java:883)
at org.hsqldb.index.IndexAVLMemory.insert(IndexAVLMemory.java:233)
at org.hsqldb.persist.RowStoreAVL.indexRow(RowStoreAVL.java:241)
at org.hsqldb.persist.RowStoreAVL.moveData(RowStoreAVL.java:564)
at org.hsqldb.TableWorks.moveData(TableWorks.java:1382)
at org.hsqldb.TableWorks.dropConstraint(TableWorks.java:1030)
at org.hsqldb.SchemaManager.dropConstraint(SchemaManager.java:1525)
at org.hsqldb.StatementSchema.getResult(StatementSchema.java:888)
at org.hsqldb.StatementSchema.execute(StatementSchema.java:274)
at org.hsqldb.Session.executeCompiledStatement(Session.java:1384)
at org.hsqldb.Session.executeDirectStatement(Session.java:1259)
at org.hsqldb.Session.execute(Session.java:1024)
at org.hsqldb.jdbc.JDBCStatement.fetchResult(JDBCStatement.java:1789)
... 24 more
2016-09-05T11:04:05 ERROR [ ] [Thread-49 ] c.e.c.f.core.AbstractSpringAwareIntegrationTest - Database loading failed for files: [RemoteUserResetPasswordListPresenterIT_awm.xml] Error message: Error while executing SQL ALTER TABLE PROCESS
DROP CONSTRAINT PROCESS_FK_CURRENTACTIVITYID_ACTIVITYDEFINITION
2016-09-05T11:04:05 INFO [ ] [Thread-49 ] c.e.c.s.interceptor.RestLoggerInterceptor - The process finished successfully
You need to report the CREATE TABLE statements for the tables, and any ALTER TABLE ADD CONSTRAINT, plust some test data.
This might be the same issue that was fixed and committed yesterday. Please check out the code from SVN /base/trunk and test.
I have tried it but unfortunatly I've got the same error :(
In that case please report the CREATE TABLE and the added constraits, including foreign key constraints on other tables that reference this.
Thank you for your prompt reply!
I will send it if I "can".
Hi,
I got closer to solution. The problem only occurs if we update same record (PROCESS) twice and throw an exception (rollback) in one transaction.
Just in this case "ALTER TABLE PROCESS
DROP CONSTRAINT PROCESS_FK_CURRENTACTIVITYID_ACTIVITYDEFINITION" will fail as above.
If we update once in one tr or without exception then everything is ok.
see the sql log:
2016-09-09 09:03:26.728 139 SELECT ...
2016-09-09 09:03:26.728 139 COMMIT
2016-09-09 09:03:26.728 139 ROLLBACK
2016-09-09 09:03:55.934 140 COMMIT
2016-09-09 09:03:55.934 140 SELECT ...
2016-09-09 09:03:55.934 140 SELECT ...
2016-09-09 09:03:55.934 140 SELECT ...
2016-09-09 09:03:55.934 140 SELECT ...
2016-09-09 09:03:55.934 140 SELECT ...
2016-09-09 09:03:55.944 140 UPDATE ...
2016-09-09 09:03:55.954 140 UPDATE AWM.PROCESS SET DESCRIPTION = ? WHERE (ID = ?) ('',22)
2016-09-09 09:03:55.954 140 UPDATE ...
2016-09-09 09:03:55.984 140 UPDATE AWM.PROCESS SET LASTACTIONDATE = ? WHERE (ID = ?) (TIMESTAMP'2016-09-09 09:03:55.914000',22)
2016-09-09 09:03:55.984 140 SELECT ...
2016-09-09 09:04:13.951 140 SELECT ...
2016-09-09 09:04:13.951 140 SELECT ...
2016-09-09 09:04:13.961 140 SELECT ...
2016-09-09 09:04:13.961 140 SELECT ...
2016-09-09 09:04:13.961 140 SELECT ...
2016-09-09 09:04:13.961 141 SELECT ...
2016-09-09 09:04:13.961 141 SELECT ...
2016-09-09 09:04:13.961 141 COMMIT
2016-09-09 09:04:13.961 141 ROLLBACK
2016-09-09 09:04:13.971 142 SELECT ...
2016-09-09 09:04:13.971 142 COMMIT
2016-09-09 09:04:13.971 142 ROLLBACK
2016-09-09 09:04:52.752 140 SELECT ...
here we throw the exception!!!
2016-09-09 09:05:35.302 140 ROLLBACK
2016-09-09 09:05:39.927 140 COMMIT
2016-09-09 09:05:44.639 140 ROLLBACK
You have the SQL log. Please report the top of the log where there are CREATE TABLE statements and any ALTER TABLE that adds a constraint or any CREATE INDEX statement.
Last edit: Fred Toussi 2016-09-09
I attached a very simple test which simulates the bug.
It turned out “hsqldb.tx=mvcc” connection parameter is necessary to the error.
Thanks for the test. Fixed and committed to SVN /base/trunk
Thanks a lot! It's working :)