MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Andrei Elkin Date:June 15 2007 10:59am
Subject:bk commit into 5.0 tree (aelkin:1.2440) BUG#23333
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of elkin. When elkin 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, 2007-06-15 13:59:18+03:00, aelkin@stripped +8 -0
  Bug #23333 stored function + non-transac table + transac table = breaks stmt-based binlog
  
  Binlogging of the statement with a side effect like a modified non-trans table did not happen.
  The artifact involved all binloggable dml queries.
  
  Fixed with changing the binlogging conditions all over the code to exploit thd->no_trans_update.top introduced
  by the patch for bug#27417.
  
  Multi-delete case has own specific addressed by another bug_29136. Multi-update case has been addressed by bug#27716 and
  patch and will need merging.

  mysql-test/r/sp_trans.result@stripped, 2007-06-15 13:59:11+03:00, aelkin@stripped +3 -2
    results changed

  mysql-test/r/stored_routines_side_effect.result@stripped, 2007-06-15 13:59:11+03:00, aelkin@stripped +72 -2
    results changed

  mysql-test/t/sp_trans.test@stripped, 2007-06-15 13:59:11+03:00, aelkin@stripped +1 -2
    making check of the part relating to bug#23333 compatible with other tests. Fixing comments that got
    obsolete after fix for the current bug. 

  mysql-test/t/stored_routines_side_effect.test@stripped, 2007-06-15 13:59:11+03:00, aelkin@stripped +114 -2
    basic tests involving most of binloggable dml. Multi-update,delete tests are in separate patches (separete bugs)

  sql/sql_delete.cc@stripped, 2007-06-15 13:59:11+03:00, aelkin@stripped +3 -2
    deploying the binlogging check with thd->no_trans_update.top

  sql/sql_insert.cc@stripped, 2007-06-15 13:59:12+03:00, aelkin@stripped +14 -13
    binlogging when thd->no_trans_update.top is TRUE

  sql/sql_load.cc@stripped, 2007-06-15 13:59:12+03:00, aelkin@stripped +2 -2
    binlogging when thd->no_trans_update.top is true

  sql/sql_update.cc@stripped, 2007-06-15 13:59:12+03:00, aelkin@stripped +1 -1
    binlogging when thd->no_trans_update.top is true

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	aelkin
# Host:	dsl-hkibras1-ff5dc300-70.dhcp.inet.fi
# Root:	/home/elkin/MySQL/TEAM/FIXES/5.0/bug23333-sf_and_nonta

--- 1.198/sql/sql_delete.cc	2007-06-12 20:32:21 +03:00
+++ 1.199/sql/sql_delete.cc	2007-06-15 13:59:11 +03:00
@@ -322,7 +322,7 @@ cleanup:
   delete select;
   thd->no_trans_update.top|= thd->no_trans_update.stmt;
   /* See similar binlogging code in sql_update.cc, for comments */
-  if ((error < 0) || (deleted && !transactional_table))
+  if ((error < 0) || thd->no_trans_update.top)
   {
     if (mysql_bin_log.is_open())
     {
@@ -824,7 +824,8 @@ bool multi_delete::send_eof()
     query_cache_invalidate3(thd, delete_tables, 1);
   }
   thd->no_trans_update.top|= thd->no_trans_update.stmt;
-  if ((local_error == 0) || (deleted && normal_tables))
+  DBUG_ASSERT(!normal_tables || !deleted || thd->no_trans_update.stmt);
+  if ((local_error == 0) || thd->no_trans_update.top)
   {
     if (mysql_bin_log.is_open())
     {

--- 1.231/sql/sql_insert.cc	2007-06-12 20:32:21 +03:00
+++ 1.232/sql/sql_insert.cc	2007-06-15 13:59:12 +03:00
@@ -755,8 +755,8 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
 
     transactional_table= table->file->has_transactions();
 
-    thd->no_trans_update.top|= thd->no_trans_update.stmt;
     if ((changed= (info.copied || info.deleted || info.updated)))
+      
     {
       /*
         Invalidate the table in the query cache if something changed.
@@ -764,20 +764,21 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
         before binlog writing and ha_autocommit_or_rollback
       */
       query_cache_invalidate3(thd, table_list, 1);
-      if (error <= 0 || !transactional_table)
+    }
+    thd->no_trans_update.top|= thd->no_trans_update.stmt;
+    if ((changed && error <= 0) || thd->no_trans_update.top)
+    {
+      if (mysql_bin_log.is_open())
       {
-        if (mysql_bin_log.is_open())
-        {
-          if (error <= 0)
-            thd->clear_error();
-          Query_log_event qinfo(thd, thd->query, thd->query_length,
-                                transactional_table, FALSE);
+        if (error <= 0)
+          thd->clear_error();
+        Query_log_event qinfo(thd, thd->query, thd->query_length,
+                              transactional_table, FALSE);
           if (mysql_bin_log.write(&qinfo) && transactional_table)
             error=1;
-        }
-        if (thd->no_trans_update.stmt)
-          thd->no_trans_update.all= TRUE;
       }
+      if (thd->no_trans_update.stmt)
+        thd->no_trans_update.all= TRUE;
     }
     DBUG_ASSERT(transactional_table || !changed || thd->no_trans_update.stmt);
     if (transactional_table)
@@ -2704,6 +2705,7 @@ void select_insert::send_error(uint errc
     */
     DBUG_VOID_RETURN;
   }
+  changed= (info.copied || info.deleted || info.updated);
   transactional_table= table->file->has_transactions();
   if (!thd->prelocked_mode)
     table->file->end_bulk_insert();
@@ -2714,8 +2716,7 @@ void select_insert::send_error(uint errc
     error while inserting into a MyISAM table) we must write to the binlog (and
     the error code will make the slave stop).
   */
-  if ((changed= info.copied || info.deleted || info.updated) &&
-      !transactional_table)
+  if (thd->no_trans_update.top)
   {
     if (last_insert_id)
       thd->insert_id(last_insert_id);		// For binary log

--- 1.114/sql/sql_load.cc	2007-06-12 20:32:21 +03:00
+++ 1.115/sql/sql_load.cc	2007-06-15 13:59:12 +03:00
@@ -445,7 +445,7 @@ bool mysql_load(THD *thd,sql_exchange *e
       /* If the file was not empty, wrote_create_file is true */
       if (lf_info.wrote_create_file)
       {
-	if ((info.copied || info.deleted) && !transactional_table)
+	if (thd->no_trans_update.top)
 	  write_execute_load_query_log_event(thd, handle_duplicates,
 					     ignore, transactional_table);
 	else
@@ -483,7 +483,7 @@ bool mysql_load(THD *thd,sql_exchange *e
 #endif /*!EMBEDDED_LIBRARY*/
   if (transactional_table)
     error=ha_autocommit_or_rollback(thd,error);
-
+  
   /* ok to client sent only after binlog write and engine commit */
   send_ok(thd, info.copied + info.deleted, 0L, name);
 err:

--- 1.216/sql/sql_update.cc	2007-06-12 20:32:22 +03:00
+++ 1.217/sql/sql_update.cc	2007-06-15 13:59:12 +03:00
@@ -549,7 +549,7 @@ int mysql_update(THD *thd,
     Sometimes we want to binlog even if we updated no rows, in case user used
     it to be sure master and slave are in same state.
   */
-  if ((error < 0) || (updated && !transactional_table))
+  if ((error < 0) || thd->no_trans_update.top)
   {
     if (mysql_bin_log.is_open())
     {

--- 1.1/mysql-test/r/stored_routines_side_effect.result	2007-06-12 20:32:22 +03:00
+++ 1.2/mysql-test/r/stored_routines_side_effect.result	2007-06-15 13:59:11 +03:00
@@ -17,8 +17,8 @@ insert into t2 values (bug27417(2));
 ERROR 23000: Duplicate entry '2' for key 1
 show master status;
 File	Position	Binlog_Do_DB	Binlog_Ignore_DB
-master-bin.000001	98		
-/* only (!) with fixes for #23333 will show there is the query */;
+master-bin.000001	196		
+/* the offset must denote there is the query */;
 select count(*) from t1 /* must be 3 */;
 count(*)
 3
@@ -43,4 +43,74 @@ select count(*) from t1 /* must be 7 */;
 count(*)
 7
 drop table t1,t2;
+CREATE TABLE t1 (a int  NOT NULL auto_increment primary key) ENGINE=MyISAM;
+CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
+CREATE TABLE t3 (a int, PRIMARY KEY (a), b int unique);
+insert into t2 values (1);
+reset master;
+insert into t2 values (bug27417(1));
+ERROR 23000: Duplicate entry '1' for key 1
+show master status /* the offset must denote there is the query */;
+File	Position	Binlog_Do_DB	Binlog_Ignore_DB
+master-bin.000001	267		
+select count(*) from t1 /* must be 1 */;
+count(*)
+1
+delete from t1;
+delete from t2;
+insert into t2 values (2);
+reset master;
+insert into t2 select bug27417(1) union select bug27417(2);
+ERROR 23000: Duplicate entry '2' for key 1
+show master status /* the offset must denote there is the query */;
+File	Position	Binlog_Do_DB	Binlog_Ignore_DB
+master-bin.000001	290		
+select count(*) from t1 /* must be 2 */;
+count(*)
+2
+delete from t1;
+insert into t3 values (1,1),(2,3),(3,4);
+reset master;
+update t3 set b=b+bug27417(1);
+ERROR 23000: Duplicate entry '4' for key 2
+show master status /* the offset must denote there is the query */;
+File	Position	Binlog_Do_DB	Binlog_Ignore_DB
+master-bin.000001	190		
+select count(*) from t1 /* must be 2 */;
+count(*)
+2
+delete from t1;
+delete from t2;
+delete from t3;
+insert into t2 values (1);
+insert into t3 values (1,1);
+create trigger trg_del before delete on t2 for each row 
+insert into t3 values (bug27417(1), 2);
+reset master;
+delete from t2;
+ERROR 23000: Duplicate entry '1' for key 1
+show master status /* the offset must denote there is the query */;
+File	Position	Binlog_Do_DB	Binlog_Ignore_DB
+master-bin.000001	246		
+select count(*) from t1 /* must be 1 */;
+count(*)
+1
+delete from t1;
+create table t4 (a int default 0, b int primary key) engine=innodb;
+insert into t4 values (0, 17);
+reset master;
+load data infile '../std_data_ln/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2);
+ERROR 23000: Duplicate entry '17' for key 1
+select * from t4;
+a	b
+0	17
+select count(*) from t1 /* must be 2 */;
+count(*)
+2
+show master status /* the offset must denote there is the query */;
+File	Position	Binlog_Do_DB	Binlog_Ignore_DB
+master-bin.000001	376		
+drop trigger trg_del;
+drop table t1,t2,t3;
+drop function bug27417;
 end of tests

--- 1.1/mysql-test/t/stored_routines_side_effect.test	2007-06-12 20:32:22 +03:00
+++ 1.2/mysql-test/t/stored_routines_side_effect.test	2007-06-15 13:59:11 +03:00
@@ -5,7 +5,7 @@
 # table thd->no_trans_update.stmt must be TRUE;
 # the assert is active with debug build
 #
-
+--source include/have_innodb.inc
 
 --disable_warnings
 drop function if exists bug27417;
@@ -36,7 +36,7 @@ reset master;
 
 --error ER_DUP_ENTRY
 insert into t2 values (bug27417(2)); 
-show master status; /* only (!) with fixes for #23333 will show there is the query */;
+show master status; /* the offset must denote there is the query */;
 select count(*) from t1 /* must be 3 */;
 
 reset master;
@@ -52,5 +52,117 @@ delete t2 from t2 where t2.a=bug27417(10
 select count(*) from t1 /* must be 7 */;
 
 drop table t1,t2;
+
+# Bug#23333 using the patch (and the test) for bug#27471
+CREATE TABLE t1 (a int  NOT NULL auto_increment primary key) ENGINE=MyISAM;
+CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
+CREATE TABLE t3 (a int, PRIMARY KEY (a), b int unique);
+
+
+#
+# INSERT
+#
+
+# prepare
+  
+ insert into t2 values (1);
+ reset master;
+
+# execute
+
+ --error ER_DUP_ENTRY
+ insert into t2 values (bug27417(1)); 
+  
+# check
+
+ show master status /* the offset must denote there is the query */;
+ select count(*) from t1 /* must be 1 */;
+
+#
+# INSERT SELECT
+#
+
+# prepare
+ delete from t1;
+ delete from t2;
+ insert into t2 values (2);
+ reset master;
+
+# execute
+
+ --error ER_DUP_ENTRY
+ insert into t2 select bug27417(1) union select bug27417(2); 
+  
+# check
+
+ show master status /* the offset must denote there is the query */;
+ select count(*) from t1 /* must be 2 */;
+
+#
+# UPDATE (multi-update see bug#27716)
+#
+
+# prepare
+ delete from t1;
+ insert into t3 values (1,1),(2,3),(3,4);
+ reset master;
+
+# execute
+ --error ER_DUP_ENTRY
+ update t3 set b=b+bug27417(1);
+
+# check
+ show master status /* the offset must denote there is the query */;
+ select count(*) from t1 /* must be 2 */;
+
+
+#
+# DELETE (for multi-delete see Bug #29136)
+#
+
+# prepare
+ delete from t1;
+ delete from t2;
+ delete from t3;
+ insert into t2 values (1);
+ insert into t3 values (1,1);
+ create trigger trg_del before delete on t2 for each row 
+   insert into t3 values (bug27417(1), 2);
+ reset master;
+
+# execute
+ --error ER_DUP_ENTRY
+ delete from t2;
+# check
+ show master status /* the offset must denote there is the query */;
+ select count(*) from t1 /* must be 1 */;
+
+
+#
+# LOAD DATA
+#
+
+# prepare
+ delete from t1;
+ create table t4 (a int default 0, b int primary key) engine=innodb;
+ insert into t4 values (0, 17);
+ reset master;
+
+# execute
+ --error ER_DUP_ENTRY
+ load data infile '../std_data_ln/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2);
+# check
+ select * from t4;
+ select count(*) from t1 /* must be 2 */;
+ show master status /* the offset must denote there is the query */;
+
+#
+# bug#23333 cleanup
+#
+
+
+drop trigger trg_del;
+drop table t1,t2,t3;
+drop function bug27417;
 
 --echo end of tests

--- 1.12/mysql-test/r/sp_trans.result	2007-03-24 19:19:58 +02:00
+++ 1.13/mysql-test/r/sp_trans.result	2007-06-15 13:59:11 +03:00
@@ -546,8 +546,9 @@ end|
 reset master|
 insert into t2 values (bug23333(),1)|
 ERROR 23000: Duplicate entry '1' for key 1
-show binlog events from 98 /* with fixes for #23333 will show there is the query */|
-Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+show master status /* the offset must denote there is the query */|
+File	Position	Binlog_Do_DB	Binlog_Ignore_DB
+master-bin.000001	326		
 select count(*),@a from t1 /* must be 1,1 */|
 count(*)	@a
 1	1

--- 1.12/mysql-test/t/sp_trans.test	2007-03-24 19:19:58 +02:00
+++ 1.13/mysql-test/t/sp_trans.test	2007-06-15 13:59:11 +03:00
@@ -580,8 +580,7 @@ end|
 reset master|
 --error ER_DUP_ENTRY
 insert into t2 values (bug23333(),1)| 
---replace_column 2 # 5 # 6 #
-show binlog events from 98 /* with fixes for #23333 will show there is the query */|
+show master status /* the offset must denote there is the query */|
 select count(*),@a from t1 /* must be 1,1 */|
 
 #
Thread
bk commit into 5.0 tree (aelkin:1.2440) BUG#23333Andrei Elkin15 Jun