From: Sergey Petrunia Date: February 10 2009 8:51am Subject: Re: bzr commit into mysql-5.0-bugteam branch (ramil:2730) Bug#35383 List-Archive: http://lists.mysql.com/commits/65705 Message-Id: <20090210085152.GV20995@pslp2.localdomain> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7BIT Hi! On Wed, Jan 28, 2009 at 05:55:04PM +0000, Ramil Kalimullin wrote: > #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 > A summary of our discussions on irc and some more notes: For 5.0 ------- 1. One problem of the fix is that it will cause a warning to be produced for statments like CREATE TABLE tmp AS SELECT sp_var; which are, as far I know, common and safe. There is a workaround though - they know sp_var's type and can change the SP to avoid warnings. 2. On the other hand, detection of sp variables in the select list is rather complicated because in order to do it properly one will have to walk into subquery predicates and all expressions they have (LEFT JOIN ON clauses, HAVING, ORDER BY, etc, etc). We don't have a code to do this ATM. 3. I get a warning when it is invoked from a stored function: mysql> create function f1 (a int) returns int modifies sql data begin declare x int ; create temporary table t2 as select x; drop temporary table t2; return a+1; end// Query OK, 0 rows affected (0.00 sec) mysql> select f1(2)// +-------+ | f1(2) | +-------+ | 3 | +-------+ 1 row in set, 1 warning (0.01 sec) mysql> show warnings// +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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?) | +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) This is wrong because functions are not ever binlogged in per-statement way. They are either not logged at all or logged as one statement. IIRC the right way to check if you're in a situation where the query won't be logged separately is: (gdb) p mysql_bin_log.is_query_in_union(thd, thd->query_id) $2 = true but please check that. For 5.1 ------- According to Andrei Elkin, there should be no problems when using binlog-format=mixed and binlog-format=row. We should * issue no warning when these modes are on Warning text ------------ Original: This function contains a query like 'CREATE TABLE ... SELECT local_var' and binary logging is enabled, that might cause problems. I think this one is better: Stored routine $dbname.$spname ran a statement that may cause problems with binary log, see "NAME_CONST issues" in Binary Logging of Stored Programs secntion of the manual. and we should put a description into the manual. > 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; > > > -- > MySQL Code Commits Mailing List > For list archives: http://lists.mysql.com/commits > To unsubscribe: http://lists.mysql.com/commits?unsub=sergefp@stripped > -- BR Sergey -- Sergey Petrunia, Lead Software Engineer MySQL AB, www.mysql.com Office: N/A Blog: http://s.petrunia.net/blog