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
-
MDEV-24083 Contrary to documentation, DDL for temp tables doesn't work inside read-only transactions
- Confirmed
-
MDEV-18507 can't update temporary table when joined with table with triggers on read-only
- Closed