This does not look like a problem of the InnoDB storage engine, but rather a problem with the implementation of versioned tables in MariaDB.
I would expect the ROW_START column to be appended to the PRIMARY KEY (I will assume it is id) and therefore to each index. So, we would have PRIMARY KEY(id,ROW_START). If the SQL layer (including the versioning related changes) told InnoDB that the secondary index definition is UNIQUE KEY manufacturer_pn_UN(manufacturer,manufacturer_pn), then the internal representation of the UNIQUE KEY manufacturer_pn_UN in InnoDB should be (manufacturer, manufacturer_pn, id, ROW_START). Because the dict_index_t::n_uniq would be 2, InnoDB would flag a uniqueness violations. InnoDB only tolerates ‘duplicates’ for NULL values, and in that case the internal uniqueness will be guaranteed by the PRIMARY KEY values. Example:
CREATE TABLE t(a INT PRIMARY KEY, b INT UNIQUE) ENGINE=InnoDB; |
INSERT INTO t VALUES(1,NULL),(2,NULL); |
Here, in the index tree of UNIQUE b(b,a) we would have the values (NULL,1),(NULL,2).
I don’t know if this really is a code bug or a user error. A possible work-around could be to explicitly include the ROW_START column in the index:
set @@system_versioning_alter_history=1; |
ALTER TABLE manufacturer_pn |
ADD CONSTRAINT manufacturer_pn_UN |
UNIQUE KEY (manufacturer, manufacturer_pn, ROW_START); |
In that way, the internal representation would be (manufacturer,manufacturer_pn,ROW_START,id), and the uniqueness violation should disappear.
tgross35, can you try this? I did not try it myself, because your SQL example is not self-contained.
ROW_START shouldn't be part of a PRIMARY KEY, why would it? It'd mean you can do
create table t1 (id int primary key, b varchar(100)) with system versioning; |
insert t1 values (1, "first row"); |
insert t1 values (1, "another first row o_O"); |
but ROW_END is, indeed, implicitly appended to unique keys.
See first two rows, at (or just before) 2021-12-16 00:13:34 you had two rows with the value Yageo-RC0805FR-071ML.
As documentation says, table metadata is not versioned, after ALTER TABLE the new table looks as if it had new structure from the very beginning, only the data is versioned.
https://mariadb.com/kb/en/system-versioned-tables/#system_versioning_alter_history
And because you had conflicting rows earlier, you cannot add a UNIQUE constraint that was violated somewhere in the table's past.
I have since upgraded this server to 11.1, but the problem still seems to exist. @marko that query claims ROW_START does not exist, probably because it is anonymous.
But I think Sergei found the issue, I didn't even realize that two of those rows must have gotten inserted in the same transaction. That makes sense, this is easy enough to repeat knowing that:
MariaDB [db]> create table t1 (id serial, a int, b int) with system versioning; |
MariaDB [db]> insert into t1 (id, a, b) values (1, 1, 1), (2, 1, 1); |
MariaDB [db]> delete from t1; |
MariaDB [db]> select * from t1; |
Empty set (0.000 sec) |
|
|
MariaDB [db]> select *, ROW_START, ROW_END from t1 for system_time all; |
+----+------+------+----------------------------+----------------------------+ |
| id | a | b | ROW_START | ROW_END |
|
+----+------+------+----------------------------+----------------------------+ |
| 1 | 1 | 1 | 2024-01-23 23:54:16.065755 | 2024-01-23 23:56:09.719628 |
|
| 2 | 1 | 1 | 2024-01-23 23:54:16.065755 | 2024-01-23 23:56:09.719628 |
|
+----+------+------+----------------------------+----------------------------+ |
2 rows in set (0.000 sec) |
|
|
MariaDB [db]> set @@system_versioning_alter_history=1; |
MariaDB [db]> alter table t1 add constraint foo unique key (a, b); |
ERROR 1062 (23000): Duplicate entry '1-1' for key 'foo' |
It threw me off that the time isn't included in the error message, I couldn't understand why adding other keys with duplicate history worked but this specific one failed.
Thanks all for the help. Wish that `DELETE HISTORY` took a where clause 
I stand corrected on how the PRIMARY KEY is being padded with version-related metadata. I think that for InnoDB and MyRocks, ROW_START would have been a better choice than ROW_END. In heap-organized storage engines it might matter less.
A DELETE or UPDATE operation would have to update ROW_END of the original record. In InnoDB, updating a PRIMARY KEY column will require delete-marking the record in all indexes, inserting a new record in all indexes, and eventually, purging the delete-marked record. In MyRocks, I understand that this operation would involve inserting a tombstone record for the record that may have carried an infinite ROW_END, followed by inserting a new record with the ROW_END set to the current time. As far as I can tell, ROW_START is ‘immutable’ after the record has been created, and therefore it would have been a better choice in this regard. That is, a DELETE (or the DELETE part of a versioned UPDATE) would only update a non-indexed column in the existing record.