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

Can't update a temporay table joined with a non-temporary inside a read-only transaction

    XMLWordPrintable

Details

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

    Description

      Similarly to MDEV-18507, there seems to be a problem updating a temporary table when it's joined to a non-temporary, even if the latter isn't being updated.

      On version 10.3.25, this fails with "#1792 - Cannot execute statement in a READ ONLY transaction", despite only attempting to update the temporary table:

      CREATE TEMPORARY TABLE t (id int unsigned NOT NULL PRIMARY KEY,flag tinyint NOT NULL DEFAULT 0);
      		 
      CREATE TABLE p (id int unsigned NOT NULL PRIMARY KEY);
      		 
      INSERT INTO p VALUES (2);
      		 
      INSERT INTO t (id) VALUES (1),(2),(3),(4),(5);
      		 
       
      		 
      SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
      		 
      START TRANSACTION READ ONLY;
      		 
       
      		 
      UPDATE t INNER JOIN p USING (id) SET t.flag=1;
      		 
       
      		 
      COMMIT;
      		 
      DROP TABLE p;
      		 
      DROP TEMPORARY TABLE t;

      However, if you modify the UPDATE to use a subquery instead of a join, it now works:

      UPDATE t SET t.flag=1 WHERE id IN (SELECT id FROM p);

      I think both should work?

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24083 Contrary to documentation, DDL for temp tables doesn't work inside read-only transactions

          • Major - Major loss of function.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18507 can't update temporary table when joined with table with triggers on read-only

          • Major - Major loss of function.
          • Closed

          Activity

            People

              sanja Oleksandr Byelkin
              Dean T Dean Trower
              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.