Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4.7
Description
Example code below demonstrates the issue. It seems that the transaction_id for transaction 1 is not stored in the transaction_registry, even though the results of the transaction are committed to the DB.
drop table if exists productFK1;
drop table if exists products1;
create table products1 (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
start_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW START,
end_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME(start_trxid, end_trxid),
PRIMARY KEY (id)) WITH SYSTEM VERSIONING;
create table productFK1 (
product_id INT(11) NOT NULL,
CONSTRAINT `fk_productId` FOREIGN KEY (`product_id`) REFERENCES products1(id)
) WITH SYSTEM VERSIONING;
– Transaction 1
begin;
insert into products1 (name) VALUES('t1');
– this fails foreign key constraint and error
insert into productFK1 (product_id) VALUES(100);
commit;
– Transaction 2
begin;
insert into products1 (name) VALUES('t2');
insert into productFK1 (product_id) VALUES(1);
commit;
– Notice both t1 and t2 are present in the table:
select name, start_trxid from products1 ;
– But where did the t1 trxid go?:
select transaction_id from mysql.transaction_registry;
Attachments
Issue Links
- relates to
-
MDEV-16226 TRX_ID-based System Versioning refactoring
-
- Stalled
-
Activity
I should mention that this causes a larger issue with transaction-precise SVT: In the above case, selecting from the table AS OF historical time will cause an error like this:
{ Error: (conn=299246, no: 4129, SQLState: HY000) TRX_ID 25889 not found in `mysql.transaction_registry`
Because transaction id 25889 was recorded as start_trxid in the user table, but no corresponding entry for 25889 was created in the transaction_registry.
mkg Yes, that certainly reproduces. Transactional versioning needs more polishing (MDEV-16226) but unless we know good use for it these tasks are of minor priority. May I ask why did you decide to try it? Do you know any real production requirements where transactional versioning is preferred in favor of timestamp-based?
Hi Aleksey,
I, perhaps mistakenly, decided to use transaction versioning in a production application I have developed. The application needs to show the user the history of changes they have made to the data. We chose to use transaction-precise versioning instead of timestamp versioning for the obvious reason that we wanted a strong guarantee that it provides. We did not want the risk that our application would say a change was made at time=T, when actually that change wasn't visible until time>T.
If this feature is not ready for prime-time, then perhaps you should note that here so that others do not make the same mistake I made: https://mariadb.com/kb/en/library/temporal-data-tables/
Matt
I see. The original design pursued more general goal of going beyond timer resolution. `transaction_registry` has many drawbacks though: it is slow, it is hard to backup/restore, it is not portable between nodes. So your message led me to think again of a design without `transaction_registry`. Thanks and sorry for the trouble!
Any news on this one? Can you reproduce on your side?
thanks