[MDEV-14056] DROP TEMPORARY TABLE IF EXISTS causes error 1290 with read_only option Created: 2017-10-12  Updated: 2017-10-17  Resolved: 2017-10-17

Status: Closed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 5.5, 10.0, 10.1, 10.1.28, 10.2
Fix Version/s: 5.5.58, 10.0.33, 10.1.29, 10.2.10

Type: Bug Priority: Critical
Reporter: Mark Peter Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None
Environment:

CentOS 6



 Description   

I have a replicated slave with "read_only=1" set in my.cnf

I have a user set up as follows:

CREATE USER 'slave_user'@'localhost' IDENTIFIED BY 'slave_pswd';
GRANT ALL ON mydb.* TO 'slave_user'@'localhost' WITH GRANT OPTION;

If I log in as 'slave_user', I can create a temporary table, write to it, and then drop it successfully:

CREATE TEMPORARY TABLE temp_x (id integer);
INSERT INTO temp_x values (1);
DROP TEMPORARY TABLE IF EXISTS temp_x;

However, if I have not created a temporary table, and I start with:

DROP TEMPORARY TABLE IF EXISTS temp_x;

I get the error: ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement

I have countless lines of application code which always checks to make sure a temporary table is dropped before creating it to avoid an "already exists" error. Always worked on MySQL 5.6 and lower, on the exact same slave database with read_only. After upgrading to MariaDB 10.1, this problem emerged.



 Comments   
Comment by Elena Stepanova [ 2017-10-12 ]

Thanks for the report.

The problem appeared in 5.5.57 with this commit:

commit 9b3360ea4417ed653d5c7eed29f4ef7e80618e43
Author: Sergei Golubchik <serg@mariadb.org>
Date:   Tue Jul 18 14:47:40 2017 +0200
 
    BUG#25250768: WRITING ON A READ_ONLY=ON SERVER WITHOUT SUPER PRIVILEGE
    
    simplify.
    add a test case.

Test case

CREATE USER 'foo';
SET GLOBAL read_only= on;
--connect (con1,localhost,foo,,)
DROP TEMPORARY TABLE IF EXISTS t1;
 
# Cleanup
--disconnect con1
--connection default
DROP USER 'foo';
SET GLOBAL read_only= DEFAULT;

Comment by Mark Peter [ 2017-10-12 ]

Thank you for the acknowledgment, Elena, and also for creating the simplified test case. If a fix is made, will there likely be a patch? Or would it be included in a new 10.1 minor version?

Comment by Elena Stepanova [ 2017-10-12 ]

markalanpeter,

As soon as the fix is made, which will hopefully happen soon, it will appear in the git repository in the lowest branch where it is fixed (I expect it to be 5.5), and will be merged up to higher versions soon afterwards; so, if you build from sources, you'll be able to take it as a patch from git. Otherwise, it will be released with every minor release which happen after the fix.

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