bk commit into 5.0 tree (dlenev:1.2139)

[dlenev]

Below is the list of changes that have just been committed into a local
5.0 repository of dlenev. When dlenev does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2139 06/04/07 16:59:21 dlenev@stripped +5 -0
  Proposed fix for bugs#12472/#15137 'CREATE TABLE ... SELECT ... which
  explicitly or implicitly uses stored function gives "Table not locked"
  error'
  
  CREATE TABLE ... SELECT ... statement which was explicitly or implicitly
  (through view) using stored function gave "Table not locked" error.
  
  When we are executing CREATE TABLE ... SELECT ... statement which
  SELECT part requires execution in prelocked mode we should ignore
  set of locked tables in order to get access to the table we just
  have created.
  
  Better fix should also address problem of possible deadlock
  caused by the fact that CREATE TABLE .. SELECT ... lock tables
  in two stages. But such fix is too intrusive for 5.0.

  sql/sql_table.cc
    1.304 06/04/07 16:59:16 dlenev@stripped +14 -1
    create_table_from_items():
      When we are executing CREATE TABLE ... SELECT ... statement which
      SELECT part requires execution in prelocked mode we should ignore
      set of locked tables in order to get access to the table we just
      have created. We probably want to do this if we are under real LOCK
      TABLES since it will widen window for deadlock too much.

  sql/sql_base.cc
    1.331 06/04/07 16:59:16 dlenev@stripped +4 -1
    open_table():
      Added flag which allows open table ignoring set of locked tables and
      prelocked mode.

  sql/mysql_priv.h
    1.381 06/04/07 16:59:16 dlenev@stripped +2 -1
    Added flag which can be passed to open_table() routine in order to ignore
    set of locked tables and prelocked mode.

  mysql-test/t/sp.test
    1.182 06/04/07 16:59:16 dlenev@stripped +23 -0
    Added tests for bugs#12472/#15137 'CREATE TABLE ... SELECT ... which
    explicitly or implicitly uses stored function gives "Table not locked" error'

  mysql-test/r/sp.result
    1.194 06/04/07 16:59:16 dlenev@stripped +25 -0
    Added tests for bugs#12472/#15137 'CREATE TABLE ... SELECT ... which
    explicitly or implicitly uses stored function gives "Table not locked" error'

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	dlenev
# Host:	jabberwock.site
# Root:	/home/dlenev/mysql-5.0-bg12472

--- 1.380/sql/mysql_priv.h	2006-03-30 17:14:51 +04:00
+++ 1.381/sql/mysql_priv.h	2006-04-07 16:59:16 +04:00
@@ -1312,10 +1312,11 @@
 
 MYSQL_LOCK *mysql_lock_tables(THD *thd, TABLE **table, uint count,
                               uint flags, bool *need_reopen);
-/* mysql_lock_tables() flags bits */
+/* mysql_lock_tables() and open_table() flags bits */
 #define MYSQL_LOCK_IGNORE_GLOBAL_READ_LOCK      0x0001
 #define MYSQL_LOCK_IGNORE_FLUSH                 0x0002
 #define MYSQL_LOCK_NOTIFY_IF_NEED_REOPEN        0x0004
+#define MYSQL_OPEN_IGNORE_LOCKED_TABLES         0x0008
 
 void mysql_unlock_tables(THD *thd, MYSQL_LOCK *sql_lock);
 void mysql_unlock_read_tables(THD *thd, MYSQL_LOCK *sql_lock);

--- 1.330/sql/sql_base.cc	2006-03-06 12:41:16 +03:00
+++ 1.331/sql/sql_base.cc	2006-04-07 16:59:16 +04:00
@@ -1076,6 +1076,8 @@
                           MYSQL_LOCK_IGNORE_FLUSH - Open table even if
                           someone has done a flush or namelock on it.
                           No version number checking is done.
+                          MYSQL_OPEN_IGNORE_LOCKED_TABLES - Open table
+                          ignoring set of locked tables and prelocked mode.
 
   IMPLEMENTATION
     Uses a cache of open tables to find a table not in use.
@@ -1135,7 +1137,8 @@
     }
   }
 
-  if (thd->locked_tables || thd->prelocked_mode)
+  if (!(flags & MYSQL_OPEN_IGNORE_LOCKED_TABLES) &&
+      (thd->locked_tables || thd->prelocked_mode))
   {						// Using table locks
     TABLE *best_table= 0;
     int best_distance= INT_MIN;

--- 1.303/sql/sql_table.cc	2006-03-30 17:14:51 +04:00
+++ 1.304/sql/sql_table.cc	2006-04-07 16:59:16 +04:00
@@ -1819,6 +1819,9 @@
     don't want to delete from it) 2) it would be written before the CREATE
     TABLE, which is a wrong order. So we keep binary logging disabled when we
     open_table().
+    NOTE: By locking table which we just have created (or for which we just have
+    have found that it already exists) separately from other tables used by the
+    statement we create potential window for deadlock.
     TODO: create and open should be done atomic !
   */
   {
@@ -1827,8 +1830,18 @@
                             create_info, *extra_fields, *keys, 0,
                             select_field_count))
     {
+      /*
+        If we are here in prelocked mode we either create temporary table
+        or prelocked mode is caused by the SELECT part of this statement.
+      */
+      DBUG_ASSERT(!thd->prelocked_mode ||
+                  create_info->options & HA_LEX_CREATE_TMP_TABLE ||
+                  thd->lex->requires_prelocking());
+
       if (! (table= open_table(thd, create_table, thd->mem_root, (bool*) 0,
-                               MYSQL_LOCK_IGNORE_FLUSH)))
+                               (MYSQL_LOCK_IGNORE_FLUSH |
+                                ((thd->prelocked_mode == PRELOCKED) ?
+                                 MYSQL_OPEN_IGNORE_LOCKED_TABLES:0)))))
         quick_rm_table(create_info->db_type, create_table->db,
                        table_case_name(create_info, create_table->table_name));
     }

--- 1.193/mysql-test/r/sp.result	2006-03-28 16:18:44 +04:00
+++ 1.194/mysql-test/r/sp.result	2006-04-07 16:59:16 +04:00
@@ -4837,4 +4837,29 @@
 b	3
 a	1
 delete from t1|
+drop function if exists bug12472|
+create function bug12472() returns int return (select count(*) from t1)|
+create table t3 as select bug12472() as i|
+show create table t3|
+Table	Create Table
+t3	CREATE TABLE `t3` (
+  `i` int(11) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select * from t3|
+i
+0
+drop table t3|
+create view v1 as select bug12472() as j|
+create table t3 as select * from v1|
+show create table t3|
+Table	Create Table
+t3	CREATE TABLE `t3` (
+  `j` bigint(11) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select * from t3|
+j
+0
+drop table t3|
+drop view v1|
+drop function bug12472|
 drop table t1,t2;

--- 1.181/mysql-test/t/sp.test	2006-03-28 16:17:15 +04:00
+++ 1.182/mysql-test/t/sp.test	2006-04-07 16:59:16 +04:00
@@ -5684,6 +5684,29 @@
 
 
 #
+# BUG#12472/BUG#15137 'CREATE TABLE ... SELECT ... which explicitly or
+# implicitly uses stored function gives "Table not locked" error'.
+#
+--disable_warnings
+drop function if exists bug12472|
+--enable_warnings
+create function bug12472() returns int return (select count(*) from t1)|
+# Check case when function is used directly
+create table t3 as select bug12472() as i|
+show create table t3|
+select * from t3|
+drop table t3|
+# Check case when function is used indirectly through view
+create view v1 as select bug12472() as j|
+create table t3 as select * from v1|
+show create table t3|
+select * from t3|
+drop table t3|
+drop view v1|
+drop function bug12472|
+
+
+#
 # BUG#NNNN: New bug synopsis
 #
 #--disable_warnings