MySQL Bugs: #44958: Setting read_only won't succeed if tx is already started in different connection

Bug #44958	Setting read_only won't succeed if tx is already started in different connection
Submitted:	19 May 2009 18:27	Modified:	4 Aug 2010 10:13
Reporter:	Hema Sridharan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Tests: Replication	Severity:	S3 (Non-critical)
Version:	6.0-bzr,5.4	OS:	Any
Assigned to:	Luis Soares	CPU Architecture:	Any
Tags:	disabled

Description:
Create database and tables.
Create user test.
Start a transaction in master1 as root user and also as user test in master2.
Now change the global read only variable to 1
Perform commit in both the connections.
Commit will be successful as root user and will fail for user test.

At this point the test will hang after issuing commit. Please see below for reproducible test case.

How to repeat:
-- source include/master-slave.inc
-- source include/have_innodb.inc

# Create a test and replicate it to slave
connection master;
create user test;
sync_slave_with_master;

connect (master2,127.0.0.1,test,,test,$MASTER_MYPORT,);
connect (slave2,127.0.0.1,test,,test,$SLAVE_MYPORT,);

connection master1;

create table t1(a int) engine=InnoDB;
insert into t1 values(1001);

connection master;
set global read_only=0;

connection master1;
BEGIN;
insert into t1 values(1002);

connection master2;
BEGIN;
insert into t1 values(1003);

connection master;
set global read_only=1;

connection master1;
## works even with read_only=1, because master1 is root
COMMIT;

connection master2;
--error ER_OPTION_PREVENTS_STATEMENT
COMMIT;

At this point, the test hangs and fails.
An alternative to solve this problem is to set the read_only flag before inserts take place. This way commit will pass for connection with root user privileges and fail when connection is without root privilege.

The following test case can solve the problem.

-- source include/master-slave.inc
-- source include/have_innodb.inc
# Create a test and replicate it to slave
connection master;
create user test;
sync_slave_with_master;

connect (master2,127.0.0.1,test,,test,$MASTER_MYPORT,);
connect (slave2,127.0.0.1,test,,test,$SLAVE_MYPORT,);

connection master1;

create table t1(a int) engine=InnoDB;
insert into t1 values(1001);

connection master;
set global read_only=0;

connection master1;
BEGIN;

connection master2;
BEGIN;

connection master;
set global read_only=1;

connection master1;
insert into t1 values(1002);

connection master2;
--error ER_OPTION_PREVENTS_STATEMENT
insert into t1 values(1003);

connection master1;
## works even with read_only=1, because master1 is root
COMMIT;

Thank you for the report.

Verified as described.

Version 5.1 does not hang, but rejects commit of master2 connection.

Problem also global variables should not affect existent sessions. (http://dev.mysql.com/doc/refman/5.1/en/set-option.html: "If you change a global system variable, the value is remembered and used for new connections until the server restarts. ... However, the change affects the corresponding session variable only for clients that connect after the change. "

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/74571

2846 Luis Soares	2009-05-20
      BUG#44958: Setting read_only won't succeed if tx is already started in
      different connection
      
      This bug was opened to report the disabling of test rpl_read_only
      after patch for BUG#989 was pushed and to reenable this test case with
      the appropriate changes.
      
      This test case consists of three parts:
      
      1. check that the readonly variable is not replicated. The variable is
         set on the master before any locking takes place. This is safe wrt
         to the patch for BUG#989.
      
      2. check that user root (on conn1) can commit even if 'set
         read_only=1' is done concurrently (on conn3) just before conn1
         issues commit. This checks also that other users than root will not
         be able to commit under the same scenario.
      
      3. check that the sql slave thread is able to apply the changes even
         if read_only is set. This is safe wrt to the patch for BUG#989.
      
      The test case now hangs in 2. because it tries to set the flag
      read_only, while there are active changes going on (and mdl locks are
      taken). 
      
      The proposed fix addresses this, by changing a little the test case in
      2., so that the read_only flag is set before the changes are
      done. This makes the test not to hang anymore and keeps as close as
      possible the same assertions from before: i) assertion that the SUPER
      user can commit persists; ii) assertion that the non-super user cannot
      commit is changed - since read_only is set before the inserts, now
      when this user tries to insert it will get an error (ie, the check is
      performed earlier - before commit).
     @ mysql-test/suite/rpl/r/rpl_read_only.result
        updates to result file.
     @ mysql-test/suite/rpl/t/disabled.def
        Removes the rpl_read_only test from the list of disabled tests.
     @ mysql-test/suite/rpl/t/rpl_read_only.test
        Changes the setting of read_only variable to happen before any 
        locking takes place.

This patch only changes a test case, and is approved.

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/77349

2813 Luis Soares	2009-06-26
      BUG#44958: Setting read_only won't succeed if tx is already started in
      different connection
            
      This bug was opened to report the disabling of test rpl_read_only
      after patch for BUG#989 was pushed and to reenable this test case with
      the appropriate changes.
            
      This test case consists of three parts:
            
      1. check that the readonly variable is not replicated. The variable is
         set on the master before any locking takes place. This is safe wrt
         to the patch for BUG#989.
            
      2. check that user root (on conn1) can commit even if 'set
          read_only=1' is done concurrently (on conn3) just before conn1
          issues commit. This checks also that other users than root will not
          be able to commit under the same scenario.
            
      3. check that the sql slave thread is able to apply the changes even
         if read_only is set. This is safe wrt to the patch for BUG#989.
           
      The test case now hangs in 2. because it tries to set the flag
      read_only, while there are active changes going on (and mdl locks are
      taken). 
            
      The proposed fix addresses this, by changing a little the test case in
      2., so that the read_only flag is set before the changes are
      done. This makes the test not to hang anymore and keeps as close as
      possible the same assertions from before: i) assertion that the SUPER
      user can commit persists; ii) assertion that the non-super user cannot
      commit is changed - since read_only is set before the inserts, now
      when this user tries to insert it will get an error (ie, the check is
      performed earlier - before commit).
     @ mysql-test/suite/rpl/r/rpl_read_only.result
        updates to result file.
     @ mysql-test/suite/rpl/t/disabled.def
        Removes the rpl_read_only test from the list of disabled tests.
     @ mysql-test/suite/rpl/t/rpl_read_only.test
        Changes the setting of read_only variable to happen before any 
        locking takes place.

Pushed to mysql-azalea-bugfixing.

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090702084644-k95gd2asolvz2zpu) (version source revid:luis.soares@sun.com-20090629083542-87rjmhmf34xzmvp3) (merge vers: 5.4.4-alpha) (pib:11)

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090702084644-k95gd2asolvz2zpu) (version source revid:luis.soares@sun.com-20090629083542-87rjmhmf34xzmvp3) (merge vers: 5.4.4-alpha) (pib:11)

Per comment above, closing without any further action (testing changes only, no changes for end users to be documented).

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/102603

2968 Luis Soares	2010-03-08
      BUG#44958: Setting read_only won't succeed if tx is already started in
      different connection
      
      Backporting cset from 6.0 revision id:
      luis.soares@sun.com-20090626142234-8da6l9d991pw8tyx
      
      This bug was opened to report the disabling of test rpl_read_only
      after patch for BUG#989 was pushed and to reenable this test case with
      the appropriate changes.
                          
      This test case consists of three parts:
                          
      1. check that the readonly variable is not replicated. The variable is
         set on the master before any locking takes place. This is safe wrt
         to the patch for BUG#989.
                          
      2. check that user root (on conn1) can commit even if 'set
         read_only=1' is done concurrently (on conn3) just before conn1
         issues commit. This checks also that other users than root will not
         be able to commit under the same scenario.
                          
      3. check that the sql slave thread is able to apply the changes even
         if read_only is set. This is safe wrt to the patch for BUG#989.
                        
      The test case now hangs in 2. because it tries to set the flag
      read_only, while there are active changes going on (and mdl locks are
      taken). 
                          
      The proposed fix addresses this, by changing a little the test case in
      2., so that the read_only flag is set before the changes are
      done. This makes the test not to hang anymore and keeps as close as
      possible the same assertions from before: i) assertion that the SUPER
      user can commit persists; ii) assertion that the non-super user cannot
      commit is changed - since read_only is set before the inserts, now
      when this user tries to insert it will get an error (ie, the check is
      performed earlier - before commit).

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100324081249-yfwol7qtcek6dh7w) (version source revid:alik@sun.com-20100324081113-kc7x1iytnplww91u) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100324081159-5b8juv8ldiqwce8v) (version source revid:alik@sun.com-20100324081105-y72rautcea375zxm) (pib:16)

No changelog entry needed.

Setting report to Need Merge pending push of Celosia to release tree.

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:alik@sun.com-20100324081105-y72rautcea375zxm) (merge vers: 5.6.99-m4) (pib:18)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:alik@sun.com-20100324081105-y72rautcea375zxm) (merge vers: 5.6.99-m4) (pib:18)

No changelog entry required; see Paul's comment above.

Closed.