Top Nav

MySQL Import Problem – Autoincrement

Trying to dump and import a mysql database from an old MySQL 3.x server to a 4.x server. Here’s an exerpt from the dump file:

What’s happening, is when it inserts the first row, it’s
auto-incrementing the key value to 1, so the next row that tries to
import gets a “Duplicate entry ‘1’ for key 1” error.

The solution was to add the following line to the top of your sql dump file:

Later versions of mysql do this automatically.

Problem is the first insert tries to put a 0 into the autoincrement
field. Normal behavior is for the 0 to be converted to the next
available increment which is one. Then the second row fails because
there is already a 1 in the table. The mode setting above tells mysql to
allow the 0 in the auto increment field.