#At file:///home/bzr/bugs/b36763-mysql-5.1-bugteam/ based on revid:timothy.smith@stripped
2748 Mats Kindahl 2009-02-06
Bug #36763
TRUNCATE TABLE fails to replicate when stmt-based binlogging is not supported.
There were two separate problems with the code, both of which are fixed with
this patch:
1. An error was printed by InnoDB for TRUNCATE TABLE in statement mode when
the in isolation levels READ COMMITTED and READ UNCOMMITTED since InnoDB
does permit statement-based replication for DML statements. However,
the TRUNCATE TABLE is not transactional, but is a DDL, and should therefore
be allowed to be replicated as a statement.
2. The statement was not logged in mixed mode because of the error above, but
the error was not reported to the client.
This patch fixes the problem by treating TRUNCATE TABLE a DDL, that is, it is
always logged as a statement and not reporting an error from InnoDB for TRUNCATE
TABLE.
added:
mysql-test/extra/binlog_tests/binlog_truncate.test
mysql-test/suite/binlog/r/binlog_truncate_innodb.result
mysql-test/suite/binlog/r/binlog_truncate_myisam.result
mysql-test/suite/binlog/t/binlog_truncate_innodb-master.opt
mysql-test/suite/binlog/t/binlog_truncate_innodb.test
mysql-test/suite/binlog/t/binlog_truncate_myisam.test
modified:
mysql-test/extra/rpl_tests/rpl_truncate.test
mysql-test/extra/rpl_tests/rpl_truncate_helper.test
mysql-test/suite/binlog/t/disabled.def
mysql-test/suite/rpl/r/rpl_truncate_2myisam.result
mysql-test/suite/rpl/r/rpl_truncate_3innodb.result
sql/sql_base.cc
sql/sql_delete.cc
per-file messages:
mysql-test/extra/binlog_tests/binlog_truncate.test
Adding new test to check that TRUNCATE TABLE is written correctly
to the binary log.
mysql-test/extra/rpl_tests/rpl_truncate.test
Removing redundant testing by eliminating settings of BINLOG_FORMAT.
mysql-test/extra/rpl_tests/rpl_truncate_helper.test
Replacing slave and master reset code with include file.
Removing settings of BINLOG_FORMAT.
Replacing printing of table contents to compare master and slave
with diff_tables.inc.
mysql-test/suite/binlog/t/binlog_truncate_innodb.test
Adding test for testing that TRUNCATE TABLE is logged correctly for InnoDB
in all isolation levels.
mysql-test/suite/binlog/t/binlog_truncate_myisam.test
Adding test for testing that TRUNCATE TABLE is logged correctly for MyISAM.
mysql-test/suite/binlog/t/disabled.def
Disabling binlog_truncate_innodb since it does not work (yet).
sql/sql_base.cc
Correcting setting of capabilities flags to make the comparison with 0
later in the code work correctly.
sql/sql_delete.cc
Re-organizing code to ensure that TRUNCATE TABLE is logged in statement
format and that row format is not used unless there are rows to log (which
there are not when delete_all_rows() is called, so this has to be logged
as a statement).
=== added file 'mysql-test/extra/binlog_tests/binlog_truncate.test'
--- a/mysql-test/extra/binlog_tests/binlog_truncate.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/extra/binlog_tests/binlog_truncate.test 2009-02-06 16:06:41 +0000
@@ -0,0 +1,27 @@
+# BUG #36763: TRUNCATE TABLE fails to replicate when stmt-based
+# binlogging is not supported.
+
+# This should always be logged as a statement, even when executed as a
+# row-by-row deletion.
+
+# $before_truncate A statement to execute (just) before issuing the
+# TRUNCATE TABLE
+
+
+eval CREATE TABLE t1 (a INT) ENGINE=$engine;
+eval CREATE TABLE t2 (a INT) ENGINE=$engine;
+INSERT INTO t2 VALUES (1),(2),(3);
+let $binlog_start = query_get_value("SHOW MASTER STATUS", Position, 1);
+if (`select length('$before_truncate') > 0`) {
+ eval $before_truncate;
+}
+--echo **** Truncate of empty table shall be logged
+TRUNCATE TABLE t1;
+
+if (`select length('$before_truncate') > 0`) {
+ eval $before_truncate;
+}
+TRUNCATE TABLE t2;
+source include/show_binlog_events.inc;
+
+DROP TABLE t1,t2;
=== modified file 'mysql-test/extra/rpl_tests/rpl_truncate.test'
--- a/mysql-test/extra/rpl_tests/rpl_truncate.test 2006-12-07 14:18:35 +0000
+++ b/mysql-test/extra/rpl_tests/rpl_truncate.test 2009-02-06 16:06:41 +0000
@@ -9,27 +9,8 @@
--source include/master-slave.inc
-let $format = STATEMENT;
-let $stmt = TRUNCATE TABLE;
+let $trunc_stmt = TRUNCATE TABLE;
--source extra/rpl_tests/rpl_truncate_helper.test
-let $format = MIXED;
-let $stmt = TRUNCATE TABLE;
+let $trunc_stmt = DELETE FROM;
--source extra/rpl_tests/rpl_truncate_helper.test
-
-let $format = ROW;
-let $stmt = TRUNCATE TABLE;
---source extra/rpl_tests/rpl_truncate_helper.test
-
-let $format = STATEMENT;
-let $stmt = DELETE FROM;
---source extra/rpl_tests/rpl_truncate_helper.test
-
-let $format = MIXED;
-let $stmt = DELETE FROM;
---source extra/rpl_tests/rpl_truncate_helper.test
-
-let $format = ROW;
-let $stmt = DELETE FROM;
---source extra/rpl_tests/rpl_truncate_helper.test
-
=== modified file 'mysql-test/extra/rpl_tests/rpl_truncate_helper.test'
--- a/mysql-test/extra/rpl_tests/rpl_truncate_helper.test 2007-11-02 12:00:38 +0000
+++ b/mysql-test/extra/rpl_tests/rpl_truncate_helper.test 2009-02-06 16:06:41 +0000
@@ -1,42 +1,35 @@
-connection slave;
-STOP SLAVE;
-source include/wait_for_slave_to_stop.inc;
-connection master;
---disable_warnings
-DROP TABLE IF EXISTS t1;
---enable_warnings
-connection slave;
---disable_warnings
-DROP TABLE IF EXISTS t1;
---enable_warnings
-RESET SLAVE;
-START SLAVE;
+source include/reset_master_and_slave.inc;
--echo **** On Master ****
connection master;
-eval SET SESSION BINLOG_FORMAT=$format;
-eval SET GLOBAL BINLOG_FORMAT=$format;
-
eval CREATE TABLE t1 (a INT, b LONG) ENGINE=$engine;
INSERT INTO t1 VALUES (1,1), (2,2);
-SELECT * FROM t1;
---echo **** On Slave ****
sync_slave_with_master;
-INSERT INTO t1 VALUE (3,3);
-SELECT * FROM t1;
--echo **** On Master ****
connection master;
-eval $stmt t1;
-SELECT * FROM t1;
---echo **** On Slave ****
+eval $trunc_stmt t1;
+sync_slave_with_master;
+
+let $diff_table_1=master:test.t1;
+let $diff_table_2=slave:test.t1;
+source include/diff_tables.inc;
+
+--echo ==== Test using a table with delete triggers ====
+--echo **** On Master ****
+connection master;
+SET @count := 1;
+eval CREATE TABLE t2 (a INT, b LONG) ENGINE=$engine;
+CREATE TRIGGER trg1 BEFORE DELETE ON t1 FOR EACH ROW SET @count := @count + 1;
sync_slave_with_master;
-# Should be empty
-SELECT * FROM t1;
--echo **** On Master ****
connection master;
-DROP TABLE t1;
-let $SERVER_VERSION=`select version()`;
-source include/show_binlog_events.inc;
+eval $trunc_stmt t1;
+sync_slave_with_master;
+
+let $diff_table_1=master:test.t2;
+let $diff_table_2=slave:test.t2;
+source include/diff_tables.inc;
connection master;
-RESET MASTER;
+DROP TABLE t1,t2;
+sync_slave_with_master;
=== added file 'mysql-test/suite/binlog/r/binlog_truncate_innodb.result'
--- a/mysql-test/suite/binlog/r/binlog_truncate_innodb.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/binlog/r/binlog_truncate_innodb.result 2009-02-06 16:06:41 +0000
@@ -0,0 +1,63 @@
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+CREATE TABLE t2 (a INT) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(2),(3);
+**** Truncate of empty table shall be logged
+TRUNCATE TABLE t1;
+TRUNCATE TABLE t2;
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
+master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+CREATE TABLE t2 (a INT) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(2),(3);
+SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+**** Truncate of empty table shall be logged
+TRUNCATE TABLE t1;
+SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+TRUNCATE TABLE t2;
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
+master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+CREATE TABLE t2 (a INT) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(2),(3);
+SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
+**** Truncate of empty table shall be logged
+TRUNCATE TABLE t1;
+SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
+TRUNCATE TABLE t2;
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
+master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+CREATE TABLE t2 (a INT) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(2),(3);
+SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+**** Truncate of empty table shall be logged
+TRUNCATE TABLE t1;
+SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+TRUNCATE TABLE t2;
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
+master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+CREATE TABLE t2 (a INT) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(2),(3);
+SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+**** Truncate of empty table shall be logged
+TRUNCATE TABLE t1;
+SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+TRUNCATE TABLE t2;
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
+master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2
+DROP TABLE t1,t2;
=== added file 'mysql-test/suite/binlog/r/binlog_truncate_myisam.result'
--- a/mysql-test/suite/binlog/r/binlog_truncate_myisam.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/binlog/r/binlog_truncate_myisam.result 2009-02-06 16:06:41 +0000
@@ -0,0 +1,11 @@
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+CREATE TABLE t2 (a INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1),(2),(3);
+**** Truncate of empty table shall be logged
+TRUNCATE TABLE t1;
+TRUNCATE TABLE t2;
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
+master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2
+DROP TABLE t1,t2;
=== added file 'mysql-test/suite/binlog/t/binlog_truncate_innodb-master.opt'
--- a/mysql-test/suite/binlog/t/binlog_truncate_innodb-master.opt 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/binlog/t/binlog_truncate_innodb-master.opt 2009-02-06 16:06:41 +0000
@@ -0,0 +1 @@
+--loose-innodb
\ No newline at end of file
=== added file 'mysql-test/suite/binlog/t/binlog_truncate_innodb.test'
--- a/mysql-test/suite/binlog/t/binlog_truncate_innodb.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/binlog/t/binlog_truncate_innodb.test 2009-02-06 16:06:41 +0000
@@ -0,0 +1,22 @@
+source include/have_log_bin.inc;
+source include/have_innodb.inc;
+
+let $engine = InnoDB;
+source extra/binlog_tests/binlog_truncate.test;
+
+# Under transaction isolation level READ UNCOMMITTED and READ
+# COMMITTED, InnoDB does not permit statement-based replication of
+# row-deleting statement. In these cases, TRUNCATE TABLE should still
+# be replicated as a statement.
+
+let $before_truncate = SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+source extra/binlog_tests/binlog_truncate.test;
+
+let $before_truncate = SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
+source extra/binlog_tests/binlog_truncate.test;
+
+let $before_truncate = SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+source extra/binlog_tests/binlog_truncate.test;
+
+let $before_truncate = SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+source extra/binlog_tests/binlog_truncate.test;
=== added file 'mysql-test/suite/binlog/t/binlog_truncate_myisam.test'
--- a/mysql-test/suite/binlog/t/binlog_truncate_myisam.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/binlog/t/binlog_truncate_myisam.test 2009-02-06 16:06:41 +0000
@@ -0,0 +1,4 @@
+source include/have_log_bin.inc;
+
+let $engine = MyISAM;
+source extra/binlog_tests/binlog_truncate.test;
=== modified file 'mysql-test/suite/binlog/t/disabled.def'
--- a/mysql-test/suite/binlog/t/disabled.def 2008-02-28 21:50:15 +0000
+++ b/mysql-test/suite/binlog/t/disabled.def 2009-02-06 16:06:41 +0000
@@ -9,3 +9,5 @@
# Do not use any TAB characters for whitespace.
#
##############################################################################
+binlog_truncate_innodb : BUG#42643 2009-02-06 mats Changes to InnoDB requires to complete fix for BUG#36763
+
=== modified file 'mysql-test/suite/rpl/r/rpl_truncate_2myisam.result'
--- a/mysql-test/suite/rpl/r/rpl_truncate_2myisam.result 2008-03-28 12:16:41 +0000
+++ b/mysql-test/suite/rpl/r/rpl_truncate_2myisam.result 2009-02-06 16:06:41 +0000
@@ -4,241 +4,43 @@ reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
+**** Resetting master and slave ****
STOP SLAVE;
-DROP TABLE IF EXISTS t1;
-DROP TABLE IF EXISTS t1;
RESET SLAVE;
-START SLAVE;
-**** On Master ****
-SET SESSION BINLOG_FORMAT=STATEMENT;
-SET GLOBAL BINLOG_FORMAT=STATEMENT;
-CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM;
-INSERT INTO t1 VALUES (1,1), (2,2);
-SELECT * FROM t1;
-a b
-1 1
-2 2
-**** On Slave ****
-INSERT INTO t1 VALUE (3,3);
-SELECT * FROM t1;
-a b
-1 1
-2 2
-3 3
-**** On Master ****
-TRUNCATE TABLE t1;
-SELECT * FROM t1;
-a b
-**** On Slave ****
-SELECT * FROM t1;
-a b
-**** On Master ****
-DROP TABLE t1;
-show binlog events from <binlog_start>;
-Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1
-master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM
-master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1,1), (2,2)
-master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
-master-bin.000001 # Query # # use `test`; DROP TABLE t1
RESET MASTER;
-STOP SLAVE;
-DROP TABLE IF EXISTS t1;
-DROP TABLE IF EXISTS t1;
-RESET SLAVE;
START SLAVE;
**** On Master ****
-SET SESSION BINLOG_FORMAT=MIXED;
-SET GLOBAL BINLOG_FORMAT=MIXED;
CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1,1), (2,2);
-SELECT * FROM t1;
-a b
-1 1
-2 2
-**** On Slave ****
-INSERT INTO t1 VALUE (3,3);
-SELECT * FROM t1;
-a b
-1 1
-2 2
-3 3
**** On Master ****
TRUNCATE TABLE t1;
-SELECT * FROM t1;
-a b
-**** On Slave ****
-SELECT * FROM t1;
-a b
-**** On Master ****
-DROP TABLE t1;
-show binlog events from <binlog_start>;
-Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1
-master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM
-master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1,1), (2,2)
-master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
-master-bin.000001 # Query # # use `test`; DROP TABLE t1
-RESET MASTER;
-STOP SLAVE;
-DROP TABLE IF EXISTS t1;
-DROP TABLE IF EXISTS t1;
-RESET SLAVE;
-START SLAVE;
+Comparing tables master:test.t1 and slave:test.t1
+==== Test using a table with delete triggers ====
**** On Master ****
-SET SESSION BINLOG_FORMAT=ROW;
-SET GLOBAL BINLOG_FORMAT=ROW;
-CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM;
-INSERT INTO t1 VALUES (1,1), (2,2);
-SELECT * FROM t1;
-a b
-1 1
-2 2
-**** On Slave ****
-INSERT INTO t1 VALUE (3,3);
-SELECT * FROM t1;
-a b
-1 1
-2 2
-3 3
+SET @count := 1;
+CREATE TABLE t2 (a INT, b LONG) ENGINE=MyISAM;
+CREATE TRIGGER trg1 BEFORE DELETE ON t1 FOR EACH ROW SET @count := @count + 1;
**** On Master ****
TRUNCATE TABLE t1;
-SELECT * FROM t1;
-a b
-**** On Slave ****
-SELECT * FROM t1;
-a b
-**** On Master ****
-DROP TABLE t1;
-show binlog events from <binlog_start>;
-Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1
-master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM
-master-bin.000001 # Query # # use `test`; BEGIN
-master-bin.000001 # Table_map # # table_id: # (test.t1)
-master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
-master-bin.000001 # Query # # use `test`; COMMIT
-master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
-master-bin.000001 # Query # # use `test`; DROP TABLE t1
-RESET MASTER;
+Comparing tables master:test.t2 and slave:test.t2
+DROP TABLE t1,t2;
+**** Resetting master and slave ****
STOP SLAVE;
-DROP TABLE IF EXISTS t1;
-DROP TABLE IF EXISTS t1;
RESET SLAVE;
-START SLAVE;
-**** On Master ****
-SET SESSION BINLOG_FORMAT=STATEMENT;
-SET GLOBAL BINLOG_FORMAT=STATEMENT;
-CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM;
-INSERT INTO t1 VALUES (1,1), (2,2);
-SELECT * FROM t1;
-a b
-1 1
-2 2
-**** On Slave ****
-INSERT INTO t1 VALUE (3,3);
-SELECT * FROM t1;
-a b
-1 1
-2 2
-3 3
-**** On Master ****
-DELETE FROM t1;
-SELECT * FROM t1;
-a b
-**** On Slave ****
-SELECT * FROM t1;
-a b
-**** On Master ****
-DROP TABLE t1;
-show binlog events from <binlog_start>;
-Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1
-master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM
-master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1,1), (2,2)
-master-bin.000001 # Query # # use `test`; DELETE FROM t1
-master-bin.000001 # Query # # use `test`; DROP TABLE t1
RESET MASTER;
-STOP SLAVE;
-DROP TABLE IF EXISTS t1;
-DROP TABLE IF EXISTS t1;
-RESET SLAVE;
START SLAVE;
**** On Master ****
-SET SESSION BINLOG_FORMAT=MIXED;
-SET GLOBAL BINLOG_FORMAT=MIXED;
CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1,1), (2,2);
-SELECT * FROM t1;
-a b
-1 1
-2 2
-**** On Slave ****
-INSERT INTO t1 VALUE (3,3);
-SELECT * FROM t1;
-a b
-1 1
-2 2
-3 3
**** On Master ****
DELETE FROM t1;
-SELECT * FROM t1;
-a b
-**** On Slave ****
-SELECT * FROM t1;
-a b
-**** On Master ****
-DROP TABLE t1;
-show binlog events from <binlog_start>;
-Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1
-master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM
-master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1,1), (2,2)
-master-bin.000001 # Query # # use `test`; DELETE FROM t1
-master-bin.000001 # Query # # use `test`; DROP TABLE t1
-RESET MASTER;
-STOP SLAVE;
-DROP TABLE IF EXISTS t1;
-DROP TABLE IF EXISTS t1;
-RESET SLAVE;
-START SLAVE;
+Comparing tables master:test.t1 and slave:test.t1
+==== Test using a table with delete triggers ====
**** On Master ****
-SET SESSION BINLOG_FORMAT=ROW;
-SET GLOBAL BINLOG_FORMAT=ROW;
-CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM;
-INSERT INTO t1 VALUES (1,1), (2,2);
-SELECT * FROM t1;
-a b
-1 1
-2 2
-**** On Slave ****
-INSERT INTO t1 VALUE (3,3);
-SELECT * FROM t1;
-a b
-1 1
-2 2
-3 3
+SET @count := 1;
+CREATE TABLE t2 (a INT, b LONG) ENGINE=MyISAM;
+CREATE TRIGGER trg1 BEFORE DELETE ON t1 FOR EACH ROW SET @count := @count + 1;
**** On Master ****
DELETE FROM t1;
-SELECT * FROM t1;
-a b
-**** On Slave ****
-SELECT * FROM t1;
-a b
-3 3
-**** On Master ****
-DROP TABLE t1;
-show binlog events from <binlog_start>;
-Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1
-master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM
-master-bin.000001 # Query # # use `test`; BEGIN
-master-bin.000001 # Table_map # # table_id: # (test.t1)
-master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
-master-bin.000001 # Query # # use `test`; COMMIT
-master-bin.000001 # Query # # use `test`; BEGIN
-master-bin.000001 # Table_map # # table_id: # (test.t1)
-master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F
-master-bin.000001 # Query # # use `test`; COMMIT
-master-bin.000001 # Query # # use `test`; DROP TABLE t1
-RESET MASTER;
+Comparing tables master:test.t2 and slave:test.t2
+DROP TABLE t1,t2;
=== modified file 'mysql-test/suite/rpl/r/rpl_truncate_3innodb.result'
--- a/mysql-test/suite/rpl/r/rpl_truncate_3innodb.result 2007-12-14 13:40:45 +0000
+++ b/mysql-test/suite/rpl/r/rpl_truncate_3innodb.result 2009-02-06 16:06:41 +0000
@@ -4,259 +4,43 @@ reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
+**** Resetting master and slave ****
STOP SLAVE;
-DROP TABLE IF EXISTS t1;
-DROP TABLE IF EXISTS t1;
RESET SLAVE;
-START SLAVE;
-**** On Master ****
-SET SESSION BINLOG_FORMAT=STATEMENT;
-SET GLOBAL BINLOG_FORMAT=STATEMENT;
-CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB;
-INSERT INTO t1 VALUES (1,1), (2,2);
-SELECT * FROM t1;
-a b
-1 1
-2 2
-**** On Slave ****
-INSERT INTO t1 VALUE (3,3);
-SELECT * FROM t1;
-a b
-1 1
-2 2
-3 3
-**** On Master ****
-TRUNCATE TABLE t1;
-SELECT * FROM t1;
-a b
-**** On Slave ****
-SELECT * FROM t1;
-a b
-**** On Master ****
-DROP TABLE t1;
-show binlog events from <binlog_start>;
-Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1
-master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB
-master-bin.000001 # Query # # use `test`; BEGIN
-master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1,1), (2,2)
-master-bin.000001 # Xid # # COMMIT /* XID */
-master-bin.000001 # Query # # use `test`; BEGIN
-master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
-master-bin.000001 # Xid # # COMMIT /* XID */
-master-bin.000001 # Query # # use `test`; DROP TABLE t1
RESET MASTER;
-STOP SLAVE;
-DROP TABLE IF EXISTS t1;
-DROP TABLE IF EXISTS t1;
-RESET SLAVE;
START SLAVE;
**** On Master ****
-SET SESSION BINLOG_FORMAT=MIXED;
-SET GLOBAL BINLOG_FORMAT=MIXED;
CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1,1), (2,2);
-SELECT * FROM t1;
-a b
-1 1
-2 2
-**** On Slave ****
-INSERT INTO t1 VALUE (3,3);
-SELECT * FROM t1;
-a b
-1 1
-2 2
-3 3
**** On Master ****
TRUNCATE TABLE t1;
-SELECT * FROM t1;
-a b
-**** On Slave ****
-SELECT * FROM t1;
-a b
+Comparing tables master:test.t1 and slave:test.t1
+==== Test using a table with delete triggers ====
**** On Master ****
-DROP TABLE t1;
-show binlog events from <binlog_start>;
-Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1
-master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB
-master-bin.000001 # Query # # use `test`; BEGIN
-master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1,1), (2,2)
-master-bin.000001 # Xid # # COMMIT /* XID */
-master-bin.000001 # Query # # use `test`; BEGIN
-master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
-master-bin.000001 # Xid # # COMMIT /* XID */
-master-bin.000001 # Query # # use `test`; DROP TABLE t1
-RESET MASTER;
-STOP SLAVE;
-DROP TABLE IF EXISTS t1;
-DROP TABLE IF EXISTS t1;
-RESET SLAVE;
-START SLAVE;
-**** On Master ****
-SET SESSION BINLOG_FORMAT=ROW;
-SET GLOBAL BINLOG_FORMAT=ROW;
-CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB;
-INSERT INTO t1 VALUES (1,1), (2,2);
-SELECT * FROM t1;
-a b
-1 1
-2 2
-**** On Slave ****
-INSERT INTO t1 VALUE (3,3);
-SELECT * FROM t1;
-a b
-1 1
-2 2
-3 3
+SET @count := 1;
+CREATE TABLE t2 (a INT, b LONG) ENGINE=InnoDB;
+CREATE TRIGGER trg1 BEFORE DELETE ON t1 FOR EACH ROW SET @count := @count + 1;
**** On Master ****
TRUNCATE TABLE t1;
-SELECT * FROM t1;
-a b
-**** On Slave ****
-SELECT * FROM t1;
-a b
-**** On Master ****
-DROP TABLE t1;
-show binlog events from <binlog_start>;
-Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1
-master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB
-master-bin.000001 # Query # # use `test`; BEGIN
-master-bin.000001 # Table_map # # table_id: # (test.t1)
-master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
-master-bin.000001 # Xid # # COMMIT /* XID */
-master-bin.000001 # Query # # use `test`; BEGIN
-master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
-master-bin.000001 # Xid # # COMMIT /* XID */
-master-bin.000001 # Query # # use `test`; DROP TABLE t1
-RESET MASTER;
+Comparing tables master:test.t2 and slave:test.t2
+DROP TABLE t1,t2;
+**** Resetting master and slave ****
STOP SLAVE;
-DROP TABLE IF EXISTS t1;
-DROP TABLE IF EXISTS t1;
RESET SLAVE;
-START SLAVE;
-**** On Master ****
-SET SESSION BINLOG_FORMAT=STATEMENT;
-SET GLOBAL BINLOG_FORMAT=STATEMENT;
-CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB;
-INSERT INTO t1 VALUES (1,1), (2,2);
-SELECT * FROM t1;
-a b
-1 1
-2 2
-**** On Slave ****
-INSERT INTO t1 VALUE (3,3);
-SELECT * FROM t1;
-a b
-1 1
-2 2
-3 3
-**** On Master ****
-DELETE FROM t1;
-SELECT * FROM t1;
-a b
-**** On Slave ****
-SELECT * FROM t1;
-a b
-**** On Master ****
-DROP TABLE t1;
-show binlog events from <binlog_start>;
-Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1
-master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB
-master-bin.000001 # Query # # use `test`; BEGIN
-master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1,1), (2,2)
-master-bin.000001 # Xid # # COMMIT /* XID */
-master-bin.000001 # Query # # use `test`; BEGIN
-master-bin.000001 # Query # # use `test`; DELETE FROM t1
-master-bin.000001 # Xid # # COMMIT /* XID */
-master-bin.000001 # Query # # use `test`; DROP TABLE t1
RESET MASTER;
-STOP SLAVE;
-DROP TABLE IF EXISTS t1;
-DROP TABLE IF EXISTS t1;
-RESET SLAVE;
START SLAVE;
**** On Master ****
-SET SESSION BINLOG_FORMAT=MIXED;
-SET GLOBAL BINLOG_FORMAT=MIXED;
CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1,1), (2,2);
-SELECT * FROM t1;
-a b
-1 1
-2 2
-**** On Slave ****
-INSERT INTO t1 VALUE (3,3);
-SELECT * FROM t1;
-a b
-1 1
-2 2
-3 3
**** On Master ****
DELETE FROM t1;
-SELECT * FROM t1;
-a b
-**** On Slave ****
-SELECT * FROM t1;
-a b
+Comparing tables master:test.t1 and slave:test.t1
+==== Test using a table with delete triggers ====
**** On Master ****
-DROP TABLE t1;
-show binlog events from <binlog_start>;
-Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1
-master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB
-master-bin.000001 # Query # # use `test`; BEGIN
-master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1,1), (2,2)
-master-bin.000001 # Xid # # COMMIT /* XID */
-master-bin.000001 # Query # # use `test`; BEGIN
-master-bin.000001 # Query # # use `test`; DELETE FROM t1
-master-bin.000001 # Xid # # COMMIT /* XID */
-master-bin.000001 # Query # # use `test`; DROP TABLE t1
-RESET MASTER;
-STOP SLAVE;
-DROP TABLE IF EXISTS t1;
-DROP TABLE IF EXISTS t1;
-RESET SLAVE;
-START SLAVE;
-**** On Master ****
-SET SESSION BINLOG_FORMAT=ROW;
-SET GLOBAL BINLOG_FORMAT=ROW;
-CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB;
-INSERT INTO t1 VALUES (1,1), (2,2);
-SELECT * FROM t1;
-a b
-1 1
-2 2
-**** On Slave ****
-INSERT INTO t1 VALUE (3,3);
-SELECT * FROM t1;
-a b
-1 1
-2 2
-3 3
+SET @count := 1;
+CREATE TABLE t2 (a INT, b LONG) ENGINE=InnoDB;
+CREATE TRIGGER trg1 BEFORE DELETE ON t1 FOR EACH ROW SET @count := @count + 1;
**** On Master ****
DELETE FROM t1;
-SELECT * FROM t1;
-a b
-**** On Slave ****
-SELECT * FROM t1;
-a b
-3 3
-**** On Master ****
-DROP TABLE t1;
-show binlog events from <binlog_start>;
-Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1
-master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB
-master-bin.000001 # Query # # use `test`; BEGIN
-master-bin.000001 # Table_map # # table_id: # (test.t1)
-master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
-master-bin.000001 # Xid # # COMMIT /* XID */
-master-bin.000001 # Query # # use `test`; BEGIN
-master-bin.000001 # Table_map # # table_id: # (test.t1)
-master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F
-master-bin.000001 # Xid # # COMMIT /* XID */
-master-bin.000001 # Query # # use `test`; DROP TABLE t1
-RESET MASTER;
+Comparing tables master:test.t2 and slave:test.t2
+DROP TABLE t1,t2;
=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc 2009-01-07 12:11:37 +0000
+++ b/sql/sql_base.cc 2009-02-06 16:06:41 +0000
@@ -5103,8 +5103,15 @@ int decide_logging_format(THD *thd, TABL
{
if (mysql_bin_log.is_open() && (thd->options & OPTION_BIN_LOG))
{
- handler::Table_flags flags_some_set= handler::Table_flags();
- handler::Table_flags flags_all_set= ~handler::Table_flags();
+ /*
+ Compute the starting vectors for the computations by creating a
+ set with all the capabilities bits set and one with no
+ capabilities bits set.
+ */
+ handler::Table_flags flags_some_set= 0;
+ handler::Table_flags flags_all_set=
+ HA_BINLOG_ROW_CAPABLE | HA_BINLOG_STMT_CAPABLE;
+
my_bool multi_engine= FALSE;
void* prev_ht= NULL;
for (TABLE_LIST *table= tables; table; table= table->next_global)
=== modified file 'sql/sql_delete.cc'
--- a/sql/sql_delete.cc 2009-01-09 10:20:32 +0000
+++ b/sql/sql_delete.cc 2009-02-06 16:06:41 +0000
@@ -51,6 +51,11 @@ bool mysql_delete(THD *thd, TABLE_LIST *
THD::killed_state killed_status= THD::NOT_KILLED;
DBUG_ENTER("mysql_delete");
+ THD::enum_binlog_query_type query_type=
+ thd->lex->sql_command == SQLCOM_TRUNCATE ?
+ THD::STMT_QUERY_TYPE :
+ THD::ROW_QUERY_TYPE;
+
if (open_and_lock_tables(thd, table_list))
DBUG_RETURN(TRUE);
if (!(table= table_list->table))
@@ -135,6 +140,11 @@ bool mysql_delete(THD *thd, TABLE_LIST *
DBUG_PRINT("debug", ("Trying to use delete_all_rows()"));
if (!(error=table->file->ha_delete_all_rows()))
{
+ /*
+ If delete_all_rows() is used, it is not possible to log the
+ query in row format, so we have to log it in statement format.
+ */
+ query_type= THD::STMT_QUERY_TYPE;
error= -1; // ok
deleted= maybe_deleted;
goto cleanup;
@@ -374,6 +384,11 @@ cleanup:
{
if (mysql_bin_log.is_open())
{
+ bool const is_trans=
+ thd->lex->sql_command == SQLCOM_TRUNCATE ?
+ FALSE :
+ transactional_table;
+
if (error < 0)
thd->clear_error();
/*
@@ -381,10 +396,13 @@ cleanup:
storage engine does not inject the rows itself, we replicate
statement-based; otherwise, 'ha_delete_row()' was used to
delete specific rows which we might log row-based.
+
+ Note that TRUNCATE TABLE is not transactional and should
+ therefore be treated as a DDL.
*/
- int log_result= thd->binlog_query(THD::ROW_QUERY_TYPE,
+ int log_result= thd->binlog_query(query_type,
thd->query, thd->query_length,
- transactional_table, FALSE, killed_status);
+ is_trans, FALSE, killed_status);
if (log_result && transactional_table)
{
| Thread |
|---|
| • bzr commit into mysql-5.1-bugteam branch (mats:2748) Bug#36763 | Mats Kindahl | 9 Feb |
