BUG#4286 - HANDLER tables are closed by FLUSH TABLE(S).

BUG#4335 - one name can be handler open'ed many times.
Reworked the HANDLER functions and interface.
Using a HASH to store information on open tables that
survives FLUSH TABLE.
HANDLER tables alias names must now be unique, though it
is allowed in 4.0 to qualify them with the database name
of the base table.


mysql-test/r/flush_table.result:
  BUG#4286 - HANDLER tables are closed by FLUSH TABLE(S).
  BUG#4335 - one name can be handler open'ed many times.
  Moved pure handler test results to handler.result.
  Added the new test results.
mysql-test/r/handler.result:
  BUG#4286 - HANDLER tables are closed by FLUSH TABLE(S).
  BUG#4335 - one name can be handler open'ed many times.
  Moved pure handler test results from flush_table.result to here.
mysql-test/t/flush_table.test:
  BUG#4286 - HANDLER tables are closed by FLUSH TABLE(S).
  BUG#4335 - one name can be handler open'ed many times.
  Moved pure handler tests to handler.test.
  Added new tests.
mysql-test/t/handler.test:
  BUG#4286 - HANDLER tables are closed by FLUSH TABLE(S).
  BUG#4335 - one name can be handler open'ed many times.
  Moved pure handler tests from flush_table.test to here.
sql/mysql_priv.h:
  BUG#4286 - HANDLER tables are closed by FLUSH TABLE(S).
  BUG#4335 - one name can be handler open'ed many times.
  Reworked the handler interface.
sql/sql_base.cc:
  BUG#4286 - HANDLER tables are closed by FLUSH TABLE(S).
  BUG#4335 - one name can be handler open'ed many times.
  Replaced mysql_ha_close_list() by the better named
  function mysql_ha_flush() with readable options.
sql/sql_class.cc:
  BUG#4286 - HANDLER tables are closed by FLUSH TABLE(S).
  BUG#4335 - one name can be handler open'ed many times.
  Added initialization for the handler tables hash.
  Changed the handler tables clean-up code.
  Unreleted to bug: Changed the order of THD initialization
  to avoid warning messages on Linux with gcc.
sql/sql_class.h:
  BUG#4286 - HANDLER tables are closed by FLUSH TABLE(S).
  BUG#4335 - one name can be handler open'ed many times.
  Added the handler tables HASH to THD.
sql/sql_handler.cc:
  BUG#4286 - HANDLER tables are closed by FLUSH TABLE(S).
  BUG#4335 - one name can be handler open'ed many times.
  Completely reworked the handler functions.
  Added an introducing comment, describing the new functionality.
sql/sql_table.cc:
  BUG#4286 - HANDLER tables are closed by FLUSH TABLE(S).
  BUG#4335 - one name can be handler open'ed many times.
  replaced mysql_ha_close() by the better named
  function mysql_ha_flush() with readable options.

10 files changed, 986 insertions, 304 deletions

diff --git a/mysql-test/r/flush_table.result b/mysql-test/r/flush_table.result
index cfba428e2e8..ff69291193f 100644
--- a/mysql-test/r/flush_table.result
+++ b/mysql-test/r/flush_table.result
@@ -7,58 +7,6 @@ check table t1;
 Table	Op	Msg_type	Msg_text
 test.t1	check	status	OK
 drop table t1;
-drop database if exists test_test;
-create database test_test;
-use test_test;
-create table t1(table_id char(20) primary key);
-insert into t1 values ('test_test.t1');
-insert into t1 values ('');
-handler t1 open;
-handler t1 read first limit 9;
-table_id
-test_test.t1
-
-create table t2(table_id char(20) primary key);
-insert into t2 values ('test_test.t2');
-insert into t2 values ('');
-handler t2 open;
-handler t2 read first limit 9;
-table_id
-test_test.t2
-
-use test;
-drop table if exists t1;
-create table t1(table_id char(20) primary key);
-insert into t1 values ('test.t1');
-insert into t1 values ('');
-handler t1 open;
-handler t1 read first limit 9;
-table_id
-test.t1
-
-use test;
-handler test.t1 read first limit 9;
-table_id
-test.t1
-
-handler test.t2 read first limit 9;
-Unknown table 't2' in HANDLER
-handler test_test.t1 read first limit 9;
-table_id
-test_test.t1
-
-handler test_test.t2 read first limit 9;
-table_id
-test_test.t2
-
-handler test_test.t1 close;
-drop table test_test.t1;
-handler test_test.t2 close;
-drop table test_test.t2;
-drop database test_test;
-use test;
-handler test.t1 close;
-drop table test.t1;
 drop table if exists t1;
 drop table if exists t2;
 create table t1(table_id char(20) primary key);
@@ -84,14 +32,23 @@ test.t2
 
 flush tables;
 handler a1 read first limit 9;
-Unknown table 'a1' in HANDLER
+table_id
+test.t1
+
 handler a2 read first limit 9;
-Unknown table 'a2' in HANDLER
+table_id
+test.t1
+
 handler t2 read first limit 9;
-Unknown table 't2' in HANDLER
+table_id
+test.t2
+
 handler t1 open as a1;
+Not unique table/alias: 'a1'
 handler t1 open as a2;
+Not unique table/alias: 'a2'
 handler t2 open;
+Not unique table/alias: 't2'
 handler a1 read first limit 9;
 table_id
 test.t1
@@ -106,15 +63,43 @@ test.t2
 
 flush table t1;
 handler a1 read first limit 9;
-Unknown table 'a1' in HANDLER
+table_id
+test.t1
+
 handler a2 read first limit 9;
-Unknown table 'a2' in HANDLER
+table_id
+test.t1
+
 handler t2 read first limit 9;
 table_id
 test.t2
 
 flush table t2;
 handler t2 close;
-Unknown table 't2' in HANDLER
 drop table t1;
 drop table t2;
+create table t1(table_id char(20) primary key);
+insert into t1 values ('Record-01');
+insert into t1 values ('Record-02');
+insert into t1 values ('Record-03');
+insert into t1 values ('Record-04');
+insert into t1 values ('Record-05');
+handler t1 open;
+handler t1 read first limit 1;
+table_id
+Record-01
+handler t1 read next limit 1;
+table_id
+Record-02
+handler t1 read next limit 1;
+table_id
+Record-03
+flush table t1;
+handler t1 read next limit 1;
+table_id
+Record-01
+handler t1 read next limit 1;
+table_id
+Record-02
+handler t1 close;
+drop table t1;
diff --git a/mysql-test/r/handler.result b/mysql-test/r/handler.result
index 50d51cf14f4..5af153930d5 100644
--- a/mysql-test/r/handler.result
+++ b/mysql-test/r/handler.result
@@ -203,3 +203,247 @@ handler t1 read a=(1) where b=1;
 a	b
 handler t1 close;
 drop table t1;
+drop database if exists test_test;
+create database test_test;
+use test_test;
+create table t1(table_id char(20) primary key);
+insert into t1 values ('test_test.t1');
+insert into t1 values ('');
+handler t1 open;
+handler t1 read first limit 9;
+table_id
+test_test.t1
+
+create table t2(table_id char(20) primary key);
+insert into t2 values ('test_test.t2');
+insert into t2 values ('');
+handler t2 open;
+handler t2 read first limit 9;
+table_id
+test_test.t2
+
+use test;
+drop table if exists t1;
+create table t1(table_id char(20) primary key);
+insert into t1 values ('test.t1');
+insert into t1 values ('');
+handler t1 open;
+Not unique table/alias: 't1'
+use test;
+handler test.t1 read first limit 9;
+Unknown table 'test.t1' in HANDLER
+handler test_test.t1 read first limit 9;
+table_id
+test_test.t1
+
+handler t1 read first limit 9;
+table_id
+test_test.t1
+
+handler test_test.t2 read first limit 9;
+table_id
+test_test.t2
+
+handler t2 read first limit 9;
+table_id
+test_test.t2
+
+handler test_test.t1 close;
+handler t1 close;
+Unknown table 't1' in HANDLER
+drop table test_test.t1;
+handler test_test.t2 close;
+handler t2 close;
+Unknown table 't2' in HANDLER
+drop table test_test.t2;
+drop database test_test;
+use test;
+handler test.t1 close;
+Unknown table 'test.t1' in HANDLER
+handler t1 close;
+Unknown table 't1' in HANDLER
+drop table test.t1;
+drop database if exists test_test;
+drop table if exists t1;
+drop table if exists t2;
+drop table if exists t3;
+create database test_test;
+use test_test;
+create table t1 (c1 char(20));
+insert into t1 values ('test_test.t1');
+create table t3 (c1 char(20));
+insert into t3 values ('test_test.t3');
+handler t1 open;
+handler t1 read first limit 9;
+c1
+test_test.t1
+handler t1 open h1;
+handler h1 read first limit 9;
+c1
+test_test.t1
+use test;
+create table t1 (c1 char(20));
+create table t2 (c1 char(20));
+create table t3 (c1 char(20));
+insert into t1 values ('t1');
+insert into t2 values ('t2');
+insert into t3 values ('t3');
+handler t1 open;
+Not unique table/alias: 't1'
+handler t2 open t1;
+Not unique table/alias: 't1'
+handler t3 open t1;
+Not unique table/alias: 't1'
+handler t1 read first limit 9;
+c1
+test_test.t1
+handler test.t1 close;
+Unknown table 'test.t1' in HANDLER
+handler test.t1 open h1;
+Not unique table/alias: 'h1'
+handler test_test.t1 open h1;
+Not unique table/alias: 'h1'
+handler test_test.t3 open h3;
+handler test.t1 open h2;
+handler t1 read first limit 9;
+c1
+test_test.t1
+handler h1 read first limit 9;
+c1
+test_test.t1
+handler h2 read first limit 9;
+c1
+t1
+handler h3 read first limit 9;
+c1
+test_test.t3
+handler test.h2 read first limit 9;
+c1
+t1
+handler test.h1 close;
+Unknown table 'test.h1' in HANDLER
+handler test_test.t1 close;
+handler test_test.h1 close;
+handler h2 close;
+handler t1 read first limit 9;
+Unknown table 't1' in HANDLER
+handler h1 read first limit 9;
+Unknown table 'h1' in HANDLER
+handler h2 read first limit 9;
+Unknown table 'h2' in HANDLER
+handler h3 read first limit 9;
+c1
+test_test.t3
+handler test_test.h3 read first limit 9;
+c1
+test_test.t3
+use test_test;
+handler h3 read first limit 9;
+c1
+test_test.t3
+handler test.h3 read first limit 9;
+Unknown table 'test.h3' in HANDLER
+handler test_test.h3 close;
+use test;
+drop table t3;
+drop table t2;
+drop table t1;
+drop database test_test;
+create table t1 (c1 char(20));
+insert into t1 values ("t1");
+handler t1 open as h1;
+handler h1 read first limit 9;
+c1
+t1
+create table t2 (c1 char(20));
+insert into t2 values ("t2");
+handler t2 open as h2;
+handler h2 read first limit 9;
+c1
+t2
+create table t3 (c1 char(20));
+insert into t3 values ("t3");
+handler t3 open as h3;
+handler h3 read first limit 9;
+c1
+t3
+create table t4 (c1 char(20));
+insert into t4 values ("t4");
+handler t4 open as h4;
+handler h4 read first limit 9;
+c1
+t4
+create table t5 (c1 char(20));
+insert into t5 values ("t5");
+handler t5 open as h5;
+handler h5 read first limit 9;
+c1
+t5
+alter table t1 engine=MyISAM;
+handler h1 read first limit 9;
+Unknown table 'h1' in HANDLER
+handler h2 read first limit 9;
+c1
+t2
+handler h3 read first limit 9;
+c1
+t3
+handler h4 read first limit 9;
+c1
+t4
+handler h5 read first limit 9;
+c1
+t5
+alter table t5 engine=MyISAM;
+handler h1 read first limit 9;
+Unknown table 'h1' in HANDLER
+handler h2 read first limit 9;
+c1
+t2
+handler h3 read first limit 9;
+c1
+t3
+handler h4 read first limit 9;
+c1
+t4
+handler h5 read first limit 9;
+Unknown table 'h5' in HANDLER
+alter table t3 engine=MyISAM;
+handler h1 read first limit 9;
+Unknown table 'h1' in HANDLER
+handler h2 read first limit 9;
+c1
+t2
+handler h3 read first limit 9;
+Unknown table 'h3' in HANDLER
+handler h4 read first limit 9;
+c1
+t4
+handler h5 read first limit 9;
+Unknown table 'h5' in HANDLER
+handler h2 close;
+handler h4 close;
+handler t1 open as h1_1;
+handler t1 open as h1_2;
+handler t1 open as h1_3;
+handler h1_1 read first limit 9;
+c1
+t1
+handler h1_2 read first limit 9;
+c1
+t1
+handler h1_3 read first limit 9;
+c1
+t1
+alter table t1 engine=MyISAM;
+handler h1_1 read first limit 9;
+Unknown table 'h1_1' in HANDLER
+handler h1_2 read first limit 9;
+Unknown table 'h1_2' in HANDLER
+handler h1_3 read first limit 9;
+Unknown table 'h1_3' in HANDLER
+drop table t1;
+drop table t2;
+drop table t3;
+drop table t4;
+drop table t5;
diff --git a/mysql-test/t/flush_table.test b/mysql-test/t/flush_table.test
index ad81f266afc..58c12bad3fa 100644
--- a/mysql-test/t/flush_table.test
+++ b/mysql-test/t/flush_table.test
@@ -13,62 +13,9 @@ check table t1;
 drop table t1;
 
 #
-# Check if two database names beginning the same are seen as different.
-#
-# This database begins like the usual 'test' database.
-#
---disable_warnings
-drop database if exists test_test;
---enable_warnings
-create database test_test;
-use test_test;
-create table t1(table_id char(20) primary key);
-insert into t1 values ('test_test.t1');
-insert into t1 values ('');
-handler t1 open;
-handler t1 read first limit 9;
-create table t2(table_id char(20) primary key);
-insert into t2 values ('test_test.t2');
-insert into t2 values ('');
-handler t2 open;
-handler t2 read first limit 9;
-#
-# This is the usual 'test' database.
-#
-use test;
---disable_warnings
-drop table if exists t1;
---enable_warnings
-create table t1(table_id char(20) primary key);
-insert into t1 values ('test.t1');
-insert into t1 values ('');
-handler t1 open;
-handler t1 read first limit 9;
-#
-# Check accesibility of all the tables.
-#
-use test;
-handler test.t1 read first limit 9;
---error 1109;
-handler test.t2 read first limit 9;
-handler test_test.t1 read first limit 9;
-handler test_test.t2 read first limit 9;
-#
-# Cleanup.
-#
-handler test_test.t1 close;
-drop table test_test.t1;
-handler test_test.t2 close;
-drop table test_test.t2;
-drop database test_test;
-#
-use test;
-handler test.t1 close;
-drop table test.t1;
-
-#
 # In the following test FLUSH TABLES produces a deadlock
-# (hang forever) if the fix for bug#3565 is missing.
+# (hang forever) if the fix for BUG #3565 is missing.
+# And it shows that handler tables are re-opened after flush (BUG #4286).
 #
 --disable_warnings
 drop table if exists t1;
@@ -87,28 +34,43 @@ handler a1 read first limit 9;
 handler a2 read first limit 9;
 handler t2 read first limit 9;
 flush tables;
---error 1109;
 handler a1 read first limit 9;
---error 1109;
 handler a2 read first limit 9;
---error 1109;
 handler t2 read first limit 9;
 #
+--error 1066
 handler t1 open as a1;
+--error 1066
 handler t1 open as a2;
+--error 1066
 handler t2 open;
 handler a1 read first limit 9;
 handler a2 read first limit 9;
 handler t2 read first limit 9;
 flush table t1;
---error 1109;
 handler a1 read first limit 9;
---error 1109;
 handler a2 read first limit 9;
 handler t2 read first limit 9;
 flush table t2;
---error 1109;
 handler t2 close;
 drop table t1;
 drop table t2;
 
+#
+# The fix for BUG #4286 cannot restore the position after a flush.
+#
+create table t1(table_id char(20) primary key);
+insert into t1 values ('Record-01');
+insert into t1 values ('Record-02');
+insert into t1 values ('Record-03');
+insert into t1 values ('Record-04');
+insert into t1 values ('Record-05');
+handler t1 open;
+handler t1 read first limit 1;
+handler t1 read next limit 1;
+handler t1 read next limit 1;
+flush table t1;
+handler t1 read next limit 1;
+handler t1 read next limit 1;
+handler t1 close;
+drop table t1;
diff --git a/mysql-test/t/handler.test b/mysql-test/t/handler.test
index 1f7f32c930a..53fe8c0a059 100644
--- a/mysql-test/t/handler.test
+++ b/mysql-test/t/handler.test
@@ -135,3 +135,207 @@ handler t1 read a=(1) where b=1;
 handler t1 close;
 drop table t1;
 
+#
+# Check if two database names beginning the same are seen as different.
+#
+# This database begins like the usual 'test' database.
+#
+--disable_warnings
+drop database if exists test_test;
+--enable_warnings
+create database test_test;
+use test_test;
+create table t1(table_id char(20) primary key);
+insert into t1 values ('test_test.t1');
+insert into t1 values ('');
+handler t1 open;
+handler t1 read first limit 9;
+create table t2(table_id char(20) primary key);
+insert into t2 values ('test_test.t2');
+insert into t2 values ('');
+handler t2 open;
+handler t2 read first limit 9;
+#
+# This is the usual 'test' database.
+#
+use test;
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1(table_id char(20) primary key);
+insert into t1 values ('test.t1');
+insert into t1 values ('');
+--error 1066
+handler t1 open;
+#
+# Check accesibility of all the tables.
+#
+use test;
+--error 1109;
+handler test.t1 read first limit 9;
+handler test_test.t1 read first limit 9;
+handler t1 read first limit 9;
+handler test_test.t2 read first limit 9;
+handler t2 read first limit 9;
+#
+# Cleanup.
+#
+
+handler test_test.t1 close;
+--error 1109;
+handler t1 close;
+drop table test_test.t1;
+handler test_test.t2 close;
+--error 1109;
+handler t2 close;
+drop table test_test.t2;
+drop database test_test;
+#
+use test;
+--error 1109;
+handler test.t1 close;
+--error 1109;
+handler t1 close;
+drop table test.t1;
+
+#
+# BUG#4335
+#
+--disable_warnings
+drop database if exists test_test;
+drop table if exists t1;
+drop table if exists t2;
+drop table if exists t3;
+--enable_warnings
+create database test_test;
+use test_test;
+create table t1 (c1 char(20));
+insert into t1 values ('test_test.t1');
+create table t3 (c1 char(20));
+insert into t3 values ('test_test.t3');
+handler t1 open;
+handler t1 read first limit 9;
+handler t1 open h1;
+handler h1 read first limit 9;
+use test;
+create table t1 (c1 char(20));
+create table t2 (c1 char(20));
+create table t3 (c1 char(20));
+insert into t1 values ('t1');
+insert into t2 values ('t2');
+insert into t3 values ('t3');
+--error 1066
+handler t1 open;
+--error 1066
+handler t2 open t1;
+--error 1066
+handler t3 open t1;
+handler t1 read first limit 9;
+--error 1109
+handler test.t1 close;
+--error 1066
+handler test.t1 open h1;
+--error 1066
+handler test_test.t1 open h1;
+handler test_test.t3 open h3;
+handler test.t1 open h2;
+handler t1 read first limit 9;
+handler h1 read first limit 9;
+handler h2 read first limit 9;
+handler h3 read first limit 9;
+handler test.h2 read first limit 9;
+--error 1109
+handler test.h1 close;
+handler test_test.t1 close;
+handler test_test.h1 close;
+handler h2 close;
+--error 1109
+handler t1 read first limit 9;
+--error 1109
+handler h1 read first limit 9;
+--error 1109
+handler h2 read first limit 9;
+handler h3 read first limit 9;
+handler test_test.h3 read first limit 9;
+use test_test;
+handler h3 read first limit 9;
+--error 1109
+handler test.h3 read first limit 9;
+handler test_test.h3 close;
+use test;
+drop table t3;
+drop table t2;
+drop table t1;
+drop database test_test;
+
+#
+# Test if fix for BUG#4286 correctly closes handler tables.
+#
+create table t1 (c1 char(20));
+insert into t1 values ("t1");
+handler t1 open as h1;
+handler h1 read first limit 9;
+create table t2 (c1 char(20));
+insert into t2 values ("t2");
+handler t2 open as h2;
+handler h2 read first limit 9;
+create table t3 (c1 char(20));
+insert into t3 values ("t3");
+handler t3 open as h3;
+handler h3 read first limit 9;
+create table t4 (c1 char(20));
+insert into t4 values ("t4");
+handler t4 open as h4;
+handler h4 read first limit 9;
+create table t5 (c1 char(20));
+insert into t5 values ("t5");
+handler t5 open as h5;
+handler h5 read first limit 9;
+# close first
+alter table t1 engine=MyISAM;
+--error 1109;
+handler h1 read first limit 9;
+handler h2 read first limit 9;
+handler h3 read first limit 9;
+handler h4 read first limit 9;
+handler h5 read first limit 9;
+# close last
+alter table t5 engine=MyISAM;
+--error 1109;
+handler h1 read first limit 9;
+handler h2 read first limit 9;
+handler h3 read first limit 9;
+handler h4 read first limit 9;
+--error 1109;
+handler h5 read first limit 9;
+# close middle
+alter table t3 engine=MyISAM;
+--error 1109;
+handler h1 read first limit 9;
+handler h2 read first limit 9;
+--error 1109;
+handler h3 read first limit 9;
+handler h4 read first limit 9;
+--error 1109;
+handler h5 read first limit 9;
+handler h2 close;
+handler h4 close;
+# close all depending handler tables
+handler t1 open as h1_1;
+handler t1 open as h1_2;
+handler t1 open as h1_3;
+handler h1_1 read first limit 9;
+handler h1_2 read first limit 9;
+handler h1_3 read first limit 9;
+alter table t1 engine=MyISAM;
+--error 1109;
+handler h1_1 read first limit 9;
+--error 1109;
+handler h1_2 read first limit 9;
+--error 1109;
+handler h1_3 read first limit 9;
+drop table t1;
+drop table t2;
+drop table t3;
+drop table t4;
+drop table t5;
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index acc07eb6188..8b41774e970 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -540,12 +540,15 @@ int mysql_find_files(THD *thd,List<char> *files, const char *db,
                 const char *path, const char *wild, bool dir);
 
 /* sql_handler.cc */
-int mysql_ha_open(THD *thd, TABLE_LIST *tables);
-int mysql_ha_close(THD *thd, TABLE_LIST *tables,
-                   bool dont_send_ok=0, bool dont_lock=0, bool no_alias=0);
-int mysql_ha_close_list(THD *thd, TABLE_LIST *tables, bool flushed=0);
+int mysql_ha_open(THD *thd, TABLE_LIST *tables, bool reopen= 0);
+int mysql_ha_close(THD *thd, TABLE_LIST *tables);
 int mysql_ha_read(THD *, TABLE_LIST *,enum enum_ha_read_modes,char *,
                List<Item> *,enum ha_rkey_function,Item *,ha_rows,ha_rows);
+int mysql_ha_flush(THD *thd, TABLE_LIST *tables, int mode_flags);
+/* mysql_ha_flush mode_flags bits */
+#define MYSQL_HA_CLOSE_FINAL        0x00
+#define MYSQL_HA_REOPEN_ON_USAGE    0x01
+#define MYSQL_HA_FLUSH_ALL          0x02
 
 /* sql_base.cc */
 void set_item_name(Item *item,char *pos,uint length);
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 8fd7273fd78..1ecd606f7d9 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -389,7 +389,7 @@ bool close_cached_tables(THD *thd, bool if_wait_for_refresh,
     thd->proc_info="Flushing tables";
 
     close_old_data_files(thd,thd->open_tables,1,1);
-    mysql_ha_close_list(thd, tables);
+    mysql_ha_flush(thd, tables, MYSQL_HA_REOPEN_ON_USAGE | MYSQL_HA_FLUSH_ALL);
     bool found=1;
     /* Wait until all threads has closed all the tables we had locked */
     DBUG_PRINT("info", ("Waiting for others threads to close their open tables"));
@@ -859,7 +859,7 @@ TABLE *open_table(THD *thd,const char *db,const char *table_name,
   }
 
   /* close handler tables which are marked for flush */
-  mysql_ha_close_list(thd, (TABLE_LIST*) NULL, /*flushed*/ 1);
+  mysql_ha_flush(thd, (TABLE_LIST*) NULL, MYSQL_HA_REOPEN_ON_USAGE);
 
   for (table=(TABLE*) hash_search(&open_cache,(byte*) key,key_length) ;
        table && table->in_use ;
@@ -1226,7 +1226,7 @@ bool wait_for_tables(THD *thd)
   {
     thd->some_tables_deleted=0;
     close_old_data_files(thd,thd->open_tables,0,dropping_tables != 0);
-    mysql_ha_close_list(thd, (TABLE_LIST*) NULL, /*flushed*/ 1);
+    mysql_ha_flush(thd, (TABLE_LIST*) NULL, MYSQL_HA_REOPEN_ON_USAGE);
     if (!table_is_used(thd->open_tables,1))
       break;
     (void) pthread_cond_wait(&COND_refresh,&LOCK_open);
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index eb6e74a58c4..c829778151b 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -78,9 +78,9 @@ extern "C" void free_user_var(user_var_entry *entry)
 ** Thread specific functions
 ****************************************************************************/
 
-THD::THD():user_time(0),fatal_error(0),last_insert_id_used(0),
-	   insert_id_used(0),rand_used(0),in_lock_tables(0),
-	   global_read_lock(0),bootstrap(0)
+THD::THD():user_time(0),global_read_lock(0),fatal_error(0),
+           last_insert_id_used(0),insert_id_used(0),rand_used(0),
+           in_lock_tables(0),bootstrap(0)
 {
   host=user=priv_user=db=query=ip=0;
   host_or_ip= "connecting host";
@@ -90,6 +90,7 @@ THD::THD():user_time(0),fatal_error(0),last_insert_id_used(0),
   query_error=0;
   next_insert_id=last_insert_id=0;
   open_tables=temporary_tables=handler_tables=0;
+  hash_clear(&handler_tables_hash);
   current_tablenr=0;
   handler_items=0;
   tmp_table=0;
@@ -215,11 +216,9 @@ void THD::cleanup(void)
     lock=locked_tables; locked_tables=0;
     close_thread_tables(this);
   }
-  if (handler_tables)
-  {
-    open_tables=handler_tables; handler_tables=0;
-    close_thread_tables(this);
-  }
+  mysql_ha_flush(this, (TABLE_LIST*) 0,
+                 MYSQL_HA_CLOSE_FINAL | MYSQL_HA_FLUSH_ALL);
+  hash_free(&handler_tables_hash);
   close_temporary_tables(this);
   hash_free(&user_vars);
   if (global_read_lock)
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 30947041b7d..d84a5ba88ff 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -421,6 +421,7 @@ public:
      and are still in use by this thread
   */
   TABLE   *open_tables,*temporary_tables, *handler_tables;
+  HASH    handler_tables_hash;
   // TODO: document the variables below
   MYSQL_LOCK *lock,*locked_tables;
   ULL	  *ull;
diff --git a/sql/sql_handler.cc b/sql/sql_handler.cc
index 272289b6176..f056651919f 100644
--- a/sql/sql_handler.cc
+++ b/sql/sql_handler.cc
@@ -17,10 +17,6 @@
 
 /* HANDLER ... commands - direct access to ISAM */
 
-#include "mysql_priv.h"
-#include "sql_select.h"
-#include <assert.h>
-
 /* TODO:
   HANDLER blabla OPEN [ AS foobar ] [ (column-list) ]
 
@@ -38,183 +34,404 @@
   all the sql_alloc'ed memory. It's harder to work around...
 */
 
+/*
+  There are two containers holding information about open handler tables.
+  The first is 'thd->handler_tables'. It is a linked list of TABLE objects.
+  It is used like 'thd->open_tables' in the table cache. The trick is to
+  exchange these two lists during open and lock of tables. Thus the normal
+  table cache code can be used.
+  The second container is a HASH. It holds objects of the type TABLE_LIST.
+  Despite its name, no lists of tables but only single structs are hashed
+  (the 'next' pointer is always NULL). The reason for theis second container
+  is, that we want handler tables to survive FLUSH TABLE commands. A table
+  affected by FLUSH TABLE must be closed so that other threads are not
+  blocked by handler tables still in use. Since we use the normal table cache
+  functions with 'thd->handler_tables', the closed tables are removed from
+  this list. Hence we need the original open information for the handler
+  table in the case that it is used again. This information is handed over
+  to mysql_ha_open() as a TABLE_LIST. So we store this information in the
+  second container, where it is not affected by FLUSH TABLE. The second
+  container is implemented as a hash for performance reasons. Consequently,
+  we use it not only for re-opening a handler table, but also for the
+  HANDLER ... READ commands. For this purpose, we store a pointer to the
+  TABLE structure (in the first container) in the TBALE_LIST object in the
+  second container. When the table is flushed, the pointer is cleared.
+*/
+
+#include "mysql_priv.h"
+#include "sql_select.h"
+#include <assert.h>
+
+#define HANDLER_TABLES_HASH_SIZE 120
+
+static enum enum_ha_read_modes rkey_to_rnext[]=
+    { RNEXT_SAME, RNEXT, RPREV, RNEXT, RPREV, RNEXT, RPREV };
+
 #define HANDLER_TABLES_HACK(thd) {      \
   TABLE *tmp=thd->open_tables;          \
   thd->open_tables=thd->handler_tables; \
   thd->handler_tables=tmp; }
 
-static TABLE **find_table_ptr_by_name(THD *thd,const char *db,
-                                      const char *table_name,
-                                      bool is_alias, bool dont_lock,
-                                      bool *was_flushed);
+static int mysql_ha_flush_table(THD *thd, TABLE **table_ptr, int mode_flags);
+
+
+/*
+  Get hash key and hash key length.
+
+  SYNOPSIS
+    mysql_ha_hash_get_key()
+    tables                      Pointer to the hash object.
+    key_len_p   (out)           Pointer to the result for key length.
+    first                       Unused.
+
+  DESCRIPTION
+    The hash object is an TABLE_LIST struct.
+    The hash key is the alias name.
+    The hash key length is the alias name length plus one for the
+    terminateing NUL character.
 
-int mysql_ha_open(THD *thd, TABLE_LIST *tables)
+  RETURN
+    Pointer to the TABLE_LIST struct.
+*/
+
+static char *mysql_ha_hash_get_key(TABLE_LIST *tables, uint *key_len_p,
+                                   my_bool first __attribute__((unused)))
 {
-  HANDLER_TABLES_HACK(thd);
-  int err=open_tables(thd,tables);
-  HANDLER_TABLES_HACK(thd);
-  if (err)
-    return -1;
+  *key_len_p= strlen(tables->alias) + 1 ; /* include '\0' in comparisons */
+  return tables->alias;
+}
 
-  // there can be only one table in *tables
-  if (!(tables->table->file->table_flags() & HA_CAN_SQL_HANDLER))
-  {
-    my_printf_error(ER_ILLEGAL_HA,ER(ER_ILLEGAL_HA),MYF(0), tables->alias);
-    mysql_ha_close(thd, tables,1);
-    return -1;
-  }
 
-  send_ok(&thd->net);
-  return 0;
+/*
+  Free an hash object.
+
+  SYNOPSIS
+    mysql_ha_hash_free()
+    tables                      Pointer to the hash object.
+
+  DESCRIPTION
+    The hash object is an TABLE_LIST struct.
+
+  RETURN
+    Nothing
+*/
+
+static void mysql_ha_hash_free(TABLE_LIST *tables)
+{
+  my_free((char*) tables, MYF(0));
 }
 
 
 /*
-  Close a HANDLER table.
+  Open a HANDLER table.
 
   SYNOPSIS
-    mysql_ha_close()
+    mysql_ha_open()
     thd                         Thread identifier.
-    tables                      A list of tables with the first entry to close.
-    dont_send_ok                Suppresses the commands' ok message and
-                                error message and error return.
-    dont_lock                   Suppresses the normal locking of LOCK_open.
+    tables                      A list of tables with the first entry to open.
+    reopen                      Re-open a previously opened handler table.
 
   DESCRIPTION
     Though this function takes a list of tables, only the first list entry
-    will be closed. Broadcasts a COND_refresh condition.
-    If mysql_ha_close() is not called from the parser, 'dont_send_ok'
-    must be set.
-    If the caller did already lock LOCK_open, it must set 'dont_lock'.
-
-  IMPLEMENTATION
-    find_table_ptr_by_name() closes the table, if a FLUSH TABLE is outstanding.
-    It returns a NULL pointer in this case, but flags the situation in
-    'was_flushed'. In that case the normal ER_UNKNOWN_TABLE error messages
-    is suppressed.
+    will be opened.
+    'reopen' is set when a handler table is to be re-opened. In this case,
+    'tables' is the pointer to the hashed TABLE_LIST object which has been
+    saved on the original open.
+    'reopen' is also used to suppress the sending of an 'ok' message or
+    error messages.
 
   RETURN
-    0  ok
-    -1 error
+    0    ok
+    != 0 error
 */
 
-int mysql_ha_close(THD *thd, TABLE_LIST *tables,
-                   bool dont_send_ok, bool dont_lock, bool no_alias)
+int mysql_ha_open(THD *thd, TABLE_LIST *tables, bool reopen)
 {
-  TABLE         **table_ptr;
-  bool          was_flushed;
-
-  table_ptr= find_table_ptr_by_name(thd, tables->db, tables->alias,
-                                    !no_alias, dont_lock, &was_flushed);
-  if (*table_ptr)
+  TABLE_LIST    *hash_tables;
+  char          *db;
+  char          *name;
+  char          *alias;
+  uint          dblen;
+  uint          namelen;
+  uint          aliaslen;
+  int           err;
+  DBUG_ENTER("mysql_ha_open");
+  DBUG_PRINT("enter",("mysql_ha_open: '%s'.'%s' as '%s' reopen %d",
+                      tables->db, tables->real_name, tables->alias, reopen));
+
+  if (! hash_inited(&thd->handler_tables_hash))
+  {
+    /*
+      HASH entries are of type TABLE_LIST.
+    */
+    if (hash_init(&thd->handler_tables_hash, HANDLER_TABLES_HASH_SIZE, 0, 0,
+                  (hash_get_key) mysql_ha_hash_get_key,
+                  (hash_free_key) mysql_ha_hash_free, 0))
+      goto err;
+  }
+  else if (! reopen) /* Otherwise we have 'tables' already. */
   {
-    if (!dont_lock)
-      VOID(pthread_mutex_lock(&LOCK_open));
-    if (close_thread_table(thd, table_ptr))
+    if (hash_search(&thd->handler_tables_hash, (byte*) tables->alias,
+                    strlen(tables->alias) + 1))
     {
-      /* Tell threads waiting for refresh that something has happened */
-      VOID(pthread_cond_broadcast(&COND_refresh));
+      DBUG_PRINT("info",("mysql_ha_open:  duplicate '%s'", tables->alias));
+      if (! reopen)
+        my_printf_error(ER_NONUNIQ_TABLE, ER(ER_NONUNIQ_TABLE),
+                        MYF(0), tables->alias);
+      goto err;
     }
-    if (!dont_lock)
-      VOID(pthread_mutex_unlock(&LOCK_open));
   }
-  else if (!was_flushed && !dont_send_ok)
+
+  /*
+    open_tables() will set 'tables->table' if successful.
+    It must be NULL for a real open when calling open_tables().
+  */
+  DBUG_ASSERT(! tables->table);
+  HANDLER_TABLES_HACK(thd);
+  err=open_tables(thd,tables);
+  HANDLER_TABLES_HACK(thd);
+  if (err)
+    goto err;
+
+  /* There can be only one table in '*tables'. */
+  if (! (tables->table->file->table_flags() & HA_CAN_SQL_HANDLER))
   {
-    my_printf_error(ER_UNKNOWN_TABLE, ER(ER_UNKNOWN_TABLE), MYF(0),
-                    tables->alias, "HANDLER");
-    return -1;
+    if (! reopen)
+      my_printf_error(ER_ILLEGAL_HA,ER(ER_ILLEGAL_HA),MYF(0), tables->alias);
+    mysql_ha_close(thd, tables);
+    goto err;
+  }
+
+  if (! reopen)
+  {
+    /* copy the TABLE_LIST struct */
+    dblen= strlen(tables->db) + 1;
+    namelen= strlen(tables->real_name) + 1;
+    aliaslen= strlen(tables->alias) + 1;
+    if (!(my_multi_malloc(MYF(MY_WME),
+                          &hash_tables, sizeof(*hash_tables),
+                          &db, dblen,
+                          &name, namelen,
+                          &alias, aliaslen,
+                          NullS)))
+    {
+      DBUG_PRINT("exit",("mysql_ha_open: malloc ERROR"));
+      goto err;
+    }
+    /* structure copy */
+    *hash_tables= *tables;
+    hash_tables->db= db;
+    hash_tables->real_name= name;
+    hash_tables->alias= alias;
+    memcpy(hash_tables->db, tables->db, dblen);
+    memcpy(hash_tables->real_name, tables->real_name, namelen);
+    memcpy(hash_tables->alias, tables->alias, aliaslen);
+
+    /* add to hash */
+    if (hash_insert(&thd->handler_tables_hash, (byte*) hash_tables))
+    {
+      mysql_ha_close(thd, tables);
+      goto err;
+    }
   }
-  if (!dont_send_ok)
+
+  if (! reopen)
     send_ok(&thd->net);
-  return 0;
+  DBUG_PRINT("exit",("mysql_ha_open: OK"));
+  DBUG_RETURN(0);
+
+err:
+  DBUG_PRINT("exit",("mysql_ha_open: ERROR"));
+  DBUG_RETURN(-1);
 }
 
 
 /*
-  Close a list of HANDLER tables.
+  Close a HANDLER table.
 
   SYNOPSIS
-    mysql_ha_close_list()
+    mysql_ha_close()
     thd                         Thread identifier.
-    tables                      The list of tables to close. If NULL,
-                                close all HANDLER tables.
-    flushed                     Close only tables which are marked flushed.
-                                Used only if tables is NULL.
+    tables                      A list of tables with the first entry to close.
 
   DESCRIPTION
-    The list of HANDLER tables may be NULL, in which case all HANDLER
-    tables are closed. Broadcasts a COND_refresh condition, for
-    every table closed. If 'tables' is NULL and 'flushed' is set,
-    all HANDLER tables marked for flush are closed.
-    The caller must lock LOCK_open.
-
-  IMPLEMENTATION
-    find_table_ptr_by_name() closes the table, if it is marked for flush.
-    It returns a NULL pointer in this case, but flags the situation in
-    'was_flushed'. In that case the normal ER_UNKNOWN_TABLE error messages
-    is suppressed.
+    Though this function takes a list of tables, only the first list entry
+    will be closed. Broadcasts a COND_refresh condition.
 
   RETURN
-    0  ok
+    0    ok
+    != 0 error
 */
 
-int mysql_ha_close_list(THD *thd, TABLE_LIST *tables, bool flushed)
+int mysql_ha_close(THD *thd, TABLE_LIST *tables)
 {
-  TABLE_LIST    *tl_item;
+  TABLE_LIST    *hash_tables;
   TABLE         **table_ptr;
-
-  if (tables)
+  bool          was_flushed= FALSE;
+  bool          not_opened;
+  DBUG_ENTER("mysql_ha_close");
+  DBUG_PRINT("enter",("mysql_ha_close: '%s'.'%s' as '%s'",
+                      tables->db, tables->real_name, tables->alias));
+
+  if ((hash_tables= (TABLE_LIST*) hash_search(&thd->handler_tables_hash,
+                                              (byte*) tables->alias,
+                                              strlen(tables->alias) + 1)))
   {
-    for (tl_item= tables ; tl_item; tl_item= tl_item->next)
+    /*
+      Though we could take the table pointer from hash_tables->table,
+      we must follow the thd->handler_tables chain anyway, as we need the
+      address of the 'next' pointer referencing this table
+      for close_thread_table().
+    */
+    for (table_ptr= &(thd->handler_tables);
+         *table_ptr && (*table_ptr != hash_tables->table);
+           table_ptr= &(*table_ptr)->next);
+
+#if MYSQL_VERSION_ID < 40100
+    if (*tables->db && strcmp(hash_tables->db, tables->db))
     {
-      mysql_ha_close(thd, tl_item, /*dont_send_ok*/ 1,
-                     /*dont_lock*/ 1, /*no_alias*/ 1);
+      DBUG_PRINT("info",("mysql_ha_close: wrong db"));
+      hash_tables= NULL;
     }
-  }
-  else
-  {
-    table_ptr= &(thd->handler_tables);
-    while (*table_ptr)
+    else
+#endif
     {
-      if (! flushed || ((*table_ptr)->version != refresh_version))
+      if (*table_ptr)
       {
+        VOID(pthread_mutex_lock(&LOCK_open));
         if (close_thread_table(thd, table_ptr))
         {
           /* Tell threads waiting for refresh that something has happened */
           VOID(pthread_cond_broadcast(&COND_refresh));
         }
-        continue;
+        VOID(pthread_mutex_unlock(&LOCK_open));
       }
-      table_ptr= &((*table_ptr)->next);
+
+      hash_delete(&thd->handler_tables_hash, (byte*) hash_tables);
     }
   }
-  return 0;
+
+  if (! hash_tables)
+  {
+#if MYSQL_VERSION_ID < 40100
+    char buff[MAX_DBKEY_LENGTH];
+    if (*tables->db)
+      strxnmov(buff, sizeof(buff), tables->db, ".", tables->real_name, NullS);
+    else
+      strncpy(buff, tables->alias, sizeof(buff));
+    my_printf_error(ER_UNKNOWN_TABLE, ER(ER_UNKNOWN_TABLE), MYF(0),
+                    buff, "HANDLER");
+#else
+    my_printf_error(ER_UNKNOWN_TABLE, ER(ER_UNKNOWN_TABLE), MYF(0),
+                    tables->alias, "HANDLER");
+#endif
+    DBUG_PRINT("exit",("mysql_ha_close: ERROR"));
+    DBUG_RETURN(-1);
+  }
+
+  send_ok(&thd->net);
+  DBUG_PRINT("exit",("mysql_ha_close: OK"));
+  DBUG_RETURN(0);
 }
 
-static enum enum_ha_read_modes rkey_to_rnext[]=
-    { RNEXT_SAME, RNEXT, RPREV, RNEXT, RPREV, RNEXT, RPREV };
 
+/*
+  Read from a HANDLER table.
+
+  SYNOPSIS
+    mysql_ha_read()
+    thd                         Thread identifier.
+    tables                      A list of tables with the first entry to read.
+    mode
+    keyname
+    key_expr
+    ha_rkey_mode
+    cond
+    select_limit
+    offset_limit
 
+  RETURN
+    0    ok
+    != 0 error
+*/
+ 
 int mysql_ha_read(THD *thd, TABLE_LIST *tables,
     enum enum_ha_read_modes mode, char *keyname, List<Item> *key_expr,
     enum ha_rkey_function ha_rkey_mode, Item *cond,
     ha_rows select_limit,ha_rows offset_limit)
 {
-  int err, keyno=-1;
-  bool was_flushed;
-  TABLE *table= *find_table_ptr_by_name(thd, tables->db, tables->alias,
-                                        /*is_alias*/ 1, /*dont_lock*/ 0,
-                                        &was_flushed);
+  TABLE_LIST    *hash_tables;
+  TABLE         *table;
+  int           err;
+  int           keyno=-1;
+  uint          num_rows;
+  bool          was_flushed;
+  MYSQL_LOCK    *lock;
+  DBUG_ENTER("mysql_ha_read");
+  DBUG_PRINT("enter",("mysql_ha_read: '%s'.'%s' as '%s'",
+                      tables->db, tables->real_name, tables->alias));
+
+  List<Item> list;
+  list.push_front(new Item_field(NULL,NULL,"*"));
+  List_iterator<Item> it(list);
+  it++;
+
+  if ((hash_tables= (TABLE_LIST*) hash_search(&thd->handler_tables_hash,
+                                              (byte*) tables->alias,
+                                              strlen(tables->alias) + 1)))
+  {
+    table= hash_tables->table;
+    DBUG_PRINT("info",("mysql_ha_read: found in hash '%s'.'%s' as '%s' tab %p",
+                       hash_tables->db, hash_tables->real_name,
+                       hash_tables->alias, table));
+    if (!table)
+    {
+      /*
+        The handler table has been closed. Re-open it.
+      */
+      if (mysql_ha_open(thd, hash_tables, 1))
+      {
+        DBUG_PRINT("exit",("mysql_ha_read: reopen failed"));
+        goto err0;
+      }
+
+      table= hash_tables->table;
+      DBUG_PRINT("info",("mysql_ha_read: re-opened '%s'.'%s' as '%s' tab %p",
+                         hash_tables->db, hash_tables->real_name,
+                         hash_tables->alias, table));
+    }
+
+#if MYSQL_VERSION_ID < 40100
+    if (*tables->db && strcmp(table->table_cache_key, tables->db))
+    {
+      DBUG_PRINT("info",("mysql_ha_read: wrong db"));
+      table= NULL;
+    }
+#endif
+  }
+  else
+    table= NULL;
+
   if (!table)
   {
-    my_printf_error(ER_UNKNOWN_TABLE,ER(ER_UNKNOWN_TABLE),MYF(0),
-		    tables->alias,"HANDLER");
-    return -1;
+#if MYSQL_VERSION_ID < 40100
+    char buff[MAX_DBKEY_LENGTH];
+    if (*tables->db)
+      strxnmov(buff, sizeof(buff), tables->db, ".", tables->real_name, NullS);
+    else
+      strncpy(buff, tables->alias, sizeof(buff));
+    my_printf_error(ER_UNKNOWN_TABLE, ER(ER_UNKNOWN_TABLE), MYF(0),
+                    buff, "HANDLER");
+#else
+    my_printf_error(ER_UNKNOWN_TABLE, ER(ER_UNKNOWN_TABLE), MYF(0),
+                    tables->alias, "HANDLER");
+#endif
+    goto err0;
   }
   tables->table=table;
 
   if (cond && cond->fix_fields(thd,tables))
-    return -1;
+    goto err0;
 
   table->file->init_table_handle_for_HANDLER(); // Only InnoDB requires it
 
@@ -224,24 +441,19 @@ int mysql_ha_read(THD *thd, TABLE_LIST *tables,
     {
       my_printf_error(ER_KEY_DOES_NOT_EXITS,ER(ER_KEY_DOES_NOT_EXITS),MYF(0),
           keyname,tables->alias);
-      return -1;
+      goto err0;
     }
     table->file->index_init(keyno);
   }
 
-  List<Item> list;
-  list.push_front(new Item_field(NULL,NULL,"*"));
-  List_iterator<Item> it(list);
-  uint num_rows;
-  it++;
-
-  insert_fields(thd,tables,tables->db,tables->alias,&it);
+  if (insert_fields(thd,tables,tables->db,tables->alias,&it))
+    goto err0;
 
   select_limit+=offset_limit;
   send_fields(thd,list,1);
 
   HANDLER_TABLES_HACK(thd);
-  MYSQL_LOCK *lock=mysql_lock_tables(thd,&tables->table,1);
+  lock= mysql_lock_tables(thd, &tables->table, 1);
   HANDLER_TABLES_HACK(thd);
   
   byte *key;
@@ -363,83 +575,155 @@ int mysql_ha_read(THD *thd, TABLE_LIST *tables,
 ok:
   mysql_unlock_tables(thd,lock);
   send_eof(&thd->net);
-  return 0;
+  DBUG_PRINT("exit",("mysql_ha_read: OK"));
+  DBUG_RETURN(0);
 err:
   mysql_unlock_tables(thd,lock);
 err0:
-  return -1;
+  DBUG_PRINT("exit",("mysql_ha_read: ERROR"));
+  DBUG_RETURN(-1);
 }
 
 
 /*
-  Find a HANDLER table by name.
+  Flush (close) a list of HANDLER tables.
 
   SYNOPSIS
-    find_table_ptr_by_name()
+    mysql_ha_flush()
     thd                         Thread identifier.
-    db                          Database (schema) name.
-    table_name                  Table name ;-).
-    is_alias                    Table name may be an alias name.
-    dont_lock                   Suppresses the normal locking of LOCK_open.
+    tables                      The list of tables to close. If NULL,
+                                close all HANDLER tables [marked as flushed].
+    mode_flags                  MYSQL_HA_CLOSE_FINAL finally close the table.
+                                MYSQL_HA_REOPEN_ON_USAGE mark for reopen.
+                                MYSQL_HA_FLUSH_ALL flush all tables, not only
+                                those marked for flush.
 
   DESCRIPTION
-    Find the table 'db'.'table_name' in the list of HANDLER tables of the
-    thread 'thd'. If the table has been marked by FLUSH TABLE(S), close it,
-    flag this situation in '*was_flushed' and broadcast a COND_refresh
-    condition.
-    An empty database (schema) name matches all database (schema) names.
-    If the caller did already lock LOCK_open, it must set 'dont_lock'.
-
-  IMPLEMENTATION
-    Just in case that the table is twice in 'thd->handler_tables' (!?!),
-    the loop does not break when the table was flushed. If another table
-    by that name was found and not flushed, '*was_flushed' is cleared again,
-    since a pointer to an open HANDLER table is returned.
+    The list of HANDLER tables may be NULL, in which case all HANDLER
+    tables are closed (if MYSQL_HA_FLUSH_ALL) is set.
+    If 'tables' is NULL and MYSQL_HA_FLUSH_ALL is not set,
+    all HANDLER tables marked for flush are closed.
+    Broadcasts a COND_refresh condition, for every table closed.
+    The caller must lock LOCK_open.
+
+  NOTE
+    Since mysql_ha_flush() is called when the base table has to be closed,
+    we compare real table names, not aliases. Hence, database names matter.
 
   RETURN
-    *was_flushed                Table has been closed due to FLUSH TABLE.
-    NULL     A HANDLER Table by that name does not exist (any more).
-    != NULL  Pointer to the TABLE structure.
+    0  ok
 */
 
-static TABLE **find_table_ptr_by_name(THD *thd, const char *db,
-                                      const char *table_name,
-                                      bool is_alias, bool dont_lock,
-                                      bool *was_flushed)
+int mysql_ha_flush(THD *thd, TABLE_LIST *tables, int mode_flags)
 {
-  int dblen;
-  TABLE **table_ptr;
-
-  DBUG_ASSERT(db);
-  dblen= *db ? strlen(db)+1 : 0;
-  table_ptr= &(thd->handler_tables);
-  *was_flushed= FALSE;
+  TABLE_LIST    **tmp_tables_p;
+  TABLE_LIST    *tmp_tables;
+  TABLE         **table_ptr;
+  bool          was_flushed;
+  DBUG_ENTER("mysql_ha_flush");
+  DBUG_PRINT("enter",("mysql_ha_flush: tables %p mode_flags 0x%02x",
+                      tables, mode_flags));
 
-  for (TABLE *table=*table_ptr; table ; table=*table_ptr)
+  if (tables)
   {
-    if ((!dblen || !memcmp(table->table_cache_key, db, dblen)) &&
-        !my_strcasecmp((is_alias ? table->table_name : table->real_name),
-                       table_name))
+    /* Close all tables in the list. */
+    for (tmp_tables= tables ; tmp_tables; tmp_tables= tmp_tables->next)
     {
-      if (table->version != refresh_version)
+      DBUG_PRINT("info",("mysql_ha_flush: in tables list '%s'.'%s' as '%s'",
+                         tmp_tables->db, tmp_tables->real_name,
+                         tmp_tables->alias));
+      /* Close all currently open handler tables with the same base table. */
+      table_ptr= &(thd->handler_tables);
+      while (*table_ptr)
       {
-        if (!dont_lock)
-          VOID(pthread_mutex_lock(&LOCK_open));
-        if (close_thread_table(thd, table_ptr))
+        if ((! *tmp_tables->db ||
+             ! my_strcasecmp((*table_ptr)->table_cache_key, tmp_tables->db)) &&
+            ! my_strcasecmp((*table_ptr)->real_name, tmp_tables->real_name))
         {
-          /* Tell threads waiting for refresh that something has happened */
-          VOID(pthread_cond_broadcast(&COND_refresh));
+          DBUG_PRINT("info",("mysql_ha_flush: *table_ptr '%s'.'%s' as '%s'",
+                             (*table_ptr)->table_cache_key,
+                             (*table_ptr)->real_name,
+                             (*table_ptr)->table_name));
+          mysql_ha_flush_table(thd, table_ptr, mode_flags);
+          continue;
         }
-        if (!dont_lock)
-          VOID(pthread_mutex_unlock(&LOCK_open));
-        *was_flushed= TRUE;
+        table_ptr= &(*table_ptr)->next;
+      }
+      /* end of handler_tables list */
+    }
+    /* end of flush tables list */
+  }
+  else
+  {
+    /* Close all currently open tables [which are marked for flush]. */
+    table_ptr= &(thd->handler_tables);
+    while (*table_ptr)
+    {
+      if ((mode_flags & MYSQL_HA_FLUSH_ALL) ||
+          ((*table_ptr)->version != refresh_version))
+      {
+        mysql_ha_flush_table(thd, table_ptr, mode_flags);
         continue;
       }
-      *was_flushed= FALSE;
-      break;
+      table_ptr= &(*table_ptr)->next;
+    }
+  }
+
+  DBUG_PRINT("exit",("mysql_ha_flush: OK"));
+  DBUG_RETURN(0);
+}
+
+/*
+  Flush (close) a table.
+
+  SYNOPSIS
+    mysql_ha_flush_table()
+    thd                         Thread identifier.
+    table                       The table to close.
+    mode_flags                  MYSQL_HA_CLOSE_FINAL finally close the table.
+                                MYSQL_HA_REOPEN_ON_USAGE mark for reopen.
+
+  DESCRIPTION
+    Broadcasts a COND_refresh condition, for every table closed.
+    The caller must lock LOCK_open.
+
+  RETURN
+    0  ok
+*/
+
+static int mysql_ha_flush_table(THD *thd, TABLE **table_ptr, int mode_flags)
+{
+  TABLE_LIST    *hash_tables;
+  TABLE         *table= *table_ptr;
+  bool          was_flushed;
+  DBUG_ENTER("mysql_ha_flush_table");
+  DBUG_PRINT("info",("mysql_ha_flush_table: '%s'.'%s' as '%s' flags 0x%02x",
+                     table->table_cache_key, table->real_name,
+                     table->table_name, mode_flags));
+
+  if ((hash_tables= (TABLE_LIST*) hash_search(&thd->handler_tables_hash,
+                                        (*table_ptr)->table_name,
+                                        strlen((*table_ptr)->table_name) + 1)))
+  {
+    if (! (mode_flags & MYSQL_HA_REOPEN_ON_USAGE))
+    {
+      /* This is a final close. Remove from hash. */
+      hash_delete(&thd->handler_tables_hash, (byte*) hash_tables);
     }
-    table_ptr=&(table->next);
+    else
+    {
+      /* Mark table as closed, ready for re-open. */
+      hash_tables->table= NULL;
+    }
+  }    
+
+  if (close_thread_table(thd, table_ptr))
+  {
+    /* Tell threads waiting for refresh that something has happened */
+    VOID(pthread_cond_broadcast(&COND_refresh));
   }
-  return table_ptr;
+
+  DBUG_PRINT("exit",("mysql_ha_flush_table: OK"));
+  DBUG_RETURN(0);
 }
 
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 96eebd98ac3..b218a224977 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -176,7 +176,7 @@ int mysql_rm_table_part2(THD *thd, TABLE_LIST *tables, bool if_exists,
   for (table=tables ; table ; table=table->next)
   {
     char *db=table->db;
-    mysql_ha_close(thd, table, /*dont_send_ok*/ 1, /*dont_lock*/ 1);
+    mysql_ha_flush(thd, table, MYSQL_HA_CLOSE_FINAL);
     if (!close_temporary_table(thd, db, table->real_name))
     {
       tmp_table_deleted=1;
@@ -1242,7 +1242,7 @@ static int mysql_admin_table(THD* thd, TABLE_LIST* tables,
   if (send_fields(thd, field_list, 1))
     DBUG_RETURN(-1);
 
-  mysql_ha_close(thd, tables, /*dont_send_ok*/ 1, /*dont_lock*/ 1);
+  mysql_ha_flush(thd, tables, MYSQL_HA_CLOSE_FINAL);
   for (table = tables; table; table = table->next)
   {
     char table_name[NAME_LEN*2+2];
@@ -1503,7 +1503,7 @@ int mysql_alter_table(THD *thd,char *new_db, char *new_name,
   }
   used_fields=create_info->used_fields;
   
-  mysql_ha_close(thd, table_list, /*dont_send_ok*/ 1, /*dont_lock*/ 1);
+  mysql_ha_flush(thd, table_list, MYSQL_HA_CLOSE_FINAL);
   if (!(table=open_ltable(thd,table_list,TL_WRITE_ALLOW_READ)))
     DBUG_RETURN(-1);