read-only blocks SELECT statements in PXC

[Arthin]

# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.3 (Santiago)

Percona-XtraDB-Cluster-client-5.5.28-23.7.369.rhel6.x86_64
Percona-XtraDB-Cluster-devel-5.5.28-23.7.369.rhel6.x86_64
percona-toolkit-2.1.7-1.noarch
Percona-XtraDB-Cluster-shared-5.5.28-23.7.369.rhel6.x86_64
percona-xtrabackup-2.0.3-470.rhel6.x86_64
Percona-XtraDB-Cluster-server-5.5.28-23.7.369.rhel6.x86_64
Percona-XtraDB-Cluster-galera-2.0-1.117.rhel6.x86_64

#mysql -h1.1.1.1 -P3306 -uptest -p
user ptest has select privilege on this database.

mysql> select * from uniqname_white limit 10;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
mysql> show variables like 'read%';
+----------------------+----------+
| Variable_name | Value |
+----------------------+----------+
| read_buffer_size | 8388608 |
| read_only | ON |
| read_rnd_buffer_size | 33554432 |
+----------------------+----------+

mysql>set global read_only=off;
logon with ptest, and execute query
mysql> select * from uniqname_white limit 10;
at this time,query is ok.

the cluster nodes with read_only=on will block normal user query.

[Arthin (arthin)]

case 1 is ok:

begin;
use abc;
select count(*) from id_seq;
rollback;

case 2 get an error:

set autocommit=0;
use abc;
select count(*) from id_seq;
commit;

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

case 3 get an error:
set autocommit=1;
use abc;
select count(*) from id_seq;

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

[Raghavendra D Prabhu (raghavendra-prabhu)]

@Arthin,

I have not been able to reproduce this with latest PXC. Can you
retry and let us know?

[xiewen (xiewenxiewen)]

@raghavendra-prabhu

here is the test case

1. start a single node cluster

bin/mysqld_safe --defaults-file=/DATA/mysql/my.cnf --wsrep_cluster_address=gcomm:// &

2. create a normal user with permission to select on a table

mysql -h 127.0.0.1 -P 3306 -u root test -e"
drop table if exists t;
create table t (a int) engine=innodb;
insert into t values (2);
grant select on test.t to 'a'@'%' identified by 'pwd';
"

3. select statement will be blocked if read_only was set

mysql -h 127.0.0.1 -P 3306 -u root -e "set @@global.read_only=0"
mysql -h 127.0.0.1 -P 3306 -ua -ppwd -e "select * from test.t"
+------+
| a |
+------+
| 2 |
+------+

mysql -h 127.0.0.1 -P 3306 -u root -e "set @@global.read_only=1"
mysql -h 127.0.0.1 -P 3306 -ua -ppwd -e "select * from test.t"
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement

[Seppo Jaakola (seppo-jaakola)]

Problem was that wsrep handlerton was always declared as read/write, unconditionally.

Fix pushed in revision: http://bazaar.launchpad.net/~codership/codership-mysql/5.5-23/revision/3901 ,where wsrep handlerton will follow innodb transactions' read/write setting

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXC-1274