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

UPDATE not working properly on transaction precise system versioned table

    XMLWordPrintable

Details

    Description

      Starting MariaDB server version 10.4.18 some SQL updates on the transaction precise versioned table fail.
      100% reproducible with v10.4.18 and 10.4.19, see example below and detailed log in attachment v18_mariadb.log.

      Attached also the same SQL commands executed against version 10.4.17 where everything works fine - v17_mariadb.log, as well as server errorlog - did not spot anything special in there.

      Example:

      create or replace table xxx (nid int, nstate int, ntype int, primary key(nid) );
       
      alter table xxx add tx_start BIGINT UNSIGNED GENERATED ALWAYS AS ROW START INVISIBLE, add tx_end BIGINT UNSIGNED GENERATED ALWAYS AS ROW END INVISIBLE, add PERIOD FOR SYSTEM_TIME(tx_start, tx_end), add SYSTEM VERSIONING;
       
      insert into xxx values (1,1,1);
      Query OK, 1 row affected (0.003 sec)
       
      select nid, nstate, tx_start, tx_end from xxx for system_time all where nid=1;
      +-----+--------+----------+----------------------+
      | nid | nstate | tx_start | tx_end               |
      +-----+--------+----------+----------------------+
      |   1 |      1 |   189558 | 18446744073709551615 |
      +-----+--------+----------+----------------------+
      1 row in set (0.000 sec)
       
      start transaction;
      Query OK, 0 rows affected (0.000 sec)
       
      update xxx set nstate=nstate where nid=1;
      Query OK, 0 rows affected (0.001 sec)
      Rows matched: 1  Changed: 0  Warnings: 0
       
      select nid, nstate, tx_start, tx_end from xxx for system_time all where nid=1;
      +-----+--------+----------+----------------------+
      | nid | nstate | tx_start | tx_end               |
      +-----+--------+----------+----------------------+
      |   1 |      1 |   189558 |               189561 |
      |   1 |      1 |   189558 | 18446744073709551615 |
      +-----+--------+----------+----------------------+
      2 rows in set (0.001 sec)
       
      update xxx set nstate=3 where nid=1;
      ERROR 1761 (23000): Foreign key constraint for table 'xxx', record '1-18446744073709551615' would lead to a duplicate entry in table 'xxx', key 'PRIMARY'
      

      Attachments

        1. v18_mariadb.zip
          27 kB
        2. v17_v18_errorlog.zip
          5 kB
        3. v17_mariadb.zip
          27 kB

        Issue Links

          Activity

            People

              midenok Aleksey Midenkov
              ykantoni YURII KANTONISTOV
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.