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.


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).

3 files changed, 51 insertions, 50 deletions

diff --git a/mysql-test/extra/binlog_tests/binlog_truncate.test b/mysql-test/extra/binlog_tests/binlog_truncate.test
new file mode 100644
index 00000000000..dce33b3cef0
--- /dev/null
+++ b/mysql-test/extra/binlog_tests/binlog_truncate.test
@@ -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;
diff --git a/mysql-test/extra/rpl_tests/rpl_truncate.test b/mysql-test/extra/rpl_tests/rpl_truncate.test
index bca53336514..7036ab126e1 100644
--- a/mysql-test/extra/rpl_tests/rpl_truncate.test
+++ b/mysql-test/extra/rpl_tests/rpl_truncate.test
@@ -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
-
diff --git a/mysql-test/extra/rpl_tests/rpl_truncate_helper.test b/mysql-test/extra/rpl_tests/rpl_truncate_helper.test
index 76db74acfa1..cd1ce93177a 100644
--- a/mysql-test/extra/rpl_tests/rpl_truncate_helper.test
+++ b/mysql-test/extra/rpl_tests/rpl_truncate_helper.test
@@ -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;