Mysql – MariaDB: Duplicate entry for key on OPTIMIZE with no duplications

indexinnodbmariadbMySQL

There is a table named contracts with following structure:

MariaDB [db_name]> DESCRIBE `contracts`;
+-----------------------------+--------------+------+-----+---------+----------------+
| Field                       | Type         | Null | Key | Default | Extra          |
+-----------------------------+--------------+------+-----+---------+----------------+
| id                          | int(11)      | NO   | PRI | NULL    | auto_increment |
| name                        | varchar(255) | YES  |     | NULL    |                |
| number                      | varchar(255) | YES  |     | NULL    |                |
| service_name                | varchar(255) | YES  |     | NULL    |                |
| begin_date                  | date         | YES  |     | NULL    |                |
| end_date                    | date         | YES  |     | NULL    |                |
| main_contract               | varchar(255) | YES  |     | NULL    |                |
| status                      | varchar(255) | YES  |     | NULL    |                |
| uuid                        | varchar(36)  | YES  |     | NULL    |                |
| database_id                 | int(11)      | YES  |     | NULL    |                |
| abonent_uuid                | varchar(36)  | YES  | MUL | NULL    |                |
| deleted                     | tinyint(1)   | YES  | MUL | 0       |                |
| active                      | tinyint(1)   | YES  |     | NULL    |                |
| payment_condition_name      | varchar(255) | YES  |     | NULL    |                |
| payment_condition_form_type | varchar(255) | YES  |     | NULL    |                |
| gas                         | tinyint(1)   | NO   |     | 0       |                |
| service_uuid                | varchar(36)  | YES  |     | NULL    |                |
| document_type               | int(11)      | YES  |     | NULL    |                |
| document_id                 | varchar(255) | YES  |     | NULL    |                |
| document_status             | varchar(255) | YES  |     | NULL    |                |
| signed                      | tinyint(1)   | YES  |     | 0       |                |
| saved                       | tinyint(1)   | YES  | MUL | 0       |                |
| organization_code           | varchar(255) | YES  |     | NULL    |                |
+-----------------------------+--------------+------+-----+---------+----------------+
23 rows in set (0.00 sec)

There is a unique composite index on abonent_uuid and uuid:

MariaDB [db_name]> SHOW INDEX FROM `contracts`;
+-----------+------------+----------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name                   | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| contracts |          0 | PRIMARY                    |            1 | id           | A         |      384633 |     NULL | NULL   |      | BTREE      |         |               |
| contracts |          0 | index_contr_on_abon_uuid   |            1 | abonent_uuid | A         |      384633 |     NULL | NULL   | YES  | BTREE      |         |               |
| contracts |          0 | index_contr_on_abon_uuid   |            2 | uuid         | A         |      384633 |     NULL | NULL   | YES  | BTREE      |         |               |
| contracts |          1 | index_contracts_on_deleted |            1 | deleted      | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| contracts |          1 | index_contracts_on_saved   |            1 | saved        | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+----------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

When I try to make mysqlcheck or OPTIMIZE (mysqlcheck -u root -p -o db_name contracts) on this table, I got this error:

db_name.contracts
note     : Table does not support optimize, doing recreate + analyze instead
error    : Duplicate entry '5661d82c-70d1-b019-3200-ca61e3f72e46-b9073669-4c3d-a089-6c2f-ddc' for key 'index_contr_on_abon_uuid'
status   : Operation failed

So we've got the following conflicting uuids:

5661d82c-70d1-b019-3200-ca61e3f7
2e46-b9073669-4c3d-a089-6c2f-ddc

Or only parts of them, full uuids are 36-bytes length. I don't know why MariaDB is cropping them. Indexes were created using this query:

ALTER TABLE `contracts` ADD UNIQUE INDEX `index_contr_on_abon_uuid` (`abonent_uuid`, `uuid`);

Also I've tried this (same result):

DROP INDEX `index_contr_on_abon_uuid` ON `contracts`;
ALTER TABLE `contracts` ADD UNIQUE INDEX `index_contr_on_abon_uuid` (`abonent_uuid`(36), `uuid`(36));

But there are NO non-unique records in the table (even if we crop uuids):

MariaDB [db_name]>   SELECT `abonent_uuid`, `uuid`
  ->   FROM `contracts`
  ->   GROUP BY SUBSTRING(`abonent_uuid`, 1, 32), SUBSTRING(`uuid`, 1, 32)
  ->   HAVING COUNT(*) > 1;
Empty set (8.36 sec)

This error vanishes sometimes, but sometimes appears again sporadically. There were NO operations produced on this table during my experiments.

Hence, the questions: WHY does this error emerge? HOW can I fix it?

P.S.

Debian GNU/Linux 9 (stretch)

mysql Ver 15.1 Distrib 10.1.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

ENGINE=InnoDB;

Best Answer

"Normal" UUIDs are 36 characters (including 4 dashes), not 32. Fix that, and your dup key may go away.