New restore in 1.30 fails with foreign key constraint error

[clarkac1]

Description of the bug

After running update.php which included the new backup (see https://docs.backdropcms.org/documentation/creating-backups) I tried the restore. The restore failed with the message:

SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails

This was the only message I could find. The system was still usable and I guess had not been updated.

Steps To Reproduce

1. Create a backup folder
2. Add the line into settings.php in a 1.30 system to point to the backup folder
3. Run an update which will create a backup. Be aware: If there is no backup to be done, no backup is created.
4. Run example.com/core/restore.php

Actual behavior

The following message was produced:

SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails

Expected behavior

System should be restored to prior to the update done in 3. above

Additional information

Add any other information that could help, such as:

• Backdrop CMS version: 130
• Web server and its version: Apache 2.4.62
• PHP version: 8.3.14
• Database server (MySQL or MariaDB?) and its version: 10.6.19-MariaDB
• Operating System and its version: Linux 3.10.0-962.3.2.lve1.5.81.el7.x86_64
• Browser(s) and their versions: Firefox 134.0.2

[clarkac1]

I should add that this (test) site has CiviCRM installed, sharing the same database. Is the new backup/restore going to work in that situation?

[indigoxela]

Is the new backup/restore going to work in that situation?

Probably not out of the box, but with some settings. Have a look at the code, as there's probably no detailed documentation available, yet: https://github.com/backdrop/backdrop/blob/1.x/core/includes/backup/backup.database.inc#L134

But the settings form isn't fully implemented yet...

When you run an update with backup, which doesn't need a backup... what do the backup files look like after that? The directory gets created? What's in it?

The restore seems to find them, but they're not usable?

[quicksketch]

I should add that this (test) site has CiviCRM installed, sharing the same database. Is the new backup/restore going to work in that situation?

The Backup functionality currently backs up all tables in the active database (except simpletest tables and the contents of cache tables).

Then when running the Restore functionality, it drops each table before creating it again. It sounds like possibly the tables need to dropped in a particular order when foreign keys are in use (Backdrop does not use real MySQL foreign keys, but CiviCRM might).

I found a similar issue from Backup and Migrate module here: https://www.drupal.org/project/backup_migrate/issues/1542274, which also seemed to affect CiviCRM users in particular.

It looks like they solved the problem by disabling foreign key checks during the restore operation.

[quicksketch]

I filed a PR at backdrop/backdrop#5020

It includes the following patches:

• https://www.drupal.org/project/backup_migrate/issues/1542274#comment-7969247
• https://www.drupal.org/project/backup_migrate/issues/3143250#comment-13648990

The second patch is not necessary to fix this particular issue, but I ran across that same problem while inspecting the header and footer code segments. As both patches are cross-ports from Drupal 7 B&M they both seem safe to include.

[quicksketch]

Elaborating a little on how that PR works (theoretically):

There is the following line of SQL in the backup SQL file that should disable foreign key checks during restore:

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

However during restore, the current check of $first3 = substr($line, 0, 2); is incorrectly only checking the first two characters of the line, making the comparison "/*" == "/*!", which will never be true. Changing the condition to include 3 characters corrects the comparison, making it so "/*!" == "/*!", which evaluates to true and the SQL command is executed, disabling foreign key checks during the restore process.

@clarkac1 Could you try applying those changes to your Backdrop installation and see if it fixes the restore capability?

[argiepiano]

As both patches are cross-ports from Drupal 7 B&M they both seem safe to include.

Hmm... these need to be ported to Backdrop's B&M as well. I'll open an issue there.