#At file:///home/bzr/bugs/b36763-mysql-5.1-bugteam/ based on revid:timothy.smith@stripped
2748 Mats Kindahl 2009-02-03
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/rpl/r/rpl_truncate_2myisam.result
mysql-test/suite/rpl/r/rpl_truncate_3innodb.result
sql/sql_base.cc
sql/sql_delete.cc
storage/innobase/handler/ha_innodb.cc
=== 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-03 12:14:22 +0000
@@ -0,0 +1,23 @@
+# 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.
+
+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-03 12:14:22 +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-03 12:14:22 +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-03 12:14:22 +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-03 12:14:22 +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-03 12:14:22 +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-03 12:14:22 +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-03 12:14:22 +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/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-03 12:14:22 +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-03 12:14:22 +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-03 12:14:22 +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-03 12:14:22 +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)
{
=== modified file 'storage/innobase/handler/ha_innodb.cc'
--- a/storage/innobase/handler/ha_innodb.cc 2009-01-16 16:49:07 +0000
+++ b/storage/innobase/handler/ha_innodb.cc 2009-02-03 12:14:22 +0000
@@ -2278,9 +2278,15 @@ handler::Table_flags
ha_innobase::table_flags() const
{
/* Need to use tx_isolation here since table flags is (also)
- called before prebuilt is inited. */
- ulong const tx_isolation = thd_tx_isolation(current_thd);
- if (tx_isolation <= ISO_READ_COMMITTED)
+ called before prebuilt is inited.
+
+ See ha_innobase::external_lock() for information on the
+ treatement of TRUNCATE TABLE. */
+ THD *thd = current_thd;
+ ulong const tx_isolation = thd_tx_isolation(thd);
+ ulong const sql_command = thd_sql_command(thd);
+ if (sql_command != SQLCOM_TRUNCATE
+ && tx_isolation <= ISO_READ_COMMITTED)
return int_table_flags;
return int_table_flags | HA_BINLOG_STMT_CAPABLE;
}
@@ -6790,8 +6796,20 @@ ha_innobase::external_lock(
/* Statement based binlogging does not work in isolation level
READ UNCOMMITTED and READ COMMITTED since the necessary
locks cannot be taken. In this case, we print an
- informative error message and return with an error. */
- if (lock_type == F_WRLCK)
+ informative error message and return with an error.
+
+ We treat TRUNCATE TABLE specially, since it will be replicated
+ as a statement and is roughly equivalent to the following
+ sequences of statements:
+
+ LOCK TABLE t1 WRITE;
+ CREATE TEMPORARY TABLE __tt1 LIKE t1;
+ DROP TABLE t1;
+ CREATE TABLE t1 LIKE __tt1;
+ DROP TABLE __tt1;
+ UNLOCK TABLES;
+ */
+ if (thd_sql_command(thd) != SQLCOM_TRUNCATE && lock_type == F_WRLCK)
{
ulong const binlog_format= thd_binlog_format(thd);
ulong const tx_isolation = thd_tx_isolation(current_thd);
