MySQL Bugs: #50709: read_only affects temporary table

Bug #50709	read_only affects temporary table
Submitted:	29 Jan 2010 4:50	Modified:	14 Oct 2010 16:20
Reporter:	Siva Nookala	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.1.38	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	insert, read_only, temporary tables

Description:
After starting the server in read_only mode, we could not insert records into temporary table. The server allows us to create the temporary table but when I try to insert into the temporary table I see the error

Error Code : 1290
The MySQL server is running with the --read-only option so it cannot execute this statement

This issue was reported in bug# 4544 and fix was available from 5.0.16. It may have worked at some time but not working in 5.1.38

How to repeat:
Start the server in read_only mode using a user account with SUPER privilege. 

SET GLOBAL READ_ONLY=TRUE;

Then using a non-super user run the statement

create temporary table tmp_tbl
select 1 from dual;

Suggested fix:
In read_only mode server should allow CRUD operations for temporary tables.

Thank you for the problem report. Please, check if this problem is repeatable with a newer version, 5.1.42.

Another bug# 33669 for this same issue is reported for 5.1.41 and 6.0.4-p2

http://bugs.mysql.com/bug.php?id=33669

Thank you for the feedback.

Please provide output of SHOW GRANTS for user you are trying to create temporary table as.

Output of SHOW GRANTS

GRANT SELECT, INSERT, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EVENT ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*676243218923905CF94CB52A3C9D3EB30CE8E20D'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `test_db`.* TO 'test'@'%'

Thank you for the feedback.

I still can not repeat described behavior. Please provide your configuration file too.

Hi, 

I am working on windows and did not change anything in my installed my.ini file. Apart from the root user I created a user called "test" without GRANT permissions.  Using root I executed SET GLOBAL READ_ONLY=TRUE; statement. 

Then with test user (in a different session) I tried to insert records into temporary tables. 

Thank you for you help. 
Please let me know if you need anything else. 

Siva

Thank you for the feedback.

> Using root I executed SET GLOBAL READ_ONLY=TRUE; statement. 

Have you reconnected test user after you issued this statement?

Yes, you have to reconnect test user after you set the database in read_only mode

Found a solution. I can insert into the temporary table if I create it using MEMORY engine.

Thank you for the feedback.

Windows default configuration has default storage engine InnoDB. So this really looks like same as bug #33669. I assume having temporary table created with option engine=MyISAM would work for you. Please check, so we are sure no new bug here.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I have the problem with temporary tables on slave servers (so all working with read_only) on various 5.1 versions (verified on 5.1.37, 5.1.47, 5.1.49).
All 5.0 versions I used seem not affected.

The problem is to make any update operation to a temporary table.

Here is a recipe how to replicate the bug (I hope you don't think it's a feature):
(No need to set up master - replica to test it)

-----------
Login as super user.

CREATE DATABASE test;
CREATE TABLE test.test1 ( name char(10) );
INSERT INTO test.test1 values("dpa");

GRANT SELECT, INSERT, UPDATE, DELETE, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'test'@'localhost' IDENTIFIED BY 'test123';

SET GLOBAL read_only=1;
SET GLOBAL storage_engine="InnoDB";

Then relogin to the server:

mysql -utest -ptest123

CREATE TEMPORARY TABLE test.test2 ( name char(10) );
Query OK, 0 rows affected (0.09 sec)

DESC test.test2;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |

INSERT INTO test.test2 values ("a");
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

CREATE TEMPORARY TABLE test.test3 ( name char(10) ) AS SELECT name from test.test1;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
----------

MyISAM and Memory engines are not affected, but since many people have InnoDB as default is a serious issue to change all the applications to define explicit some other engine.

Please if you also have any other workaround than using 'super' right or turn off 'read_only' let me know :)

Not sure why the OS for this bug page is set to Windows, but for me it happens on Linux.

Przemek,

you experience bug #33669. Please either use MyISAM for temporary tables or upgrade to 5.5 series.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".