Hi Jon,
Could you have some suggestion for improving
the error message for bug#47863? Thanks!
Best Regards,
Daogang
Alfranio Correia wrote:
> Hi Daogang,
>
> Great work.
> Cheers.
>
>
> STATUS
> ------
> Approved Pending Changes
>
> REQUIRED CHANGES
> ----------------
> 1 - Improve the error messages.
> Please, ask Jon what he thinks.
>
> REQUESTS
> --------
> n/a
>
> SUGGESTIONS
> -----------
> 1 - See comments in-line
>
>
> Dao-Gang.Qu@stripped wrote:
>
>> #At file:///home/daogangqu/mysql/bzrwork/bug47863/mysql-5.1-rep%2B3/ based on
> revid:aelkin@stripped
>>
>> 3125 Dao-Gang.Qu@stripped 2009-12-10
>> Bug #47863 binlog_format should be writable only at transaction
> boundaries
>>
>> When @@session.binlog_format is modified inside a transaction,
>> it can cause slave to go out of sync.
>>
>> To fix the problem, make the SESSION variable 'binlog_format'
>> read-only inside a transaction.
>>
>
> s/SESSION/session/
>
>
>
>> @ mysql-test/suite/rpl/r/rpl_binlog_format.result
>> Test Result for bug#47863.
>> @ mysql-test/suite/rpl/t/rpl_binlog_format.test
>> Added the test file to verify if the session variable 'binlog_format'
>> is read-only in transaction and sub-statements, and is writable at
>> transaction boundaries.
>>
>
> s/in/inside/
>
>
>> @ sql/set_var.cc
>> Added code to make the SESSION variable 'binlog_format'
>> read-only inside a transaction.
>>
>
> s/SESSION/session/
>
>
>
>> added:
>> mysql-test/suite/rpl/r/rpl_binlog_format.result
>> mysql-test/suite/rpl/t/rpl_binlog_format.test
>> modified:
>> sql/set_var.cc
>> sql/share/errmsg.txt
>> === added file 'mysql-test/suite/rpl/r/rpl_binlog_format.result'
>> --- a/mysql-test/suite/rpl/r/rpl_binlog_format.result 1970-01-01 00:00:00 +0000
>> +++ b/mysql-test/suite/rpl/r/rpl_binlog_format.result 2009-12-10 09:47:58 +0000
>> @@ -0,0 +1,66 @@
>> +stop slave;
>> +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
>> +reset master;
>> +reset slave;
>> +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
>> +start slave;
>> +call mtr.add_suppression("Unsafe statement binlogged in statement format "
>> + "since BINLOG_FORMAT .* STATEMENT. Reason for "
>> + "unsafeness: Non-transactional reads or writes "
>> + "are unsafe if they occur after transactional "
>> + "reads or writes inside a transaction.");
>> +create table t1 (a int) engine= myisam;
>> +create table t2 (a int) engine= innodb;
>> +set @@session.binlog_format= statement;
>> +# The value of 'binlog_format' is changed to STATEMENT.
>> +insert into t1 values (1);
>> +begin;
>> +insert into t2 select * from t1;
>> +Warnings:
>> +Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT =
> STATEMENT. Reason for unsafeness: Non-transactional reads or writes are unsafe if they
> occur after transactional reads or writes inside a transaction.
>> +# Check the session variable 'binlog_format' is read-only
>> +# inside a transaction.
>> +set @@session.binlog_format= row;
>> +ERROR HY000: Cannot change the binary logging format inside a transaction
>> +insert into t1 values (2);
>> +Warnings:
>> +Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT =
> STATEMENT. Reason for unsafeness: Non-transactional reads or writes are unsafe if they
> occur after transactional reads or writes inside a transaction.
>> +commit;
>> +# The value of 'binlog_format' is not changed to ROW.
>> +select * from t2;
>> +a
>> +1
>> +select * from t1;
>> +a
>> +1
>> +2
>> +select * from t2;
>> +a
>> +1
>> +select * from t1;
>> +a
>> +1
>> +2
>> +create table t3(a int, b int) engine= innodb;
>> +create table t4(a int) engine= innodb;
>> +create table t5(a int) engine= innodb;
>> +create trigger tr2 after insert on t3 for each row begin
>> +insert into t4(a) values(1);
>> +set @@session.binlog_format= row;
>> +insert into t4(a) values(2);
>> +insert into t5(a) values(3);
>> +end |
>> +# Check the session variable 'binlog_format' is read-only
>> +# in sub-statements.
>> +insert into t3(a,b) values(1,1);
>> +ERROR HY000: Cannot change the binary logging format inside a stored function or
> trigger
>> +# The value of 'binlog_format' is not changed to ROW.
>> +select * from t4;
>> +a
>> +select * from t4;
>> +a
>> +drop table t1;
>> +drop table t2;
>> +drop table t3;
>> +drop table t4;
>> +drop table t5;
>>
>> === added file 'mysql-test/suite/rpl/t/rpl_binlog_format.test'
>> --- a/mysql-test/suite/rpl/t/rpl_binlog_format.test 1970-01-01 00:00:00 +0000
>> +++ b/mysql-test/suite/rpl/t/rpl_binlog_format.test 2009-12-10 09:47:58 +0000
>> @@ -0,0 +1,85 @@
>> +#
>> +# BUG#47863
>> +# This test verifies if the session variable 'binlog_format'
>> +# is read-only inside a transaction and in sub-statements.
>> +#
>> +
>> +source include/master-slave.inc;
>> +source include/have_innodb.inc;
>> +source include/have_binlog_format_row.inc;
>> +
>> +call mtr.add_suppression("Unsafe statement binlogged in statement format "
>> + "since BINLOG_FORMAT .* STATEMENT. Reason for "
>> + "unsafeness: Non-transactional reads or writes "
>> + "are unsafe if they occur after transactional "
>> + "reads or writes inside a transaction.");
>> +create table t1 (a int) engine= myisam;
>> +create table t2 (a int) engine= innodb;
>> +
>> +set @@session.binlog_format= statement;
>> +if (`SELECT @@session.binlog_format = 'STATEMENT'`)
>> +{
>> + --echo # The value of 'binlog_format' is changed to STATEMENT.
>> +}
>> +
>> +insert into t1 values (1);
>> +
>> +begin;
>> + insert into t2 select * from t1;
>> +--echo # Check the session variable 'binlog_format' is read-only
>> +--echo # inside a transaction.
>> +--error 1658
>> + set @@session.binlog_format= row;
>> + insert into t1 values (2);
>> +commit;
>> +
>> +if (`SELECT @@session.binlog_format = 'STATEMENT'`)
>> +{
>> + --echo # The value of 'binlog_format' is not changed to ROW.
>> +}
>> +
>> +select * from t2;
>> +select * from t1;
>> +
>> +sync_slave_with_master;
>> +connection slave;
>> +select * from t2;
>> +select * from t1;
>> +
>> +connection master;
>> +create table t3(a int, b int) engine= innodb;
>> +create table t4(a int) engine= innodb;
>> +create table t5(a int) engine= innodb;
>> +delimiter |;
>> +eval create trigger tr2 after insert on t3 for each row begin
>> + insert into t4(a) values(1);
>> + set @@session.binlog_format= row;
>> + insert into t4(a) values(2);
>> + insert into t5(a) values(3);
>> +end |
>> +delimiter ;|
>> +
>> +--echo # Check the session variable 'binlog_format' is read-only
>> +--echo # in sub-statements.
>> +--error 1560
>> +insert into t3(a,b) values(1,1);
>> +
>> +if (`SELECT @@session.binlog_format = 'STATEMENT'`)
>> +{
>> + --echo # The value of 'binlog_format' is not changed to ROW.
>> +}
>> +
>> +select * from t4;
>> +
>> +sync_slave_with_master;
>> +connection slave;
>> +select * from t4;
>> +
>> +connection master;
>> +drop table t1;
>> +drop table t2;
>> +drop table t3;
>> +drop table t4;
>> +drop table t5;
>> +sync_slave_with_master;
>> +
>>
>> === modified file 'sql/set_var.cc'
>> --- a/sql/set_var.cc 2009-11-30 18:20:26 +0000
>> +++ b/sql/set_var.cc 2009-12-10 09:47:58 +0000
>> @@ -1288,6 +1288,14 @@ bool sys_var_thd_binlog_format::is_reado
>> my_error(ER_STORED_FUNCTION_PREVENTS_SWITCH_BINLOG_FORMAT, MYF(0));
>> return 1;
>> }
>> + /*
>> + Make the SESSION variable 'binlog_format' read-only inside a transaction.
>> + */
>> + if (thd->active_transaction())
>> + {
>> + my_error(ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT, MYF(0));
>> + return 1;
>> + }
>> return sys_var_thd_enum::is_readonly();
>> }
>>
>>
>> === modified file 'sql/share/errmsg.txt'
>> --- a/sql/share/errmsg.txt 2009-11-30 18:20:26 +0000
>> +++ b/sql/share/errmsg.txt 2009-12-10 09:47:58 +0000
>> @@ -6249,3 +6249,6 @@ ER_DEBUG_SYNC_TIMEOUT
>> ER_DEBUG_SYNC_HIT_LIMIT
>> eng "debug sync point hit limit reached"
>> ger "Debug Sync Point Hit Limit erreicht"
>> +ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT
>> + eng "Cannot change the binary logging format inside a transaction"
>>
>
> Improve the message: "It is not possible to change the binary logging format within a
> transaction"
>
>
>> + ger "Das Bin?og-Format kann innerhalb einer transaction"
>>
>
> I don't speak german but google does not like it. :)
>
>
>>
>> ------------------------------------------------------------------------
>>
>>
>>
