0

I recently had a craft update fail when moving from 2.4x to 2.5x. This was probably caused because the linux user craft was using did not have the proper permissions. This is all good and fine. Craft created a DB backup before updating and this is where I am having a very strange issue. This DB backup has tons of duplicates, seemingly on every table. Importing the database always ends in Duplicate entry '1' for key 'PRIMARY'. The dump is also trying to add the same tables multiple times. The database I am importing also into has nothing in it. Has anyone run into this issue?

Here is a partial database dump: http://pastebin.com/FRgp1FPy

If you for example look at line 100 and 135, you can see that the dump has duplicate primary keys in the table craft_assetfiles. This dump also shows that the craft_assetfiles table is being added twice and with different data each time.

| improve this question | | | | |
  • What's the actual error occurring? Can you post some of the SQL code with the duplicate data? – Brad Bell Dec 14 '15 at 17:13
1

It's because (at least for the one table you posted) your table is missing its foreign keys, which is responsible for maintaining uniqueness on their referenced columns. For example, the asset_files table should have this as part of its DDL:

CONSTRAINT `craft_assetfiles_folderId_fk` FOREIGN KEY (`folderId`) REFERENCES `craft_assetfolders` (`id`) ON DELETE CASCADE,
CONSTRAINT `craft_assetfiles_id_fk` FOREIGN KEY (`id`) REFERENCES `craft_elements` (`id`) ON DELETE CASCADE,
CONSTRAINT `craft_assetfiles_sourceId_fk` FOREIGN KEY (`sourceId`) REFERENCES `craft_assetsources` (`id`) ON DELETE CASCADE

This can happen if the database was imported to a MySQL box that only had MyISAM (which doesn't support foreign keys) and the keys will silently get dropped.

Or we've seen it happen from a failed import/export whether from a script or a MySQL client.

Regardless, you can use this script to try and help restore the missing foreign keys: https://github.com/pixelandtonic/craft-fixfks

Be aware you still might have to do some manual data cleanup from duplicated/inconsistent data.

| improve this answer | | | | |
  • It actually looks like the database was using InnoDB on those tables. Could you expand on the "failed" import / export script? It does not seem like a export script failure would cause just the foreign keys from not importing. Everything else seems to be there. – David B Dec 14 '15 at 19:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.