[MDEV-20458] missing row in mysql.transaction_registry when error occurred during transaction Created: 2019-08-30  Updated: 2023-10-06

Status: Confirmed
Project: MariaDB Server
Component/s: Versioned Tables
Affects Version/s: 10.4.7
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Matt Assignee: Aleksey Midenkov
Resolution: Unresolved Votes: 0
Labels: trx-versioning

Issue Links:
Relates
relates to MDEV-16226 TRX_ID-based System Versioning refact... Stalled

 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;

 Comments   
Comment by Matt [ 2019-12-02 ]

Any news on this one? Can you reproduce on your side?

thanks

Comment by Matt [ 2019-12-03 ]

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.

Comment by Aleksey Midenkov [ 2019-12-04 ]

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?

Comment by Matt [ 2019-12-04 ]

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

Comment by Aleksey Midenkov [ 2019-12-05 ]

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!

Generated at Thu Feb 08 08:59:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.