Re: from Sybase 12.5 to MySql 5

Mon, 31 Jul 2006 06:31:52 -0700

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

Reply via email to