From: guilhem
Date: June 16 2006 1:18pm
Subject: bk commit into 5.1 tree (guilhem:1.2176) BUG#20499
List-Archive: http://lists.mysql.com/commits/7760
X-Bug: 20499
Message-Id: <200606161318.k5GDIDnK002190@gbichot3.local>

Below is the list of changes that have just been committed into a local
5.1 repository of guilhem. When guilhem 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.2176 06/06/16 15:18:06 guilhem@stripped +13 -0
  * Mixed replication mode * :
  1) Fix for BUG#19630 "stored function inserting into two auto_increment breaks
  statement-based binlog":
  a stored function inserting into two such tables may fail to replicate
  (inserting wrong data in the slave's copy of the second table) if the slave's
  second table had an internal auto_increment counter different from master's.
  Because the auto_increment value autogenerated by master for the 2nd table
  does not go into binlog, only the first does, so the slave lack information.
  To fix this, if running in mixed binlogging mode, if the stored function or
  trigger plans to update two different tables both having auto_increment
  columns, we switch to row-based for the whole function.
  We don't have a simple solution for statement-based binlogging mode, there
  the bug remains and will be documented as a known problem.
  Re-enabling rpl_switch_stm_row_mixed.
  2) Proposal to make mixed mode work with stored functions using UUID() and
  UDFs (so far a documented limitation); if the approach looks correct it will
  be pushed otherwise it will be held back, separated from 1) and 3), and
  forgotten about. This patch introduces a behaviour change in mixed mode:
  stored *procedures* are now binlogged either entirely row-based or entirely
  statement-based, we don't automatically switch in the middle and back.
  3) Fix for BUG#20499 "mixed mode with temporary table breaks binlog":
  a temporary table containing e.g. UUID is naturally not binlogged,
  so any query updating a permanent table with data from the temporary table
  will run wrongly on slave. Solution: in mixed mode we shouldn't switch back
  from row-based to statement-based when there exists temporary tables.

  sql/sql_parse.cc
    1.553 06/06/16 15:18:00 guilhem@stripped +5 -8
    For the mixed mode to work with stored functions using UUID and UDF, we need
    to move the switch-back-from-row-to-statement out of
    mysql_execute_command() (which is executed for each statement, causing
    the binlogging mode to change in the middle of the function, which would
    not work): the switch now happens in mysql_reset_thd_for_next_command(),
    which is called only from mysql_parse(), which is called only by top
    statements.
    The switch to row-based is now done in lock_tables(), no need to keep it
    in mysql_execute_command().

  sql/sql_lex.h
    1.232 06/06/16 15:18:00 guilhem@stripped +4 -1
    fix for build without row-based replication

  sql/sql_lex.cc
    1.182 06/06/16 15:18:00 guilhem@stripped +2 -0
    fix for build without row-based replication

  sql/sql_class.h
    1.296 06/06/16 15:18:00 guilhem@stripped +11 -1
    Fix for BUG#20499 "mixed mode with temporary table breaks binlog"

  sql/sql_base.cc
    1.326 06/06/16 15:18:00 guilhem@stripped +64 -0
    For BUG#19630 "stored function inserting into two auto_increment
    breaks statement-based binlog":
    When we come to locking tables, we have collected all tables used by
    functions, views and triggers, we detect if we're going to update two tables
    having auto_increment columns. If yes, statement-based binlogging won't work
    (Intvar_log_event records only one insert_id) so, if in mixed binlogging
    mode, switch to row-based.
    For making mixed mode work with stored functions using UUID/UDF:
    we have parsed the whole body so know if some elements need row-based,
    it's the latest possible (hence, the best) moment to switch to row-based
    if in mixed mode; it cannot happen later as generation of Table_map row-based
    binlog events happens right after, in handler::external_lock().

  sql/sp_head.h
    1.88 06/06/16 15:18:00 guilhem@stripped +3 -0
    new sp_head member (see sp_head.cc)

  sql/sp_head.cc
    1.223 06/06/16 15:18:00 guilhem@stripped +12 -0
    new sp_head::m_binlog_row_based_if_mixed remembers if, when parsing the 
    routine, we found at least one element (UUID(), UDF) requiring row-based
    binlogging.

  sql/sp.cc
    1.111 06/06/16 15:18:00 guilhem@stripped +8 -0
    When a routine adds its tables to the top statement's tables, if this routine
    needs row-based binlogging, mark the entire top statement as well. Needed for
    making the mixed replication mode work with stored functions.

  sql/set_var.cc
    1.177 06/06/16 15:18:00 guilhem@stripped +2 -2
    cosmetic: in_sub_stmt is exactly meant to say if we are in stored function/trigger, so better use it.

  sql/item_create.cc
    1.63 06/06/16 15:17:59 guilhem@stripped +2 -0
    fix for build without row-based replication

  mysql-test/t/rpl_switch_stm_row_mixed.test
    1.5 06/06/16 15:17:59 guilhem@stripped +216 -47
    adding disable_ps_protocol, as running all queries using prep stmts leads to 
    more Table_map_log events thus a different SHOW BINLOG EVENTS. This test does 
    run many queries as prep stmts (see the PREPARE commands in it), so it's ok
    that it does not run all of them this way.
    Test for BUG#19630 "stored function inserting into two auto_increment breaks
    statement-based binlog":
    we test that it goes row-based, but only when needed;
    without the bugfix, master and slave's data differed.
    Test for BUG#20499 "mixed mode with temporary table breaks binlog":
    without the bugfix, slave had 2 rows, not 3.
    Making strings used more different, for easier tracking of "by which routine
    was this binlog line generated".

  mysql-test/t/disabled.def
    1.159 06/06/16 15:17:59 guilhem@stripped +0 -1
    test passed for me in SBR and RBR; referred bug, BUG#18590, was marked
    duplicate of BUG#18492, fixed on May 2nd.

  mysql-test/r/rpl_switch_stm_row_mixed.result
    1.6 06/06/16 15:17:59 guilhem@stripped +383 -61
    testing BUG#19630 "stored function inserting into two auto_increment breaks
    statement-based binlog", testing stored function using UUID(), testing
    BUG#20499 "mixed mode with temporary table breaks binlog".
    I have carefully checked this big result file, the notable facts are:
    - some stored procedures (like foo()) used to have certain sub-stmts
    binlogged stmt-based and others (those invoking UUID) row-based,
    now their entire body is binlogged row-based.
    - we have a few more occurences of patterns like this in the binlog:
    master-bin.000001     487     Table_map       1       532     table_id: 17 (mysqltest1.t1)
    master-bin.000001     532     Write_rows      1       561     table_id: 281474976710655 flags: STMT_END_F
    (except that you don't see the id in the result file because it's replaced
    by #).
    This very big id is the sign that a table was write locked and then was not
    written; I know RBR generates such events in this case. What causes those
    in this test are PREPARE commands. They appear after my patch, because in the
    old code, we switch to row-based only at execution time, while in my patch
    we switch to row-based independently of if this is execution or preparation
    (as we switch in lock_tables() instead of mysql_execute_command()). This
    is innocuous.
    - The temporary table "t1" in the end has its CREATE in binlog; this is not
    needed when row-based is used, though innocuous; I have told this to Mats.

# 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:	guilhem
# Host:	gbichot3.local
# Root:	/home/mysql_src/mysql-5.1-new-19630

--- 1.62/sql/item_create.cc	2006-04-23 02:00:03 +02:00
+++ 1.63/sql/item_create.cc	2006-06-16 15:17:59 +02:00
@@ -426,7 +426,9 @@
 Item *create_func_uuid(void)
 {
   THD *thd= current_thd;
+#ifdef HAVE_ROW_BASED_REPLICATION
   thd->lex->binlog_row_based_if_mixed= 1;
+#endif
   return new(thd->mem_root) Item_func_uuid();
 }
 

--- 1.325/sql/sql_base.cc	2006-05-19 16:39:28 +02:00
+++ 1.326/sql/sql_base.cc	2006-06-16 15:18:00 +02:00
@@ -49,6 +49,8 @@
 static void close_old_data_files(THD *thd, TABLE *table, bool abort_locks,
                                  bool send_refresh);
 static bool reopen_table(TABLE *table);
+static bool
+has_two_write_locked_tables_with_auto_increment(TABLE_LIST *tables);
 
 
 extern "C" byte *table_cache_key(const byte *record,uint *length,
@@ -3293,6 +3295,12 @@
 
   *need_reopen= FALSE;
 
+#ifdef HAVE_ROW_BASED_REPLICATION
+  /* CREATE ... SELECT UUID() locks no tables, we have to test here */
+  if (thd->lex->binlog_row_based_if_mixed)
+    thd->set_current_stmt_binlog_row_based_if_mixed();
+#endif /*HAVE_ROW_BASED_REPLICATION*/
+
   if (!tables)
     DBUG_RETURN(0);
 
@@ -3323,6 +3331,19 @@
     {
       thd->in_lock_tables=1;
       thd->options|= OPTION_TABLE_LOCK;
+#ifdef HAVE_ROW_BASED_REPLICATION
+      /*
+        If we have >= 2 different tables to update with auto_inc columns,
+        statement-based binlogging won't work. We can solve this problem in
+        mixed mode by switching to row-based binlogging:
+      */
+      if (thd->variables.binlog_format == BINLOG_FORMAT_MIXED &&
+          has_two_write_locked_tables_with_auto_increment(tables))
+      {
+        thd->lex->binlog_row_based_if_mixed= 1;
+        thd->set_current_stmt_binlog_row_based_if_mixed();
+      }
+#endif
     }
 
     if (! (thd->lock= mysql_lock_tables(thd, start, (uint) (ptr - start),
@@ -6365,3 +6386,46 @@
   DBUG_VOID_RETURN;
 }
 
+
+/*
+  Tells if two (or more) tables have auto_increment columns and we want to
+  lock those tables with a write lock.
+
+  SYNOPSIS
+    has_two_write_locked_tables_with_auto_increment
+      tables        Table list
+
+  NOTES:
+    Call this function only when you have established the list of all tables
+    which you'll want to update (including stored functions, triggers, views
+    inside your statement).
+
+  RETURN
+    0  No
+    1  Yes
+*/
+
+static bool
+has_two_write_locked_tables_with_auto_increment(TABLE_LIST *tables)
+{
+  char *first_table_name= NULL, *first_db;
+  for (TABLE_LIST *table= tables; table; table= table->next_global)
+  {
+    /* we must do preliminary checks as table->table may be NULL */
+    if (!table->placeholder() && !table->schema_table &&
+        table->table->found_next_number_field &&
+        (table->lock_type >= TL_WRITE_ALLOW_WRITE))
+    {
+      if (first_table_name == NULL)
+      {
+        first_table_name= table->table_name;
+        first_db= table->db;
+        DBUG_ASSERT(first_db);
+      }
+      else if (strcmp(first_db, table->db) ||
+               strcmp(first_table_name, table->table_name))
+        return 1;
+    }
+  }
+  return 0;
+}

--- 1.295/sql/sql_class.h	2006-05-23 20:26:11 +02:00
+++ 1.296/sql/sql_class.h	2006-06-16 15:18:00 +02:00
@@ -1408,7 +1408,17 @@
   }
   inline void reset_current_stmt_binlog_row_based()
   {
-    current_stmt_binlog_row_based= test(variables.binlog_format == BINLOG_FORMAT_ROW);
+    /*
+      If there are temporary tables, don't reset back to
+      statement-based. Indeed it could be that:
+      CREATE TEMPORARY TABLE t SELECT UUID(); # row-based
+      INSERT INTO u SELECT * FROM t; # stmt-based
+      and then the INSERT will fail as t's CREATE was not logged.
+      Then we continue with row-based.
+    */
+    if (!temporary_tables)
+      current_stmt_binlog_row_based=
+        test(variables.binlog_format == BINLOG_FORMAT_ROW);
   }
 #endif /*HAVE_ROW_BASED_REPLICATION*/
 };

--- 1.181/sql/sql_lex.cc	2006-05-24 14:33:37 +02:00
+++ 1.182/sql/sql_lex.cc	2006-06-16 15:18:00 +02:00
@@ -189,7 +189,9 @@
   lex->nest_level=0 ;
   lex->allow_sum_func= 0;
   lex->in_sum_func= NULL;
+#ifdef HAVE_ROW_BASED_REPLICATION
   lex->binlog_row_based_if_mixed= 0;
+#endif
   DBUG_VOID_RETURN;
 }
 

--- 1.231/sql/sql_lex.h	2006-05-23 20:25:02 +02:00
+++ 1.232/sql/sql_lex.h	2006-06-16 15:18:00 +02:00
@@ -892,11 +892,14 @@
   uint8 create_view_check;
   bool drop_if_exists, drop_temporary, local_file, one_shot_set;
   bool in_comment, ignore_space, verbose, no_write_to_binlog;
+  bool tx_chain, tx_release;
   /*
     binlog_row_based_if_mixed tells if the parsing stage detected that some
     items require row-based binlogging to give a reliable binlog/replication.
   */
-  bool tx_chain, tx_release, binlog_row_based_if_mixed;
+#ifdef HAVE_ROW_BASED_REPLICATION
+  bool binlog_row_based_if_mixed;
+#endif
   /*
     Special JOIN::prepare mode: changing of query is prohibited.
     When creating a view, we need to just check its syntax omitting

--- 1.552/sql/sql_parse.cc	2006-05-29 15:16:01 +02:00
+++ 1.553/sql/sql_parse.cc	2006-06-16 15:18:00 +02:00
@@ -2468,11 +2468,6 @@
     statistic_increment(thd->status_var.com_stat[lex->sql_command],
                         &LOCK_status);
 
-#ifdef HAVE_ROW_BASED_REPLICATION
-  if (lex->binlog_row_based_if_mixed)
-    thd->set_current_stmt_binlog_row_based_if_mixed();
-#endif /*HAVE_ROW_BASED_REPLICATION*/
-
   switch (lex->sql_command) {
   case SQLCOM_SELECT:
   {
@@ -5148,9 +5143,6 @@
   */
   if (thd->one_shot_set && lex->sql_command != SQLCOM_SET_OPTION)
     reset_one_shot_variables(thd);
-#ifdef HAVE_ROW_BASED_REPLICATION
-  thd->reset_current_stmt_binlog_row_based();
-#endif /*HAVE_ROW_BASED_REPLICATION*/
 
   /*
     The return value for ROW_COUNT() is "implementation dependent" if the
@@ -5700,6 +5692,11 @@
     thd->rand_used= 0;
     thd->sent_row_count= thd->examined_row_count= 0;
   }
+#ifdef HAVE_ROW_BASED_REPLICATION
+  /* If in a routine, we reset only at end of top statement. */
+  thd->reset_current_stmt_binlog_row_based();
+#endif /*HAVE_ROW_BASED_REPLICATION*/
+
   DBUG_VOID_RETURN;
 }
 

--- 1.5/mysql-test/r/rpl_switch_stm_row_mixed.result	2006-03-13 15:34:15 +01:00
+++ 1.6/mysql-test/r/rpl_switch_stm_row_mixed.result	2006-06-16 15:17:59 +02:00
@@ -18,18 +18,18 @@
 ROW	ROW
 CREATE TABLE t1 (a varchar(100));
 prepare stmt1 from 'insert into t1 select concat(UUID(),?)';
-set @string="emergency";
-insert into t1 values("work");
+set @string="emergency_1_";
+insert into t1 values("work_2_");
 execute stmt1 using @string;
 deallocate prepare stmt1;
 prepare stmt1 from 'insert into t1 select ?';
-insert into t1 values(concat(UUID(),"work"));
+insert into t1 values(concat(UUID(),"work_3_"));
 execute stmt1 using @string;
 deallocate prepare stmt1;
-insert into t1 values(concat("for",UUID()));
-insert into t1 select "yesterday";
-create temporary table tmp(a char(3));
-insert into tmp values("see");
+insert into t1 values(concat("for_4_",UUID()));
+insert into t1 select "yesterday_5_";
+create temporary table tmp(a char(100));
+insert into tmp values("see_6_");
 set binlog_format=statement;
 ERROR HY000: Cannot switch out of the row-based binary log format when the session has open temporary tables
 insert into t1 select * from tmp;
@@ -55,16 +55,16 @@
 @@global.binlog_format	@@session.binlog_format
 STATEMENT	STATEMENT
 prepare stmt1 from 'insert into t1 select ?';
-set @string="emergency";
-insert into t1 values("work");
+set @string="emergency_7_";
+insert into t1 values("work_8_");
 execute stmt1 using @string;
 deallocate prepare stmt1;
 prepare stmt1 from 'insert into t1 select ?';
-insert into t1 values("work");
+insert into t1 values("work_9_");
 execute stmt1 using @string;
 deallocate prepare stmt1;
-insert into t1 values("for");
-insert into t1 select "yesterday";
+insert into t1 values("for_10_");
+insert into t1 select "yesterday_11_";
 set binlog_format=default;
 select @@global.binlog_format, @@session.binlog_format;
 @@global.binlog_format	@@session.binlog_format
@@ -75,16 +75,16 @@
 @@global.binlog_format	@@session.binlog_format
 STATEMENT	STATEMENT
 prepare stmt1 from 'insert into t1 select ?';
-set @string="emergency";
-insert into t1 values("work");
+set @string="emergency_12_";
+insert into t1 values("work_13_");
 execute stmt1 using @string;
 deallocate prepare stmt1;
 prepare stmt1 from 'insert into t1 select ?';
-insert into t1 values("work");
+insert into t1 values("work_14_");
 execute stmt1 using @string;
 deallocate prepare stmt1;
-insert into t1 values("for");
-insert into t1 select "yesterday";
+insert into t1 values("for_15_");
+insert into t1 select "yesterday_16_";
 set binlog_format=mixed;
 select @@global.binlog_format, @@session.binlog_format;
 @@global.binlog_format	@@session.binlog_format
@@ -94,22 +94,22 @@
 @@global.binlog_format	@@session.binlog_format
 MIXED	MIXED
 prepare stmt1 from 'insert into t1 select concat(UUID(),?)';
-set @string="emergency";
-insert into t1 values("work");
+set @string="emergency_17_";
+insert into t1 values("work_18_");
 execute stmt1 using @string;
 deallocate prepare stmt1;
 prepare stmt1 from 'insert into t1 select ?';
-insert into t1 values(concat(UUID(),"work"));
+insert into t1 values(concat(UUID(),"work_19_"));
 execute stmt1 using @string;
 deallocate prepare stmt1;
-insert into t1 values(concat("for",UUID()));
-insert into t1 select "yesterday";
+insert into t1 values(concat("for_20_",UUID()));
+insert into t1 select "yesterday_21_";
 prepare stmt1 from 'insert into t1 select ?';
-insert into t1 values(concat(UUID(),"work"));
+insert into t1 values(concat(UUID(),"work_22_"));
 execute stmt1 using @string;
 deallocate prepare stmt1;
-insert into t1 values(concat("for",UUID()));
-insert into t1 select "yesterday";
+insert into t1 values(concat("for_23_",UUID()));
+insert into t1 select "yesterday_24_";
 create table t2 select UUID();
 create table t3 select 1 union select UUID();
 create table t4 select * from t1 where 3 in (select 1 union select 2 union select UUID() union select 3);
@@ -117,17 +117,17 @@
 insert into t5 select UUID() from t1 where 3 in (select 1 union select 2 union select 3 union select * from t4);
 create procedure foo()
 begin
-insert into t1 values("work");
-insert into t1 values(concat("for",UUID()));
-insert into t1 select "yesterday";
+insert into t1 values("work_25_");
+insert into t1 values(concat("for_26_",UUID()));
+insert into t1 select "yesterday_27_";
 end|
 create procedure foo2()
 begin
-insert into t1 values(concat("emergency",UUID()));
-insert into t1 values("work");
-insert into t1 values(concat("for",UUID()));
+insert into t1 values(concat("emergency_28_",UUID()));
+insert into t1 values("work_29_");
+insert into t1 values(concat("for_30_",UUID()));
 set session binlog_format=row; # accepted for stored procs
-insert into t1 values("more work");
+insert into t1 values("more work_31_");
 set session binlog_format=mixed;
 end|
 create function foo3() returns bigint unsigned
@@ -142,12 +142,24 @@
 ERROR HY000: Cannot change the binary logging format inside a stored function or trigger
 select * from t1 where a="alarm";
 a
+drop function foo3;
+create function foo3() returns bigint unsigned
+begin
+insert into t1 values("foo3_32_");
+call foo();
+return 100;
+end|
+insert into t2 select foo3();
+prepare stmt1 from 'insert into t2 select foo3()';
+execute stmt1;
+execute stmt1;
+deallocate prepare stmt1;
 select count(*) from t1;
 count(*)
-36
+48
 select count(*) from t2;
 count(*)
-1
+4
 select count(*) from t3;
 count(*)
 2
@@ -157,6 +169,200 @@
 select count(*) from t5;
 count(*)
 58
+set binlog_format=mixed;
+drop table t1,t2,t3;
+create table t1 (a int primary key auto_increment, b varchar(100));
+create table t2 (a int primary key auto_increment, b varchar(100));
+create table t3 (b varchar(100));
+create function f (x varchar(100)) returns int deterministic
+begin
+insert into t1 values(null,x);
+insert into t2 values(null,x);
+return 1;
+end|
+select f("try_41_");
+f("try_41_")
+1
+select * from t1;
+a	b
+1	try_41_
+select * from t2;
+a	b
+1	try_41_
+use mysqltest1;
+insert into t2 values(2,null),(3,null),(4,null);
+delete from t2 where a>=2;
+select * from t1;
+a	b
+1	try_41_
+select * from t2;
+a	b
+1	try_41_
+select f("try_42_");
+f("try_42_")
+1
+select * from t1;
+a	b
+1	try_41_
+2	try_42_
+select * from t2;
+a	b
+1	try_41_
+2	try_42_
+select * from t1;
+a	b
+1	try_41_
+2	try_42_
+select * from t2;
+a	b
+1	try_41_
+2	try_42_
+insert into t2 values(3,null),(4,null);
+delete from t2 where a>=3;
+prepare stmt1 from 'select f(?)';
+set @string="try_43_";
+insert into t1 values(null,"try_44_");
+execute stmt1 using @string;
+f(?)
+1
+deallocate prepare stmt1;
+select * from t1;
+a	b
+1	try_41_
+2	try_42_
+3	try_44_
+4	try_43_
+select * from t2;
+a	b
+1	try_41_
+2	try_42_
+3	try_43_
+select * from t1;
+a	b
+1	try_41_
+2	try_42_
+3	try_44_
+4	try_43_
+select * from t2;
+a	b
+1	try_41_
+3	try_43_
+2	try_42_
+drop table t1;
+create table t1 (a int, b varchar(100), key(a));
+select f("try_45_");
+f("try_45_")
+1
+drop table t1;
+create table t1 (a int primary key auto_increment, b varchar(100));
+drop function f;
+truncate table t1;
+truncate table t2;
+create function f1 (x varchar(100)) returns int deterministic
+begin
+insert into t1 values(null,x);
+return 1;
+end|
+create function f2 (x varchar(100)) returns int deterministic
+begin
+insert into t2 values(null,x);
+return 1;
+end|
+select f1("try_46_"),f2("try_47_");
+f1("try_46_")	f2("try_47_")
+1	1
+select * from t1;
+a	b
+1	try_46_
+select * from t2;
+a	b
+1	try_47_
+insert into t2 values(2,null),(3,null),(4,null);
+delete from t2 where a>=2;
+select * from t1;
+a	b
+1	try_46_
+select * from t2;
+a	b
+1	try_47_
+select f1("try_48_"),f2("try_49_");
+f1("try_48_")	f2("try_49_")
+1	1
+insert into t3 values(concat("try_50_",f1("try_51_"),f2("try_52_")));
+select * from t1;
+a	b
+1	try_46_
+2	try_48_
+3	try_51_
+select * from t2;
+a	b
+1	try_47_
+2	try_49_
+3	try_52_
+select * from t3;
+b
+try_50_11
+select * from t1;
+a	b
+1	try_46_
+2	try_48_
+3	try_51_
+select * from t2;
+a	b
+1	try_47_
+3	try_52_
+2	try_49_
+select * from t3;
+b
+try_50_11
+drop function f2;
+create function f2 (x varchar(100)) returns int deterministic
+begin
+declare y int;
+insert into t1 values(null,x);
+set y = (select count(*) from t2);
+return y;
+end|
+select f1("try_53_"),f2("try_54_");
+f1("try_53_")	f2("try_54_")
+1	3
+select * from t1;
+a	b
+1	try_46_
+2	try_48_
+3	try_51_
+4	try_53_
+5	try_54_
+select * from t2;
+a	b
+1	try_47_
+2	try_49_
+3	try_52_
+select * from t1;
+a	b
+1	try_46_
+2	try_48_
+3	try_51_
+4	try_53_
+5	try_54_
+select * from t2;
+a	b
+1	try_47_
+3	try_52_
+2	try_49_
+drop table t1,t2;
+CREATE TEMPORARY TABLE t1 SELECT UUID();
+create table t2 like t1;
+INSERT INTO t2 SELECT * FROM t1;
+insert into t2 values("try_55_");
+drop table t1;
+insert into t2 values("try_56_");
+select count(*) from mysqltest1.t2;
+count(*)
+3
+select count(*) from mysqltest1.t2;
+count(*)
+3
 show binlog events from 102;
 Log_name	Pos	Event_type	Server_id	End_log_pos	Info
 master-bin.000001	#	Query	1	#	drop database if exists mysqltest1
@@ -182,39 +388,41 @@
 master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
 master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
 master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
-master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values("work")
-master-bin.000001	#	User var	1	#	@`string`=_latin1 0x656D657267656E6379 COLLATE latin1_swedish_ci
+master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values("work_8_")
+master-bin.000001	#	User var	1	#	@`string`=_latin1 0x656D657267656E63795F375F COLLATE latin1_swedish_ci
 master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 select @'string'
-master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values("work")
-master-bin.000001	#	User var	1	#	@`string`=_latin1 0x656D657267656E6379 COLLATE latin1_swedish_ci
+master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values("work_9_")
+master-bin.000001	#	User var	1	#	@`string`=_latin1 0x656D657267656E63795F375F COLLATE latin1_swedish_ci
 master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 select @'string'
-master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values("for")
-master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 select "yesterday"
-master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values("work")
-master-bin.000001	#	User var	1	#	@`string`=_latin1 0x656D657267656E6379 COLLATE latin1_swedish_ci
+master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values("for_10_")
+master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 select "yesterday_11_"
+master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values("work_13_")
+master-bin.000001	#	User var	1	#	@`string`=_latin1 0x656D657267656E63795F31325F COLLATE latin1_swedish_ci
 master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 select @'string'
-master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values("work")
-master-bin.000001	#	User var	1	#	@`string`=_latin1 0x656D657267656E6379 COLLATE latin1_swedish_ci
+master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values("work_14_")
+master-bin.000001	#	User var	1	#	@`string`=_latin1 0x656D657267656E63795F31325F COLLATE latin1_swedish_ci
 master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 select @'string'
-master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values("for")
-master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 select "yesterday"
-master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values("work")
+master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values("for_15_")
+master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 select "yesterday_16_"
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
+master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values("work_18_")
 master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
 master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
 master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
 master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
-master-bin.000001	#	User var	1	#	@`string`=_latin1 0x656D657267656E6379 COLLATE latin1_swedish_ci
+master-bin.000001	#	User var	1	#	@`string`=_latin1 0x656D657267656E63795F31375F COLLATE latin1_swedish_ci
 master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 select @'string'
 master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
 master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
-master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 select "yesterday"
+master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 select "yesterday_21_"
 master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
 master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
-master-bin.000001	#	User var	1	#	@`string`=_latin1 0x656D657267656E6379 COLLATE latin1_swedish_ci
+master-bin.000001	#	User var	1	#	@`string`=_latin1 0x656D657267656E63795F31375F COLLATE latin1_swedish_ci
 master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 select @'string'
 master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
 master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
-master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 select "yesterday"
+master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 select "yesterday_24_"
 master-bin.000001	#	Query	1	#	use `mysqltest1`; CREATE TABLE `t2` (
   `UUID()` varchar(36) CHARACTER SET utf8 NOT NULL DEFAULT ''
 )
@@ -239,17 +447,17 @@
 master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
 master-bin.000001	#	Query	1	#	use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo()
 begin
-insert into t1 values("work");
-insert into t1 values(concat("for",UUID()));
-insert into t1 select "yesterday";
+insert into t1 values("work_25_");
+insert into t1 values(concat("for_26_",UUID()));
+insert into t1 select "yesterday_27_";
 end
 master-bin.000001	#	Query	1	#	use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo2()
 begin
-insert into t1 values(concat("emergency",UUID()));
-insert into t1 values("work");
-insert into t1 values(concat("for",UUID()));
+insert into t1 values(concat("emergency_28_",UUID()));
+insert into t1 values("work_29_");
+insert into t1 values(concat("for_30_",UUID()));
 set session binlog_format=row; # accepted for stored procs
-insert into t1 values("more work");
+insert into t1 values("more work_31_");
 set session binlog_format=mixed;
 end
 master-bin.000001	#	Query	1	#	use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function foo3() returns bigint unsigned
@@ -258,15 +466,129 @@
 insert into t1 values("alarm");
 return 100;
 end
-master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values("work")
 master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
 master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
-master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 select "yesterday"
 master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
 master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
-master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values("work")
 master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
 master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
 master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
 master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
+master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
+master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
+master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	1	#	use `mysqltest1`; drop function foo3
+master-bin.000001	#	Query	1	#	use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function foo3() returns bigint unsigned
+begin
+insert into t1 values("foo3_32_");
+call foo();
+return 100;
+end
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t2)
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
+master-bin.000001	#	Write_rows	1	#	table_id: #
+master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t2)
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
+master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t2)
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
+master-bin.000001	#	Write_rows	1	#	table_id: #
+master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t2)
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
+master-bin.000001	#	Write_rows	1	#	table_id: #
+master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	1	#	use `mysqltest1`; drop table t1,t2,t3
+master-bin.000001	#	Query	1	#	use `mysqltest1`; create table t1 (a int primary key auto_increment, b varchar(100))
+master-bin.000001	#	Query	1	#	use `mysqltest1`; create table t2 (a int primary key auto_increment, b varchar(100))
+master-bin.000001	#	Query	1	#	use `mysqltest1`; create table t3 (b varchar(100))
+master-bin.000001	#	Query	1	#	use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function f (x varchar(100)) returns int deterministic
+begin
+insert into t1 values(null,x);
+insert into t2 values(null,x);
+return 1;
+end
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t2)
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
+master-bin.000001	#	Write_rows	1	#	table_id: #
+master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t2)
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
+master-bin.000001	#	Write_rows	1	#	table_id: #
+master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t2)
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
+master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Intvar	1	#	INSERT_ID=3
+master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values(null,"try_44_")
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t2)
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
+master-bin.000001	#	Write_rows	1	#	table_id: #
+master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	1	#	use `mysqltest1`; drop table t1
+master-bin.000001	#	Query	1	#	use `mysqltest1`; create table t1 (a int, b varchar(100), key(a))
+master-bin.000001	#	Intvar	1	#	INSERT_ID=4
+master-bin.000001	#	Query	1	#	use `mysqltest1`; SELECT `f`(_latin1'try_45_')
+master-bin.000001	#	Query	1	#	use `mysqltest1`; drop table t1
+master-bin.000001	#	Query	1	#	use `mysqltest1`; create table t1 (a int primary key auto_increment, b varchar(100))
+master-bin.000001	#	Query	1	#	use `mysqltest1`; drop function f
+master-bin.000001	#	Query	1	#	use `mysqltest1`; truncate table t1
+master-bin.000001	#	Query	1	#	use `mysqltest1`; truncate table t2
+master-bin.000001	#	Query	1	#	use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function f1 (x varchar(100)) returns int deterministic
+begin
+insert into t1 values(null,x);
+return 1;
+end
+master-bin.000001	#	Query	1	#	use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function f2 (x varchar(100)) returns int deterministic
+begin
+insert into t2 values(null,x);
+return 1;
+end
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t2)
+master-bin.000001	#	Write_rows	1	#	table_id: #
+master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t2)
+master-bin.000001	#	Write_rows	1	#	table_id: #
+master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t3)
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t1)
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t2)
+master-bin.000001	#	Write_rows	1	#	table_id: #
+master-bin.000001	#	Write_rows	1	#	table_id: #
+master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	1	#	use `mysqltest1`; drop function f2
+master-bin.000001	#	Query	1	#	use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function f2 (x varchar(100)) returns int deterministic
+begin
+declare y int;
+insert into t1 values(null,x);
+set y = (select count(*) from t2);
+return y;
+end
+master-bin.000001	#	Intvar	1	#	INSERT_ID=4
+master-bin.000001	#	Query	1	#	use `mysqltest1`; SELECT `f1`(_latin1'try_53_')
+master-bin.000001	#	Intvar	1	#	INSERT_ID=5
+master-bin.000001	#	Query	1	#	use `mysqltest1`; SELECT `f2`(_latin1'try_54_')
+master-bin.000001	#	Query	1	#	use `mysqltest1`; drop table t1,t2
+master-bin.000001	#	Query	1	#	use `mysqltest1`; CREATE TEMPORARY TABLE `t1` (
+  `UUID()` varchar(36) CHARACTER SET utf8 NOT NULL DEFAULT ''
+)
+master-bin.000001	#	Query	1	#	use `mysqltest1`; COMMIT
+master-bin.000001	#	Query	1	#	use `mysqltest1`; CREATE TABLE `t2` (
+  `UUID()` varchar(36) CHARACTER SET utf8 NOT NULL DEFAULT ''
+)
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t2)
+master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Table_map	1	#	table_id: # (mysqltest1.t2)
+master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t2 values("try_56_")
 drop database mysqltest1;

--- 1.4/mysql-test/t/rpl_switch_stm_row_mixed.test	2006-03-13 15:34:15 +01:00
+++ 1.5/mysql-test/t/rpl_switch_stm_row_mixed.test	2006-06-16 15:17:59 +02:00
@@ -1,6 +1,7 @@
 -- source include/have_binlog_format_row.inc
 -- source include/master-slave.inc
 
+--disable_ps_protocol
 connection master;
 --disable_warnings
 drop database if exists mysqltest1;
@@ -15,22 +16,22 @@
 CREATE TABLE t1 (a varchar(100));
 
 prepare stmt1 from 'insert into t1 select concat(UUID(),?)';
-set @string="emergency";
-insert into t1 values("work");
+set @string="emergency_1_";
+insert into t1 values("work_2_");
 execute stmt1 using @string;
 deallocate prepare stmt1;
 
 prepare stmt1 from 'insert into t1 select ?';
-insert into t1 values(concat(UUID(),"work"));
+insert into t1 values(concat(UUID(),"work_3_"));
 execute stmt1 using @string;
 deallocate prepare stmt1;
 
-insert into t1 values(concat("for",UUID()));
-insert into t1 select "yesterday";
+insert into t1 values(concat("for_4_",UUID()));
+insert into t1 select "yesterday_5_";
 
 # verify that temp tables prevent a switch to SBR
-create temporary table tmp(a char(3));
-insert into tmp values("see");
+create temporary table tmp(a char(100));
+insert into tmp values("see_6_");
 --error ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR
 set binlog_format=statement;
 insert into t1 select * from tmp;
@@ -47,18 +48,18 @@
 select @@global.binlog_format, @@session.binlog_format;
 
 prepare stmt1 from 'insert into t1 select ?';
-set @string="emergency";
-insert into t1 values("work");
+set @string="emergency_7_";
+insert into t1 values("work_8_");
 execute stmt1 using @string;
 deallocate prepare stmt1;
 
 prepare stmt1 from 'insert into t1 select ?';
-insert into t1 values("work");
+insert into t1 values("work_9_");
 execute stmt1 using @string;
 deallocate prepare stmt1;
 
-insert into t1 values("for");
-insert into t1 select "yesterday";
+insert into t1 values("for_10_");
+insert into t1 select "yesterday_11_";
 
 # test SET DEFAULT (=statement at this point of test)
 set binlog_format=default;
@@ -69,18 +70,18 @@
 select @@global.binlog_format, @@session.binlog_format;
 
 prepare stmt1 from 'insert into t1 select ?';
-set @string="emergency";
-insert into t1 values("work");
+set @string="emergency_12_";
+insert into t1 values("work_13_");
 execute stmt1 using @string;
 deallocate prepare stmt1;
 
 prepare stmt1 from 'insert into t1 select ?';
-insert into t1 values("work");
+insert into t1 values("work_14_");
 execute stmt1 using @string;
 deallocate prepare stmt1;
 
-insert into t1 values("for");
-insert into t1 select "yesterday";
+insert into t1 values("for_15_");
+insert into t1 select "yesterday_16_";
 
 # and now the mixed mode
 
@@ -90,26 +91,26 @@
 select @@global.binlog_format, @@session.binlog_format;
 
 prepare stmt1 from 'insert into t1 select concat(UUID(),?)';
-set @string="emergency";
-insert into t1 values("work");
+set @string="emergency_17_";
+insert into t1 values("work_18_");
 execute stmt1 using @string;
 deallocate prepare stmt1;
 
 prepare stmt1 from 'insert into t1 select ?';
-insert into t1 values(concat(UUID(),"work"));
+insert into t1 values(concat(UUID(),"work_19_"));
 execute stmt1 using @string;
 deallocate prepare stmt1;
 
-insert into t1 values(concat("for",UUID()));
-insert into t1 select "yesterday";
+insert into t1 values(concat("for_20_",UUID()));
+insert into t1 select "yesterday_21_";
 
 prepare stmt1 from 'insert into t1 select ?';
-insert into t1 values(concat(UUID(),"work"));
+insert into t1 values(concat(UUID(),"work_22_"));
 execute stmt1 using @string;
 deallocate prepare stmt1;
 
-insert into t1 values(concat("for",UUID()));
-insert into t1 select "yesterday";
+insert into t1 values(concat("for_23_",UUID()));
+insert into t1 select "yesterday_24_";
 
 # Test of CREATE TABLE SELECT
 
@@ -126,17 +127,17 @@
 delimiter |;
 create procedure foo()
 begin
-insert into t1 values("work");
-insert into t1 values(concat("for",UUID()));
-insert into t1 select "yesterday";
+insert into t1 values("work_25_");
+insert into t1 values(concat("for_26_",UUID()));
+insert into t1 select "yesterday_27_";
 end|
 create procedure foo2()
 begin
-insert into t1 values(concat("emergency",UUID()));
-insert into t1 values("work");
-insert into t1 values(concat("for",UUID()));
+insert into t1 values(concat("emergency_28_",UUID()));
+insert into t1 values("work_29_");
+insert into t1 values(concat("for_30_",UUID()));
 set session binlog_format=row; # accepted for stored procs
-insert into t1 values("more work");
+insert into t1 values("more work_31_");
 set session binlog_format=mixed;
 end|
 create function foo3() returns bigint unsigned
@@ -154,6 +155,23 @@
 select foo3();
 select * from t1 where a="alarm";
 
+# Now test function which calls procedure
+drop function foo3;
+delimiter |;
+create function foo3() returns bigint unsigned
+begin
+  insert into t1 values("foo3_32_");
+  call foo();
+  return 100;
+end|
+delimiter ;|
+insert into t2 select foo3();
+
+prepare stmt1 from 'insert into t2 select foo3()';
+execute stmt1;
+execute stmt1;
+deallocate prepare stmt1;
+
 # If you want to do manual testing of the mixed mode regarding UDFs (not
 # testable automatically as quite platform- and compiler-dependent),
 # you just need to set the variable below to 1, and to
@@ -164,19 +182,19 @@
 {
   CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.so';
   prepare stmt1 from 'insert into t1 select metaphon(?)';
-  set @string="emergency";
-  insert into t1 values("work");
+  set @string="emergency_133_";
+  insert into t1 values("work_134_");
   execute stmt1 using @string;
   deallocate prepare stmt1;
   prepare stmt1 from 'insert into t1 select ?';
-  insert into t1 values(metaphon("work"));
+  insert into t1 values(metaphon("work_135_"));
   execute stmt1 using @string;
   deallocate prepare stmt1;
-  insert into t1 values(metaphon("for"));
-  insert into t1 select "yesterday";
-  create table t6 select metaphon("for");
-  create table t7 select 1 union select metaphon("for");
-  create table t8 select * from t1 where 3 in (select 1 union select 2 union select metaphon("for") union select 3);
+  insert into t1 values(metaphon("for_136_"));
+  insert into t1 select "yesterday_137_";
+  create table t6 select metaphon("for_138_");
+  create table t7 select 1 union select metaphon("for_139_");
+  create table t8 select * from t1 where 3 in (select 1 union select 2 union select metaphon("for_140_") union select 3);
   create table t9 select * from t1 where 3 in (select 1 union select 2 union select curdate() union select 3);
 }
 
@@ -196,21 +214,172 @@
   select count(*) from t9;
 }
 
---replace_column 2 # 5 #
---replace_regex /table_id: [0-9]+/table_id: #/
-show binlog events from 102;
 sync_slave_with_master;
 # as we're using UUID we don't SELECT but use "diff" like in rpl_row_UUID
 --exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info mysqltest1 > $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql
 --exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info mysqltest1 > $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_slave.sql
 
-connection master;
-drop database mysqltest1;
-sync_slave_with_master;
-
 # Let's compare. Note: If they match test will pass, if they do not match
 # the test will show that the diff statement failed and not reject file
 # will be created. You will need to go to the mysql-test dir and diff
 # the files your self to see what is not matching
 
 --exec diff $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_slave.sql;
+
+# This tests the fix to
+# BUG#19630 stored function inserting into two auto_increment breaks statement-based binlog
+# We verify that under the mixed binlog mode, a stored function
+# modifying at least two tables having an auto_increment column,
+# is binlogged row-based. Indeed in statement-based binlogging,
+# only the auto_increment value generated for the first table
+# is recorded in the binlog, the value generated for the 2nd table
+# lacking.
+
+connection master;
+set binlog_format=mixed;
+drop table t1,t2,t3;
+create table t1 (a int primary key auto_increment, b varchar(100));
+create table t2 (a int primary key auto_increment, b varchar(100));
+create table t3 (b varchar(100));
+delimiter |;
+create function f (x varchar(100)) returns int deterministic
+begin
+ insert into t1 values(null,x);
+ insert into t2 values(null,x);
+ return 1;
+end|
+delimiter ;|
+select f("try_41_");
+select * from t1;
+select * from t2;
+# Two operations which compensate each other except that their net
+# effect is that they advance the auto_increment counter of t2 on slave:
+sync_slave_with_master;
+use mysqltest1;
+insert into t2 values(2,null),(3,null),(4,null);
+delete from t2 where a>=2;
+select * from t1;
+select * from t2;
+
+connection master;
+# this is the call which didn't replicate well
+select f("try_42_");
+select * from t1;
+select * from t2;
+sync_slave_with_master;
+select * from t1;
+select * from t2;
+
+# now use prepared statement and test again, just to see that the RBB
+# mode isn't set at PREPARE but at EXECUTE.
+
+insert into t2 values(3,null),(4,null);
+delete from t2 where a>=3;
+
+connection master;
+prepare stmt1 from 'select f(?)';
+set @string="try_43_";
+insert into t1 values(null,"try_44_"); # should be SBB
+execute stmt1 using @string; # should be RBB
+deallocate prepare stmt1;
+select * from t1;
+select * from t2;
+sync_slave_with_master;
+select * from t1;
+select * from t2;
+
+# verify that if only one table has auto_inc, it does not trigger RBB
+# (we'll check in binlog further below)
+
+connection master;
+drop table t1;
+create table t1 (a int, b varchar(100), key(a));
+select f("try_45_");
+
+# restore table's key
+drop table t1;
+create table t1 (a int primary key auto_increment, b varchar(100));
+
+# now test if it's two functions, each of them inserts in one table
+
+drop function f;
+truncate table t1;
+truncate table t2;
+delimiter |;
+create function f1 (x varchar(100)) returns int deterministic
+begin
+ insert into t1 values(null,x);
+ return 1;
+end|
+create function f2 (x varchar(100)) returns int deterministic
+begin
+ insert into t2 values(null,x);
+ return 1;
+end|
+delimiter ;|
+select f1("try_46_"),f2("try_47_");
+select * from t1;
+select * from t2;
+sync_slave_with_master;
+insert into t2 values(2,null),(3,null),(4,null);
+delete from t2 where a>=2;
+select * from t1;
+select * from t2;
+
+connection master;
+# Test with SELECT and INSERT
+select f1("try_48_"),f2("try_49_");
+insert into t3 values(concat("try_50_",f1("try_51_"),f2("try_52_")));
+select * from t1;
+select * from t2;
+select * from t3;
+sync_slave_with_master;
+select * from t1;
+select * from t2;
+select * from t3;
+
+# verify that if f2 does only read on an auto_inc table, this does not
+# switch to RBB
+connection master;
+drop function f2;
+delimiter |;
+create function f2 (x varchar(100)) returns int deterministic
+begin
+ declare y int;
+ insert into t1 values(null,x);
+ set y = (select count(*) from t2);
+ return y;
+end|
+delimiter ;|
+select f1("try_53_"),f2("try_54_");
+select * from t1;
+select * from t2;
+sync_slave_with_master;
+select * from t1;
+select * from t2;
+
+# Test for BUG#20499 "mixed mode with temporary table breaks binlog"
+# Slave used to have only 2 rows instead of 3.
+connection master;
+drop table t1,t2;
+CREATE TEMPORARY TABLE t1 SELECT UUID();
+create table t2 like t1;
+INSERT INTO t2 SELECT * FROM t1;
+# we'll verify that this one is done RBB
+insert into t2 values("try_55_");
+drop table t1;
+# we'll verify that this one is done SBB
+insert into t2 values("try_56_");
+select count(*) from mysqltest1.t2;
+sync_slave_with_master;
+select count(*) from mysqltest1.t2;
+
+connection master;
+--replace_column 2 # 5 #
+--replace_regex /table_id: [0-9]+/table_id: #/
+show binlog events from 102;
+
+connection master;
+drop database mysqltest1;
+sync_slave_with_master;
+--enable_ps_protocol

--- 1.158/mysql-test/t/disabled.def	2006-05-22 21:57:16 +02:00
+++ 1.159/mysql-test/t/disabled.def	2006-06-16 15:17:59 +02:00
@@ -30,7 +30,6 @@
 rpl_ndb_innodb2ndb       : Bug #19710  	Cluster replication to partition table fails on DELETE FROM statement
 rpl_ndb_log              : BUG#18947 2006-03-21 tomas CRBR: order in binlog of create table and insert (on different table) not determ
 rpl_ndb_myisam2ndb       : Bug #19710  	Cluster replication to partition table fails on DELETE FROM statement
-rpl_switch_stm_row_mixed : BUG#18590 2006-03-28 brian
 rpl_row_blob_innodb      : BUG#18980 2006-04-10 kent    Test fails randomly
 rpl_row_func003 	 : BUG#19074 2006-13-04 andrei  test failed
 rpl_row_inexist_tbl      : BUG#18948 2006-03-09 mats    Disabled since patch makes this test wait forever

--- 1.176/sql/set_var.cc	2006-05-23 20:26:10 +02:00
+++ 1.177/sql/set_var.cc	2006-06-16 15:18:00 +02:00
@@ -1307,9 +1307,9 @@
   }
 #endif /*HAVE_ROW_BASED_REPLICATION*/
   /*
-    if in a stored function, it's too late to change mode
+    if in a stored function/trigger, it's too late to change mode
   */
-  if (thd->spcont && thd->prelocked_mode)
+  if (thd->in_sub_stmt)
   {
     my_error(ER_STORED_FUNCTION_PREVENTS_SWITCH_BINLOG_FORMAT, MYF(0));
     return 1;    

--- 1.110/sql/sp.cc	2006-05-22 12:05:11 +02:00
+++ 1.111/sql/sp.cc	2006-06-16 15:18:00 +02:00
@@ -1628,6 +1628,14 @@
           sp->add_used_tables_to_table_list(thd, &lex->query_tables_last,
                                             rt->belong_to_view);
       }
+#ifdef HAVE_ROW_BASED_REPLICATION
+      /*
+        If this routine needs row-based, the entire top statement too (we
+        don't switch in the middle).
+      */
+      if (sp->m_binlog_row_based_if_mixed)
+        lex->binlog_row_based_if_mixed= 1;
+#endif
     }
     first= FALSE;
   }

--- 1.222/sql/sp_head.cc	2006-05-29 15:42:55 +02:00
+++ 1.223/sql/sp_head.cc	2006-06-16 15:18:00 +02:00
@@ -467,6 +467,9 @@
    m_flags(0), m_recursion_level(0), m_next_cached_sp(0),
    m_first_instance(this), m_first_free_instance(this), m_last_cached_sp(this),
    m_cont_level(0)
+#ifdef HAVE_ROW_BASED_REPLICATION
+  , m_binlog_row_based_if_mixed(0)
+#endif
 {
   m_return_field_def.charset = NULL;
 
@@ -1675,6 +1678,15 @@
   oldlex->ptr= sublex->ptr;
   oldlex->next_state= sublex->next_state;
   oldlex->trg_table_fields.push_back(&sublex->trg_table_fields);
+
+#ifdef HAVE_ROW_BASED_REPLICATION
+  /*
+    If this substmt needs row-based, the entire routine does too (we don't
+    switch in the middle).
+  */
+  if (sublex->binlog_row_based_if_mixed)
+    m_binlog_row_based_if_mixed= 1;
+#endif
 
   /*
     Add routines which are used by statement to respective set for

--- 1.87/sql/sp_head.h	2006-05-15 13:35:22 +02:00
+++ 1.88/sql/sp_head.h	2006-06-16 15:18:00 +02:00
@@ -134,6 +134,9 @@
   uint m_flags;                 // Boolean attributes of a stored routine
 
   create_field m_return_field_def; /* This is used for FUNCTIONs only. */
+#ifdef HAVE_ROW_BASED_REPLICATION
+  bool m_binlog_row_based_if_mixed; // if >=1 sub-stmt needs row-based
+#endif
 
   const uchar *m_tmp_query;	// Temporary pointer to sub query string
   st_sp_chistics *m_chistics;