• unknown's avatar
    Patch changing how ALTER TABLE implementation handles table locking · 1a60685c
    unknown authored
    and invalidation in the most general case (non-temporary table and
    not simple RENAME or ENABLE/DISABLE KEYS or partitioning command).
    
    See comment for sql/sql_table.cc for more information.
    
    These changes are prerequisite for 5.1 version of fix for bug #23667
    "CREATE TABLE LIKE is not isolated from alteration by other connections"
    
    
    mysql-test/include/mix1.inc:
      Extended coverage for behavior of ALTER TABLE statement under LOCK TABLES,
      which should be consistent across all platforms and for all engines.
    mysql-test/r/alter_table-big.result:
      Changed test for bug #25044 to use @@debug and injected sleeps
      infrastructure. Extended test coverage for ALTER TABLE's behavior
      under concurrency.
    mysql-test/r/alter_table.result:
      Extended coverage for behavior of ALTER TABLE statement under LOCK TABLES,
      which should be consistent across all platforms and for all engines.
    mysql-test/r/innodb_mysql.result:
      Extended coverage for behavior of ALTER TABLE statement under LOCK TABLES,
      which should be consistent across all platforms and for all engines.
    mysql-test/t/alter_table-big.test:
      Changed test for bug #25044 to use @@debug and injected sleeps
      infrastructure. Extended test coverage for ALTER TABLE's behavior
      under concurrency.
    mysql-test/t/alter_table.test:
      Extended coverage for behavior of ALTER TABLE statement under LOCK TABLES,
      which should be consistent across all platforms and for all engines.
    sql/mysql_priv.h:
      Made functions reopen_table() and close_handle_and_leave_table_as_lock()
      available outside of sql_base.cc file.
      Changed close_data_tables() in such way that after closing handler
      for the table it leaves TABLE object for it in table cache not as
      placeholder for ordinary name-lock but as placeholder for an exclusive
      name-lock. Renamed this routine to close_data_files_and_morph_locks().
    sql/sql_base.cc:
      Made functions reopen_table() and close_handle_and_leave_table_as_lock()
      available outside of sql_base.cc file.
      Changed close_data_tables() in such way that after closing handler
      for the table it leaves TABLE object for it in table cache not as
      placeholder for ordinary name-lock but as placeholder for an exclusive
      name-lock. Renamed this routine to close_data_files_and_morph_locks().
      Also adjusted it so it can work properly not only in LOCK TABLES mode.
    sql/sql_table.cc:
      Changed the way in which ALTER TABLE implementation handles table
      locking and invalidation in the most general case (non-temporary table
      and not simple RENAME or ENABLE/DISABLE KEYS or partitioning command)
      
      Now after preparing new version of the table we:
      1) Wait until all other threads close old version of table.
      2) Close instances of table open by this thread and replace them
         with exclusive name-locks.
      3) Rename the old table to a temp name, rename the new one to the
         old name.
      4) If we are under LOCK TABLES and don't do ALTER TABLE ... RENAME
         we reopen new version of table.
      5) Write statement to the binary log.
      6) If we are under LOCK TABLES and do ALTER TABLE ... RENAME we
         remove name-locks from list of open tables and table cache.
      7) If we are not not under LOCK TABLES we rely on close_thread_tables()
         call to remove name-locks from table cache and list of open table. 
      
      Such approach:
      a) Eliminates possibility for concurrent statement to sneak in and get
         access to the new version of the table before ALTER TABLE gets logged
         into binary log.
      b) Ensures that ALTER TABLE behaves under LOCK TABLES in the same way
         on all platforms and for all engines (in 5.0 this was not true) 
      c) Preserves nice invariant that if table is open in some connection
         there is a guarantee that .FRM file for this table exists and is
         properly named.
    1a60685c
alter_table-big.test 3.59 KB
#
# Tests for various concurrency-related aspects of ALTER TABLE implemetation
#
# This test takes rather long time so let us run it only in --big-test mode
--source include/big_test.inc
# We are using some debug-only features in this test
--source include/have_debug.inc
# Also we are using SBR to check that statements are executed
# in proper order.
--source include/have_binlog_format_mixed_or_statement.inc


#
# Test for bug #25044 "ALTER TABLE ... ENABLE KEYS acquires global
# 'opening tables' lock".
#
# ALTER TABLE ... ENABLE KEYS should not acquire LOCK_open mutex for
# the whole its duration as it prevents other queries from execution.
--disable_warnings
drop table if exists t1, t2;
--enable_warnings
connect (addconroot, localhost, root,,);
connection default;
create table t1 (n1 int, n2 int, n3 int,
                key (n1, n2, n3),
                key (n2, n3, n1),
                key (n3, n1, n2));
create table t2 (i int);

# Starting from 5.1 we have runtime settable @@debug variable,
# which can be used for introducing delays at certain points of
# statement execution, so we don't need many rows in 't1' to make
# this test repeatable.
alter table t1 disable keys;
insert into t1 values (RAND()*1000, RAND()*1000, RAND()*1000);

# Later we use binlog to check the order in which statements are
# executed so let us reset it first.
reset master;
set session debug="+d,sleep_alter_enable_indexes";
--send alter table t1 enable keys;
connection addconroot;
--sleep 2
# This statement should not be blocked by in-flight ALTER and therefore
# should be executed and written to binlog before ALTER TABLE ... ENABLE KEYS
# finishes.
insert into t2 values (1);
# And this should wait until the end of ALTER TABLE ... ENABLE KEYS.
insert into t1 values (1, 1, 1);
connection default;
--reap
set session debug="-d,sleep_alter_enable_indexes";
# Check that statements were executed/binlogged in correct order.
--replace_column 2 # 5 #
show binlog events in 'master-bin.000001' from 106;

# Clean up
drop tables t1, t2;


--echo End of 5.0 tests

#
# Additional coverage for the main ALTER TABLE case
#
# We should be sure that table being altered is properly
# locked during statement execution and in particular that
# no DDL or DML statement can sneak in and get access to
# the table when real operation has already taken place
# but this fact has not been noted in binary log yet.
--disable_warnings
drop table if exists t1, t2, t3;
--enable_warnings
create table t1 (i int);
# We are going to check that statements are logged in correct order
reset master;
set session debug="+d,sleep_alter_before_main_binlog";
--send alter table t1 change i c char(10) default 'Test1';
connection addconroot;
--sleep 2
insert into t1 values ();
select * from t1;
connection default;
--reap
--send alter table t1 change c vc varchar(100) default 'Test2';
connection addconroot;
--sleep 2
rename table t1 to t2;
connection default;
--reap
drop table t2;
# And now tests for ALTER TABLE with RENAME clause. In this
# case target table name should be properly locked as well.
create table t1 (i int);
--send alter table t1 change i c char(10) default 'Test3', rename to t2;
connection addconroot;
--sleep 2
insert into t2 values ();
select * from t2;
connection default;
--reap
--send alter table t2 change c vc varchar(100) default 'Test2', rename to t1;
connection addconroot;
--sleep 2
rename table t1 to t3;
connection default;
--reap
drop table t3;
set session debug="-d,sleep_alter_before_main_binlog";

# Check that all statements were logged in correct order
--replace_column 2 # 5 #
show binlog events in 'master-bin.000001' from 106;


--echo End of 5.1 tests