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

    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

              • Assignee:
                serg Sergei Golubchik
                Reporter:
                TalbotMcInnis Talbot McInnis
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: