-1

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;

  • Did you mean to define UUID columns as VARBINARY may be? I suspect you're confusing binary UUID values with their character representations somewhere. – mustaccio Jul 5 at 12:58
  • Explain where your UUIDs come from. 2e46-b9073669-4c3d-a089-6c2f-ddc is not a valid format, and it is probably truncated. – Rick James Jul 5 at 17:10
  • @RickJames, UUID is truncated in the INDEX. Original UUIDs in table records are full 36 characters (as expected). As you can see in the question above I've tried creating index, specifying the length of index field explicitly, but this composite index still remains 32+32 char long. Furthermore, there are no duplicates even with truncated UUIDs, which I proved by making SELECT query using GROUP BY with SUBSTRING function. – Prolif Ovar Jul 6 at 10:33
  • @mustaccio, UUIDs are defined as varchar(36), as you can see in my DESCRIBE query. – Prolif Ovar Jul 6 at 10:51
  • 1
    @ProlifOvar - the pair of "conflicting uuids" looked strange. So, I was guessing that the break between the uuids was not what you had guessed. I'm guessing that the "Duplicate entry '...'" message is truncated to 64 chars. – Rick James Jul 8 at 13:35
0

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

  • This doesn't explain the problem. – ypercubeᵀᴹ Jul 5 at 19:10
  • @ypercubeᵀᴹ - This may not explain the problem, but it points out that the OP has a programming error(s). – Rick James Jul 5 at 19:26

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.