Search code examples
mysqlmagentoauto-incrementdump

Magento sql import and auto increment duplicate error

I import a Magento sql dump in my phpmyadmin local (wamp), after changing the URL in core_config_data, my website works locally but the second time, I have an error :

a:5:{i:0;s:298:"SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate  '0' for key 'PRIMARY', query was: INSERT INTO `log_visitor_info`...

If I remove hte line in the log_visitor_info table, it works. It looks like an auto increment problem but how to fix it ?

Solution

  • There can be 2 reasons for this behaviour

    1. Auto increment initialisation setting

    As MySQL documentation on auto increment says:

    You can also explicitly assign 0 to the column to generate sequence numbers, unless the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled.

    Therefore, in the MySQL server you re importing your data into, this NO_AUTO_VALUE_ON_ZERO SQL mode is enabled, therefore MySQL treats the 0 values as literal value, not as a value that should be trigger the next auto increment value to be assigned.

    Solution: either disable this sql mode setting or omit this field from the insert statement.

    1. Conflict within the import file or between existing the data within the database and data to be imported

    You have to check if there is no existing data in the database or other data item in the file being imported that conflicts with the record producing the error.