List:Commits« Previous MessageNext Message »
From:Ramil Kalimullin Date:March 25 2009 4:48pm
Subject:bzr commit into mysql-5.0-bugteam branch (ramil:2772) Bug#35383
View as plain text  
#At file:///home/ram/mysql/stage/mysql-5.0-pe-stage/ based on revid:joro@stripped

 2772 Ramil Kalimullin	2009-03-25
      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 FUNC(local_var ..."
      are logged as
      "CREATE TABLE ... SELECT FUNC(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 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").
     @ mysql-test/r/binlog.result
        Fix for bug#35383: binlog playback and replication breaks
        due to name_const substitution
          - test result.
     @ mysql-test/t/binlog.test
        Fix for bug#35383: binlog playback and replication breaks
        due to name_const substitution
          - test case.
     @ sql/sp_head.cc
        Fix for bug#35383: binlog playback and replication breaks 
        due to name_const substitution
          - set thd->query_name_consts if there's NAME_CONST()
        substitution(s).
     @ 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:
      mysql-test/r/binlog.result
      mysql-test/t/binlog.test
      sql/sp_head.cc
      sql/sql_class.cc
      sql/sql_class.h
      sql/sql_parse.cc
=== 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-03-25 16:48:10 +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	Invoked routine ran a statement that may cause problems with binary log, see 'NAME_CONST issues' in 'Binary Logging of Stored Programs' section of the manual.
 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
@@ -613,4 +615,42 @@ DROP PROCEDURE p1;
 DROP TABLE t1;
 DROP DATABASE bug39182;
 USE test;
+CREATE PROCEDURE p1(IN v1 INT)
+BEGIN
+CREATE TABLE t1 SELECT v1;
+DROP TABLE t1;
+END//
+CREATE PROCEDURE p2()
+BEGIN
+DECLARE v1 INT;
+CREATE TABLE t1 SELECT v1+1;
+DROP TABLE t1;
+END//
+CREATE PROCEDURE p3(IN v1 INT)
+BEGIN
+CREATE TABLE t1 SELECT 1 FROM DUAL WHERE v1!=0;
+DROP TABLE t1;
+END//
+CREATE PROCEDURE p4(IN v1 INT)
+BEGIN
+DECLARE v2 INT;
+CREATE TABLE t1 SELECT 1, v1, v2;
+DROP TABLE t1;
+CREATE TABLE t1 SELECT 1, v1+1, v2;
+DROP TABLE t1;
+END//
+CALL p1(1);
+CALL p2();
+Warnings:
+Warning	1105	Invoked routine ran a statement that may cause problems with binary log, see 'NAME_CONST issues' in 'Binary Logging of Stored Programs' section of the manual.
+CALL p3(0);
+Warnings:
+Warning	1105	Invoked routine ran a statement that may cause problems with binary log, see 'NAME_CONST issues' in 'Binary Logging of Stored Programs' section of the manual.
+CALL p4(0);
+Warnings:
+Warning	1105	Invoked routine ran a statement that may cause problems with binary log, see 'NAME_CONST issues' in 'Binary Logging of Stored Programs' section of the manual.
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
+DROP PROCEDURE p3;
+DROP PROCEDURE p4;
 End of 5.0 tests

=== modified file 'mysql-test/t/binlog.test'
--- a/mysql-test/t/binlog.test	2008-10-01 09:48:47 +0000
+++ b/mysql-test/t/binlog.test	2009-03-25 16:48:10 +0000
@@ -161,4 +161,44 @@ DROP TABLE t1;
 DROP DATABASE bug39182;
 USE test;
 
+#
+# Bug#35383: binlog playback and replication breaks due to 
+# name_const substitution
+#
+DELIMITER //;
+CREATE PROCEDURE p1(IN v1 INT)
+BEGIN
+  CREATE TABLE t1 SELECT v1;
+  DROP TABLE t1;
+END//
+CREATE PROCEDURE p2()
+BEGIN
+  DECLARE v1 INT;
+  CREATE TABLE t1 SELECT v1+1;
+  DROP TABLE t1;
+END//
+CREATE PROCEDURE p3(IN v1 INT)
+BEGIN
+  CREATE TABLE t1 SELECT 1 FROM DUAL WHERE v1!=0;
+  DROP TABLE t1;
+END//
+CREATE PROCEDURE p4(IN v1 INT)
+BEGIN
+  DECLARE v2 INT;
+  CREATE TABLE t1 SELECT 1, v1, v2;
+  DROP TABLE t1;
+  CREATE TABLE t1 SELECT 1, v1+1, v2;
+  DROP TABLE t1;
+END//
+DELIMITER ;//
+
+CALL p1(1);
+CALL p2();
+CALL p3(0);
+CALL p4(0);
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
+DROP PROCEDURE p3;
+DROP PROCEDURE p4;
+
 --echo End of 5.0 tests

=== modified file 'sql/sp_head.cc'
--- a/sql/sp_head.cc	2009-02-10 22:47:54 +0000
+++ b/sql/sp_head.cc	2009-03-25 16:48:10 +0000
@@ -894,6 +894,8 @@ subst_spvars(THD *thd, sp_instr *instr, 
   qbuf.length(0);
   cur= query_str->str;
   prev_pos= res= 0;
+  thd->query_name_consts= 0;
+  
   for (Item_splocal **splocal= sp_vars_uses.front(); 
        splocal < sp_vars_uses.back(); splocal++)
   {
@@ -927,6 +929,8 @@ subst_spvars(THD *thd, sp_instr *instr, 
     res|= qbuf.append(')');
     if (res)
       break;
+      
+    thd->query_name_consts++;
   }
   res|= qbuf.append(cur + prev_pos, query_str->length - prev_pos);
   if (res)
@@ -2621,6 +2625,7 @@ sp_instr_stmt::execute(THD *thd, uint *n
       *nextp= m_ip+1;
     thd->query= query;
     thd->query_length= query_length;
+    thd->query_name_consts= 0;
   }
   DBUG_RETURN(res);
 }

=== modified file 'sql/sql_class.cc'
--- a/sql/sql_class.cc	2009-03-19 20:20:15 +0000
+++ b/sql/sql_class.cc	2009-03-25 16:48:10 +0000
@@ -219,6 +219,7 @@ THD::THD()
   one_shot_set= 0;
   file_id = 0;
   query_id= 0;
+  query_name_consts= 0;
   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	2009-03-05 10:10:44 +0000
+++ b/sql/sql_class.h	2009-03-25 16:48:10 +0000
@@ -1556,6 +1556,9 @@ public:
   sp_cache   *sp_proc_cache;
   sp_cache   *sp_func_cache;
 
+  /** number of name_const() substitutions, see sp_head.cc:subst_spvars() */
+  uint       query_name_consts;
+
   /*
     If we do a purge of binary logs, log index info of the threads
     that are currently reading it needs to be adjusted. To do that

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2009-03-25 13:37:21 +0000
+++ b/sql/sql_parse.cc	2009-03-25 16:48:10 +0000
@@ -3211,6 +3211,42 @@ mysql_execute_command(THD *thd)
     }
     if (select_lex->item_list.elements)		// With select
     {
+      /*
+        If:
+        a) we inside an SP and there was NAME_CONST substitution,
+        b) binlogging is on,
+        c) we log the SP as separate statements
+        raise a warning, as it may cause problems
+        (see 'NAME_CONST issues' in 'Binary Logging of Stored Programs')
+       */
+      if (thd->query_name_consts && 
+          mysql_bin_log.is_open() &&
+          !mysql_bin_log.is_query_in_union(thd, thd->query_id))
+      {
+        List_iterator_fast<Item> it(select_lex->item_list);
+        Item *item;
+        uint splocal_refs= 0;
+        /* Count SP local vars in the top-level SELECT list */
+        while ((item= it++))
+        {
+          if (item->is_splocal())
+            splocal_refs++;
+        }
+        /*
+          If it differs from number of NAME_CONST substitution applied,
+          we may have a SOME_FUNC(NAME_CONST()) in the SELECT list,
+          that may cause a problem with binary log (see BUG#35383),
+          raise a warning. 
+        */
+        if (splocal_refs != thd->query_name_consts)
+          push_warning(thd, 
+                       MYSQL_ERROR::WARN_LEVEL_WARN,
+                       ER_UNKNOWN_ERROR,
+"Invoked routine ran a statement that may cause problems with "
+"binary log, see 'NAME_CONST issues' in 'Binary Logging of Stored Programs' "
+"section of the manual.");
+      }
+      
       select_result *sel_result;
 
       select_lex->options|= SELECT_NO_UNLOCK;


Attachment: [text/bzr-bundle] bzr/ramil@mysql.com-20090325164810-6f2p315p5bbigygp.bundle
Thread
bzr commit into mysql-5.0-bugteam branch (ramil:2772) Bug#35383Ramil Kalimullin25 Mar