Patch changing how 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).

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.

2 files changed, 124 insertions, 19 deletions

diff --git a/mysql-test/t/alter_table-big.test b/mysql-test/t/alter_table-big.test
index befe6e14977..5d2c0ba0bb6 100644
--- a/mysql-test/t/alter_table-big.test
+++ b/mysql-test/t/alter_table-big.test
@@ -1,7 +1,12 @@
-# In order to be more or less robust test for bug#25044 has to take
-# significant time (e.g. about 9 seconds on my (Dmitri's) computer)
-# so we probably want execute it only in --big-test mode.
+#
+# 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
 
 
@@ -22,27 +27,20 @@ create table t1 (n1 int, n2 int, n3 int,
                 key (n3, n1, n2));
 create table t2 (i int);
 
-# Populating 't1' table with keys disabled, so ALTER TABLE .. ENABLE KEYS
-# will run for some time
+# 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;
---disable_query_log
-insert into t1 values (RAND()*1000,RAND()*1000,RAND()*1000);
-let $1=19;
-while ($1)
-{
- eval insert into t1 select RAND()*1000,RAND()*1000,RAND()*1000 from t1;
- dec $1;
-}
---enable_query_log
+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;
-let $show_type= PROCESSLIST;
-let $show_pattern= '%Repair by sorting%alter table t1 enable keys%';
---source include/wait_show_pattern.inc
+--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.
@@ -51,12 +49,68 @@ insert into t2 values (1);
 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 102;
+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
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test
index 965528642bf..7d3e9bba533 100644
--- a/mysql-test/t/alter_table.test
+++ b/mysql-test/t/alter_table.test
@@ -727,7 +727,58 @@ ALTER TABLE t1 MODIFY COLUMN v VARCHAR(4);
 SELECT * FROM t1;
 DROP TABLE t1;
 
-# End of 5.0 tests
+--echo End of 5.0 tests
+
+#
+# Extended test coverage for ALTER TABLE behaviour under LOCK TABLES
+# It should be consistent across all platforms and for all engines
+# (Before 5.1 this was not true as behavior was different between 
+# Unix/Windows and transactional/non-transactional tables).
+# See also innodb_mysql.test
+#
+--disable_warnings
+drop table if exists t1, t2, t3;
+--enable_warnings
+create table t1 (i int);
+create table t3 (j int);
+insert into t1 values ();
+insert into t3 values ();
+# Table which is altered under LOCK TABLES it should stay in list of locked
+# tables and be available after alter takes place unless ALTER contains RENAME
+# clause. We should see the new definition of table, of course.
+lock table t1 write, t3 read;
+# Example of so-called 'fast' ALTER TABLE
+alter table t1 modify i int default 1;
+insert into t1 values ();
+select * from t1;
+# And now full-blown ALTER TABLE
+alter table t1 change i c char(10) default "Two";
+insert into t1 values ();
+select * from t1;
+# If table is renamed then it should be removed from the list
+# of locked tables. 'Fast' ALTER TABLE with RENAME clause:
+alter table t1 modify c char(10) default "Three", rename to t2;
+--error ER_TABLE_NOT_LOCKED
+select * from t1;
+--error ER_TABLE_NOT_LOCKED
+select * from t2;
+select * from t3;
+unlock tables;
+insert into t2 values ();
+select * from t2;
+lock table t2 write, t3 read;
+# Full ALTER TABLE with RENAME
+alter table t2 change c vc varchar(100) default "Four", rename to t1;
+--error ER_TABLE_NOT_LOCKED
+select * from t1;
+--error ER_TABLE_NOT_LOCKED
+select * from t2;
+select * from t3;
+unlock tables;
+insert into t1 values ();
+select * from t1;
+drop tables t1, t3;
+
 
 #
 # Bug#18775 - Temporary table from alter table visible to other threads