Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3.25, 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
-
None
-
Linux, WHM
Description
Similarly to MDEV-18507, there seems to be a problem updating a temporary table when it's joined to a non-temporary, even if the latter isn't being updated.
On version 10.3.25, this fails with "#1792 - Cannot execute statement in a READ ONLY transaction", despite only attempting to update the temporary table:
CREATE TEMPORARY TABLE t (id int unsigned NOT NULL PRIMARY KEY,flag tinyint NOT NULL DEFAULT 0); |
CREATE TABLE p (id int unsigned NOT NULL PRIMARY KEY); |
INSERT INTO p VALUES (2); |
INSERT INTO t (id) VALUES (1),(2),(3),(4),(5); |
|
|
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
START TRANSACTION READ ONLY; |
|
|
UPDATE t INNER JOIN p USING (id) SET t.flag=1; |
|
|
COMMIT; |
DROP TABLE p; |
DROP TEMPORARY TABLE t; |
|
However, if you modify the UPDATE to use a subquery instead of a join, it now works:
UPDATE t SET t.flag=1 WHERE id IN (SELECT id FROM p); |
|
I think both should work?
Attachments
Issue Links
- relates to
-
-
- Confirmed
-
-
-
- Closed
-
Activity
Should be fixed for multi-table deletes too - I haven't tried, but I'd assume the same problem occurs.
Probably not. multi-update is the unique one where in, say, update t1, t2 SET a=b the server needs to map all fields to tables to know what table is actually going to be updated. Multi-delete doesn't not need it, the syntax unambiguously identifies what tables are to be modified.
Both should behave identically. The second succeeds, so the first should succeed too.
The problem is that until all tables are opened, the server doesn't necessarily know what table is going to be updated. So table locks and privilege checks are postponed until all tables are opened and the statement is fully analyzed (fix_fields).
But metadata locks happen early, so the server has to take MDL_SHARED_WRITE just in case. And open_table() errors out on mdl write in a read-only transaction. The fix should be to tolerate mdl write at least for multi-updates and error out later, after fix_fields. And also downgrade mdl to MDL_SHARED_READ.