Thanks for posting the outcome, it's always useful for us to know about potential gotchas people encoutered.
Florent On 17 Jul 2008, at 12:32, Alex Viggio wrote: > Hi Tiry, > > I never learn. One of the Golden Rules of systems -- change one thing > at a time! Upgrading to 5.1.5 at the same time I was moving Nuxeo from > a Mac (MySQL 5.0.51b) to a RedHat server (MySQL 5.0.45-log) was a > bad idea. > > Folks -- if you don't plan on using MySQL skip the rest of this email. > You will save yourself a headache :) > > I appear to have found a solution to those three errors, and this > might help others trying to use MySQL. Not that I'm recommending this, > but our managed hosting environment requires it over PostgreSQL. > > In summary, the MySQL schema dumped in the MySQL backup of my Mac > referenced Nuxeo tables by a mix of lower and upper case, e.g. both > "jbpm_processdefinition" and "". Apparently case matters, especially > for InnoDB because MySQL "silently ignored" foreign key constraints > for MyISAM tables. Nice huh? > > So I made this addition to the /etc/my.cnf on my RedHat server since > that MySQL variable defaults to '0' on Unix systems: > > [mysqld] > set-variable=lower_case_table_names=1 > > Restarted mysqld, restored my database backup with the new setting in > place, and I can now launch Nuxeo on the remote host without any > ConstraintViolationExceptions! Some links on InnoDB and foreign keys: > > http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html > > http://www.think-and-link.org/mysql/manual_InnoDB.html#InnoDB_foreign_key_constraints > > - Alex > > On Thu, Jul 17, 2008 at 4:13 AM, Tiry <[EMAIL PROTECTED]> wrote: >> Hi, >> >> There are no JBPM version changes between 5.1.4 and 5.1.5. >> So the process definitions and JBPM structure should be exactly the >> same. >> Is there any change in the JDBC driver you are using or the version >> of MySQL >> ? >> >> >> Alex Viggio a écrit : >>> >>> I'm seeing three SQL constraint errors when I start up the Nuxeo/ >>> JBoss >>> server talking to a MySQL/InnoDB database. By default it appears >>> MySQL >>> is rather loose with foreign key constraint checking, which is why >>> we >>> are requiring InnoDB tables. >>> >>> This one is the last of three errors, noted because I can provide >>> some >>> MySQL debug info below: >>> >>> 03:22:15,537 WARN [JDBCExceptionReporter] SQL Error: 1452, >>> SQLState: >>> 23000 >>> 03:22:15,538 ERROR [JDBCExceptionReporter] Cannot add or update a >>> child row: a foreign key constraint fails (`nuxeo/JBPM_NODE`, >>> CONSTRAINT `FK_NODE_PROCDEF` FOREIGN KEY (`PROCESSDEFINITION_`) >>> REFERENCES `jbpm_processdefinition` (`ID_`)) >>> 03:22:15,538 ERROR [ComponentManager] Failed to create component: >>> >>> service:org >>> .nuxeo.ecm.platform.forum.DefaultWorkflowDefinitionContributions >>> org.hibernate.exception.ConstraintViolationException: could not >>> insert: [org.jbpm.graph.node.StartState] >>> >>> >>> If I check the MySQL InnoDB status on my db server, I see some >>> useful >>> info on the foreign key error. At the time of this attempted insert, >>> it appears that there is no JBPM_PROCESSDEFINITION record with an >>> 'id' >>> of 197. That would explain why the SQL INSERT into JBPM_NODE is >>> failing on the constraint. The highest JBPM_PROCESSDEFINITION id in >>> the JBPM_NODE table in this database is 185, so this was working >>> until >>> recently. I do see that new rows were added to the >>> JBPM_PROCESSDEFINITION table, so an ID_ of 197 now exists, but the >>> next time I start the server it will be looking to insert an >>> incremented foreign key value of 198 or higher into JBPM_NODE, so >>> the >>> constraint check will fail every time going forward. >>> >>> mysql> show engine innodb status; >>> ------------------------ >>> LATEST FOREIGN KEY ERROR >>> ------------------------ >>> 080717 3:22:15 Transaction: >>> TRANSACTION 0 1624, ACTIVE 0 sec, process no 7265, OS thread id >>> 1175591232 inserting, thread declared inside InnoDB 500 >>> mysql tables in use 1, locked 1 >>> 2 lock struct(s), heap size 368, undo log entries 1 >>> MySQL thread id 172, query id 5370 216.87.95.92 el_user update >>> insert into JBPM_NODE (NAME_, PROCESSDEFINITION_, ISASYNC_, ACTION_, >>> SUPERSTATE_, CLASS_) values ('start', 197, 0, null, null, 'R') >>> Foreign key constraint fails for table `nuxeo/JBPM_NODE`: >>> , >>> CONSTRAINT `FK_NODE_PROCDEF` FOREIGN KEY (`PROCESSDEFINITION_`) >>> REFERENCES `jbpm_processdefinition` (`ID_`) >>> Trying to add to index `FK_NODE_PROCDEF` tuple: >>> DATA TUPLE: 2 fields; >>> 0: len 8; hex 80000000000000c5; asc ;; 1: len 8; hex >>> 800000000000022c; asc ,;; >>> >>> But the parent table `nuxeo/jbpm_processdefinition` or its .ibd file >>> does not currently exist! >>> >>> >>> A few things changed recently for me. First, I upgraded this Nuxeo >>> instance from 5.1.4 to 5.1.5. Second, I've moved the MySQL >>> database to >>> a remote host. One concern would be if the order in which the >>> JBPM_NODE and JBPM_PROCESSDEFINITION records are being added changed >>> in the new release. Maybe JBPM_PROCESSDEFINITION record was >>> correctly >>> being added first, but is now being added after the attempt to add a >>> JBPM_NODE record with the invalid foreign key value? >>> >>> The first error is the following and the second error looks >>> identical >>> to the third error showcased above. >>> >>> 03:36:38,387 WARN [JDBCExceptionReporter] SQL Error: 1452, >>> SQLState: >>> 23000 >>> 03:36:38,388 ERROR [JDBCExceptionReporter] Cannot add or update a >>> child row: a foreign key constraint fails (`nuxeo/JBPM_ACTION`, >>> CONSTRAINT `FK_ACTION_EVENT` FOREIGN KEY (`EVENT_`) REFERENCES >>> `jbpm_event` (`ID_`)) >>> 03:36:38,390 ERROR [ComponentManager] Failed to create component: >>> >>> service:org >>> .nuxeo.ecm.platform.workflow.DefaultWorkflowDefinitionContributions >>> org.hibernate.exception.ConstraintViolationException: could not >>> insert: [org.jbpm.graph.action.Script] >>> >>> >>> The server does start up and I can login from a browser and click >>> around, but I'm concerned about some corrupt state on the server >>> given >>> those three failed SQL INSERTs. What are we risking by proceeding >>> with >>> missing JBPM_ACTION and JBPM_NODE records (data)? >>> >>> - Alex >>> _______________________________________________ >>> ECM mailing list >>> [email protected] >>> http://lists.nuxeo.com/mailman/listinfo/ecm >>> >> >> > _______________________________________________ > ECM mailing list > [email protected] > http://lists.nuxeo.com/mailman/listinfo/ecm -- Florent Guillaume, Director of R&D, Nuxeo Open Source Enterprise Content Management (ECM) http://www.nuxeo.com http://www.nuxeo.org +33 1 40 33 79 87 _______________________________________________ ECM mailing list [email protected] http://lists.nuxeo.com/mailman/listinfo/ecm
