mysqlimport and "duplicate entry" error

Question

I've used mysqlimport for MyISAM tables a lot in the past, without any problems. But now i want to import data into a InnoDB table and are facing the following problem: mysqlimport reports the following error and won't import anything:

mysqlimport: Error: 1062, Duplicate entry '1' for key 'PRIMARY', when using table: ...

... and i don't know how to resolve this error. The table i want to import the data into is freshly created, without any data. The table looks like the following:

CREATE TABLE `member` (
    `member_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID of Member',
    ...
    PRIMARY KEY (`member_id`),
    ...
) ENGINE=InnoDB;

The data i want to import includes the "member_id", which is defined as "auto_increment" in the table. Of course there are no duplicate 'member_id' in the csv-file -- i've tripple-checked this. Can this cause any errors when importing into MySQL ... and if so: how can i resolve this?

MySQL Server version is: 5.5.8

can you paste a few lines of your csv file here ? and with the output of this query "SHOW CREATE TABLE `member`" — risyasin, Apr 09 '11 at 10:13
i was able to reproduce this with a simple 1-column table and one single row ... i was able to fix this by de-activating the auto-increment and re-enabling it after import, however ... — harald, Apr 11 '11 at 06:17

score 0 · Accepted Answer · answered Apr 11 '11 at 06:27

After toying around with this the whole weekend, i came to the conclusion, that mysqlimport is not for InnoDB tables:

I was able to reproduce the problem from my question with a simple 1-column table and one single row ... i was able to fix this by de-activating the auto-increment and re-enabling it after import. However, i ran into other problems:

mysqlimport would complain foreign key constraints and reject importing tables, even though all depending tables and rows where available. So: mysqlimport won't work, but the same data with INSERT statement worked.

The solution for this is as simple as not using mysqlimport. Use the LOAD DATA INFILE statement instead:

mysql -u <user-name> -p<password> --host=<host> --port=<port> -e "\
    USE <database-name>; \
    SET FOREIGN_KEY_CHECKS = 0; \
    LOAD DATA INFILE '<csv-file-name>' \
    INTO TABLE <table-name> (<comma-separated-list-of-column-names>); \
    SET FOREIGN_KEY_CHECKS = 1;"

mysqlimport and "duplicate entry" error

1 Answers1