Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.37, 10.1.38, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
-
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
-
-
- Closed
-
- relates to
-
-
- Confirmed
-
Activity
Thanks a lot for the report!
Reproducible on MariaDB 5.5-10.4
create table t1 (id int not null, v1 varchar(10) not null); |
insert into t1 values (1,1),(2,2); |
|
|
create table t2 (v2 varchar(10) not null); |
insert into t2 values (1),(2); |
|
|
create trigger t1_after_update after update on t1 |
for each row insert into t2 (v2) values ('v2'); |
|
|
create user foo; |
grant select, insert, update, delete, create, drop, reload, index, alter, show databases, create temporary tables, lock tables, execute, create view, show view, create routine, alter routine, trigger on *.* to 'foo'@'%'; |
|
|
set @start_read_only = @@global.read_only; |
set global read_only=1; |
|
|
connect (a, localhost, foo); |
|
|
create temporary table temp_t1 (id int not null, update_me varchar(10)); |
insert into temp_t1 values (1,1),(2,2),(3,3); |
|
|
update temp_t1 left join t1 on temp_t1.id = t1.id set temp_t1.update_me = 'hello'; |
|
|
connection default; |
SET GLOBAL read_only = @start_read_only; |
drop table t1,t2,temp_t1; |
MariaDB [test]> UPDATE TEMP_TABLE1 LEFT JOIN table1 ON TEMP_TABLE1.id = table1.id SET TEMP_TABLE1.update_me = 'hello';
|
ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement
|
See these commits in bb-5.5-serg:
a3d17cf0ddb MDEV-18507 can't update temporary table when joined with table with triggers on read-only
|
cbad3d24301 bugfix: multi-update checked privileges on views incorrectly
|
...
|
aea5a601197 cleanup
|
As usual, "cleanup" does not change the behavior, just rearranging code. "bugfix" fixes a related bug, and includes changes needed for MDEV-18507 (the bug showed up when I started making changes for MDEV-18507).
This issue has been present in several past versions, and to date we have been able to work around it by manipulating our queries but we hit one where all workarounds incur a significant performance penalty. We have disabled read_only for now but we are at risk until this can be fixed so we can turn on read-only again.