Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-20458

missing row in mysql.transaction_registry when error occurred during transaction

    XMLWordPrintable

    Details

      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

          Activity

            People

            Assignee:
            midenok Aleksey Midenkov
            Reporter:
            mkg Matt
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated: