Home / server / Questions / 257673
Accepted
harald
Asked: 2011-04-10 01:42:47 +0800 CST

mysqlimport and "duplicate entry" error

  • 772

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

mysql csv import

1 Answers

  1. Best Answer

    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;"
    • 0