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

Question

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.

Answer 1

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.

Answer 2

Plan A: Use INSERT IGNORE in your batched inserts. That way the dup keys do not cause trouble.

Plan B: Insert into a table with INDEX, not UNIQUE. Then you can investigate the duplicates before deciding what to do with them.

You understand that CHAR(15) utf8 occupies 45 bytes always for most ROW_FORMATs? Perhaps VARCHAR(15) would be better? (Please don't quote the debunked wives' tale about FIXED being better in MyISAM.)

Or maybe the data is, say, old IPv4 strings? They work fine with CHARACTER SET ascii -- 15 bytes for CHAR(15) or 1-16 bytes for VARCHAR(15). Then, what about IPv6? And about inability to compare ranges?