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

Contrary to documentation, DDL for temp tables doesn't work inside read-only transactions

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.3.25, 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
    • 10.5
    • None
    • None
    • Linux, WHM

    Description

      According to the documentation for "START TRANSACTION":

      READ ONLY mode allows the storage engine to apply optimizations that cannot be used for transactions which write data. The only exception to this rule is that read only transactions can perform DDL statements on temporary tables.

      Except it doesn't work:

      SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
      START TRANSACTION READ ONLY;
       
      CREATE TEMPORARY TABLE t (id int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,val int unsigned NOT NULL);
      INSERT INTO t (val) VALUES (22),(96),(47),(5),(22);
      SELECT * FROM t;
      DROP TEMPORARY TABLE t;
       
      COMMIT;
      

      ...Fails with "#1792 - Cannot execute statement in a READ ONLY transaction" on the CREATE TEMPORARY TABLE statement.

      Attachments

        Issue Links

          Activity

            People

              sanja Oleksandr Byelkin
              Dean T Dean Trower
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.