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.

6 files changed, 103 insertions, 0 deletions

diff --git a/mysql-test/suite/binlog/r/binlog_truncate_innodb.result b/mysql-test/suite/binlog/r/binlog_truncate_innodb.result
new file mode 100644
index 00000000000..ab237898a74
--- /dev/null
+++ b/mysql-test/suite/binlog/r/binlog_truncate_innodb.result
@@ -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;
diff --git a/mysql-test/suite/binlog/r/binlog_truncate_myisam.result b/mysql-test/suite/binlog/r/binlog_truncate_myisam.result
new file mode 100644
index 00000000000..544882c2c9b
--- /dev/null
+++ b/mysql-test/suite/binlog/r/binlog_truncate_myisam.result
@@ -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;
diff --git a/mysql-test/suite/binlog/t/binlog_truncate_innodb-master.opt b/mysql-test/suite/binlog/t/binlog_truncate_innodb-master.opt
new file mode 100644
index 00000000000..69cc489a969
--- /dev/null
+++ b/mysql-test/suite/binlog/t/binlog_truncate_innodb-master.opt
@@ -0,0 +1 @@
+--loose-innodb
\ No newline at end of file
diff --git a/mysql-test/suite/binlog/t/binlog_truncate_innodb.test b/mysql-test/suite/binlog/t/binlog_truncate_innodb.test
new file mode 100644
index 00000000000..9695710377e
--- /dev/null
+++ b/mysql-test/suite/binlog/t/binlog_truncate_innodb.test
@@ -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;
diff --git a/mysql-test/suite/binlog/t/binlog_truncate_myisam.test b/mysql-test/suite/binlog/t/binlog_truncate_myisam.test
new file mode 100644
index 00000000000..994647ab78a
--- /dev/null
+++ b/mysql-test/suite/binlog/t/binlog_truncate_myisam.test
@@ -0,0 +1,4 @@
+source include/have_log_bin.inc;
+
+let $engine = MyISAM;
+source extra/binlog_tests/binlog_truncate.test;
diff --git a/mysql-test/suite/binlog/t/disabled.def b/mysql-test/suite/binlog/t/disabled.def
index 888298bbb09..0018387de94 100644
--- a/mysql-test/suite/binlog/t/disabled.def
+++ b/mysql-test/suite/binlog/t/disabled.def
@@ -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
+