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

UPDATE not working properly on transaction precise system versioned table

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Critical
    • Resolution: Unresolved
    • Affects Version/s: 10.4.18
    • Fix Version/s: None
    • Component/s: Versioned Tables
    • Labels:
    • Environment:
      CentOS 7

      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 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. v17_mariadb.zip
          27 kB
        2. v17_v18_errorlog.zip
          5 kB
        3. v18_mariadb.zip
          27 kB

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            ykantoni YURII KANTONISTOV
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

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