Mysql – How to fix #1062 error received while importing EE database to localhost from remote server

importMySQL

I am trying to import a MySQL database into the localhost database that was exported from the remote database and I am receiving a #1062 error (which is weirdly in French!):

#1062 - Duplicata du champ '1' pour la clef 'PRIMARY'

Google translate: # 1062 - Duplicate field '1' for key 'PRIMARY'

The code that it highlights is as follows:

--
-- Dumping data for table `exp_accessories`
--

INSERT INTO `exp_accessories` (`accessory_id`, `class`, `member_groups`, `controllers`, `accessory_version`) VALUES
(1, 'Expressionengine_info_acc', '1|5', 'content|members|admin_content|design|tools_communicate|homepage|addons_fieldtypes|content_files|admin_system|tools_data|addons|tools|tools_logs|tools_utilities|addons_accessories|content_files_modal|myaccount|addons_modules|addons_plugins|content_publish|content_edit|addons_extensions', '1.0'),
(2, 'Cartthrob_acc', '1|5', 'content|members|admin_content|design|tools_communicate|homepage|addons_fieldtypes|content_files|admin_system|tools_data|addons|tools|tools_logs|tools_utilities|addons_accessories|content_files_modal|myaccount|addons_modules|addons_plugins|content_publish|content_edit|addons_extensions', '1.0');

I am a novice with SQL and I can't see what it is on about. I can't see a duplicate in the primary key, the accessory_ids appear to be 1 and 2? Am I missing something here? And why is there a duplicate here and not on the remote server?

Many thanks

Gabe

Best Answer

Your local table is likely not empty, thus the error when conflicting with existent data. If you do not want to keep any of the current data, you need to delete all rows before the insert. One fast way to do that is with:

TRUNCATE TABLE exp_accessories;

Usually, mysqldump will drop the table before import, like this:

DROP TABLE IF EXISTS `exp_accessories`;

Regarding French, client sessions can change the default language setting by doing SET lc_messages = 'en_US'; and receive error messages in a different language.

Related Solutions

Mysql – Error while importing a “.sql” file in database

Since you have an ASCII file, the script could be giving problems because it is came from a Windows machine and you are attempting to load it into mysql on a Linux machine.

You may want to try one of two things:

Using the mysql client in Windows, load the data remotely.
Use sql client in raw mode in one of two ways
- mysql -hIP -r -uroot -p DN_name < filename.sql
- mysql -hIP -B -uroot -p DN_name < filename.sql

Postgresql – Getting error while importing large data through csv

It really depends which constraints are violated. You neglected to disclose that in your question.

To disable triggers (including foreign key constraints):

ALTER TABLE tbl DISABLE TRIGGER ALL;

Be sure to revert it afterwards:

ALTER TABLE tbl ENABLE TRIGGER ALL;

Alternative

An alternative would be to import your CSV file into a temporary copy of the target table and repair any data that would violate constraints before inserting from there. Depends on the size of the file and available RAM. You could also make this a regular table for huge files or longer processing.

CREATE TEMP TABLE tmp AS SELECT * FROM target_table LIMIT 0;
COPY tmp FROM '/path/tp/file.csv';

For instance, to remove duplicates from imported data as well as between import and existing data:

INSERT INTO target_table
SELECT DISTINCT *
FROM tmp
LEFT JOIN target_table t0 ON ...
WHERE t0.target_table_id IS NULL;

Best Answer

Related Solutions

Mysql – Error while importing a “.sql” file in database

Postgresql – Getting error while importing large data through csv

Alternative

Related Question