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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DROP TABLE IF EXISTS ds_categories; CREATE TABLE ds_categories ( catID int(11) NOT NULL auto_increment, catName varchar(80) default NULL, catDesc text NOT NULL, catPhoto varchar(200) NOT NULL default '', catPhotoWidth int(11) NOT NULL default '0', catPhotoHeight int(11) NOT NULL default '0', PRIMARY KEY (catID), UNIQUE KEY catID (catID) ) TYPE=MyISAM; INSERT INTO ds_categories VALUES (0,'DEFAULT','','',0,0); INSERT INTO ds_categories VALUES (1,'Products','','',0,0); |
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:
|
1 |
SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO'; |
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.