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

can't update temporary table when joined with table with triggers on read-only

    XMLWordPrintable

Details

    Description

      Very similar to an old mySQL bug (https://bugs.mysql.com/bug.php?id=86163), you will get an error about read only when executing an update statement against a temp table if that table is joined to non-temp tables, even though the targetting columns are all in the temporary table.

      Borrowing the example from that bug report:

      ## As root user with SUPER privilege run the following:
       
      CREATE TABLE table1(
      	`id` INT NOT NULL,
      	`name` VARCHAR(10) NOT NULL
      );
       
      CREATE TABLE log1(
      	info VARCHAR(10) NOT NULL
      );
       
      SET GLOBAL read_only=1;
       
      ## Insert some fake data
       
      ## Then as another user that has basic privileges but not the SUPER privilege run the following:
       
      CREATE TEMPORARY TABLE TEMP_TABLE1 (
      	`id` INT NOT NULL,
      	`update_me` VARCHAR(10)
      );
       
      UPDATE TEMP_TABLE1 LEFT JOIN table1 ON TEMP_TABLE1.id = table1.id SET TEMP_TABLE1.update_me = 'hello';
       
      ## you should get an error complaining that it is running with the --read-only option
      

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              TalbotMcInnis Talbot McInnis
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.