In 1.2 insert operation sorting is enabled on all adapters. In 1.1 we
were trying to be smart and only enabled it when we thought
constraints might be involved (MySQL was assumed to be constraint-
free :-)).
To work around it, you can do this:
MySQLAdapter adapter = new MySQLAdapter();
adapter.setSupportsFkConstraints(true);
DataNode node = ...
node.setAdapter(adapter);
Andrus
On Jul 31, 2006, at 8:11 AM, Tobias SCHOESSLER wrote:
>
> 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.runBatchUpdateAsIndividualQuer
> ies(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.runBatchUpdateAsIndividualQuer
> ies(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
>
>
>
>
>
This archive was generated by hypermail 2.0.0 : Mon Jul 31 2006 - 09:31:54 EDT