MySQL Bugs: #86163: can't update temporary table when joined with table with triggers on read-only

Bug #86163	can't update temporary table when joined with table with triggers on read-only
Submitted:	2 May 2017 21:29	Modified:	17 Mar 0:40
Reporter:	Bret Westenskow	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	All, 5.7.18	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	read only, temporary table, trigger, UPDATE

Description:
You can't run an update on a temporary table on a read only host when the update contains a join to a table that has a trigger. I originally found this bug on mysql 5.1 but have looked at other versions and I believe this bug is on them all.

How to repeat:
## 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
);

delimiter //

CREATE TRIGGER `table1_after_update` AFTER UPDATE ON `table1`
FOR EACH ROW
BEGIN
		INSERT INTO log1 (info) VALUES ('info');
END;//

delimiter ;

SET GLOBAL read_only=1;

## 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

Suggested fix:
The server should allow the update query on the temporary table to go through. It shouldn't care that a real table with triggers on a read only host is joined in the query because nothing on the real table gets changed.

Thank you for the bug report. Please provide the statement you used to create and define the basic privileges for the second user without root privileges. Thanks in advance.

CREATE USER 'testuser1'@'%' IDENTIFIED BY 'test';

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 'testuser1'@'%';

Thank you for providing the requested details.
Observed with 5.7.18 build(also, seems to be repeatable in latest 5.5/5.6 GA's as well).

Thanks,
Umesh

This issue is known to affect all releases 5.7+. Updated "Known Issues in MySQL" (see dev.mysql.com/doc/refman/en/known-issues.html) in all current versions with this info (mysqldoc rev 80825).

Status unchanged.

This is a known problem. It is not easy to address this corner case and would require a lot of efforts to make it right. Hence at this moment it has been decided to document it as a known problem.

https://dev.mysql.com/doc/refman/8.0/en/known-issues.html