MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Mats Kindahl Date:February 3 2009 12:14pm
Subject:bzr commit into mysql-5.1-bugteam branch (mats:2748) Bug#36763
View as plain text  
#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);

Thread
bzr commit into mysql-5.1-bugteam branch (mats:2748) Bug#36763Mats Kindahl3 Feb
  • Re: bzr commit into mysql-5.1-bugteam branch (mats:2748) Bug#36763He Zhenxing6 Feb