Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Cannot Reproduce
-
10.4.12
-
None
-
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