So I've been snapshotting away before every significant change to my site and now came the time where I actually encountered a problem and tried to roll back to a previous snapshot. What I got was reams of error messages pertaining to civicrm, starting with

* user warning: Cannot delete or update a parent row: a foreign key constraint fails query: DROP TABLE civicrm_acl in /path/to/drupal/modules/demo/demo.module on line 303.
* user warning: Cannot delete or update a parent row: a foreign key constraint fails query: DROP TABLE civicrm_activity in /path/to/drupal/modules/demo/demo.module on line 303.
* user warning: Cannot delete or update a parent row: a foreign key constraint fails query: DROP TABLE civicrm_address in /path/to/drupal/modules/demo/demo.module on line 303.
* user warning: Cannot delete or update a parent row: a foreign key constraint fails query: DROP TABLE civicrm_case in /path/to/drupal/modules/demo/demo.module on line 303.
* user warning: Cannot delete or update a parent row: a foreign key constraint fails query: DROP TABLE civicrm_component in /path/to/drupal/modules/demo/demo.module on line 303.
* user warning: Cannot delete or update a parent row: a foreign key constraint fails query: DROP TABLE civicrm_contact in /path/to/drupal/modules/demo/demo.module on line 303.

and going on like that for a few pages, then

* user warning: Duplicate entry '1' for key 1 query: INSERT INTO `civicrm_acl` VALUES (1, 'Edit All Contacts', 0, 'civicrm_acl_role', 1, 'Edit', 'civicrm_saved_search', 0, NULL, NULL, 1), (2, 'Core ACL', 0, 'civicrm_acl_role', 0, 'All', 'access CiviMail subscribe/unsubscribe pages', NULL, NULL, NULL, 1), (3, 'Core ACL', 0, 'civicrm_acl_role', 0, 'All', 'access all custom data', NULL, NULL, NULL, 1), (4, 'Core ACL', 0, 'civicrm_acl_role', 0, 'All', 'make online contributions', NULL, NULL, NULL, 1), (5, 'Core ACL', 0, 'civicrm_acl_role', 0, 'All', 'make online pledges', NULL, NULL, NULL, 1), (6, 'Core ACL', 0, 'civicrm_acl_role', 0, 'All', 'profile listings and forms', NULL, NULL, NULL, 1), (7, 'Core ACL', 0, 'civicrm_acl_role', 0, 'All', 'register for events', NULL, NULL, NULL, 1), (8, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'access CiviCRM', NULL, NULL, NULL, 1), (9, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'access CiviContribute', NULL, NULL, NULL, 1), (10, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'access CiviEvent', NULL, NULL, NULL, 1), (11, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'access CiviMail', NULL, NULL, NULL, 1), (12, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'access CiviMail subscribe/unsubscribe pages', NULL, NULL, NULL, 1), (13, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'access CiviMember', NULL, NULL, NULL, 1), (14, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'access CiviPledge', NULL, NULL, NULL, 1), (15, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'access Contact Dashboard', NULL, NULL, NULL, 1), (16, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'access all custom data', NULL, NULL, NULL, 1), (17, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'access uploaded files', NULL, NULL, NULL, 1), (18, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'add contacts', NULL, NULL, NULL, 1), (19, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'administer CiviCRM', NULL, NULL, NULL, 1), (20, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'edit all contacts', NULL, NULL, NULL, 1), (21, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'edit contributions', NULL, NULL, NULL, 1), (22, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'edit event participants', NULL, NULL, NULL, 1), (23, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'edit groups', NULL, NULL, NULL, 1), (24, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'edit memberships', NULL, NULL, NULL, 1), (25, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'edit pledges', NULL, NULL, NULL, 1), (26, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'import contacts', NULL, NULL, NULL, 1), (27, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'make online contributions', NULL, NULL, NULL, 1), (28, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'make online pledges', NULL, NULL, NULL, 1), (29, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'profile listings and forms', NULL, NULL, NULL, 1), (30, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'register for events', NULL, NULL, NULL, 1), (31, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'view all activities', NULL, NULL, NULL, 1), (32, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'view all contacts', NULL, NULL, NULL, 1), (33, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'view event info', NULL, NULL, NULL, 1), (34, 'Core ACL', 0, 'civicrm_acl_role', 1, 'All', 'view event participants', NULL, NULL, NULL, 1), (35, 'Core ACL', 0, 'civicrm_acl_role', 2, 'All', 'access CiviMail subscribe/unsubscribe pages', NULL, NULL, NULL, 1), (36, 'Core ACL', 0, 'civicrm_acl_role', 2, 'All', 'access all custom data', NULL, NULL, NULL, 1), (37, 'Core ACL', 0, 'civicrm_acl_role', 2, 'All', 'make online contributions', NULL, NULL, NULL, 1), (38, 'Core ACL', 0, 'civicrm_acl_role', 2, 'All', 'make online pledges', NULL, NULL, NULL, 1), (39, 'Core ACL', 0, 'civicrm_acl_role', 2, 'All', 'profile listings and forms', NULL, NULL, NULL, 1), (40, 'Core ACL', 0, 'civicrm_acl_role', 2, 'All', 'register for events', NULL, NULL, NULL, 1), (41, 'Core ACL', 0, 'civicrm_acl_role', 2, 'All', 'view event info', NULL, NULL, NULL, 1), (42, 'View Events', 0, 'civicrm_acl_role', 0, 'View', 'civicrm_event', 0, NULL, NULL, 1); in /path/to/drupal/drupal-6.4/includes/database.mysqli.inc on line 128.

etc, and then finally

Failed restoring database from /path/to/db.sql

I assume that I still have the pre-existing civicrm data because demo did just not restore what it could not delete, but it would be nice to know that in case of a problem it would work with the whole shebang.

CommentFileSizeAuthor
#6 demo-foreign-key-312215.patch913 bytessethfisher

Comments

sun’s picture

sun
Primary language German
Location Karlsruhe
commented
Title: doesn't play ball with civicrm » Foreign key support (CiviCRM)
Version: 6.x-1.1 » 6.x-1.x-dev
Category: bug » feature

Hm. Looks like CiviCRM is using foreign keys. Demo does not have support for foreign keys yet. However, patches would be welcome. When patching, keep in mind that Demo's SQL dumps are 99% comparable to phpMyAdmin's SQL dumps - so the schema output for foreign keys should match the output of phpMyAdmin as well.

I guess the Duplicate key error is directly tied to foreign key support.

rondp’s picture

rondp commented

Thanks for your reply, sun.
If I could code PHP and knew what foreign keys are, I would have a go at it, but unfortunately neither of the two applies. Sorry.

I do agree, though, that the duplicate key error seems to have occurred because the records had not been deleted, which (according to the error message) in turn had to do with foreign keys, so yes, they are tied. That seems rather indirect, but perhaps you see a more direct connection as you know more about the inner workings of it all.

rondp’s picture

rondp commented

Just found something in the CiviCRM documentation (for an older version but anyway) here:
http://wiki.civicrm.org/confluence/display/CRM/*Upgrade+Drupal+Systeme+v...

If you are getting foreign key errors when attempting to run the upgrade script, you can try adding the following lines to the script and running it again:

-- add this above the first line
SET FOREIGN_KEY_CHECKS = 0;
.
.
-- add this below the last line
SET FOREIGN_KEY_CHECKS = 1;

Might that help in this case as well, or would the foreign keys (mind you, I don't know what they are) somehow need to explicitly be backed up and restored as well?

smk-ka’s picture

smk-ka commented
Status: Active » Fixed

Fixed, thanks.
http://drupal.org/cvs?commit=149466

Anonymous’s picture

Anonymous (not verified) commented
Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for two weeks with no activity.

sethfisher’s picture

sethfisher commented
Category: feature » bug
Status: Closed (fixed) » Needs review
StatusFileSize
new demo-foreign-key-312215.patch913 bytes

I encountered the same issue (I'm using CiviCRM stored in the Drupal DB). I've submitted a patch which works for me.

I wrapped the DROP TABLEs in the same FOREIGN_KEY_CHECKS toggling as comment #3 (http://drupal.org/comment/reply/312215#comment-1027646) indicated. The rest of the script is already wrapped in the toggling, as per the last fix, but it happens too late as the DROP TABLEs are executed before the rest of the script.

Since the message I got indicated the error was on line 256 of demo.admin.inc, rather than line 303 of demo.module (indicated in this original issue), perhaps a code change reintroduced the issue.

sun’s picture

sun
Primary language German
Location Karlsruhe
commented
Status: Needs review » Fixed

Thanks for reporting, reviewing, and testing! Committed a slightly modified version to all branches.

A new development snapshot will be available within the next 12 hours. This improvement will be available in the next official release.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.