[MDEV-24082] Can't update a temporay table joined with a non-temporary inside a read-only transaction Created: 2020-11-01  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Data Manipulation - Update
Affects Version/s: 10.0, 10.1, 10.3.25, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Dean Trower Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Linux, WHM

Issue Links:
Relates
relates to MDEV-24083 Contrary to documentation, DDL for te... Confirmed
relates to MDEV-18507 can't update temporary table when joi... Closed

 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?

 Comments   
Comment by Sergei Golubchik [ 2020-11-02 ]

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.

Comment by Dean Trower [ 2020-11-02 ]

Should be fixed for multi-table deletes too - I haven't tried, but I'd assume the same problem occurs.

Comment by Sergei Golubchik [ 2020-11-02 ]

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.

Comment by Dean Trower [ 2020-11-02 ]

Ah, yes, you're right. DELETE doesn't exhibit the problem, I checked.

Generated at Thu Feb 08 09:27:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.