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).
| Thread |
|---|
| • bzr push into mysql-trunk branch (Dmitry.Shulga:3866 to 3867) Bug#11764747 | Dmitry Shulga | 10 Feb |
