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

UPDATE on a SYSTEM_TIME ... AUTO table under LOCK TABLES READ never returns when it needs a new history partition

    XMLWordPrintable

Details

    Description

      Likely cause: MDEV-17554, commit 92bfc0e8c4bb5c86359c29458d67f3e7836ec18a, which added the AUTO creation of history partitions in 10.9.1. The code that loops comes from that commit.

      --source include/have_partition.inc
      --source include/have_innodb.inc
      call mtr.add_suppression("need more HISTORY partitions");
      SET timestamp= UNIX_TIMESTAMP('2026-05-02 00:00:00');
      CREATE TABLE t1 (a INT) ENGINE=InnoDB WITH SYSTEM VERSIONING PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR AUTO;
      INSERT INTO t1 VALUES (1);
      --connect (con1,localhost,root,,)
      SET timestamp= UNIX_TIMESTAMP('2026-05-02 05:00:00');
      LOCK TABLES t1 READ;
      --let $id= `SELECT CONNECTION_ID()`
      --send UPDATE t1 SET a = 2
      --connection default
      --let $n= 0
      --let $busy= 1
      while ($busy)
      {
        if ($n == 50)
        {
          --eval KILL $id
          --die An UPDATE under LOCK TABLES READ that needs a new history partition does not return, it keeps running until it is killed
        }
        --sleep 0.1
        --let $busy= `SELECT COUNT(*) FROM information_schema.PROCESSLIST WHERE ID = $id AND COMMAND = 'Query'`
        --inc $n
      }
      --connection con1
      --error ER_TABLE_NOT_LOCKED_FOR_WRITE
      --reap
      UNLOCK TABLES;
      --disconnect con1
      --connection default
      DROP TABLE t1;

      Leads to:

      CS 13.1.2 87e62ca17270e163a6d41b3fc8d6f4d440f1bb88 (Optimized, Clang 22.1.8-20260622) Build 06/10/2026

      mysqltest: At line 25: An UPDATE under LOCK TABLES READ that needs a new history partition does not return, it keeps running until it is killed

      The thread stays in state "Opening tables" and uses a full CPU core until it is killed. Without AUTO, the UPDATE gives ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated. After the KILL, each later UPDATE that needs a new history partition of t1 loops in the same way, in any connection, also without LOCK TABLES. Debug builds loop in the same way.

      FLUSH TABLES t1 FOR EXPORT in place of LOCK TABLES t1 READ gives the same loop, and so do DELETE, REPLACE, INSERT ... ON DUPLICATE KEY UPDATE, SELECT ... FOR UPDATE and OPTIMIZE TABLE. Under LOCK TABLES t1 WRITE, an ALTER TABLE or CREATE TRIGGER that runs when a new history partition is needed makes the session lose its lock on t1 without an error, and the next UPDATE loops.

      From the code it seems that TABLE::vers_switch_partition() sets vers_skip_auto_create in the share and asks for the new partition, and nothing clears the flag when no partition is added. Under the read lock, open_ltable() fails in Open_table_context::recover_from_failed_open(). Under the write lock, Locked_tables_list::reopen_tables() cannot add the partition, so the reopen fails and drops t1 from LOCK TABLES. Each later open sees the flag and asks for OT_REOPEN_TABLES again.

      Attachments

        Issue Links

          Activity

            People

              midenok Aleksey Midenkov
              Roel Roel Van de Paar
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - Not Specified
                  Not Specified
                  Logged:
                  Time Spent - 1h
                  1h

                  Git Integration

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