| List: | Commits | « Previous MessageNext Message » | |
| From: | guilhem | Date: | June 16 2006 3:18pm |
| Subject: | bk commit into 5.1 tree (guilhem:1.2176) BUG#20499 | ||
| View as plain text | |||
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;
| Thread | ||
|---|---|---|
| • bk commit into 5.1 tree (guilhem:1.2176) BUG#20499 | guilhem | 16 Jun |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 0.898 sec. using MySQL 5.1.14-beta-log