Hi Mats,
Nice work, patch approved!
I have only some minor comments in line.
Mats Kindahl wrote:
> #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 @@
Please add comments on variable $before_truncate, which can be set
before sourcing this test file.
> +# 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
> -
very nice! switch binlog-format in the test is really a bad idea.
>
> === 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
Please add some comments on this helper test file, for example, how it
works, and the variables needed before source this file ($trun_stmt).
> @@ -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;
> +
Nice, this is much more understandable than the old code.
> 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);
>
>
