Error messages about "foreign key constraint fails"

leftfoot's Avatar

leftfoot

03 Jan, 2014 04:32 AM

After dumping a production Cascade-7.6 DB on one DB server and importing into another DB server, stopping, reconfiguring, and restarting Cascade-7.6, everything appears to be up and running OK, but there are some apparent error messages that occur in "catalina.out" and "cascade.log" files:

 ... 118 more
Caused by: java.sql.BatchUpdateException: Cannot delete or update a parent row: a foreign key constraint fails (`cascade703`.`cxml_assetstats`, CONSTRAINT `FK_ASSETSTATS_ASSET` FOREIGN KEY (`assetId`) REFERENCES `cxml_foldercontent` (`i
d`))
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2028)
    at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1451)
    at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
    at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
    at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
    at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246)
    ... 133 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`cascade703`.`cxml_assetstats`, CONSTRAINT `FK_ASSETSTATS_ASSET` FOREIGN KEY (`
assetId`) REFERENCES `cxml_foldercontent` (`id`))
.
.
.
    ... 138 more
2014-01-02 22:22:10,763 WARN  [JDBCExceptionReporter] SQL Error: 1451, SQLState: 23000
2014-01-02 22:22:10,763 ERROR [JDBCExceptionReporter] Cannot delete or update a parent row: a foreign key constraint fails (`cascade703`.`cxml_assetstats`, CONSTRAINT `FK_ASSETSTATS_ASSET` FOREIGN KEY (`assetId`) REFERENCES `cxml_folder
content` (`id`))
2014-01-02 22:22:10,763 ERROR [AbstractFlushingEventListener] Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update.
  1. Support Staff 1 Posted by Tim on 03 Jan, 2014 02:09 PM

    Tim's Avatar

    Hi,

    I believe that you are running into this issue which was corrected for 7.8. When you have the chance, can you try applying the 7.8.x upgrade to this instance and let me know if those messages disappear?

    If you prefer not to apply the upgrade at this point, you can also try the following:

    • Be sure to create a backup of your database (to be safe!)
    • Execute the following query:
      delete stats from cxml_assetstats stats inner join cxml_foldercontent fc on stats.assetId=fc.id where fc.isRecycled=1
      
      (this query should take care of the issue at the moment, but won't prevent it from occurring in the future)

    Let me know if this helps!

  2. 2 Posted by leftfoot on 07 Jan, 2014 04:37 PM

    leftfoot's Avatar

    -----BEGIN PGP SIGNED MESSAGE-----
    Hash: SHA1

    Thanks for the suggestions. I will try the MySQL "workaround" on a
    test server. We will eventually move to Cascade-7.8, but I am not sure
    how soon.

  3. Support Staff 3 Posted by Tim on 07 Jan, 2014 04:38 PM

    Tim's Avatar

    OK, sounds good! Just let us know if you have any questions regarding the upgrade process or the upgrade itself. Take care!

  4. Tim closed this discussion on 07 Jan, 2014 04:38 PM.

Comments are currently closed for this discussion. You can start a new one.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac