From: Dmitry Shulga Date: February 9 2012 12:58pm Subject: bzr push into mysql-trunk branch (Dmitry.Shulga:3866 to 3867) Bug#11764747 List-Archive: http://lists.mysql.com/commits/142825 X-Bug: 11764747 Message-Id: <201202091155.q19BtXSG016560@acsmt356.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3867 Dmitry Shulga 2012-02-09 Patch for bug#11764747 (formerly known as 57612): SET GLOBAL READ_ONLY=1 cannot progress when a table is locked with LOCK TABLES. The reason for the bug was that mysql server makes a flush of all open tables during handling of statement 'SET GLOBAL READ_ONLY=1'. Therefore if some of these tables were locked by "LOCK TABLE ... READ" from a different connection, then execution of statement 'SET GLOBAL READ_ONLY=1' would be waiting for the lock for such table even if the table was locked in a compatible read mode. Flushing of all open tables before setting of read_only system variable is inherited from 5.1 implementation since this was the only possible approach to ensure that there isn't any pending write operations on open tables. Start from version 5.5 and above such behaviour is guaranteed by the fact that we acquire global_read_lock before setting read_only flag. Since acquiring of global_read_lock is successful only when there isn't any active write operation then we can remove flushing of open tables from processing of SET GLOBAL READ_ONLY=1. This modification changes the server behavior so that read locks held by other connections (LOCK TABLE ... READ) no longer will block attempts to enable read_only. @ mysql-test/t/read_only.test Modified testcase in order to takes into account the changed behaviour in processing of 'set read_only' statement. @ sql/sys_vars.cc Removed flushing of open tables from processing of SET GLOBAL READ_ONLY=1. modified: mysql-test/r/read_only.result mysql-test/t/read_only.test sql/sys_vars.cc 3866 Jorgen Loland 2012-02-09 Bug#13354910 - MAKE_JOIN_STATISTICS() INVOKES RECORDS_IN_RANGE(MIN_KEY=0X0, MAX_KEY=0X0) Some range predicates that by them selves make up partial ranges are merged into a full range by the range optimizer. An example is the query for this bug: "WHERE col IN (1,2,6) OR col NOT IN (1)" This predicate is always true and is correctly merged into the range [-inf <= col <= +inf]. However, although the range optimizer did make a full range, it did not realize that it's meaningless to use this as a range predicate since that will be the same as doing an index scan. For the query in the bug the problem was that SEL_ARG::copy_max() had a bug: When checking if the range was a full range, it was checked if max_flag was set to (NO_MAX_RANGE | NO_MIN_RANGE). But the NO_MIN_FLAG is stored in min_flag, so the test is changed to (max_flag & NO_MAX_FLAG) and (min_flag & NO_MIN_FLAG). Enabling the DBUG_ASSERT(max_key || min_key) in ha_innobase::records_in_range() then showed another related bug: innodb_icp started failing because SEL_ARG::copy_min() had the very same bug as copy_max(). This was fixed as well. In an attempt at unittesting SEL_ARG, copy_min() and copy_max() have now been added to the opt_range-t gunit test. To see the assert, add "DBUG_ASSERT(min_key || max_key);" to the beginning of ha_innobase::records_in_range(). @ mysql-test/include/index_merge1.inc Added a new test to test the intended behavior of a query that changed due to the bugfix. @ mysql-test/r/index_merge_myisam.result Updated result file because an invalid execution plan changed. Also added a new test to test the intended behavior of the query that changed. @ sql/opt_range.cc SEL_ARG::copy_min() and copy_max() now checks if the NO_MIN_RANGE flag is set in min_flag and the NO_MAX_FLAG in max_flag. @ unittest/gunit/opt_range-t.cc Add tests for SEL_ARG::copy_min() and SEL_ARG::copy_max(). modified: mysql-test/include/index_merge1.inc mysql-test/r/index_merge_myisam.result sql/opt_range.cc unittest/gunit/opt_range-t.cc === modified file 'mysql-test/r/read_only.result' --- a/mysql-test/r/read_only.result 2010-08-30 06:38:09 +0000 +++ b/mysql-test/r/read_only.result 2012-02-09 12:57:42 +0000 @@ -75,18 +75,16 @@ connection default; set global read_only=1; ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction unlock tables ; -send set global read_only=1; set global read_only=1; -connection con1; select @@global.read_only; @@global.read_only -0 -unlock tables ; +1 +connection con1; select @@global.read_only; @@global.read_only 1 +unlock tables ; connection default; -reap; connection default; set global read_only=0; BEGIN; === modified file 'mysql-test/t/read_only.test' --- a/mysql-test/t/read_only.test 2009-03-06 14:56:17 +0000 +++ b/mysql-test/t/read_only.test 2012-02-09 12:57:42 +0000 @@ -169,23 +169,19 @@ connection default; --error ER_LOCK_OR_ACTIVE_TRANSACTION set global read_only=1; unlock tables ; -# The following call blocks until con1 releases the read lock. -# Blocking is a limitation, and could be improved. ---echo send set global read_only=1; -send set global read_only=1; + +# after unlock tables in current connection +# the next command must be executed successfully +set global read_only=1; +select @@global.read_only; --echo connection con1; connection con1; select @@global.read_only; unlock tables ; -let $wait_condition= SELECT @@global.read_only= 1; ---source include/wait_condition.inc -select @@global.read_only; --echo connection default; connection default; ---echo reap; -reap; # pending transaction / READ_ONLY # - is an error in the same connection === modified file 'sql/sys_vars.cc' --- a/sql/sys_vars.cc 2012-02-02 16:56:36 +0000 +++ b/sql/sys_vars.cc 2012-02-09 12:57:42 +0000 @@ -2033,14 +2033,15 @@ static bool fix_read_only(sys_var *self, } /* - Perform a 'FLUSH TABLES WITH READ LOCK'. - This is a 3 step process: - - [1] lock_global_read_lock() - - [2] close_cached_tables() - - [3] make_global_read_lock_block_commit() - [1] prevents new connections from obtaining tables locked for write. - [2] waits until all existing connections close their tables. - [3] prevents transactions from being committed. + READ_ONLY=1 prevents write locks from being taken on tables and + blocks transactions from committing. We therefore should make sure + that no such events occur while setting the read_only variable. + This is a 2 step process: + [1] lock_global_read_lock() + Prevents connections from obtaining new write locks on + tables. Note that we can still have active rw transactions. + [2] make_global_read_lock_block_commit() + Prevents transactions from committing. */ read_only= opt_readonly; @@ -2049,19 +2050,6 @@ static bool fix_read_only(sys_var *self, if (thd->global_read_lock.lock_global_read_lock(thd)) goto end_with_mutex_unlock; - /* - This call will be blocked by any connection holding a READ or WRITE lock. - Ideally, we want to wait only for pending WRITE locks, but since: - con 1> LOCK TABLE T FOR READ; - con 2> LOCK TABLE T FOR WRITE; (blocked by con 1) - con 3> SET GLOBAL READ ONLY=1; (blocked by con 2) - can cause to wait on a read lock, it's required for the client application - to unlock everything, and acceptable for the server to wait on all locks. - */ - if ((result= close_cached_tables(thd, NULL, TRUE, - thd->variables.lock_wait_timeout))) - goto end_with_read_lock; - if ((result= thd->global_read_lock.make_global_read_lock_block_commit(thd))) goto end_with_read_lock; No bundle (reason: useless for push emails).