diff options
author | Luis Soares <luis.soares@sun.com> | 2009-07-26 22:48:24 +0100 |
---|---|---|
committer | Luis Soares <luis.soares@sun.com> | 2009-07-26 22:48:24 +0100 |
commit | cf505e4494e86e09ce2c83af4dfe9f434eacb88c (patch) | |
tree | 3c0b18a6e2b864bf57586f4621b7343d5689aad8 /mysql-test/suite/rpl | |
parent | 8ec2f3d0d1943a282648bd0fe708539a539b18cc (diff) | |
download | mariadb-git-cf505e4494e86e09ce2c83af4dfe9f434eacb88c.tar.gz |
BUG#43046: mixed mode switch to row format with temp table lead
to wrong result
When using MIXED mode and issuing 'CREATE TEMPORARY TABLE t_tmp',
the statement is logged if the current binlogging mode is
STATEMENT. This causes the slave to replay the instruction and
create the temporary table as well. If there is no switch to ROW
mode, and later on a 'DROP TEMPORARY TABLE t_tmp' is issued, then
this statement will also be logged and the slave will
remove/close the temporary table.
However, if there is a switch to ROW mode between the CREATE and
DROP TEMPORARY table, the DROP statement will not be logged,
leaving the slave with a dangling temporary table.
This patch addresses this, by always logging a DROP TEMPORARY
TABLE IF EXISTS when in mixed mode and a drop statement is issued
for temporary table(s).
mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result:
Updated result file.
mysql-test/suite/rpl/t/rpl_temp_table_mix_row.test:
Added test case.
sql/sql_table.cc:
When dropping table(s) in mixed mode and current statement
logging is ROW, builds an extra DROP TEMPORARY TABLE IF
EXISTS for temporary tables that are being dropped. Later,
it logs the extra drop statement.
Diffstat (limited to 'mysql-test/suite/rpl')
-rw-r--r-- | mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result | 45 | ||||
-rw-r--r-- | mysql-test/suite/rpl/t/rpl_temp_table_mix_row.test | 95 |
2 files changed, 140 insertions, 0 deletions
diff --git a/mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result b/mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result index feffefc9dad..3a31a206b1e 100644 --- a/mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result +++ b/mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result @@ -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 diff --git a/mysql-test/suite/rpl/t/rpl_temp_table_mix_row.test b/mysql-test/suite/rpl/t/rpl_temp_table_mix_row.test index 0cb7f2cbf80..e19c3019aa1 100644 --- a/mysql-test/suite/rpl/t/rpl_temp_table_mix_row.test +++ b/mysql-test/suite/rpl/t/rpl_temp_table_mix_row.test @@ -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 |