[MDEV-18507] can't update temporary table when joined with table with triggers on read-only Created: 2019-02-07  Updated: 2020-11-01  Resolved: 2019-04-24

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Update
Affects Version/s: 5.5, 10.0, 10.1, 10.1.37, 10.1.38, 10.2, 10.3, 10.4
Fix Version/s: 10.2.24, 5.5.64, 10.1.39, 10.3.15, 10.4.5

Type: Bug Priority: Major
Reporter: Talbot McInnis Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None
Environment:

CentOS

Issue Links:
Problem/Incident
causes MDEV-19491 update query stopped working after ma... Closed
Relates
relates to MDEV-24082 Can't update a temporay table joined ... Confirmed

 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



 Comments   
Comment by Talbot McInnis [ 2019-02-07 ]

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.

Comment by Alice Sherepa [ 2019-02-11 ]

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

Comment by Sergei Golubchik [ 2019-04-05 ]

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).

Generated at Thu Feb 08 08:44:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.