4

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.

  • You can try searching for any duplicates using "select count(1) c from mytable group by field_a,field_b having c > 1;" - There might be some problem with collation or using CHAR instead of VARCHAR, but I was not able to find colliding test case (same version of mariadb) – jkavalik Jul 8 '15 at 7:25
  • I've already tried this this limit 100, but cancelled after 35 minutes. I however did an other count now. see my question update. – kromit Jul 8 '15 at 8:01
  • And please don't just describe attempt 2. Add in the question the exact code you used. – ypercubeᵀᴹ Jul 8 '15 at 8:04
  • @kromit I see, can you try with limit 5 or 10 if it returns something faster? so you have some results at least, or adding index on (field_a,field_b) (non unique) might make it much faster too. – jkavalik Jul 8 '15 at 8:07
  • I suspect it might be a corrupted index or a collation issue. But why would anyone use MyISAM these days? InnoDB should be the preferred. – ypercubeᵀᴹ Jul 8 '15 at 8:10
3

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 evtl. in MySQL.

  • Could you make it into a test case with few to few thousands of rows which reproduces this? – jkavalik Jul 8 '15 at 11:05
  • Sorry, I've tried this with 500k but without success. Seems to be something in >1 Mio area. – kromit Jul 8 '15 at 11:26
  • How did you conclude that there is no guarantee? – ypercubeᵀᴹ Jul 8 '15 at 11:37
  • Because the returned combination exist only once. I guess its just a message bug. Evtl. the left or right neighbor is the real duplicate entry. – kromit Jul 9 '15 at 6:20
  • I agree with kromit. I have updated a table, setting UNIQUE to a field and the error message was wrong (indicating a value which was not duplicated). I was able to find the real "duplicate value" and they were not the one of the message. – Peter Jul 22 '16 at 22:11
1

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? 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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.