[MDEV-22259] Duplicate entry `a-b-c` for key 'a_b_c_unique' when altering table Created: 2020-04-16  Updated: 2020-06-30  Resolved: 2020-06-30

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 10.4.12
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Sylvain RUMEU Assignee: Unassigned
Resolution: Cannot Reproduce Votes: 0
Labels: None
Environment:

debian 9, installed from official mariadb repo


 Description   

Hello

I have a table with an unique index

[10:20:12] mioux@10.1.2.81:Prisme> show create table pdc_pma;                                                                                                                                                                                
+---------+------------------------------------------------------------------------------------------------+
 
| Table   | Create Table                                                                                   |
 
|---------+------------------------------------------------------------------------------------------------|
 
| pdc_pma | CREATE TABLE `pdc_pma` (                                                                       |
 
|         |   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,                                               |
 
|         |   `pma` decimal(12,5) NOT NULL,                                                                |
 
|         |   `vente` int(11) unsigned NOT NULL,                                                           |
 
|         |   `code_isin` int(11) unsigned NOT NULL,                                                       |
 
|         |   `collaborateur` int(11) unsigned DEFAULT NULL,                                               |
 
|         |   `date` date NOT NULL,                                                                        |
 
|         |   `cgp` int(11) unsigned NOT NULL,                                                             |
 
|         |   PRIMARY KEY (`id`),                                                                          |
 
|         |   UNIQUE KEY `pma_unique` (`cgp`,`vente`,`code_isin`),                                         |
 
|         |   KEY `vente` (`vente`),                                                                       |
 
|         |   KEY `code_isin` (`code_isin`),                                                               |
 
|         |   KEY `collaborateur` (`collaborateur`),                                                       |
 
|         |   CONSTRAINT `pdc_pma_ibfk_1` FOREIGN KEY (`cgp`) REFERENCES `cgp` (`id`),                     |
 
|         |   CONSTRAINT `pdc_pma_ibfk_2` FOREIGN KEY (`collaborateur`) REFERENCES `collaborateur` (`id`), |
 
|         |   CONSTRAINT `pdc_pma_ibfk_3` FOREIGN KEY (`code_isin`) REFERENCES `code_isin` (`id`),         |
 
|         |   CONSTRAINT `pdc_pma_ibfk_4` FOREIGN KEY (`vente`) REFERENCES `vente` (`id`)                  |
 
|         | ) ENGINE=InnoDB AUTO_INCREMENT=2175037367 DEFAULT CHARSET=utf8                                 |
 
+---------+------------------------------------------------------------------------------------------------+
 
1 row in set

When trying to modify date from "not null" to "null", I have these errors

[10:11:27] mioux@10.1.2.81:Prisme> alter table pdc_pma modify `date` date null;                                         
You're about to run a destructive command.
 
Do you want to proceed? (y/n): y
 
Your call!
 
(1062, "Duplicate entry '1879-5935019-159327' for key 'pma_unique'")
 
[10:12:23] mioux@10.1.2.81:Prisme> alter table pdc_pma modify `date` date null;                                          
(1062, "Duplicate entry '3839-4877953-359170' for key 'pma_unique'")
 
[10:13:08] mioux@10.1.2.81:Prisme> alter table pdc_pma modify `date` date null;                                          
(1062, "Duplicate entry '10292-4865099-122091' for key 'pma_unique'")
 
[10:13:49] mioux@10.1.2.81:Prisme> alter table pdc_pma modify `date` date null;

No duplicate found

[09:23:18] mioux@10.1.2.81:Prisme> select cgp, vente, code_isin, count(*) from pdc_pma group by cgp, vente, code_isin h
 
                                   aving count(*) > 1;                                                                 
+-------+---------+-------------+------------+
 
| cgp   | vente   | code_isin   | count(*)   |
 
|-------+---------+-------------+------------|
 
+-------+---------+-------------+------------+
 
0 rows in set

The key found unique is never the same.

I tried to optimize the table to simulate a drop/create without success

I'm on an environment production

EDIT : The same operation on dev and staging are ok. We have a dump re-executed each week on these environments

 Comments   
Comment by Daniel Black [ 2020-04-18 ]

Do the reported duplicate key entries reported actually exist in your database?

Do their FK table entries exist?

Couldn't reproduce on a simple case:
https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=dd1f0ff0cb1a806b328d80fe3e89ed85

Comment by Sylvain RUMEU [ 2020-04-18 ]

They exists, once each

[10:05:55] srumeu@10.1.2.81:Prisme> select cgp, vente, code_isin from pdc_pma where  
                                    (cgp = 1879  and vente = 5935019 and code_isin = 159327) or 
                                    (cgp = 3839  and vente = 4877953 and code_isin = 359170) or 
                                    (cgp = 10292 and vente = 4865099 and code_isin = 122091);                  
+-------+---------+-------------+
 
| cgp   | vente   | code_isin   |
 
|-------+---------+-------------|
 
| 1879  | 5935019 | 159327      |
 
| 3839  | 4877953 | 359170      |
 
| 10292 | 4865099 | 122091      |
 
+-------+---------+-------------+

Comment by Sylvain RUMEU [ 2020-04-21 ]

I forgot to check refs tables :

[14:10:25] srumeu@10.1.2.81:Prisme> select id from cgp where id in (1879, 3839, 10292);                                                                      
+-------+
 
| id    |
 
|-------|
 
| 1879  |
 
| 3839  |
 
| 10292 |
 
+-------+
 
3 rows in set
 
[14:11:14] srumeu@10.1.2.81:Prisme> select id from vente where id in (5935019, 4877953,  4865099);                                                           
+---------+
 
| id      |
 
|---------|
 
| 4865099 |
 
| 4877953 |
 
| 5935019 |
 
+---------+
 
3 rows in set
 
[14:11:51] srumeu@10.1.2.81:Prisme> select id from code_isin where id in (159327, 359170, 122091);                                                           
+--------+
 
| id     |
 
|--------|
 
| 122091 |
 
| 159327 |
 
| 359170 |
 
+--------+
 
3 rows in set

(I had almost no doubt they exists, as we never had to disable foreign key checks)

Comment by Elena Stepanova [ 2020-05-01 ]

Are there any errors in the error log?
Did you try to run CHECK TABLE?

Does your dev environment have exact same server configuration as the production?
Which client do you use, and is it the same in production and dev environment? Can it be that the client has some extra background logic or uses extra scripts for "smarter" ALTERs?
I installed the current mycli on Debian 9 thinking it could be it, but it doesn't throw the "destructive" warning upon this ALTER, so it should be something else.

Comment by Sylvain RUMEU [ 2020-05-01 ]

CHECK TABLE did not report error. There is nothing in error log

FYI, the "destructive" warning is an option to add in .myclirc, I've set this on the environment production to avoid some mess

And sorry, but we had a master/slave configuration, the server had a very bad crash, we have stoped the master1, turned the slave2 into master2, completely trashed master1 to build a slave1... And the error is gone

I made the alter without error on the "new" master

Generated at Thu Feb 08 09:13:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.