Percona XtraDB Cluster - HA scalable solution for MySQL

read-only blocks SELECT statements in PXC

Reported by Arthin on 2012-12-17
18
This bug affects 3 people
Affects Status Importance Assigned to Milestone
MySQL patches by Codership
Medium
Seppo Jaakola
Percona XtraDB Cluster
Undecided
Unassigned

Bug Description

# 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) wrote :

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

@Arthin,

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

Changed in percona-xtradb-cluster:
status: New → Incomplete
xiewen (xiewenxiewen) wrote :

@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

Changed in codership-mysql:
importance: Undecided → Medium
assignee: nobody → Alex Yurchenko (ayurchen)
milestone: none → 5.5.30-24.8
status: New → Confirmed
Changed in codership-mysql:
milestone: 5.5.30-24.8 → 5.5.31-23.7.4
assignee: Alex Yurchenko (ayurchen) → nobody
Changed in percona-xtradb-cluster:
status: Incomplete → Triaged
milestone: none → 5.5.31-25
Changed in codership-mysql:
milestone: 5.5.31-23.7.5 → 5.5.32-23.7.6
Changed in codership-mysql:
assignee: nobody → Seppo Jaakola (seppo-jaakola)
status: Confirmed → In Progress
Seppo Jaakola (seppo-jaakola) wrote :

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

Changed in codership-mysql:
status: In Progress → Fix Committed
Changed in percona-xtradb-cluster:
status: Triaged → Fix Committed
Changed in codership-mysql:
status: Fix Committed → Fix Released
Changed in percona-xtradb-cluster:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers