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
Triage: D2 (Serious) / R3 (Medium) / E3 (Medium)

[1 Apr 2008 7:51] Todd Farmer
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.
[1 Apr 2008 7:53] Todd Farmer
Only known workaround at this time is to enable updates (eg, disable read-only).
[1 Apr 2008 14:16] Heikki Tuuri
The global read-only option is implemented in the MySQL layer of code. InnoDB does not know about it.
[4 Apr 2008 20:36] Phil Hildebrand
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
[7 Apr 2008 8:35] Bugs System
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.
[7 Apr 2008 12:31] Bugs System
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.
[7 Apr 2008 19:46] Bugs System
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.
[8 Apr 2008 5:21] Bugs System
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.
[12 Apr 2008 13:33] Jon Stephens
Questions:

1. Did this bug actually appear in a release?

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

Thanks.
[1 May 2008 6:16] Bugs System
Pushed into 5.1.25-rc
[1 May 2008 6:19] Bugs System
Pushed into 6.0.6-alpha
[13 May 2008 20:10] Paul Dubois
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.