Details
-
Type:
Bug
-
Status: Closed (View Workflow)
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 5.5, 10.0, 10.1, 10.1.37, 10.1.38, 10.2, 10.3, 10.4
-
Component/s: Data Manipulation - Update
-
Labels:None
-
Environment:CentOS
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
- causes
-
MDEV-19491 update query stopped working after mariadb upgrade 10.2.23 -> 10.2.24
-
- Closed
-
- relates to
-
MDEV-24082 Can't update a temporay table joined with a non-temporary inside a read-only transaction
-
- Confirmed
-