Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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

share|improve this question
    
can you paste a few lines of your csv file here ? and with the output of this query "SHOW CREATE TABLE member" –  risyasin Apr 9 '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 6:17

1 Answer 1

up vote 0 down vote accepted

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;"
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.