#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
