#At file:///home/lsoares/Workspace/mysql-server/bugfix/b43046/mysql-pe-to-push/ based on revid:davi.arnaut@stripped
3486 Luis Soares 2009-07-26 [merge]
BUG#43046: mixed mode switch to row format with temp table lead
to wrong result
Automerge: mysql-5.1-bugteam --> mysql-pe
modified:
mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result
mysql-test/suite/rpl/t/rpl_temp_table_mix_row.test
sql/sql_table.cc
=== modified file 'mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result'
--- a/mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result 2009-02-23 03:26:38 +0000
+++ b/mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result 2009-07-26 21:48:24 +0000
@@ -24,3 +24,48 @@ Slave_open_temp_tables 0
[on master]
DROP TABLE t1;
[on slave]
+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;
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) );
+CREATE TABLE t3 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) );
+CREATE TRIGGER tr1 AFTER DELETE ON t2 FOR EACH ROW INSERT INTO t3 () VALUES ();
+CREATE TEMPORARY TABLE t1_tmp (i1 int);
+ALTER TABLE t1_tmp ADD COLUMN b INT;
+DELETE FROM t2;
+CREATE TEMPORARY TABLE t2_tmp (a int);
+ALTER TABLE t1_tmp ADD COLUMN c INT;
+### assertion: assert that there is one open temp table on slave
+SHOW STATUS LIKE 'Slave_open_temp_tables';
+Variable_name Value
+Slave_open_temp_tables 1
+DROP TABLE t1_tmp, t2;
+INSERT INTO t1 VALUES (1);
+DROP TEMPORARY TABLE t2_tmp;
+INSERT INTO t1 VALUES (2);
+### assertion: assert that slave has no temporary tables opened
+SHOW STATUS LIKE 'Slave_open_temp_tables';
+Variable_name Value
+Slave_open_temp_tables 0
+DROP TABLE t3, t1;
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+slave-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a int)
+slave-bin.000001 # Query # # use `test`; CREATE TABLE t2 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) )
+slave-bin.000001 # Query # # use `test`; CREATE TABLE t3 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) )
+slave-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER DELETE ON t2 FOR EACH ROW INSERT INTO t3 () VALUES ()
+slave-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE t1_tmp (i1 int)
+slave-bin.000001 # Query # # use `test`; ALTER TABLE t1_tmp ADD COLUMN b INT
+slave-bin.000001 # Query # # use `test`; DROP TABLE `t2` /* generated by server */
+slave-bin.000001 # Query # # use `test`; DROP TEMPORARY TABLE IF EXISTS `t1_tmp` /* generated by server */
+slave-bin.000001 # Query # # BEGIN
+slave-bin.000001 # Table_map # # table_id: # (test.t1)
+slave-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
+slave-bin.000001 # Query # # COMMIT
+slave-bin.000001 # Query # # use `test`; DROP TEMPORARY TABLE IF EXISTS `t2_tmp` /* generated by server */
+slave-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (2)
+slave-bin.000001 # Query # # use `test`; DROP TABLE t3, t1
=== modified file 'mysql-test/suite/rpl/t/rpl_temp_table_mix_row.test'
--- a/mysql-test/suite/rpl/t/rpl_temp_table_mix_row.test 2009-03-24 08:55:03 +0000
+++ b/mysql-test/suite/rpl/t/rpl_temp_table_mix_row.test 2009-07-26 21:48:24 +0000
@@ -51,3 +51,98 @@ DROP TABLE t1;
--echo [on slave]
sync_slave_with_master;
+
+#
+# BUG#43046: mixed mode switch to row format with temp table lead to wrong
+# result
+#
+# NOTES
+# =====
+#
+# 1. Temporary tables cannot be logged using the row-based
+# format. Thus, once row-based logging is used, all subsequent
+# statements using that table are unsafe, and we approximate this
+# condition by treating all statements made by that client as
+# unsafe until the client no longer holds any temporary tables.
+#
+# 2. Two different connections can use the same temporary table
+# name without conflicting with each other or with an
+# existing non-TEMPORARY table of the same name.
+#
+# DESCRIPTION
+# ===========
+#
+# The test is implemented as follows:
+# 1. create regular tables
+# 2. create a temporary table t1_tmp: should be logged as statement
+# 3. issue an alter table: should be logged as statement
+# 4. issue statement that forces switch to RBR
+# 5. create another temporary table t2_tmp: should not be logged
+# 6. issue alter table on t1_tmp: should not be logged
+# 7. drop t1_tmp and regular table on same statement: should log both in
+# statement format (but different statements)
+# 8. issue deterministic insert: logged as row (because t2_tmp still
+# exists).
+# 9. drop t2_tmp and issue deterministic statement: should log drop and
+# query in statement format (show switch back to STATEMENT format)
+# 10. in the end the slave should not have open temp tables.
+#
+
+connect (master,127.0.0.1,root,,test,$MASTER_MYPORT,);
+-- source include/master-slave-reset.inc
+-- connection master
+
+# action: setup environment
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) );
+CREATE TABLE t3 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) );
+CREATE TRIGGER tr1 AFTER DELETE ON t2 FOR EACH ROW INSERT INTO t3 () VALUES ();
+
+# assertion: assert that CREATE is logged as STATEMENT
+CREATE TEMPORARY TABLE t1_tmp (i1 int);
+
+# assertion: assert that ALTER TABLE is logged as STATEMENT
+ALTER TABLE t1_tmp ADD COLUMN b INT;
+
+# action: force switch to RBR
+DELETE FROM t2;
+
+# assertion: assert that t2_tmp will not make into the binlog (RBR logging atm)
+CREATE TEMPORARY TABLE t2_tmp (a int);
+
+# assertion: assert that ALTER TABLE on t1_tmp will not make into the binlog
+ALTER TABLE t1_tmp ADD COLUMN c INT;
+
+-- echo ### assertion: assert that there is one open temp table on slave
+-- sync_slave_with_master
+SHOW STATUS LIKE 'Slave_open_temp_tables';
+
+-- connection master
+
+# assertion: assert that both drops are logged
+DROP TABLE t1_tmp, t2;
+
+# assertion: assert that statement is logged as row (master still has one
+# opened temporary table - t2_tmp.
+INSERT INTO t1 VALUES (1);
+
+# assertion: assert that DROP TABLE *is* logged despite CREATE is not.
+DROP TEMPORARY TABLE t2_tmp;
+
+# assertion: assert that statement is now logged as STMT (mixed mode switches
+# back to STATEMENT).
+INSERT INTO t1 VALUES (2);
+
+-- sync_slave_with_master
+
+-- echo ### assertion: assert that slave has no temporary tables opened
+SHOW STATUS LIKE 'Slave_open_temp_tables';
+
+-- connection master
+
+# action: drop remaining tables
+DROP TABLE t3, t1;
+
+-- sync_slave_with_master
+
+-- source include/show_binlog_events.inc
=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc 2009-07-10 12:31:32 +0000
+++ b/sql/sql_table.cc 2009-07-26 22:02:13 +0000
@@ -1797,6 +1797,7 @@ int mysql_rm_table_part2(THD *thd, TABLE
int non_temp_tables_count= 0;
bool some_tables_deleted=0, tmp_table_deleted=0, foreign_key_error=0;
String built_query;
+ String built_tmp_query;
DBUG_ENTER("mysql_rm_table_part2");
LINT_INIT(alias);
@@ -1900,6 +1901,25 @@ int mysql_rm_table_part2(THD *thd, TABLE
case 0:
// removed temporary table
tmp_table_deleted= 1;
+ if (thd->variables.binlog_format == BINLOG_FORMAT_MIXED &&
+ thd->current_stmt_binlog_row_based)
+ {
+ if (built_tmp_query.is_empty())
+ {
+ built_tmp_query.set_charset(system_charset_info);
+ built_tmp_query.append("DROP TEMPORARY TABLE IF EXISTS ");
+ }
+
+ built_tmp_query.append("`");
+ if (thd->db == NULL || strcmp(db,thd->db) != 0)
+ {
+ built_tmp_query.append(db);
+ built_tmp_query.append("`.`");
+ }
+ built_tmp_query.append(table->table_name);
+ built_tmp_query.append("`,");
+ }
+
continue;
case -1:
DBUG_ASSERT(thd->in_sub_stmt);
@@ -2066,29 +2086,52 @@ int mysql_rm_table_part2(THD *thd, TABLE
write_bin_log(thd, !error, thd->query, thd->query_length);
}
else if (thd->current_stmt_binlog_row_based &&
- non_temp_tables_count > 0 &&
tmp_table_deleted)
{
- /*
- In this case we have deleted both temporary and
- non-temporary tables, so:
- - since we have deleted a non-temporary table we have to
- binlog the statement, but
- - since we have deleted a temporary table we cannot binlog
- the statement (since the table has not been created on the
- slave, this might cause the slave to stop).
+ if (non_temp_tables_count > 0)
+ {
+ /*
+ In this case we have deleted both temporary and
+ non-temporary tables, so:
+ - since we have deleted a non-temporary table we have to
+ binlog the statement, but
+ - since we have deleted a temporary table we cannot binlog
+ the statement (since the table may have not been created on the
+ slave - check "if" branch below, this might cause the slave to
+ stop).
- Instead, we write a built statement, only containing the
- non-temporary tables, to the binary log
+ Instead, we write a built statement, only containing the
+ non-temporary tables, to the binary log
+ */
+ built_query.chop(); // Chop of the last comma
+ built_query.append(" /* generated by server */");
+ write_bin_log(thd, !error, built_query.ptr(), built_query.length());
+ }
+
+ /*
+ One needs to always log any temporary table drop, if:
+ 1. thread logging format is mixed mode; AND
+ 2. current statement logging format is set to row.
*/
- built_query.chop(); // Chop of the last comma
- built_query.append(" /* generated by server */");
- write_bin_log(thd, !error, built_query.ptr(), built_query.length());
+ if (thd->variables.binlog_format == BINLOG_FORMAT_MIXED)
+ {
+ /*
+ In this case we have deleted some temporary tables but we are using
+ row based logging for the statement. However, thread uses mixed mode
+ format, thence we need to log the dropping as we cannot tell for
+ sure whether the create was logged as statement previously or not, ie,
+ before switching to row mode.
+ */
+ built_tmp_query.chop(); // Chop of the last comma
+ built_tmp_query.append(" /* generated by server */");
+ write_bin_log(thd, !error, built_tmp_query.ptr(), built_tmp_query.length());
+ }
}
+
/*
The remaining cases are:
- - no tables where deleted and
- - only temporary tables where deleted and row-based
+ - no tables were deleted and
+ - only temporary tables were deleted and row-based
replication is used.
In both these cases, nothing should be written to the binary
log.
Attachment: [text/bzr-bundle] bzr/luis.soares@sun.com-20090726220213-6plwi5ihjgr4jnzu.bundle
| Thread |
|---|
| • bzr commit into mysql-pe branch (luis.soares:3486) Bug#43046 | Luis Soares | 27 Jul |
