#At file:///home/acorreia/workspace.sun/repository.mysql/bzrwork/bug-53259/mysql-trunk-bugfixing/ based on revid:alik@stripped
3045 Alfranio Correia 2010-05-17
BUG#53259 Unsafe statement binlogged in statement format w/MyIsam temp tables
After this patch, when there is no on-going transaction temporary tables are
handled according to the type of engine where they were created. However, when
there is an on-going transaction temporary tables are handled as transactional
tables in the sense that statements that access them are written temporary
tables are written to the binary log upon committing the transaction, keeping
the behavior after BUG#51894.
So, when temporary tables are accessed the following cases generate warning
messages:
1. No on-going transaction:
1.1. INSERT INTO innodb_t SELECT * FROM myisam_temporary;
1.2. INSERT INTO myisam_temporary SELECT * FROM innodb_t;
1.3. INSERT INTO myisam_t SELECT * FROM innodb_temporary;
1.4. INSERT INTO innodb_temporary SELECT * FROM myisam_t;
2. On-going transaction:
2.1. INSERT INTO myisam_t SELECT * FROM myisam_temporary;
2.2. INSERT INTO myisam_temporary SELECT * FROM myisam_t;
modified:
mysql-test/suite/binlog/r/binlog_stm_binlog.result
mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result
mysql-test/suite/rpl/r/rpl_temp_temporary.result
mysql-test/suite/rpl/t/rpl_temp_temporary.test
sql/sql_class.cc
=== modified file 'mysql-test/suite/binlog/r/binlog_stm_binlog.result'
--- a/mysql-test/suite/binlog/r/binlog_stm_binlog.result 2010-04-20 09:10:43 +0000
+++ b/mysql-test/suite/binlog/r/binlog_stm_binlog.result 2010-05-17 02:49:30 +0000
@@ -639,7 +639,9 @@ COERCIBILITY(NAME_CONST('s1', _utf8'test
COERCIBILITY(s1) d3;
DROP TEMPORARY TABLE tmp1;
END
-master-bin.000001 # Query # # use `bug39182`; DROP TEMPORARY TABLE IF EXISTS `tmp1` /* generated by server */
+master-bin.000001 # Query # # use `bug39182`; CREATE TEMPORARY TABLE tmp1
+SELECT * FROM t1 WHERE a LIKE CONCAT("%", NAME_CONST('s1',_utf8'test' COLLATE 'utf8_unicode_ci'), "%")
+master-bin.000001 # Query # # use `bug39182`; DROP TEMPORARY TABLE tmp1
DROP PROCEDURE p1;
DROP TABLE t1;
DROP DATABASE bug39182;
=== modified file 'mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result'
--- a/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result 2010-04-28 12:47:49 +0000
+++ b/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result 2010-05-17 02:49:30 +0000
@@ -472,6 +472,8 @@ select count(*) from ti /* zero */;
count(*)
0
insert into ti select * from tt;
+Warnings:
+Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements that read from both transactional (or a temporary table of any engine type) and non-transactional tables and write to any of them are unsafe.
select * from ti /* that is what slave would miss - a bug */;
a
1
@@ -498,6 +500,8 @@ select count(*) from ti /* zero */;
count(*)
0
insert into ti select * from tt;
+Warnings:
+Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements that read from both transactional (or a temporary table of any engine type) and non-transactional tables and write to any of them are unsafe.
select * from tt /* that is what otherwise slave missed - the bug */;
a
1
@@ -730,6 +734,8 @@ select count(*) from ti /* zero */;
count(*)
0
insert into ti select * from tt;
+Warnings:
+Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements that read from both transactional (or a temporary table of any engine type) and non-transactional tables and write to any of them are unsafe.
select * from ti /* that is what slave would miss - bug#28960 */;
a
1
@@ -756,6 +762,8 @@ select count(*) from ti /* zero */;
count(*)
0
insert into ti select * from tt;
+Warnings:
+Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements that read from both transactional (or a temporary table of any engine type) and non-transactional tables and write to any of them are unsafe.
select * from tt /* that is what otherwise slave missed - the bug */;
a
1
=== modified file 'mysql-test/suite/rpl/r/rpl_temp_temporary.result'
--- a/mysql-test/suite/rpl/r/rpl_temp_temporary.result 2010-04-28 12:47:49 +0000
+++ b/mysql-test/suite/rpl/r/rpl_temp_temporary.result 2010-05-17 02:49:30 +0000
@@ -21,6 +21,11 @@ CREATE TEMPORARY TABLE t_myisam_temp(id
INSERT INTO t_myisam_temp VALUES(1);
CREATE TEMPORARY TABLE t_innodb_temp(id int) engine= Innodb;
INSERT INTO t_innodb_temp VALUES(1);
+INSERT INTO t_myisam SELECT * FROM t_myisam_temp;
+INSERT INTO t_innodb SELECT * FROM t_myisam_temp;
+Warnings:
+Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements that read from both transactional (or a temporary table of any engine type) and non-transactional tables and write to any of them are unsafe.
+INSERT INTO t_innodb SELECT * FROM t_innodb_temp;
BEGIN;
INSERT INTO t_myisam SELECT * FROM t_myisam_temp;
Warnings:
@@ -30,6 +35,13 @@ INSERT INTO t_innodb SELECT * FROM t_inn
ROLLBACK;
Warnings:
Warning 1196 Some non-transactional changed tables couldn't be rolled back
+INSERT INTO t_myisam SELECT * FROM t_innodb_temp;
+Warnings:
+Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements that read from both transactional (or a temporary table of any engine type) and non-transactional tables and write to any of them are unsafe.
+INSERT INTO t_innodb SELECT * FROM t_myisam_temp;
+Warnings:
+Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements that read from both transactional (or a temporary table of any engine type) and non-transactional tables and write to any of them are unsafe.
+INSERT INTO t_innodb SELECT * FROM t_innodb_temp;
BEGIN;
INSERT INTO t_myisam SELECT * FROM t_innodb_temp;
Warnings:
@@ -61,11 +73,29 @@ show binlog events from <binlog_start>;
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query # # BEGIN
master-bin.000001 # Query # # use `test`; INSERT INTO t_myisam SELECT * FROM t_myisam_temp
+master-bin.000001 # Query # # COMMIT
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; INSERT INTO t_innodb SELECT * FROM t_myisam_temp
+master-bin.000001 # Xid # # COMMIT /* XID */
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; INSERT INTO t_innodb SELECT * FROM t_innodb_temp
+master-bin.000001 # Xid # # COMMIT /* XID */
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; INSERT INTO t_myisam SELECT * FROM t_myisam_temp
master-bin.000001 # Query # # use `test`; INSERT INTO t_innodb SELECT * FROM t_myisam_temp
master-bin.000001 # Query # # use `test`; INSERT INTO t_innodb SELECT * FROM t_innodb_temp
master-bin.000001 # Query # # ROLLBACK
master-bin.000001 # Query # # BEGIN
master-bin.000001 # Query # # use `test`; INSERT INTO t_myisam SELECT * FROM t_innodb_temp
+master-bin.000001 # Query # # COMMIT
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; INSERT INTO t_innodb SELECT * FROM t_myisam_temp
+master-bin.000001 # Xid # # COMMIT /* XID */
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; INSERT INTO t_innodb SELECT * FROM t_innodb_temp
+master-bin.000001 # Xid # # COMMIT /* XID */
+master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Query # # use `test`; INSERT INTO t_myisam SELECT * FROM t_innodb_temp
master-bin.000001 # Query # # use `test`; INSERT INTO t_innodb SELECT * FROM t_myisam_temp
master-bin.000001 # Query # # use `test`; INSERT INTO t_innodb SELECT * FROM t_innodb_temp
master-bin.000001 # Query # # ROLLBACK
=== modified file 'mysql-test/suite/rpl/t/rpl_temp_temporary.test'
--- a/mysql-test/suite/rpl/t/rpl_temp_temporary.test 2010-04-28 12:47:49 +0000
+++ b/mysql-test/suite/rpl/t/rpl_temp_temporary.test 2010-05-17 02:49:30 +0000
@@ -5,7 +5,8 @@
#
# This test verifies what follows:
#
-# 1 - If the following statements are classified as unsafe:
+# 1 - If the following statements are classified as unsafe when there is
+# an on going transaction:
# 1.1 - INSERT INTO t_myisam SELECT * FROM t_myisam_temp
# 1.2 - INSERT INTO t_myisam_temp SELECT * FROM t_myisam
#
@@ -66,12 +67,18 @@ CREATE TEMPORARY TABLE t_innodb_temp(id
INSERT INTO t_innodb_temp VALUES(1);
let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
+INSERT INTO t_myisam SELECT * FROM t_myisam_temp;
+INSERT INTO t_innodb SELECT * FROM t_myisam_temp;
+INSERT INTO t_innodb SELECT * FROM t_innodb_temp;
BEGIN;
INSERT INTO t_myisam SELECT * FROM t_myisam_temp;
INSERT INTO t_innodb SELECT * FROM t_myisam_temp;
INSERT INTO t_innodb SELECT * FROM t_innodb_temp;
ROLLBACK;
+INSERT INTO t_myisam SELECT * FROM t_innodb_temp;
+INSERT INTO t_innodb SELECT * FROM t_myisam_temp;
+INSERT INTO t_innodb SELECT * FROM t_innodb_temp;
BEGIN;
INSERT INTO t_myisam SELECT * FROM t_innodb_temp;
INSERT INTO t_innodb SELECT * FROM t_myisam_temp;
=== modified file 'sql/sql_class.cc'
--- a/sql/sql_class.cc 2010-05-14 05:28:51 +0000
+++ b/sql/sql_class.cc 2010-05-17 02:49:30 +0000
@@ -3714,12 +3714,13 @@ int THD::decide_logging_format(TABLE_LIS
{
my_bool prev_trans;
my_bool act_trans;
- if (prev_access_table->s->tmp_table || table->table->s->tmp_table)
+ if (in_multi_stmt_transaction() &&
+ (prev_access_table->s->tmp_table || table->table->s->tmp_table))
{
prev_trans= prev_access_table->s->tmp_table ? TRUE :
- prev_access_table->file->has_transactions();
+ prev_access_table->file->has_transactions();
act_trans= table->table->s->tmp_table ? TRUE :
- table->table->file->has_transactions();
+ table->table->file->has_transactions();
}
else
{
@@ -3836,7 +3837,7 @@ int THD::decide_logging_format(TABLE_LIS
(flags_write_some_set & HA_HAS_OWN_BINLOGGING))
my_error((error= ER_BINLOG_MULTIPLE_ENGINES_AND_SELF_LOGGING_ENGINE),
MYF(0));
- else if (multi_access_engine && flags_some_set & HA_HAS_OWN_BINLOGGING)
+ else if (multi_access_engine && (flags_some_set & HA_HAS_OWN_BINLOGGING))
lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_MULTIPLE_ENGINES_AND_SELF_LOGGING_ENGINE);
/* both statement-only and row-only engines involved */
Attachment: [text/bzr-bundle]
