Hi, I run into a problem with cayenne 1.1.4. I migrated the database of an
application from Sybase 12.5 to Mysql 5.
I create a set of objects and try to commit them. The objects are mapped
like this:
ENL_DOCUMENT with
foreign key to ENL_CREATION_LOG
foreign key to ENL_REVISION_LOG
Under Sybase 12.5 when I commit I get no error. The Inserts i see sent in
the Query Logger read
insert Table ENL_CREATION_LOG
insert Table ENL_REVISION_LOG
insert Table ENL_DOCUMENT
when I committing with the MySql Adapter/Backend I get a Commit Exception -
the Objects are inserted in the wrong order
insert in Table ENL_CREATION_LOG
insert in Table ENL_DOCUMENT
<- Commit Exception thrown
i migrated the schema using a tool (DbArtisan) I ran a migration from the
two databases for the existing data using clover etl, without a problem.
I use the AUTO_PK_SUPPORT with both databases. Beside the changes for
switching the databse for cayenne the application code is the same.
thank you
p.s.: I attach the original log:
Sybase 12.5:
13:39:35,812 DEBUG [http-7070-Processor25]
org.objectstyle.cayenne.access.ContextCommit:248 - Creating
InsertBatchQuery for DbEntity ENL_CREATION_LOG
13:39:35,828 DEBUG [http-7070-Processor25]
org.objectstyle.cayenne.access.ContextCommit:248 - Creating
InsertBatchQuery for DbEntity ENL_REVISION_LOG
13:39:35,828 DEBUG [http-7070-Processor25]
org.objectstyle.cayenne.access.ContextCommit:248 - Creating
InsertBatchQuery for DbEntity ENL_DOCUMENT
13:39:35,828 INFO [http-7070-Processor25]
org.objectstyle.cayenne.access.QueryLogger:391 - --- transaction started.
13:39:35,828 INFO [http-7070-Processor25]
org.objectstyle.cayenne.access.QueryLogger:435 - --- will run 3 queries.
13:39:35,843 INFO [http-7070-Processor25]
org.objectstyle.cayenne.access.QueryLogger:315 - INSERT INTO
dbo.ENL_CREATION_LOG (CREATION_LOG_UID, DATE, NOTE, USER_NAME) VALUES (?,
?, ?, ?)
13:39:35,843 INFO [http-7070-Processor25]
org.objectstyle.cayenne.access.QueryLogger:337 - [bind: 27440, '2006-07-31
13:39:28.203', NULL, 'Tobias SCHOESSLER']
13:39:35,890 INFO [http-7070-Processor25]
org.objectstyle.cayenne.access.QueryLogger:368 - === updated 1 row.
13:39:35,890 INFO [http-7070-Processor25]
org.objectstyle.cayenne.access.QueryLogger:315 - INSERT INTO
dbo.ENL_REVISION_LOG (DATE, NOTE, REVISION_LOG_UID, USER_NAME) VALUES (?,
?, ?, ?)
13:39:35,906 INFO [http-7070-Processor25]
org.objectstyle.cayenne.access.QueryLogger:337 - [bind: '2006-07-31
13:39:35.328', NULL, 27440, 'Tobias SCHOESSLER']
13:39:35,921 INFO [http-7070-Processor25]
org.objectstyle.cayenne.access.QueryLogger:368 - === updated 1 row.
13:39:35,921 INFO [http-7070-Processor25]
org.objectstyle.cayenne.access.QueryLogger:315 - INSERT INTO
dbo.ENL_DOCUMENT (AUTHOR, COUNTRY_CODE, CREATION_LOG_UID, DATE,
DOCUMENT_UID, ENL_ID, ENL_ID_NUM, ENL_ID_REV, ENL_ID_YEAR, PROPERTIES,
REVISION_LOG_UID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
13:39:35,921 INFO [http-7070-Processor25]
org.objectstyle.cayenne.access.QueryLogger:337 - [bind: NULL, 'AFG', 27440,
'2222-01-01 00:00:00.0', 2620, '2222/2', 2, NULL, 2222, NULL, 27440]
13:39:35,953 INFO [http-7070-Processor25]
org.objectstyle.cayenne.access.QueryLogger:368 - === updated 1 row.
13:39:35,953 INFO [http-7070-Processor25]
org.objectstyle.cayenne.access.QueryLogger:400 - +++ transaction committed.
MySql 5.0:
13:44:19,890 INFO [http-7070-Processor24]
org.objectstyle.cayenne.access.QueryLogger:315 - LOCK TABLES
AUTO_PK_SUPPORT WRITE
13:44:19,890 INFO [http-7070-Processor24]
org.objectstyle.cayenne.access.QueryLogger:315 - SELECT NEXT_ID FROM
AUTO_PK_SUPPORT WHERE TABLE_NAME = 'ENL_CREATION_LOG'
13:44:19,906 INFO [http-7070-Processor24]
org.objectstyle.cayenne.access.QueryLogger:315 - UPDATE AUTO_PK_SUPPORT SET
NEXT_ID = NEXT_ID + 20 WHERE TABLE_NAME = 'ENL_CREATION_LOG' AND NEXT_ID =
37720
13:44:19,906 INFO [http-7070-Processor24]
org.objectstyle.cayenne.access.QueryLogger:315 - UNLOCK TABLES
13:44:19,906 INFO [http-7070-Processor24]
org.objectstyle.cayenne.access.QueryLogger:315 - LOCK TABLES
AUTO_PK_SUPPORT WRITE
13:44:19,906 INFO [http-7070-Processor24]
org.objectstyle.cayenne.access.QueryLogger:315 - SELECT NEXT_ID FROM
AUTO_PK_SUPPORT WHERE TABLE_NAME = 'ENL_DOCUMENT'
13:44:19,906 INFO [http-7070-Processor24]
org.objectstyle.cayenne.access.QueryLogger:315 - UPDATE AUTO_PK_SUPPORT SET
NEXT_ID = NEXT_ID + 20 WHERE TABLE_NAME = 'ENL_DOCUMENT' AND NEXT_ID = 3840
13:44:19,921 INFO [http-7070-Processor24]
org.objectstyle.cayenne.access.QueryLogger:315 - UNLOCK TABLES
13:44:19,921 INFO [http-7070-Processor24]
org.objectstyle.cayenne.access.QueryLogger:315 - LOCK TABLES
AUTO_PK_SUPPORT WRITE
13:44:19,921 INFO [http-7070-Processor24]
org.objectstyle.cayenne.access.QueryLogger:315 - SELECT NEXT_ID FROM
AUTO_PK_SUPPORT WHERE TABLE_NAME = 'ENL_REVISION_LOG'
13:44:19,937 INFO [http-7070-Processor24]
org.objectstyle.cayenne.access.QueryLogger:315 - UPDATE AUTO_PK_SUPPORT SET
NEXT_ID = NEXT_ID + 20 WHERE TABLE_NAME = 'ENL_REVISION_LOG' AND NEXT_ID =
37720
13:44:19,937 INFO [http-7070-Processor24]
org.objectstyle.cayenne.access.QueryLogger:315 - UNLOCK TABLES
13:44:19,937 DEBUG [http-7070-Processor24]
org.objectstyle.cayenne.access.ContextCommit:248 - Creating
InsertBatchQuery for DbEntity ENL_CREATION_LOG
13:44:19,937 DEBUG [http-7070-Processor24]
org.objectstyle.cayenne.access.ContextCommit:248 - Creating
InsertBatchQuery for DbEntity ENL_DOCUMENT
13:44:19,937 DEBUG [http-7070-Processor24]
org.objectstyle.cayenne.access.ContextCommit:248 - Creating
InsertBatchQuery for DbEntity ENL_REVISION_LOG
13:44:19,953 INFO [http-7070-Processor24]
org.objectstyle.cayenne.access.QueryLogger:391 - --- transaction started.
13:44:19,953 INFO [http-7070-Processor24]
org.objectstyle.cayenne.access.QueryLogger:435 - --- will run 3 queries.
13:44:19,953 INFO [http-7070-Processor24]
org.objectstyle.cayenne.access.QueryLogger:315 - INSERT INTO
enl.ENL_CREATION_LOG (CREATION_LOG_UID, DATE, NOTE, USER_NAME) VALUES (?,
?, ?, ?)
13:44:19,953 INFO [http-7070-Processor24]
org.objectstyle.cayenne.access.QueryLogger:337 - [bind: 37720, '2006-07-31
13:44:10.5', NULL, 'Tobias SCHOESSLER']
13:44:19,953 INFO [http-7070-Processor24]
org.objectstyle.cayenne.access.QueryLogger:368 - === updated 1 row.
13:44:19,953 INFO [http-7070-Processor24]
org.objectstyle.cayenne.access.QueryLogger:315 - INSERT INTO
enl.ENL_DOCUMENT (AUTHOR, COUNTRY_CODE, CREATION_LOG_UID, DATE,
DOCUMENT_UID, ENL_ID, ENL_ID_NUM, ENL_ID_REV, ENL_ID_YEAR, PROPERTIES,
REVISION_LOG_UID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
13:44:19,968 INFO [http-7070-Processor24]
org.objectstyle.cayenne.access.QueryLogger:337 - [bind: NULL, 'AFG', 37720,
'2222-01-01 00:00:00.0', 3840, '2222/66', 66, NULL, 2222, NULL, 37720]
13:44:20,000 INFO [http-7070-Processor24]
org.objectstyle.cayenne.access.QueryLogger:418 - *** error.
java.sql.SQLException: Cannot add or update a child row: a foreign key
constraint fails (`enl/ENL_DOCUMENT`, CONSTRAINT
`FK_ENL_DOCU_REVISED_ENL_REVI` FOREIGN KEY (`REVISION_LOG_UID`) REFERENCES
`ENL_REVISION_LOG` (`REVISION_LOG_UID`))
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
at
com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1125)
at
com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:677)
at
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1357)
at
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1274)
at
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1259)
at
org.objectstyle.cayenne.access.DataNode.runBatchUpdateAsIndividualQueries(DataNode.java:596)
at
org.objectstyle.cayenne.access.DataNode.runBatchUpdate(DataNode.java:495)
at
org.objectstyle.cayenne.access.DataNode.performQueries(DataNode.java:353)
at
org.objectstyle.cayenne.access.ContextCommit.commit(ContextCommit.java:192)
at
org.objectstyle.cayenne.access.DataContext.commitChanges(DataContext.java:1266)
at
org.objectstyle.cayenne.access.DataContext.commitChanges(DataContext.java:1236)
at
org.unodc.proj.enl.action.UpdateDocumentAction.perform(UpdateDocumentAction.java:84)
at org.apache.struts.action.Action.execute(Action.java:420)
at
org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at
org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.undcp.common.EntryPoint.doFilter(EntryPoint.java:417)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at
org.displaytag.filter.ResponseOverrideFilter.doFilter(ResponseOverrideFilter.java:125)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at
org.apache.catalina.valves.FastCommonAccessLogValve.invoke(FastCommonAccessLogValve.java:481)
at
org.apache.catalina.authenticator.SingleSignOn.invoke(SingleSignOn.java:392)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
at
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
at
org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at
org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Unknown Source)
13:44:20,015 ERROR [http-7070-Processor24]
org.unodc.proj.enl.action.UpdateDocumentAction:100 - [v.1.1.4 January 5
2006] Commit Exception
org.objectstyle.cayenne.CayenneRuntimeException: [v.1.1.4 January 5 2006]
Commit Exception
at
org.objectstyle.cayenne.access.DataContext.commitChanges(DataContext.java:1275)
at
org.objectstyle.cayenne.access.DataContext.commitChanges(DataContext.java:1236)
at
org.unodc.proj.enl.action.UpdateDocumentAction.perform(UpdateDocumentAction.java:84)
at org.apache.struts.action.Action.execute(Action.java:420)
at
org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at
org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.undcp.common.EntryPoint.doFilter(EntryPoint.java:417)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at
org.displaytag.filter.ResponseOverrideFilter.doFilter(ResponseOverrideFilter.java:125)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at
org.apache.catalina.valves.FastCommonAccessLogValve.invoke(FastCommonAccessLogValve.java:481)
at
org.apache.catalina.authenticator.SingleSignOn.invoke(SingleSignOn.java:392)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
at
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
at
org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at
org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Cannot add or update a child row: a
foreign key constraint fails (`enl/ENL_DOCUMENT`, CONSTRAINT
`FK_ENL_DOCU_REVISED_ENL_REVI` FOREIGN KEY (`REVISION_LOG_UID`) REFERENCES
`ENL_REVISION_LOG` (`REVISION_LOG_UID`))
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
at
com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1125)
at
com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:677)
at
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1357)
at
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1274)
at
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1259)
at
org.objectstyle.cayenne.access.DataNode.runBatchUpdateAsIndividualQueries(DataNode.java:596)
at
org.objectstyle.cayenne.access.DataNode.runBatchUpdate(DataNode.java:495)
at
org.objectstyle.cayenne.access.DataNode.performQueries(DataNode.java:353)
at
org.objectstyle.cayenne.access.ContextCommit.commit(ContextCommit.java:192)
at
org.objectstyle.cayenne.access.DataContext.commitChanges(DataContext.java:1266)
... 31 more
