bk commit into 5.0 tree (aelkin:1.2491) BUG#23333

[Andrei Elkin]

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-03-23 21:54:11+02:00, aelkin@andrepl.(none) +7 -0
  Bug #23333  	stored function + non-transac table + transac table = breaks stmt-based
binlog
  Bug #13270  	INSERT,UPDATE,etc that calls func with side-effect does not go into binlog
  
  The query was not binlogged if top-level table was transactional or unmodified while
  implicitly involved via stored function  non-ta table was modified.
  
  Fixed with checking thd->no_trans_update.all flag (refactored after thd->options' 
  OPTION_STATUS_NO_TRANS_UPDATE eliminated in
  Bug#27395 OPTION_STATUS_NO_TRANS_UPDATE is not preserved at the end of SF)
  at binlog recording time, and regardless of whether top level table got modified or not
the query
  gets binlogged if the flag was set.
  
  
  

  mysql-test/r/sp_trans.result@stripped, 2007-03-23 21:54:09+02:00, aelkin@andrepl.(none) +73
-14
    results changed

  mysql-test/t/sp_trans.test@stripped, 2007-03-23 21:54:09+02:00, aelkin@andrepl.(none) +44 -3
    regression tests for bugs

  sql/mysql_priv.h@stripped, 2007-03-23 21:54:09+02:00, aelkin@andrepl.(none) +0 -3
    removal, as part of Bug#27395 OPTION_STATUS_NO_TRANS_UPDATE is not preserved at the
end of SF()

  sql/sql_delete.cc@stripped, 2007-03-23 21:54:09+02:00, aelkin@andrepl.(none) +9 -7
    binlogging even if not updated because of modified non-ta tables

  sql/sql_insert.cc@stripped, 2007-03-23 21:54:10+02:00, aelkin@andrepl.(none) +16 -14
    binlogging even if not updated because of modified non-ta tables

  sql/sql_load.cc@stripped, 2007-03-23 21:54:10+02:00, aelkin@andrepl.(none) +11 -10
    binlogging even if not updated because of modified non-ta tables

  sql/sql_update.cc@stripped, 2007-03-23 21:54:10+02:00, aelkin@andrepl.(none) +6 -4
    binlogging even if not updated because of modified non-ta tables

# 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:	andrepl.(none)
# Root:	/home/elkin/MySQL/MAIN/5.0-marvel-bug23333_sf_side_eff_binlog

--- 1.438/sql/mysql_priv.h	2007-03-16 10:47:51 +02:00
+++ 1.439/sql/mysql_priv.h	2007-03-23 21:54:09 +02:00
@@ -347,9 +347,6 @@ MY_LOCALE *my_locale_by_number(uint numb
    in the user query has requested */
 #define SELECT_ALL              (ULL(1) << 24)    // SELECT, user, parser
 
-/* Set if we are updating a non-transaction safe table */
-#define OPTION_STATUS_NO_TRANS_UPDATE   (ULL(1) << 25) // THD, intern
-
 /* The following can be set when importing tables in a 'wrong order'
    to suppress foreign key checks */
 #define OPTION_NO_FOREIGN_KEY_CHECKS    (ULL(1) << 26) // THD, user, binlog

--- 1.194/sql/sql_delete.cc	2007-03-23 17:12:56 +02:00
+++ 1.195/sql/sql_delete.cc	2007-03-23 21:54:09 +02:00
@@ -299,7 +299,8 @@ cleanup:
   delete select;
   transactional_table= table->file->has_transactions();
   /* See similar binlogging code in sql_update.cc, for comments */
-  if ((error < 0) || (deleted && !transactional_table))
+  if ((error < 0) || (deleted && !transactional_table) ||
+      thd->no_trans_update.all)
   {
     if (mysql_bin_log.is_open())
     {
@@ -308,7 +309,7 @@ cleanup:
       Query_log_event qinfo(thd, thd->query, thd->query_length,
 			    transactional_table, FALSE);
       if (mysql_bin_log.write(&qinfo) && transactional_table)
-	error=1;
+        error=1;
     }
     if (!transactional_table)
       thd->no_trans_update.all= TRUE;
@@ -779,7 +780,8 @@ bool multi_delete::send_eof()
     query_cache_invalidate3(thd, delete_tables, 1);
   }
 
-  if ((local_error == 0) || (deleted && normal_tables))
+  if ((local_error == 0) || (deleted && normal_tables) ||
+      thd->no_trans_update.all)
   {
     if (mysql_bin_log.is_open())
     {
@@ -788,7 +790,7 @@ bool multi_delete::send_eof()
       Query_log_event qinfo(thd, thd->query, thd->query_length,
 			    transactional_tables, FALSE);
       if (mysql_bin_log.write(&qinfo) && !normal_tables)
-	local_error=1;  // Log write failed: roll back the SQL statement
+        local_error=1;  // Log write failed: roll back the SQL statement
     }
     if (!transactional_tables)
       thd->no_trans_update.all= TRUE;
@@ -891,9 +893,9 @@ end:
       if (mysql_bin_log.is_open())
       {
         thd->clear_error();
-	Query_log_event qinfo(thd, thd->query, thd->query_length,
-			      0, FALSE);
-	mysql_bin_log.write(&qinfo);
+        Query_log_event qinfo(thd, thd->query, thd->query_length,
+                              0, FALSE);
+        mysql_bin_log.write(&qinfo);
       }
       send_ok(thd);		// This should return record count
     }

--- 1.226/sql/sql_insert.cc	2007-03-23 17:12:56 +02:00
+++ 1.227/sql/sql_insert.cc	2007-03-23 21:54:10 +02:00
@@ -719,20 +719,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)
+    }
+    if (changed && (error <= 0 || !transactional_table) ||
+        thd->no_trans_update.all)
+    {
+      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 (mysql_bin_log.write(&qinfo) && transactional_table)
-            error=1;
-        }
-        if (!transactional_table)
-          thd->no_trans_update.all= TRUE;
+        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 (!transactional_table)
+        thd->no_trans_update.all= TRUE;
     }
     if (transactional_table)
       error=ha_autocommit_or_rollback(thd,error);
@@ -2665,7 +2666,8 @@ void select_insert::send_error(uint errc
     the error code will make the slave stop).
   */
   if ((info.copied || info.deleted || info.updated) &&
-      !table->file->has_transactions())
+      !table->file->has_transactions() ||
+      thd->no_trans_update.all)
   {
     if (last_insert_id)
       thd->insert_id(last_insert_id);		// For binary log
@@ -2716,7 +2718,7 @@ bool select_insert::send_eof()
     if (!error)
       thd->clear_error();
     Query_log_event qinfo(thd, thd->query, thd->query_length,
-			  table->file->has_transactions(), FALSE);
+                          table->file->has_transactions(), FALSE);
     mysql_bin_log.write(&qinfo);
   }
   if ((error2=ha_autocommit_or_rollback(thd,error)) && ! error)

--- 1.110/sql/sql_load.cc	2007-03-23 17:12:56 +02:00
+++ 1.111/sql/sql_load.cc	2007-03-23 21:54:10 +02:00
@@ -419,8 +419,8 @@ bool mysql_load(THD *thd,sql_exchange *e
 
     if (read_file_from_client)
       while (!read_info.next_line())
-	;
-
+        ;
+    
 #ifndef EMBEDDED_LIBRARY
     if (mysql_bin_log.is_open())
     {
@@ -449,14 +449,15 @@ 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)
-	  write_execute_load_query_log_event(thd, handle_duplicates,
-					     ignore, transactional_table);
-	else
-	{
-	  Delete_file_log_event d(thd, db, transactional_table);
-	  mysql_bin_log.write(&d);
-	}
+        if ((info.copied || info.deleted) && !transactional_table ||
+            thd->no_trans_update.all)
+          write_execute_load_query_log_event(thd, handle_duplicates,
+                                             ignore, transactional_table);
+        else
+        {
+          Delete_file_log_event d(thd, db, transactional_table);
+          mysql_bin_log.write(&d);
+        }
       }
     }
 #endif /*!EMBEDDED_LIBRARY*/

--- 1.213/sql/sql_update.cc	2007-03-23 17:12:56 +02:00
+++ 1.214/sql/sql_update.cc	2007-03-23 21:54:10 +02:00
@@ -534,16 +534,17 @@ 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) || (updated && !transactional_table) ||
+      thd->no_trans_update.all)
   {
     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);
+                            transactional_table, FALSE);
       if (mysql_bin_log.write(&qinfo) && transactional_table)
-	error=1;				// Rollback update
+        error=1;				// Rollback update
     }
     if (!transactional_table)
       thd->no_trans_update.all= TRUE;
@@ -1532,7 +1533,8 @@ bool multi_update::send_eof()
     transactional tables.
   */
 
-  if ((local_error == 0) || (updated && !trans_safe))
+  if ((local_error == 0) || (updated && !trans_safe) ||
+      thd->no_trans_update.all)
   {
     if (mysql_bin_log.is_open())
     {

--- 1.10/mysql-test/r/sp_trans.result	2007-03-23 17:12:55 +02:00
+++ 1.11/mysql-test/r/sp_trans.result	2007-03-23 21:54:09 +02:00
@@ -531,10 +531,10 @@ drop table t3, t4|
 drop procedure bug14210|
 set @@session.max_heap_table_size=default|
 drop function if exists bug23333|
-drop table if exists t1,t2|
+drop table if exists t1,t2,t3,t4|
 CREATE TABLE t1 (a int  NOT NULL auto_increment primary key) ENGINE=MyISAM|
 CREATE TABLE t2 (a int  NOT NULL auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB|
-reset master|
+CREATE TABLE t3 (a int  NOT NULL auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB|
 insert into t2 values (1,1)|
 create function bug23333() 
 RETURNS int(11)
@@ -544,21 +544,80 @@ insert into t1 values (null);
 select count(*) from t1 into @a;
 return @a;
 end|
+reset master|
 insert into t2 values (bug23333(),1)|
 ERROR 23000: Duplicate entry '1' for key 1
-show binlog events /* must show the insert */|
+show binlog events from 98/* must show the insert */|
 Log_name	Pos	Event_type	Server_id	End_log_pos	Info
-master-bin.000001	4	Format_desc	1	98	Server ver: 5.0.40-debug-log, Binlog ver: 4
-master-bin.000001	98	Query	1	90	use `test`; insert into t2 values (1,1)
-master-bin.000001	188	Xid	1	215	COMMIT /* xid=1165 */
-master-bin.000001	215	Query	1	446	use `test`; CREATE DEFINER=`root`@`localhost` function
bug23333() 
-RETURNS int(11)
-DETERMINISTIC
-begin
-insert into t1 values (null);
-select count(*) from t1 into @a;
-return @a;
-end
+master-bin.000001	98	User var	1	42	@`a`=1
+master-bin.000001	140	Query	1	149	use `test`; insert into t2 values (bug23333(),1)
+master-bin.000001	247	Query	1	326	use `test`; ROLLBACK
 select count(*),@a from t1 /* must be 1,1 */|
 count(*)	@a
 1	1
+insert into t2 values (2,2)|
+update t2 set a= 1 where b = bug23333()+100 /* no records updated in t2 */|
+affected rows: 0
+info: Rows matched: 0  Changed: 0  Warnings: 0
+select count(*),@a from t1 /* must be 3,3 */|
+count(*)	@a
+3	3
+select count(*) from t2 /* nothing changed -> 2 records */|
+count(*)
+2
+delete from t2 where b = bug23333()+100 /* no record deleted in t2 */|
+affected rows: 0
+update t2,t3 set t2.a= 1 where t2.b = bug23333()+100 /* no updated rows */|
+affected rows: 0
+info: Rows matched: 0  Changed: 0  Warnings: 0
+insert into t3 values (1,1)|
+delete t2,t3 from t2,t3 where t2.b = bug23333() and t3.b = 0 /* no deleted rows */|
+affected rows: 0
+delete from t1 /* reset the function to return 1 at next call */|
+insert into t3 select bug23333(),bug23333() /* nothing must be inserted into t3 */|
+ERROR 23000: Duplicate entry '1' for key 1
+select count(*) from t3 /* must return 1 */|
+count(*)
+1
+delete from t1 /* reset the function to return 1 at next call */|
+create table t4 (a int default 0, b int primary key) engine=innodb|
+insert into t4 values (0, 17)|
+load data infile '../std_data_ln/rpl_loaddata.dat' into table t4 (a, @b) set b= @b +
bug23333()|
+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 binlog events from 98 /* must show inserts, updates, deletes */|
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	98	User var	1	42	@`a`=1
+master-bin.000001	140	Query	1	149	use `test`; insert into t2 values (bug23333(),1)
+master-bin.000001	247	Query	1	326	use `test`; ROLLBACK
+master-bin.000001	326	Query	1	90	use `test`; insert into t2 values (2,2)
+master-bin.000001	416	Xid	1	443	COMMIT /* xid=1174 */
+master-bin.000001	443	User var	1	42	@`a`=2
+master-bin.000001	485	Query	1	179	use `test`; update t2 set a= 1 where b = bug23333()+100
/* no records updated in t2 */
+master-bin.000001	622	Xid	1	649	COMMIT /* xid=1175 */
+master-bin.000001	649	User var	1	42	@`a`=4
+master-bin.000001	691	Query	1	174	use `test`; delete from t2 where b = bug23333()+100 /*
no record deleted in t2 */
+master-bin.000001	823	Xid	1	850	COMMIT /* xid=1184 */
+master-bin.000001	850	Query	1	137	use `test`; update t2,t3 set t2.a= 1 where t2.b =
bug23333()+100 /* no updated rows */
+master-bin.000001	987	Xid	1	1014	COMMIT /* xid=1191 */
+master-bin.000001	1014	Query	1	90	use `test`; insert into t3 values (1,1)
+master-bin.000001	1104	Xid	1	1131	COMMIT /* xid=1192 */
+master-bin.000001	1131	Query	1	145	use `test`; delete t2,t3 from t2,t3 where t2.b =
bug23333() and t3.b = 0 /* no deleted rows */
+master-bin.000001	1276	Xid	1	1303	COMMIT /* xid=1193 */
+master-bin.000001	1303	Query	1	1430	use `test`; delete from t1 /* reset the function to
return 1 at next call */
+master-bin.000001	1430	User var	1	42	@`a`=1
+master-bin.000001	1472	Query	1	187	use `test`; insert into t3 select
bug23333(),bug23333() /* nothing must be inserted into t3 */
+master-bin.000001	1617	Query	1	1688	use `test`; ROLLBACK
+master-bin.000001	1688	Query	1	1815	use `test`; delete from t1 /* reset the function to
return 1 at next call */
+master-bin.000001	1815	Query	1	1944	use `test`; create table t4 (a int default 0, b int
primary key) engine=innodb
+master-bin.000001	1944	Query	1	92	use `test`; insert into t4 values (0, 17)
+master-bin.000001	2036	Xid	1	2063	COMMIT /* xid=1205 */
+master-bin.000001	2063	User var	1	42	@`a`=1
+master-bin.000001	2105	Begin_load_query	1	77	;file_id=1;block_len=12
+master-bin.000001	2140	User var	1	119	@`a`=1
+master-bin.000001	2182	Execute_load_query	1	290	use `test`; load data infile
'../std_data_ln/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug23333() ;file_id=1

--- 1.10/mysql-test/t/sp_trans.test	2007-03-23 17:12:55 +02:00
+++ 1.11/mysql-test/t/sp_trans.test	2007-03-23 21:54:09 +02:00
@@ -561,12 +561,12 @@ set @@session.max_heap_table_size=defaul
 #
 --disable_warnings
 drop function if exists bug23333|
-drop table if exists t1,t2|
+drop table if exists t1,t2,t3,t4|
 --enable_warnings
  CREATE TABLE t1 (a int  NOT NULL auto_increment primary key) ENGINE=MyISAM|
  CREATE TABLE t2 (a int  NOT NULL auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB|
+ CREATE TABLE t3 (a int  NOT NULL auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB|
 
-reset master|
 insert into t2 values (1,1)|
 
 create function bug23333() 
@@ -578,10 +578,51 @@ begin
   return @a;
 end|
 
+reset master|
+
 --error ER_DUP_ENTRY
 insert into t2 values (bug23333(),1)| 
-show binlog events /* must show the insert */|
+show binlog events from 98/* must show the insert */|
 select count(*),@a from t1 /* must be 1,1 */|
+
+insert into t2 values (2,2)|
+--enable_info
+update t2 set a= 1 where b = bug23333()+100 /* no records updated in t2 */|
+--disable_info
+select count(*),@a from t1 /* must be 3,3 */|
+select count(*) from t2 /* nothing changed -> 2 records */|
+
+
+--enable_info
+delete from t2 where b = bug23333()+100 /* no record deleted in t2 */|
+--disable_info
+
+--enable_info
+update t2,t3 set t2.a= 1 where t2.b = bug23333()+100 /* no updated rows */|
+--disable_info
+
+insert into t3 values (1,1)|
+--enable_info
+delete t2,t3 from t2,t3 where t2.b = bug23333() and t3.b = 0 /* no deleted rows */|
+--disable_info
+
+delete from t1 /* reset the function to return 1 at next call */|
+--enable_info
+--error ER_DUP_ENTRY
+insert into t3 select bug23333(),bug23333() /* nothing must be inserted into t3 */|
+--disable_info
+select count(*) from t3 /* must return 1 */|
+
+delete from t1 /* reset the function to return 1 at next call */|
+create table t4 (a int default 0, b int primary key) engine=innodb|
+insert into t4 values (0, 17)|
+--error ER_DUP_ENTRY
+load data infile '../std_data_ln/rpl_loaddata.dat' into table t4 (a, @b) set b= @b +
bug23333()|
+select * from t4|
+select count(*) from t1 /* must be 2 */|
+
+show binlog events from 98 /* must show inserts, updates, deletes */|
+
 
 #
 # BUG#NNNN: New bug synopsis