Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.6.4
-
10.6.4-MariaDB-1:10.6.4+maria~focal-log
Description
There is an error that I am running across but am unable to repeat on a simple schema. If anyone can point me in the right direction to collect more details, I would be happy to look.
The problem arises when attempting to add a unique key on two columns with the following:
set @@system_versioning_alter_history=1; |
ALTER TABLE manufacturer_pn |
ADD CONSTRAINT manufacturer_pn_UN |
UNIQUE KEY (manufacturer, manufacturer_pn); |
This results in a duplicate entry error:
`Duplicate entry 'Yageo-RC0805FR-071ML' for key 'manufacturer_pn_UN'
In an effort to find the problem, I searched for everything that matched the problematic key:
SELECT id, manufacturer, manufacturer_pn, ROW_START, ROW_END |
FROM manufacturer_pn |
FOR SYSTEM_TIME BETWEEN (NOW() - INTERVAL 1 YEAR) AND NOW() |
WHERE manufacturer_pn LIKE 'RC0805FR-071ML' |
ORDER BY ROW_END; |
Results of the command:
id |manufacturer|manufacturer_pn|ROW_START |ROW_END |
|
----+------------+---------------+-------------------+-------------------+
|
5407|Yageo |RC0805FR-071ML |2021-12-06 10:05:11|2021-12-16 00:13:34|
|
5363|Yageo |RC0805FR-071ML |2021-12-06 10:05:11|2021-12-16 00:13:34|
|
5407|Yageo |RC0805FR-071ML |2021-12-16 00:13:34|2021-12-29 12:03:47|
|
5363|Yageo |RC0805FR-071ML |2021-12-16 00:13:34|2021-12-29 12:22:30|
|
5363|Yageo Group |RC0805FR-071ML |2021-12-29 12:22:30|2021-12-29 23:41:56|
|
5363|Yageo |RC0805FR-071ML |2021-12-29 23:41:56|2021-12-29 23:57:01|
|
5363|Yageo Test |RC0805FR-071ML |2021-12-29 23:57:01|2021-12-29 23:57:31|
|
Note that none of these rows are active, so there should be no violation. This leads me to think that adding the unique constraint is running a check on historical rows, which it shouldn't. Setting `check_constraint_checks` to 0 also has no effect, and adding the key still fails.
As mentioned, I would be happy to dig deeper to try to find the cause since I can't seem to replicate this issue on table from scratch.