List:Commits« Previous MessageNext Message »
From:Li-Bing.Song Date:August 11 2010 2:00pm
Subject:bzr commit into mysql-5.1-bugteam branch (Li-Bing.Song:3478) Bug#47899
View as plain text  
#At file:///home/anders/work/bzrwork1/mysql-5.1-wl5370/ based on revid:li-bing.song@stripped

 3478 Li-Bing.Song@stripped	2010-08-11
      Bug#47899 CREATE TABLE...SELECT binlogged wrongly if binlog_format=row
      
      When binlog_format=ROW, CREATE...SELECT is written as a CREATE statement without SELECT,
      followed by row events. This entire sequence of binlog events is wrapped in
      BEGIN...COMMIT. Like this:
        BEGIN
        row event
        row event
        CREATE TABLE t1 (a INT)
        row event
        row event
        row event
        COMMIT/ROLLBACK
      However, CREATE table has an implicit commit. So the row events will be
      written outside of transactional context.
      
      After this patch, on slave SQL thread, CREATE TABLE statement in a
      explicit transaction never ends the transaction automatically and
      also is binlogged in the transaction(same with master's binlog).
      As there is no 'CREATE TABLE' statement binlogged into a transaction
      except 'CREATE TABLE SELECT' statement.

    modified:
      mysql-test/suite/rpl/r/rpl_row_create_table.result
      mysql-test/suite/rpl/t/rpl_row_create_table.test
      sql/mysql_priv.h
      sql/sql_parse.cc
      sql/sql_table.cc
=== modified file 'mysql-test/suite/rpl/r/rpl_row_create_table.result'
--- a/mysql-test/suite/rpl/r/rpl_row_create_table.result	2010-07-30 06:23:25 +0000
+++ b/mysql-test/suite/rpl/r/rpl_row_create_table.result	2010-08-11 14:00:41 +0000
@@ -473,4 +473,103 @@ Warnings:
 Note	1050	Table 't1' already exists
 Comparing tables master:test.t1 and slave:test.t1
 DROP TABLE t1;
+
+# Bug #47899 CREATE TABLE...SELECT binlogged wrongly if binlog_format=row
+# 'CREATE TABLE' statement will not end the transaction and  still be
+# binlogged in the transaction. As no 'CREATE TABLE' statement will be
+# binlogged in a transaction, except 'CREATE TABLE ... SELECT' on RBR. 
+# It also fix bug#55877
+
+stop slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+start slave;
+DROP FUNCTION IF EXISTS f1;
+CREATE FUNCTION f1()
+RETURNS INT
+BEGIN
+INSERT INTO t2 VALUES(1);
+INSERT INTO t3 VALUES(2);
+RETURN 1;
+END|
+CREATE TABLE t2(c1 INT) ENGINE=MyISAM;
+CREATE TABLE t3(c1 INT) ENGINE=Innodb;
+CREATE TABLE t1(c1 INT KEY, c2 BLOB) ENGINE= Innodb SELECT 1 as c1, repeat('a', 1024) as c2 UNION SELECT 2, repeat('b', 1024);
+
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE `t1` (
+  `c1` int(11) NOT NULL,
+  `c2` blob,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB
+master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+Comparing tables master:test.t1 and slave:test.t1
+Comparing tables master:test.t2 and slave:test.t2
+Comparing tables master:test.t3 and slave:test.t3
+
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+slave-bin.000001	#	Query	#	#	BEGIN
+slave-bin.000001	#	Query	#	#	use `test`; CREATE TABLE `t1` (
+  `c1` int(11) NOT NULL,
+  `c2` blob,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB
+slave-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
+slave-bin.000001	#	Write_rows	#	#	table_id: #
+slave-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+slave-bin.000001	#	Xid	#	#	COMMIT /* XID */
+
+CREATE TABLE IF NOT EXISTS t1(c1 INT KEY, c2 BLOB) ENGINE= Innodb SELECT 3 as c1, repeat('a', 1024) as c2 UNION SELECT f1(), repeat('b', 1024);
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
+master-bin.000001	#	Table_map	#	#	table_id: # (test.t3)
+master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
+master-bin.000001	#	Write_rows	#	#	table_id: #
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE IF NOT EXISTS `t1` (
+  `c1` int(11) NOT NULL,
+  `c2` blob,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB
+master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
+master-bin.000001	#	Table_map	#	#	table_id: # (test.t3)
+master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	ROLLBACK
+Comparing tables master:test.t1 and slave:test.t1
+Comparing tables master:test.t2 and slave:test.t2
+Comparing tables master:test.t3 and slave:test.t3
+
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+slave-bin.000001	#	Query	#	#	BEGIN
+slave-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
+slave-bin.000001	#	Table_map	#	#	table_id: # (test.t3)
+slave-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
+slave-bin.000001	#	Write_rows	#	#	table_id: #
+slave-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+slave-bin.000001	#	Query	#	#	use `test`; CREATE TABLE IF NOT EXISTS `t1` (
+  `c1` int(11) NOT NULL,
+  `c2` blob,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB
+slave-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
+slave-bin.000001	#	Table_map	#	#	table_id: # (test.t3)
+slave-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
+slave-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+slave-bin.000001	#	Query	#	#	ROLLBACK
+DROP TABLE t1, t2, t3;
+DROP FUNCTION f1;
 end of the tests

=== modified file 'mysql-test/suite/rpl/t/rpl_row_create_table.test'
--- a/mysql-test/suite/rpl/t/rpl_row_create_table.test	2010-07-30 06:23:25 +0000
+++ b/mysql-test/suite/rpl/t/rpl_row_create_table.test	2010-08-11 14:00:41 +0000
@@ -311,6 +311,84 @@ CREATE TABLE IF NOT EXISTS t1 SELECT 1;
 let $diff_table=test.t1;
 source include/rpl_diff_tables.inc;
 DROP TABLE t1;
+sync_slave_with_master;
+
+--echo
+--echo # Bug #47899 CREATE TABLE...SELECT binlogged wrongly if binlog_format=row
+--echo # 'CREATE TABLE' statement will not end the transaction and  still be
+--echo # binlogged in the transaction. As no 'CREATE TABLE' statement will be
+--echo # binlogged in a transaction, except 'CREATE TABLE ... SELECT' on RBR. 
+--echo # It also fix bug#55877
+--echo
+SET GLOBAL autocommit=0;
+source include/master-slave-reset.inc;
+connection master;
+--disable_warnings
+DROP FUNCTION IF EXISTS f1;
+--enable_warnings
+
+--delimiter |
+CREATE FUNCTION f1()
+  RETURNS INT
+BEGIN
+  INSERT INTO t2 VALUES(1);
+  INSERT INTO t3 VALUES(2);
+  RETURN 1;
+END|
+--delimiter ;
+
+CREATE TABLE t2(c1 INT) ENGINE=MyISAM;
+CREATE TABLE t3(c1 INT) ENGINE=Innodb;
+let $master_binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
+
+sync_slave_with_master;
+connection slave;
+let $slave_binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
+
+connection master;
+CREATE TABLE t1(c1 INT KEY, c2 BLOB) ENGINE= Innodb SELECT 1 as c1, repeat('a', 1024) as c2 UNION SELECT 2, repeat('b', 1024);
+
+--echo
+let $binlog_start= $master_binlog_start;
+source include/show_binlog_events.inc;
+let $master_binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
+
+let $diff_table= test.t1;
+source include/rpl_diff_tables.inc;
+let $diff_table= test.t2;
+source include/rpl_diff_tables.inc;
+let $diff_table= test.t3;
+source include/rpl_diff_tables.inc;
+
+connection slave;
+--echo
+let $binlog_start= $slave_binlog_start;
+source include/show_binlog_events.inc;
+let $slave_binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
+
+connection master;
+--echo
+--error 1062
+CREATE TABLE IF NOT EXISTS t1(c1 INT KEY, c2 BLOB) ENGINE= Innodb SELECT 3 as c1, repeat('a', 1024) as c2 UNION SELECT f1(), repeat('b', 1024);
+--echo
+let $binlog_start= $master_binlog_start;
+source include/show_binlog_events.inc;
+
+let $diff_table= test.t1;
+source include/rpl_diff_tables.inc;
+let $diff_table= test.t2;
+source include/rpl_diff_tables.inc;
+let $diff_table= test.t3;
+source include/rpl_diff_tables.inc;
+
+connection slave;
+--echo
+let $binlog_start= $slave_binlog_start;
+source include/show_binlog_events.inc;
+
+connection master;
+DROP TABLE t1, t2, t3;
+DROP FUNCTION f1;
 
 source include/master-slave-end.inc;
 --echo end of the tests

=== modified file 'sql/mysql_priv.h'
--- a/sql/mysql_priv.h	2010-06-10 20:45:22 +0000
+++ b/sql/mysql_priv.h	2010-08-11 14:00:41 +0000
@@ -982,7 +982,7 @@ check_and_unset_inject_value(int value)
 #endif
 
 int write_bin_log(THD *thd, bool clear_error,
-                  char const *query, ulong query_length);
+                  char const *query, ulong query_length, bool is_trans= FALSE);
 
 /* sql_connect.cc */
 int check_user(THD *thd, enum enum_server_command command, 
@@ -1029,6 +1029,7 @@ void mysql_parse(THD *thd, const char *i
 
 bool mysql_test_parse_for_slave(THD *thd,char *inBuf,uint length);
 bool is_update_query(enum enum_sql_command command);
+bool is_create_select_on_slave(THD *thd);
 bool is_log_table_write_query(enum enum_sql_command command);
 bool alloc_query(THD *thd, const char *packet, uint packet_length);
 void mysql_init_select(LEX *lex);

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2010-08-11 09:30:08 +0000
+++ b/sql/sql_parse.cc	2010-08-11 14:00:41 +0000
@@ -364,6 +364,11 @@ bool is_update_query(enum enum_sql_comma
   return (sql_command_flags[command] & CF_CHANGES_DATA) != 0;
 }
 
+bool is_create_select_on_slave(THD *thd)
+{
+  return (thd->slave_thread) && (thd->options & OPTION_BEGIN);
+}
+
 /**
   Check if a sql command is allowed to write to log tables.
   @param command The SQL command
@@ -2544,8 +2549,14 @@ mysql_execute_command(THD *thd)
 
   case SQLCOM_CREATE_TABLE:
   {
-    /* If CREATE TABLE of non-temporary table, do implicit commit */
-    if (!(lex->create_info.options & HA_LEX_CREATE_TMP_TABLE))
+    /*
+      If CREATE TABLE of non-temporary table, do implicit commit.
+      'CREATE TABLE' will never be binlogged into a transaction except
+      'CREATE TABLE ... SELECT' on RBR. And we should keep the transaction
+      for 'CREATE TABLE ... SELECT'.
+     */
+    if (!(lex->create_info.options & HA_LEX_CREATE_TMP_TABLE) &&
+        !thd->slave_thread)
     {
       if (end_active_trans(thd))
       {

=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc	2010-06-10 20:45:22 +0000
+++ b/sql/sql_table.cc	2010-08-11 14:00:41 +0000
@@ -1781,7 +1781,7 @@ end:
 */
 
 int write_bin_log(THD *thd, bool clear_error,
-                  char const *query, ulong query_length)
+                  char const *query, ulong query_length, bool is_trans)
 {
   int error= 0;
   if (mysql_bin_log.is_open())
@@ -1792,7 +1792,7 @@ int write_bin_log(THD *thd, bool clear_e
     else
       errcode= query_error_code(thd, TRUE);
     error= thd->binlog_query(THD::STMT_QUERY_TYPE,
-                             query, query_length, FALSE, FALSE, errcode);
+                             query, query_length, is_trans, FALSE, errcode);
   }
   return error;
 }
@@ -3603,7 +3603,8 @@ static inline int write_create_table_bin
       (!thd->current_stmt_binlog_row_based ||
        (thd->current_stmt_binlog_row_based &&
         !(create_info->options & HA_LEX_CREATE_TMP_TABLE))))
-    return write_bin_log(thd, TRUE, thd->query(), thd->query_length());
+    return write_bin_log(thd, TRUE, thd->query(), thd->query_length(),
+                         is_create_select_on_slave(thd));
   return 0;
 }
 


Attachment: [text/bzr-bundle] bzr/li-bing.song@sun.com-20100811140041-mzh4gw8laiet09q5.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (Li-Bing.Song:3478) Bug#47899Li-Bing.Song11 Aug
  • Re: bzr commit into mysql-5.1-bugteam branch (Li-Bing.Song:3478)Bug#47899Konstantin Osipov16 Aug