<p>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 <code>asset_files</code> table should have this as part of its DDL:</p>

<pre><code>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
</code></pre>

<p>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.</p>

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

<p>Regardless, you can use this script to try and help restore the missing foreign keys: <a href="https://github.com/pixelandtonic/craft-fixfks" rel="nofollow">https://github.com/pixelandtonic/craft-fixfks</a></p>

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

CC BY-SA 3.0
2015-12-14T19:42:16.537Z
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
CC BY-SA 3.0
2015-12-14T18:22:41.683Z
2015-12-14T18:22:41.683Z
Brad Bell
<p>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? </p>

<p>Here is a partial database dump:
<a href="http://pastebin.com/FRgp1FPy" rel="nofollow">http://pastebin.com/FRgp1FPy</a></p>

<p>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.</p>

CC BY-SA 3.0
2015-12-14T17:13:48.170Z
What's the actual error occurring?  Can you post some of the SQL code with the duplicate data?
Brad Bell
<database>
Database Backup Contains Perplexing Number Of Duplicates
# Database Backup Contains Perplexing Number Of Duplicates

(Asked by: username_0 on 2015-12-14)

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.

**Tags:** database

> **Comments:**
> **username_1**: What's the actual error occurring?  Can you post some of the SQL code with the duplicate data?  

---

## 1 answers

### Answer by username_1 on 2015-12-14. Score: 1 | Accepted answer

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.

> **Comments:**
> **username_0**: 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.  

---

