Duplicate Entry For Key Error - Migration to MySQL

When creating a primary key or unique constraint after loading the data, you can get a “Duplicate entry for key 'PRIMARY'” error.

If the data in the source database is valid and there are no any duplicates you should check which collation is used in your MySQL database. By default, MySQL uses case-insensitive collation when creating a database, this means that the values that differ only in case are considered as equal.

Assume your source database is Oracle and you have the following data:

Oracle:

CREATE TABLE names
(
  name VARCHAR2(30) PRIMARY KEY  
);
 
INSERT INTO names VALUES ('Maria');
INSERT INTO names VALUES ('maria');

If you run this code in a case-insensitive MySQL database, you will get the error:

MySQL:

CREATE TABLE names
(
  name VARCHAR(30) PRIMARY KEY  
);
 
INSERT INTO names VALUES ('Maria');
INSERT INTO names VALUES ('maria');
-- ERROR 1062 (23000): Duplicate entry 'maria' for key 'PRIMARY'

To check the collation of your database in MySQL, run:

SELECT @@collation_database;
-- utf8_general_ci

_ci suffix means that it is case-insensitive.

SQLines Data tool uses utf8mb4 character set with utf8_bin collation by default, so you should not see this problem unless you change -mysql_character_set and -mysql_collate options in sqldata.cfg file.

In case you need other character set and collation choose a collate with suffix _cs in MySQL.