From: Ramil Kalimullin
Date: January 28 2009 5:55pm
Subject: bzr commit into mysql-5.0-bugteam branch (ramil:2730) Bug#35383
List-Archive: http://lists.mysql.com/commits/64371
X-Bug: 35383
Message-Id: <0KE700F120FX6290@fe-emea-10.sun.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7BIT

#At file:///home/ram/mysql/b35383-5.0-bugteam/ based on revid:chad@stripped

 2730 Ramil Kalimullin	2009-01-28
      Fix for bug#35383: binlog playback and replication breaks 
      due to name_const substitution
      
      Problem: 
      "In general, statements executed within a stored procedure
      are written to the binary log using the same rules that 
      would apply were the statements to be executed in standalone
      fashion. Some special care is taken when logging procedure 
      statements because statement execution within procedures 
      is not quite the same as in non-procedure context".
      
      For example, each reference to a local variable in SP's 
      statements is replaced by NAME_CONST(var_name, var_value).
      Queries like 
      "CREATE TABLE ... SELECT local_var ..."
      are logged as
      "CREATE TABLE ... SELECT NAME_CONST("local_var", var_value) ..."
      that leads to differrent field names and
      might result in "Incorrect column name" if var_value is long enough. 
      
      Fix: in 5.x we'll issue a warning (error???) in such a case.
      In 6.0 we should get rid of NAME_CONST().
      
      Note: this issue and change should be described in the documentation
      ("Binary Logging of Stored Programs").
modified:
  mysql-test/r/binlog.result
  mysql-test/r/rpl_sp_effects.result
  mysql-test/r/sp-vars.result
  mysql-test/r/sp.result
  sql/sp_head.cc
  sql/sql_class.cc
  sql/sql_class.h
  sql/sql_parse.cc

per-file messages:
  sql/sp_head.cc
    Fix for bug#35383: binlog playback and replication breaks 
    due to name_const substitution
      - set thd->query_has_name_const if there's NAME_CONST()
    substitution.
  sql/sql_parse.cc
    Fix for bug#35383: binlog playback and replication breaks 
    due to name_const substitution
      - issue a warning if there's NAME_CONST() substitution and
    binary logging is on for "CREATE TABLE ... SELECT ...".
=== modified file 'mysql-test/r/binlog.result'
--- a/mysql-test/r/binlog.result	2008-10-01 09:48:47 +0000
+++ b/mysql-test/r/binlog.result	2009-01-28 17:54:52 +0000
@@ -604,6 +604,8 @@ END//
 CALL p1();
 c1	c2	c3	d1	d2	d3
 utf8_general_ci	utf8_unicode_ci	utf8_unicode_ci	2	2	2
+Warnings:
+Warning	1105	This function contains a query like 'CREATE TABLE ... SELECT local_var' and binary logging is enabled, that might cause problems. (something else? shorter?)
 SHOW BINLOG EVENTS FROM 1285;
 Log_name	Pos	Event_type	Server_id	End_log_pos	Info
 master-bin.000001	1285	Query	1	1483	use `bug39182`; CREATE TEMPORARY TABLE tmp1

=== modified file 'mysql-test/r/rpl_sp_effects.result'
--- a/mysql-test/r/rpl_sp_effects.result	2007-10-21 15:37:37 +0000
+++ b/mysql-test/r/rpl_sp_effects.result	2009-01-28 17:54:52 +0000
@@ -33,6 +33,8 @@ declare a int default 4;
 create table t2 as select a;
 end//
 call p2();
+Warnings:
+Warning	1105	This function contains a query like 'CREATE TABLE ... SELECT local_var' and binary logging is enabled, that might cause problems. (something else? shorter?)
 select * from t2;
 a
 4

=== modified file 'mysql-test/r/sp-vars.result'
--- a/mysql-test/r/sp-vars.result	2007-05-16 12:25:38 +0000
+++ b/mysql-test/r/sp-vars.result	2009-01-28 17:54:52 +0000
@@ -692,6 +692,8 @@ Table	Create Table
 t1	CREATE TABLE "t1" (
   "x" datetime default NULL
 )
+Warnings:
+Warning	1105	This function contains a query like 'CREATE TABLE ... SELECT local_var' and binary logging is enabled, that might cause problems. (something else? shorter?)
 CALL p1('test');
 Table	Create Table
 t1	CREATE TABLE "t1" (
@@ -699,6 +701,7 @@ t1	CREATE TABLE "t1" (
 )
 Warnings:
 Warning	1264	Out of range value adjusted for column 'x' at row 1
+Warning	1105	This function contains a query like 'CREATE TABLE ... SELECT local_var' and binary logging is enabled, that might cause problems. (something else? shorter?)
 DROP PROCEDURE p1;
 
 ---------------------------------------------------------------

=== modified file 'mysql-test/r/sp.result'
--- a/mysql-test/r/sp.result	2008-10-17 14:55:06 +0000
+++ b/mysql-test/r/sp.result	2009-01-28 17:54:52 +0000
@@ -3833,16 +3833,21 @@ tm1	CREATE TEMPORARY TABLE `tm1` (
 Warnings:
 Warning	1264	Out of range value adjusted for column 'spv1' at row 1
 Warning	1366	Incorrect decimal value: 'test' for column 'spv1' at row 1
+Warning	1105	This function contains a query like 'CREATE TABLE ... SELECT local_var' and binary logging is enabled, that might cause problems. (something else? shorter?)
 call bug12589_2()|
 Table	Create Table
 tm1	CREATE TEMPORARY TABLE `tm1` (
   `spv1` decimal(6,3) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
+Warnings:
+Warning	1105	This function contains a query like 'CREATE TABLE ... SELECT local_var' and binary logging is enabled, that might cause problems. (something else? shorter?)
 call bug12589_3()|
 Table	Create Table
 tm1	CREATE TEMPORARY TABLE `tm1` (
   `spv1` decimal(6,3) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
+Warnings:
+Warning	1105	This function contains a query like 'CREATE TABLE ... SELECT local_var' and binary logging is enabled, that might cause problems. (something else? shorter?)
 drop procedure bug12589_1|
 drop procedure bug12589_2|
 drop procedure bug12589_3|
@@ -6346,6 +6351,8 @@ CREATE PROCEDURE p1(v DATETIME) CREATE T
 CREATE PROCEDURE p2(v INT) CREATE TABLE t1 SELECT v;
 
 CALL p1(NOW());
+Warnings:
+Warning	1105	This function contains a query like 'CREATE TABLE ... SELECT local_var' and binary logging is enabled, that might cause problems. (something else? shorter?)
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -6357,6 +6364,7 @@ DROP TABLE t1;
 CALL p1('text');
 Warnings:
 Warning	1264	Out of range value adjusted for column 'v' at row 1
+Warning	1105	This function contains a query like 'CREATE TABLE ... SELECT local_var' and binary logging is enabled, that might cause problems. (something else? shorter?)
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -6366,6 +6374,8 @@ t1	CREATE TABLE `t1` (
 DROP TABLE t1;
 
 CALL p2(10);
+Warnings:
+Warning	1105	This function contains a query like 'CREATE TABLE ... SELECT local_var' and binary logging is enabled, that might cause problems. (something else? shorter?)
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -6377,6 +6387,7 @@ DROP TABLE t1;
 CALL p2('text');
 Warnings:
 Warning	1366	Incorrect integer value: 'text' for column 'v' at row 1
+Warning	1105	This function contains a query like 'CREATE TABLE ... SELECT local_var' and binary logging is enabled, that might cause problems. (something else? shorter?)
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (

=== modified file 'sql/sp_head.cc'
--- a/sql/sp_head.cc	2009-01-15 10:48:31 +0000
+++ b/sql/sp_head.cc	2009-01-28 17:54:52 +0000
@@ -947,6 +947,7 @@ subst_spvars(THD *thd, sp_instr *instr, 
 
   thd->query= pbuf;
   thd->query_length= qbuf.length();
+  thd->query_has_name_const= TRUE;
 
   DBUG_RETURN(FALSE);
 }
@@ -2604,6 +2605,7 @@ sp_instr_stmt::execute(THD *thd, uint *n
 
   query= thd->query;
   query_length= thd->query_length;
+  thd->query_has_name_const= FALSE;
   if (!(res= alloc_query(thd, m_query.str, m_query.length+1)) &&
       !(res=subst_spvars(thd, this, &m_query)))
   {
@@ -2621,6 +2623,7 @@ sp_instr_stmt::execute(THD *thd, uint *n
       *nextp= m_ip+1;
     thd->query= query;
     thd->query_length= query_length;
+    thd->query_has_name_const= FALSE;
   }
   DBUG_RETURN(res);
 }

=== modified file 'sql/sql_class.cc'
--- a/sql/sql_class.cc	2009-01-15 09:36:34 +0000
+++ b/sql/sql_class.cc	2009-01-28 17:54:52 +0000
@@ -217,6 +217,7 @@ THD::THD()
   one_shot_set= 0;
   file_id = 0;
   query_id= 0;
+  query_has_name_const= FALSE;
   warn_id= 0;
   db_charset= global_system_variables.collation_database;
   bzero(ha_data, sizeof(ha_data));

=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2008-12-04 01:01:03 +0000
+++ b/sql/sql_class.h	2009-01-28 17:54:52 +0000
@@ -1543,6 +1543,8 @@ public:
   bool	     no_warnings_for_error; /* no warnings on call to my_error() */
   /* set during loop of derived table processing */
   bool       derived_tables_processing;
+  /** there's name_const() substitution done, see sp_head.cc:subst_spvars() */
+  bool       query_has_name_const;
   my_bool    tablespace_op;	/* This is TRUE in DISCARD/IMPORT TABLESPACE */
 
   sp_rcontext *spcont;		// SP runtime context

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2009-01-15 10:48:31 +0000
+++ b/sql/sql_parse.cc	2009-01-28 17:54:52 +0000
@@ -3164,6 +3164,14 @@ mysql_execute_command(THD *thd)
     }
     if (select_lex->item_list.elements)		// With select
     {
+      if (thd->query_has_name_const && mysql_bin_log.is_open())
+      {
+        push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN, ER_UNKNOWN_ERROR,
+                     "This function contains a query like 'CREATE TABLE "
+                     "... SELECT local_var' and binary logging is enabled, "
+                     "that might cause problems. (something else? shorter?)"); 
+      }
+      
       select_result *sel_result;
 
       select_lex->options|= SELECT_NO_UNLOCK;