·

photo credit: ....Tim via photopin cc

How to fix “Duplicate entry ‘1’ for key ‘PRIMARY'” in data migration in Magento

Tags: , ,

So this morning I was boggling on a data migration with Magento that kept throwing the following MySQL error:

This was really puzzling me, since all I did was I created a database dump and tried to import it on another server. So what was going on? Well… It turns out that Magento uses a primary key with value “0” in some cases (which is bad practice). MySQL’s default behaviour when inserting data in auto incremental fields with value “0” is to set it to the next possible auto increment value. Which – for an empty table – is “1”.

The query

So… if you have the following query:

MySQL will set the store_id  of line 1 to “1” instead of “0”, causing the importing of line 2 to fail, because there already exists a row where store_id  = 1. Oh you Magento…

The fix

The fix is utterly simple. It’s a typical example where hours of work, stress and debugging result in a one-liner that fixes it all. Make sure in your SQL-dump file to add the following line at the top:

The key here is NO_AUTO_VALUE_ON_ZERO : this tells MySQL to not set the value to the value that the auto incrementer proposes, but just set the value to “0”. And that’s it! It really is that simple…
You can also make it more permanent by editing your my.cnf -file and add the following rule to the [mysqld] -section:

You can also create a custom configuration file, just for this rule by creating a file called /etc/mysql/conf.d/sql_mode.cnf  and adding the following lines in it:

 

Visitors give this article an average rating of 3.7 out of 5.

How would you rate this article?

4 thoughts on “How to fix “Duplicate entry ‘1’ for key ‘PRIMARY'” in data migration in Magento”

  1. Jörg says:

    Hello Giel, thank you VERY MUCH for posting this – made my day :). Jörg

  2. Ricardo Albrecht says:

    Hello Giel,

    I’m trying to run a file in MySQL of my Magento store.

    I have a issue: “Duplicate entry ‘1’ for key ‘PRIMARY’.” Even adding “SET sql_mode = ‘NO_AUTO_VALUE_ON_ZERO’;”, the error continues.

    Can you help me?

    Thanks 🙂

  3. Andreas says:

    Thank you for sharing this! Saved me a few hours.

  4. Navi Srob says:

    made my day ! Thanks!

Leave a Reply