Hi Mats
I think there is an inconsistent behaviour for the following statements:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
TRUNCATE TABLE t1;
On one hand, if we run this with binlog format set to STATEMENT, it will
fail with the error:
Binary logging not possible. Message: Transaction level
'READ-UNCOMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'
So we cannot log the TRUNCATE statement in STATEMENT format.
On the other hand, if we run this with binlog format set to MIXED, the
TRUNCATE statement **WILL BE LOGGED IN STATEMENT FORMAT**, which is
inconsistent with the senarior above.
So I think we should fail for this case too.
On 2008-05-26 Mon 21:43 +0200, Mats Kindahl wrote:
> Below is the list of changes that have just been committed into a local
> 5.1 repository of mats. When mats does a push these changes
> will be propagated to the main repository and, within 24 hours after the
> push, to the public repository.
> For information on how to access the public repository
> see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
>
> ChangeSet@stripped, 2008-05-26 21:43:15+02:00, mats@mats-laptop.(none) +3 -0
> Bug #36763:
> TRUNCATE TABLE fails to replicate when stmt-based binlogging is
> not supported.
>
> When executing a TRUNCATE TABLE under MIXED mode, the TRUNCATE statement was
> not written to the binary log. The reason for this was that the statement
> was assumed to be a "row query type", meaning that the statement is logged
> only if the current statement is not row-based. Since InnoDB forces row-based
> logging for statements in READ UNCOMMITTED isolation level, this caused the
> statement to not be logged.
>
> The problem is fixed by deliberately marking TRUNCATE TABLE statements to be
> "statement query types", meaning that they are always logged as statements.
>
> mysql-test/suite/rpl/r/rpl_truncate_3innodb.result@stripped, 2008-05-26 21:43:11+02:00,
> mats@mats-laptop.(none) +12 -0
> Result file change.
>
> mysql-test/suite/rpl/t/rpl_truncate_3innodb.test@stripped, 2008-05-26 21:43:12+02:00,
> mats@mats-laptop.(none) +32 -0
> Adding code to test that truncate is replicated correctly even for InnoDB.
>
> sql/sql_delete.cc@stripped, 2008-05-26 21:43:12+02:00, mats@mats-laptop.(none) +4 -1
> Adding code to mark that TRUNCATE TABLE queries are always replicated
> as statements.
>
> diff -Nrup a/mysql-test/suite/rpl/r/rpl_truncate_3innodb.result
> b/mysql-test/suite/rpl/r/rpl_truncate_3innodb.result
> --- a/mysql-test/suite/rpl/r/rpl_truncate_3innodb.result 2007-12-14 14:40:43 +01:00
> +++ b/mysql-test/suite/rpl/r/rpl_truncate_3innodb.result 2008-05-26 21:43:11 +02:00
> @@ -260,3 +260,15 @@ master-bin.000001 # Delete_rows # # tabl
> master-bin.000001 # Xid # # COMMIT /* XID */
> master-bin.000001 # Query # # use `test`; DROP TABLE t1
> RESET MASTER;
> +[on master]
> +SET BINLOG_FORMAT=MIXED;
> +CREATE TABLE t1 (a INT) ENGINE=INNODB;
> +INSERT INTO t1 VALUES (1),(2),(3);
> +SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
> +TRUNCATE TABLE t1;
> +SELECT * FROM t1;
> +a
> +[on slave]
> +SELECT * FROM t1;
> +a
> +DROP TABLE t1;
> diff -Nrup a/mysql-test/suite/rpl/t/rpl_truncate_3innodb.test
> b/mysql-test/suite/rpl/t/rpl_truncate_3innodb.test
> --- a/mysql-test/suite/rpl/t/rpl_truncate_3innodb.test 2007-06-27 14:27:33 +02:00
> +++ b/mysql-test/suite/rpl/t/rpl_truncate_3innodb.test 2008-05-26 21:43:12 +02:00
> @@ -4,3 +4,35 @@
>
> let $engine=InnoDB;
> --source extra/rpl_tests/rpl_truncate.test
> +
> +# Resetting master and slave
> +disable_query_log;
> +connection slave;
> +STOP SLAVE;
> +source include/wait_for_slave_to_stop.inc;
> +connection master;
> +RESET MASTER;
> +connection slave;
> +RESET SLAVE;
> +START SLAVE;
> +source include/wait_for_slave_to_start.inc;
> +enable_query_log;
> +
> +# BUG #36763: TRUNCATE TABLE fails to replicate when stmt-based
> +# binlogging is not supported.
> +
> +--echo [on master]
> +connection master;
> +SET BINLOG_FORMAT=MIXED;
> +CREATE TABLE t1 (a INT) ENGINE=INNODB;
> +INSERT INTO t1 VALUES (1),(2),(3);
> +SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
> +TRUNCATE TABLE t1;
> +SELECT * FROM t1;
> +--echo [on slave]
> +sync_slave_with_master;
> +SELECT * FROM t1;
> +
> +connection master;
> +DROP TABLE t1;
> +sync_slave_with_master;
> diff -Nrup a/sql/sql_delete.cc b/sql/sql_delete.cc
> --- a/sql/sql_delete.cc 2008-03-27 13:03:58 +01:00
> +++ b/sql/sql_delete.cc 2008-05-26 21:43:12 +02:00
> @@ -382,7 +382,10 @@ cleanup:
> statement-based; otherwise, 'ha_delete_row()' was used to
> delete specific rows which we might log row-based.
> */
> - int log_result= thd->binlog_query(THD::ROW_QUERY_TYPE,
> + THD::enum_binlog_query_type query_type=
> + thd->lex->sql_command == SQLCOM_TRUNCATE ?
> + THD::STMT_QUERY_TYPE : THD::ROW_QUERY_TYPE;
> + int log_result= thd->binlog_query(query_type,
> thd->query, thd->query_length,
> transactional_table, FALSE, killed_status);
>
