Mysql – Add UNIQUE index fails with duplicate entry error, but no duplicates found

indexmariadbmyisamMySQL

Given table:

CREATE TABLE mytable (
  field_a CHAR(15) NOT NULL DEFAULT '',
  field_b MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  field_c SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  field_d SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  field_e CHAR(1) NULL DEFAULT '',
  field_f SMALLINT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=MyISAM DEFAULT CHARACTER SET=UTF8 COLLATE utf8_general_ci;

Required index:

ALTER TABLE mytable ADD UNIQUE INDEX idx_key (field_a, field_b);

Data size is about 51 Mio. rows. Following problem:

Attempt 1: If I try to create the index after the data is in the table it fails with duplicate key error. A select on the failed key returns only one(!) row.

 [23000][1062] Duplicate entry 'aaaaaaaaaaaaaaa-11111' for key 'idx_key'

 SELECT COUNT(*) FROM mytable WHERE field_a='aaaaaaaaaaaaaaa' AND field_b='11111'

returns 1 (!)

Attempt 2: If I create the index on an empty table or make a combined private key and then put the data into the table, the table contains only 27 of 51 Mio. rows (!).

Is there some kind of a limit on the unique index or a bug?

I use MariaDB 10.0.20. Please help.

Update 1

A count of unique rows

SELECT COUNT(DISTINCT field_a,field_b) from mytable;

returns 50 Mio rows. So there are about 1 Mio duplicates. This however does not explain the 27 Mio from attempt 2 or wrong exception from attempt 1.

Update 2

the table used by the attempt 2:

CREATE TABLE mytable (
  field_a CHAR(15) NOT NULL DEFAULT '',
  field_b MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  field_c SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  field_d SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  field_e CHAR(1) NULL DEFAULT '',
  field_f SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  UNIQUE KEY idx_key (field_a, field_b)
) ENGINE=MyISAM DEFAULT CHARACTER SET=UTF8 COLLATE utf8_general_ci;

Update 3

Attempt 2 error solved

To populate the table we use INSERT statements with multiple value rows in each. If a single value of the insert violated the unique constraint, all other values where also not inserted and caused 27 Mio rows instead of 50 Mio.

Approach 1:
However the bug with the wrong duplicate entry message still there even after the database were dropped and recreated, but this is an evil I can/must live with.

Best Answer

There is no guarantee that the valueaaaaaaaaaaaaaaa-11111 in the message

[23000][1062] Duplicate entry 'aaaaaaaaaaaaaaa-11111' for key 'mykey'

is the value that actually causes the violation. Seems to be a bug in MariaDB and in MySQL.

Related Question