List:Commits« Previous MessageNext Message »
From:Alfranio Correia Date:May 17 2010 2:49am
Subject:bzr commit into mysql-trunk-bugfixing branch (alfranio.correia:3045)
Bug#53259
View as plain text  
#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]
Thread
bzr commit into mysql-trunk-bugfixing branch (alfranio.correia:3045)Bug#53259Alfranio Correia17 May
  • Re: bzr commit into mysql-trunk-bugfixing branch(alfranio.correia:3045) Bug#53259Luís Soares19 May
    • Re: bzr commit into mysql-trunk-bugfixing branch(alfranio.correia:3045) Bug#53259Alfranio Correia19 May