MySQL `INSERT INTO SELECT` clause generate Duplicate entry error on unique field

Question

I use INSERT INTO SELECT to migrate users' data across databases, but it generates

Duplicate entry '                  　' for key 'users_name_unique'

although the data source is another unique index and should not contain any duplicate data.('users_name_unique' is the index name on db2.users)

Here is the query, where name field from db2.users is varchar(50) unique and not null index, and name field from db1.users is varchar(60) unique and not null index. I have already checked the length of the field in every record, and the lengths are all much smaller than 50.

INSERT INTO db2.users (name, email, uid) SELECT
    name,
    IF (mail = '', NULL, mail) AS email,
    uid
FROM
    db1.users;

There are non-printable or white spaces in the name field from db1.users.

What might be the problem?

update

I created multi test tables, and as following, there are two tables with very similar structure and no data (I changed the length on purpose since the source data is varchar(60)), but different results.

    mysql> desc ttt3;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | NO   | UNI | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> desc users;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(60)      | NO   | UNI | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> show index from ttt3;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ttt3  |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| ttt3  |          0 | name     |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> show index from users;
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users |          0 | PRIMARY           |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| users |          0 | users_name_unique |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> insert into ttt3(name) select name from scratch.users where scratch.users.uid != 0;
Query OK, 1556 rows affected (0.24 sec)
Records: 1556  Duplicates: 0  Warnings: 0

mysql> insert into users(name) select name from scratch.users where scratch.users.uid != 0;
ERROR 1062 (23000): Duplicate entry '                  　' for key 'users_name_unique'

update

It turns out that the destination field's collation is set to 'utf8_unicode_ci' and the original field is 'utf8_general_ci', changing this option solve the problem.

I'd say that either your assumption is wrong (and source query does return dupes, which should be easy to verify) or target table already has some values. — Álvaro González, Aug 09 '16 at 06:56
@ÁlvaroGonzález I have dropped all data in db2.users to do the test. — leetom, Aug 09 '16 at 06:58
In Oracle, you sometimes need to drop the index created due to the constraint too, manually. Dropping the table does not drop the index. The index remains as is, and due to its unique nature, prevents further entry. Any such concept in mysql? http://dba.stackexchange.com/questions/136643/name-already-been-used-by-an-existing-constraint-after-renaming-the-table-and-cr/136715#136715 — I_am_Batman, Aug 09 '16 at 07:00
@ÁlvaroGonzález I don't think there could be any duplicate data from a field with unique index, but I did convert the source field from utf8 to utf8_mb4 then converted it back, if it means something. — leetom, Aug 09 '16 at 07:01
@leetom: You say you have non-printables in your primary key values. I don't know if some of the names have 'null' values in the name. Possibly the db-engine treates that as 'end of string' as many programming languages do. By this the rest of you Primary key gets truncated and you end up with duplicates... — Thomas Voß, Aug 09 '16 at 07:09
@leetom: Try this example to see what I meant: `SELECT 'aaa' + CHAR(0) + 'bbb'`. It should return 'aaa' (at least on mssql Server) — Thomas Voß, Aug 09 '16 at 07:16
@I_am_Batman I just tried to drop db2 and re-created it, with same result, 　so this shouldn't be the problem. — leetom, Aug 09 '16 at 07:16
@ThomasVoß `select concat('aaa', char(0), 'bbb')` return 'aaa bbb' in MySQL. If this is the reason, these records will violate the constraint in db1.users table first. — leetom, Aug 09 '16 at 07:20
http://stackoverflow.com/questions/31580682/oracle-user-indexes-translation-to-mysql After dropping, check if the index created due to primary key is also dropped. This should be the root cause. — I_am_Batman, Aug 09 '16 at 07:26
@I_am_Batman That's not the problem since I have tried with new database. The most strange thing is that there is no problem with manually created table but error with table generated by laravel, although with same structure. — leetom, Aug 09 '16 at 08:53
@Serg It turns out that the destination field's collation is set to 'utf8_unicode_ci', changing this option solve the problem. Thanks! — leetom, Aug 09 '16 at 09:06

score 2 · Answer 1 · answered Aug 09 '16 at 10:08

Here is the reason:

The destination field's collation is set to 'utf8_unicode_ci' (laravel's default collation) and the original field is 'utf8_general_ci'.

These collations have different rules of "sort" or "equal". Changing this option solved the problem.

MySQL `INSERT INTO SELECT` clause generate Duplicate entry error on unique field

update

update

It turns out that the destination field's collation is set to 'utf8_unicode_ci' and the original field is 'utf8_general_ci', changing this option solve the problem.

1 Answers1