MySQL Bugs: #35732: read-only blocks SELECT statements in InnoDB

Bug #35732	read-only blocks SELECT statements in InnoDB
Submitted:	1 Apr 2008 7:51	Modified:	13 May 2008 20:10
Reporter:	Todd Farmer (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.24-bk	OS:	Any
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any
Tags:	regression

Description:
In 5.1.24-bk, setting the server to read-only causes SELECT statement issued against InnoDB tables (only) to fail:

mysql> use test;
Database changed
mysql> create table mupp (a int) engine=innodb;
Query OK, 0 rows affected (0.76 sec)

mysql> grant select on *.* to 'mupp'@'%';
Query OK, 0 rows affected (0.13 sec)

mysql> insert into mupp values (1),(2),(3);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> set @@global.read_only = on;
Query OK, 0 rows affected (0.09 sec)

mysql> Bye
leviathan:24526 johani$ mysql -umupp -S /tmp/51.mysql.sock test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.24-rc-enterprise-gpl MySQL Homebuilt Server

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from mupp;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

This bug is not present in 5.1.23.

How to repeat:
1.  Create InnoDB table in 5.1-bk.
2.  Insert data
3.  Create non-SUPER account with SELECT privileges
4.  Set server to read-only
5.  Connect as non-SUPER user, issue SELECT against InnoDB table.

Suggested fix:
Make SELECT statements not generate errors.

Only known workaround at this time is to enable updates (eg, disable read-only).

The global read-only option is implemented in the MySQL layer of code. InnoDB does not know about it.

This may help:

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test
Database changed
mysql> create table test_myisam (id int) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> create table test_innodb (id int) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> create user test_read_only ;
Query OK, 0 rows affected (0.01 sec)

mysql> grant select on test.* to 'test_read_only'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_myisam values (1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into test_innodb values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> set @@global.read_only = on;
Query OK, 0 rows affected (0.01 sec)

mysql> quit
Bye

$ mysql -u test_read_only test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 5.1.24-rc-log MySQL Enterprise Server (GPL) - Snapshot from  03-18-2008

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from test_myisam;
+------+
| id   |
+------+
|    1 | 
|    2 | 
|    3 | 
+------+
3 rows in set (0.00 sec)

mysql> select * from test_innodb;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

If I put a begin statement first, then it appears to work after that, even if I disconnect, and reconnect:

mysql> begin
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_innodb;
+------+
| id   |
+------+
|    1 | 
|    2 | 
|    3 | 
+------+
3 rows in set (0.00 sec)

mysql> quit
Bye

$ mysql -u test_read_only test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 43
Server version: 5.1.24-rc-log MySQL Enterprise Server (GPL) - Snapshot from  03-18-2008

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from test_myisam;
+------+
| id   |
+------+
|    1 | 
|    2 | 
|    3 | 
+------+
3 rows in set (0.00 sec)

mysql> select * from test_innodb;
+------+
| id   |
+------+
|    1 | 
|    2 | 
|    3 | 
+------+
3 rows in set (0.00 sec)

mysql> quit
Bye

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44975

ChangeSet@1.2576, 2008-04-07 13:34:55+05:00, ramil@mysql.com +3 -0
  Fix for bug #35732: read-only blocks SELECT statements in InnoDB
  
  Problem: SELECTs prohibited for a transactional SE in autocommit mode
  if read_only is set.
  
  Fix: allow them.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44989

ChangeSet@1.2576, 2008-04-07 17:31:20+05:00, ramil@mysql.com +3 -0
  Fix for bug #35732: read-only blocks SELECT statements in InnoDB
  
  Problem: SELECTs prohibited for a transactional SE in autocommit mode
  if read_only is set.
  
  Fix: allow them.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/45020

ChangeSet@1.2567, 2008-04-08 00:46:29+05:00, ramil@mysql.com +4 -0
  Fix for bug #35732: read-only blocks SELECT statements in InnoDB
  
  Problem: SELECTs prohibited for a transactional SE in autocommit mode
  if read_only is set.
  
  Fix: allow them.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/45033

ChangeSet@1.2567, 2008-04-08 10:20:58+05:00, ramil@mysql.com +4 -0
  Fix for bug #35732: read-only blocks SELECT statements in InnoDB
  
  Problem: SELECTs prohibited for a transactional SE in autocommit mode
  if read_only is set.
  
  Fix: allow them.

Questions:

1. Did this bug actually appear in a release?

2. If so, did the fix make it into 5.1.24?

Thanks.

Pushed into 5.1.25-rc

Pushed into 6.0.6-alpha

Noted in 5.1.24-ndb-6.3.13, 5.1.25, 6.0.6 changelogs.

Enabling the read_only system variable while autocommit mode was
enabled caused SELECT statements for transactional storage engines to
fail.