bk commit into 5.0 tree (bell:1.2089) BUG#14767

[sanja]

Below is the list of changes that have just been committed into a local
5.0 repository of bell. When bell 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.2089 06/03/03 02:07:50 bell@stripped +5 -0
  We should prohibit concurent read of inserting file in SP
  because it can cause problem with Query cache. (BUG#14767)

  sql/sql_yacc.yy
    1.455 06/03/03 02:07:46 bell@stripped +23 -2
    We should prohibit concurent read of inserting file in SP
    because it can cause problem with Query cache.

  sql/sql_parse.cc
    1.529 06/03/03 02:07:46 bell@stripped +11 -0
    Query cache invalidating table we was inserting in just after
    unlocking table added to avoid race condition as we had with SP.

  sql/sql_insert.cc
    1.184 06/03/03 02:07:45 bell@stripped +9 -0
    Query cache invalidating table we was inserting in just after
    unlocking table added to avoid race condition as we had with SP.

  mysql-test/t/query_cache_notembedded.test
    1.4 06/03/03 02:07:45 bell@stripped +40 -0
    The test suite to this bug added.

  mysql-test/r/query_cache_notembedded.result
    1.4 06/03/03 02:07:45 bell@stripped +30 -0
    The test suite to this bug added.

# 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:	bell
# Host:	sanja.is.com.ua
# Root:	/home/bell/mysql/bk/work-qc-5.0

--- 1.183/sql/sql_insert.cc	2006-01-06 18:36:10 +02:00
+++ 1.184/sql/sql_insert.cc	2006-03-03 02:07:45 +02:00
@@ -577,6 +577,15 @@
     if (thd->lock)
     {
       mysql_unlock_tables(thd, thd->lock);
+      /*
+        Invalidate the table in the query cache if something changed
+        after unlocking when changes become fisible.
+      */
+      if (lock_type ==  TL_WRITE_CONCURRENT_INSERT &&
+          (info.copied || info.deleted || info.updated))
+      {
+        query_cache_invalidate3(thd, table_list, 1);
+      }
       thd->lock=0;
     }
   }

--- 1.528/sql/sql_parse.cc	2006-02-25 17:46:26 +02:00
+++ 1.529/sql/sql_parse.cc	2006-03-03 02:07:46 +02:00
@@ -3313,6 +3313,17 @@
         select_lex->context.table_list= 
           select_lex->context.first_name_resolution_table= second_table;
 	res= handle_select(thd, lex, result, OPTION_SETUP_TABLES_DONE);
+        /*
+          Invalidate the table in the query cache if something changed
+          after unlocking when changes become fisible.
+        */
+        if (first_table->lock_type ==  TL_WRITE_CONCURRENT_INSERT &&
+            thd->lock)
+        {
+          mysql_unlock_tables(thd, thd->lock);
+          query_cache_invalidate3(thd, first_table, 1);
+          thd->lock=0;
+        }
         delete result;
       }
       /* revert changes for SP */

--- 1.454/sql/sql_yacc.yy	2006-02-22 00:19:07 +02:00
+++ 1.455/sql/sql_yacc.yy	2006-03-03 02:07:46 +02:00
@@ -6126,7 +6126,19 @@
 	;
 
 insert_lock_option:
-	/* empty */	{ $$= TL_WRITE_CONCURRENT_INSERT; }
+	/* empty */
+          {
+#ifdef HAVE_QUERY_CACHE
+            /*
+              If it is SP we do not allow insert optimisation whan result of
+              insert visible only after the table unlocking but everyone can
+              read table.
+            */
+            $$= (Lex->sphead ? TL_WRITE :TL_WRITE_CONCURRENT_INSERT);
+#else
+            $$= TL_WRITE_CONCURRENT_INSERT;
+#endif
+          }
 	| LOW_PRIORITY	{ $$= TL_WRITE_LOW_PRIORITY; }
 	| DELAYED_SYM	{ $$= TL_WRITE_DELAYED; }
 	| HIGH_PRIORITY { $$= TL_WRITE; }
@@ -6983,7 +6995,16 @@
 
 load_data_lock:
 	/* empty */	{ $$= YYTHD->update_lock_default; }
-	| CONCURRENT	{ $$= TL_WRITE_CONCURRENT_INSERT ; }
+	| CONCURRENT
+          {
+#ifdef HAVE_QUERY_CACHE
+            /*
+              Ignore this option in SP to avoid problem with query cache
+            */
+            if (Lex->sphead != 0)
+#endif
+              $$= TL_WRITE_CONCURRENT_INSERT;
+          }
 	| LOW_PRIORITY	{ $$= TL_WRITE_LOW_PRIORITY; };
 
 

--- 1.3/mysql-test/r/query_cache_notembedded.result	2006-02-24 18:34:08 +02:00
+++ 1.4/mysql-test/r/query_cache_notembedded.result	2006-03-03 02:07:45 +02:00
@@ -314,4 +314,34 @@
 drop procedure f3;
 drop procedure f4;
 drop table t1;
+reset query cache;
+drop function if exists f1;
+create table t1 (id int);
+create function f1 ()
+returns int
+begin
+declare i_var int;
+set i_var = sleep(3);
+insert into t1 values(3);
+set i_var = sleep(3);
+return 0;
+end;|
+ select f1();
+select sleep(4);
+sleep(4)
+0
+select * from t1;
+id
+3
+f1()
+0
+select * from t1;
+id
+3
+reset query cache;
+select * from t1;
+id
+3
+drop table t1;
+drop function f1;
 set GLOBAL query_cache_size=0;

--- 1.3/mysql-test/t/query_cache_notembedded.test	2006-02-24 18:34:08 +02:00
+++ 1.4/mysql-test/t/query_cache_notembedded.test	2006-03-03 02:07:45 +02:00
@@ -180,5 +180,45 @@
 drop procedure f4;
 drop table t1;
 
+#
+# bug#14767: INSERT in SF + concurrent SELECT with query cache
+#
+reset query cache;
+--disable_warnings
+drop function if exists f1;
+--enable_warnings
+create table t1 (id int);
+delimiter |;
+create function f1 ()
+  returns int
+begin
+  declare i_var int;
+  set i_var = sleep(3);
+  insert into t1 values(3);
+  set i_var = sleep(3);
+  return 0;
+end;|
+delimiter ;|
+
+connect (con1,localhost,root,,);
+connect (con2,localhost,root,,);
+
+connection con1;
+send select f1();
+connection con2;
+select sleep(4);
+select * from t1;
+connection con1;
+reap;
+connection con2;
+# This gives wrong result i.e. 't' table seems to be empty
+select * from t1;
+reset query cache;
+select * from t1;
+drop table t1;
+drop function f1;
+disconnect con1;
+disconnect con2;
+connection default;
 
 set GLOBAL query_cache_size=0;