bk commit into 5.0 tree (ramil:1.2497) BUG#26842

[ramil]

Below is the list of changes that have just been committed into a local
5.0 repository of ram. When ram 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-05-24 15:35:43+05:00, ramil@stripped +6 -0
  Fix for
  bug #26842: master binary log contains invalid queries - replication fails
  bug #12826: Possible to get inconsistent slave using SQL syntax Prepared Statements
  
  Problem:  
  binlogging PS' we may produce syntacticly incorrect queries in the binlog replacing 
  some parameters with variable names (instead of variable values).
  E.g. in the reported case of "limit ?" clause: replacing "?" with "@var"
  produces "limit @var" which is not a correct SQL syntax. 
  Also it may lead to different query execution on slave if we
  set and use a variable in the same statement, e.g.
  "insert into t1 values (@x:=@x+1, ?)"
  
  Fix: make the stored statement string created upon its execution use variable values
  (instead of names) to fill placeholders.

  mysql-test/r/ctype_cp932_binlog.result@stripped, 2007-05-24 15:35:42+05:00, ramil@stripped
+7 -8
    Fix for
    bug #26842: master binary log contains invalid queries - replication fails
    bug #12826: Possible to get inconsistent slave using SQL syntax Prepared Statements
      - result adjusted.

  mysql-test/r/ctype_cp932_notembedded.result@stripped, 2007-05-24 15:35:42+05:00,
ramil@stripped +1 -2
    Fix for
    bug #26842: master binary log contains invalid queries - replication fails
    bug #12826: Possible to get inconsistent slave using SQL syntax Prepared Statements
      - result adjusted.

  mysql-test/r/rpl_user_variables.result@stripped, 2007-05-24 15:35:42+05:00, ramil@stripped
+37 -3
    Fix for
    bug #26842: master binary log contains invalid queries - replication fails
    bug #12826: Possible to get inconsistent slave using SQL syntax Prepared Statements
      - test result.

  mysql-test/t/ctype_cp932_binlog.test@stripped, 2007-05-24 15:35:42+05:00, ramil@stripped +1
-1
    Fix for
    bug #26842: master binary log contains invalid queries - replication fails
    bug #12826: Possible to get inconsistent slave using SQL syntax Prepared Statements
      - test adjusted.

  mysql-test/t/rpl_user_variables.test@stripped, 2007-05-24 15:35:42+05:00, ramil@stripped
+39 -6
    Fix for
    bug #26842: master binary log contains invalid queries - replication fails
    bug #12826: Possible to get inconsistent slave using SQL syntax Prepared Statements
      - test case.

  sql/sql_prepare.cc@stripped, 2007-05-24 15:35:42+05:00, ramil@stripped +12 -25
    Fix for
    bug #26842: master binary log contains invalid queries - replication fails
    bug #12826: Possible to get inconsistent slave using SQL syntax Prepared Statements
      - set val to the variable's value (escaped if needed) then insert it into the query
        string in the position of the placeholder. We don't need to call
        get_var_with_binlog() here as there is no trace of the variable's name in the
binlog.

# 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:	ramil
# Host:	ramil.myoffice.izhnet.ru
# Root:	/home/ram/work/b26842/b26842.5.0

--- 1.18/mysql-test/r/rpl_user_variables.result	2007-05-24 15:35:47 +05:00
+++ 1.19/mysql-test/r/rpl_user_variables.result	2007-05-24 15:35:47 +05:00
@@ -253,10 +253,44 @@ SELECT * from t2;
 k
 100
 42
+drop table t1, t2;
+reset master;
+create table t1 (a int);
+prepare s from "insert into t1 values (@a),(?)";
+set @a=98;
+execute s using @a;
+prepare s from "insert into t1 values (?)";
+set @a=99;
+execute s using @a;
+prepare s from "insert into t1 select 100 limit ?";
+set @a=100;
+execute s using @a;
+show binlog events from 98;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+slave-bin.000001	98	Query	1	184	use `test`; create table t1 (a int)
+slave-bin.000001	184	User var	2	226	@`a`=98
+slave-bin.000001	226	Query	1	320	use `test`; insert into t1 values (@a),(98)
+slave-bin.000001	320	Query	1	409	use `test`; insert into t1 values (99)
+slave-bin.000001	409	Query	1	507	use `test`; insert into t1 select 100 limit 100
+select * from t1;
+a
+98
+98
+99
+100
+drop table t1;
+create table t1(a int, b int);
+prepare s1 from 'insert into t1 values (@x:=@x+1, ?)';
+set @x=1;
+execute s1 using @x;
+select * from t1;
+a	b
+2	1
+select * from t1;
+a	b
+2	1
+drop table t1;
 End of 5.0 tests.
-DROP TABLE t1;
-DROP TABLE t2;
-DROP TABLE IF EXISTS t1;
 DROP FUNCTION IF EXISTS f1;
 DROP FUNCTION IF EXISTS f2;
 CREATE TABLE t1 (i INT);

--- 1.15/mysql-test/t/rpl_user_variables.test	2007-05-24 15:35:47 +05:00
+++ 1.16/mysql-test/t/rpl_user_variables.test	2007-05-24 15:35:47 +05:00
@@ -296,13 +296,48 @@ SELECT * from t1;
 SELECT * from t2;
 
 connection master;
+drop table t1, t2;
 
---echo End of 5.0 tests.
+#
+# Bug #26842: master binary log contains invalid queries - replication fails
+#
+save_master_pos;
+connection slave;
+sync_with_master;
+reset master;
 
-# Cleanup
+connection master;
+create table t1 (a int); 
+prepare s from "insert into t1 values (@a),(?)";
+set @a=98; execute s using @a;
+prepare s from "insert into t1 values (?)";
+set @a=99; execute s using @a;
+prepare s from "insert into t1 select 100 limit ?";
+set @a=100; execute s using @a;
+
+save_master_pos;
+connection slave;
+sync_with_master;
+show binlog events from 98;
+select * from t1;
+connection master;
+drop table t1;
 
-DROP TABLE t1;
-DROP TABLE t2;
+#
+# Bug #12826: Possible to get inconsistent slave using SQL syntax Prepared Statements
+#
+connection master;
+create table t1(a int, b int);
+prepare s1 from 'insert into t1 values (@x:=@x+1, ?)';
+set @x=1; execute s1 using @x;
+select * from t1;
+sync_slave_with_master;
+connection slave;
+select * from t1;
+connection master;
+drop table t1;
+
+--echo End of 5.0 tests.
 
 # This test uses a stored function that uses user-defined variables to return data
 # The test ensures the value of the user-defined variable is replicated correctly
@@ -310,7 +345,6 @@ DROP TABLE t2;
 # This test was constructed for BUG#20141
 
 --disable_warnings
-DROP TABLE IF EXISTS t1;
 DROP FUNCTION IF EXISTS f1;
 DROP FUNCTION IF EXISTS f2;
 --enable_warnings
@@ -358,4 +392,3 @@ DROP TABLE t1;
 
 sync_slave_with_master;
 stop slave;
-

--- 1.7/mysql-test/r/ctype_cp932_binlog.result	2007-05-24 15:35:47 +05:00
+++ 1.8/mysql-test/r/ctype_cp932_binlog.result	2007-05-24 15:35:47 +05:00
@@ -9,8 +9,7 @@ EXECUTE stmt1 USING @var1;
 SHOW BINLOG EVENTS FROM 98;
 Log_name	Pos	Event_type	Server_id	End_log_pos	Info
 master-bin.000001	98	Query	1	188	use `test`; CREATE TABLE t1(f1 blob)
-master-bin.000001	188	User var	1	227	@`var1`=_binary 0x8300 COLLATE binary
-master-bin.000001	227	Query	1	323	use `test`; INSERT INTO t1 VALUES(@'var1')
+master-bin.000001	188	Query	1	283	use `test`; INSERT INTO t1 VALUES(0x8300)
 SELECT HEX(f1) FROM t1;
 HEX(f1)
 8300
@@ -30,17 +29,17 @@ HEX(s1)	HEX(s2)	d
 466F6F2773206120426172	ED40ED41ED42	47.93
 DROP PROCEDURE bug18293|
 DROP TABLE t4|
-SHOW BINLOG EVENTS FROM 402|
+SHOW BINLOG EVENTS FROM 362|
 Log_name	Pos	Event_type	Server_id	End_log_pos	Info
-master-bin.000001	402	Query	1	568	use `test`; CREATE TABLE t4 (s1 CHAR(50) CHARACTER SET
latin1,
+master-bin.000001	362	Query	1	528	use `test`; CREATE TABLE t4 (s1 CHAR(50) CHARACTER SET
latin1,
 s2 CHAR(50) CHARACTER SET cp932,
 d DECIMAL(10,2))
-master-bin.000001	568	Query	1	816	use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE
bug18293 (IN ins1 CHAR(50),
+master-bin.000001	528	Query	1	776	use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE
bug18293 (IN ins1 CHAR(50),
 IN ins2 CHAR(50) CHARACTER SET cp932,
 IN ind DECIMAL(10,2))
 BEGIN
 INSERT INTO t4 VALUES (ins1, ins2, ind);
 END
-master-bin.000001	816	Query	1	1035	use `test`; INSERT INTO t4 VALUES (
NAME_CONST('ins1',_latin1 0x466F6F2773206120426172),  NAME_CONST('ins2',_cp932
0xED40ED41ED42),  NAME_CONST('ind',47.93))
-master-bin.000001	1035	Query	1	1124	use `test`; DROP PROCEDURE bug18293
-master-bin.000001	1124	Query	1	1203	use `test`; DROP TABLE t4
+master-bin.000001	776	Query	1	995	use `test`; INSERT INTO t4 VALUES (
NAME_CONST('ins1',_latin1 0x466F6F2773206120426172),  NAME_CONST('ins2',_cp932
0xED40ED41ED42),  NAME_CONST('ind',47.93))
+master-bin.000001	995	Query	1	1084	use `test`; DROP PROCEDURE bug18293
+master-bin.000001	1084	Query	1	1163	use `test`; DROP TABLE t4

--- 1.4/mysql-test/t/ctype_cp932_binlog.test	2007-05-24 15:35:47 +05:00
+++ 1.5/mysql-test/t/ctype_cp932_binlog.test	2007-05-24 15:35:47 +05:00
@@ -51,7 +51,7 @@ CALL bug18293("Foo's a Bar", _cp932 0xED
 SELECT HEX(s1),HEX(s2),d FROM t4|
 DROP PROCEDURE bug18293|
 DROP TABLE t4|
-SHOW BINLOG EVENTS FROM 402|
+SHOW BINLOG EVENTS FROM 362|
 delimiter ;|
 
 # End of 5.0 tests

--- 1.2/mysql-test/r/ctype_cp932_notembedded.result	2007-05-24 15:35:47 +05:00
+++ 1.3/mysql-test/r/ctype_cp932_notembedded.result	2007-05-24 15:35:47 +05:00
@@ -9,8 +9,7 @@ EXECUTE stmt1 USING @var1;
 SHOW BINLOG EVENTS FROM 98;
 Log_name	Pos	Event_type	Server_id	End_log_pos	Info
 master-bin.000001	98	Query	1	188	use `test`; CREATE TABLE t1(f1 blob)
-master-bin.000001	188	User var	1	227	@`var1`=_binary 0x8300 COLLATE binary
-master-bin.000001	227	Query	1	323	use `test`; INSERT INTO t1 VALUES(@'var1')
+master-bin.000001	188	Query	1	283	use `test`; INSERT INTO t1 VALUES(0x8300)
 SELECT HEX(f1) FROM t1;
 HEX(f1)
 8300

--- 1.202/sql/sql_prepare.cc	2007-05-24 15:35:47 +05:00
+++ 1.203/sql/sql_prepare.cc	2007-05-24 15:35:47 +05:00
@@ -969,6 +969,7 @@ static bool insert_params_from_vars_with
   String buf;
   const String *val;
   uint32 length= 0;
+  THD *thd= stmt->thd;
 
   DBUG_ENTER("insert_params_from_vars");
 
@@ -979,34 +980,20 @@ static bool insert_params_from_vars_with
   {
     Item_param *param= *it;
     varname= var_it++;
-    if (get_var_with_binlog(stmt->thd, stmt->lex->sql_command,
-                            *varname, &entry))
-        DBUG_RETURN(1);
 
-    if (param->set_from_user_var(stmt->thd, entry))
+    entry= (user_var_entry *) hash_search(&thd->user_vars, (byte*)
varname->str,
+                                          varname->length);
+    /*
+      We have to call the setup_one_conversion_function() here to set
+      the parameter's members that might be needed further
+      (e.g. value.cs_info.character_set_client is used in the query_val_str()).
+    */
+    setup_one_conversion_function(thd, param, param->param_type);
+    if (param->set_from_user_var(thd, entry))
       DBUG_RETURN(1);
-    /* Insert @'escaped-varname' instead of parameter in the query */
-    if (entry)
-    {
-      char *start, *ptr;
-      buf.length(0);
-      if (buf.reserve(entry->name.length*2+3))
-        DBUG_RETURN(1);
+    val= param->query_val_str(&buf);
 
-      start= ptr= buf.c_ptr_quick();
-      *ptr++= '@';
-      *ptr++= '\'';
-      ptr+= escape_string_for_mysql(&my_charset_utf8_general_ci,
-                                    ptr, 0, entry->name.str,
-                                    entry->name.length);
-      *ptr++= '\'';
-      buf.length(ptr - start);
-      val= &buf;
-    }
-    else
-      val= &my_null_string;
-
-    if (param->convert_str_value(stmt->thd))
+    if (param->convert_str_value(thd))
       DBUG_RETURN(1);                           /* out of memory */
 
     if (query->replace(param->pos_in_query+length, 1, *val))