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

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: