| Bug #35383 | binlog playback and replication breaks due to name_const substitution | ||
|---|---|---|---|
| Submitted: | 18 Mar 2008 9:01 | Modified: | 13 Apr 2009 19:10 |
| Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S2 (Serious) |
| Version: | 5.0.56 | OS: | Any |
| Assigned to: | Ramil Kalimullin | CPU Architecture: | Any |
[18 Mar 2008 10:18]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described.
[25 Sep 2008 10:12]
Mats Kindahl
This is a bug in how the stored procedures code generates a name_const() reference. A related bug is BUG#39182.
[28 Jan 2009 17:55]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/64371 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").
[12 Feb 2009 9:42]
Sergey Petrunya
(review feedback was provided via email)
[25 Mar 2009 16:48]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/70388 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 ...".
[25 Mar 2009 19:50]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/70432 3170 Ramil Kalimullin 2009-03-25 [merge] Fix for bug#35383 null-merged to 6.0
[27 Mar 2009 14:31]
Bugs System
Pushed into 5.0.80 (revid:joro@sun.com-20090327142516-55gumdxj39z6eijj) (version source revid:ramil@mysql.com-20090325164810-6f2p315p5bbigygp) (merge vers: 5.0.80) (pib:6)
[27 Mar 2009 14:56]
Bugs System
Pushed into 5.1.34 (revid:joro@sun.com-20090327143448-wuuuycetc562ty6o) (version source revid:aelkin@mysql.com-20090326092807-d3hhx4beei09hvhw) (merge vers: 5.1.34) (pib:6)
[1 Apr 2009 19:06]
Paul DuBois
Noted in 5.1.80, 5.1.34 changelogs.
The use of NAME_CONST() can result in a problem for CREATE TABLE ...
SELECT statements when the source column expressions refer to local
variables. Converting these references to NAME_CONST() expressions
can result in column names that are different on the master and slave
servers, or names that are too long to be legal column identifiers. A
workaround is to supply aliases for columns that refer to local
variables.
Now a warning is issued in such cases that indicate possible
problems.
Added the following to the section on binary logging of stored routines:
http://dev.mysql.com/doc/refman/5.1/en/stored-programs-logging.html
The use of NAME_CONST() can result in a problem for CREATE TABLE ...
SELECT statements when the source column expressions refer to local
variables. Converting these references to NAME_CONST() expressions
can result in column names that are different on the master and slave
servers, or names that are too long to be legal column identifiers. A
workaround is to supply aliases for columns that refer to local
variables. Consider this statement when myvar has a value of 1:
CREATE TABLE t1 SELECT myvar;
This will be rewritten as follows:
CREATE TABLE t1 SELECT NAME_CONST(myvar, 1);
To ensure that the master and slave tables have the same column
names, write the statement like this:
CREATE TABLE t1 SELECT myvar AS myvar;
The rewritten statement becomes:
CREATE TABLE t1 SELECT NAME_CONST(myvar, 1) AS myvar;
Setting report to NDI pending push into 6.0.x.
[2 Apr 2009 14:22]
Paul DuBois
Correction. This is noted in the 5.0.80, 5.1.34 changelogs, not the 5.1.80, 5.1.34 changelogs.
[13 Apr 2009 9:21]
Bugs System
Pushed into 6.0.11-alpha (revid:alik@sun.com-20090413084402-snnrocwzktcl88ny) (version source revid:ramil@mysql.com-20090325194931-zpc5qb8rnki5mgoi) (merge vers: 6.0.11-alpha) (pib:6)
[13 Apr 2009 19:10]
Paul DuBois
Noted in 6.0.11 changelog.
[9 May 2009 16:39]
Bugs System
Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (merge vers: 5.1.34-ndb-6.2.18) (pib:6)
[9 May 2009 17:36]
Bugs System
Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (merge vers: 5.1.34-ndb-6.3.25) (pib:6)
[9 May 2009 18:34]
Bugs System
Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (merge vers: 5.1.34-ndb-7.0.6) (pib:6)
[10 Feb 2010 15:40]
MySQL Verification Team
this bug is not fixed. the binlogs still cannot be played back and the slave still breaks...
Description: If there are statements in stored routines such as : create temporary table t1 select substring(a,1,10); The server uses name_const() to handle the user variables used in expressions. The table t1 now can get a much longer column name on the slave than on the master. When the column names exceeds 64 chars we get an error. In "how to repeat" you'll see the create statement is wrongly logged like this: mysql> create temporary table t1 select substring( NAME_CONST('a',_latin1'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),1,10); ERROR 1166 (42000): Incorrect column name 'substring( NAME_CONST('a',_latin1'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),1,1' Therefore any user who can create an SP can break all replicating slaves, as well as binlog playbacks in the event of a system restore! How to repeat: delimiter // drop procedure if exists p1// create procedure p1() begin declare a varchar(200); set a=repeat('a',60); drop temporary table if exists t1; create temporary table t1 select substring(a,1,10); end// delimiter ; call p1(); show binlog events; (now try to execute the create statement that was logged in the binlog). Suggested fix: A workaround of course is to alias the columns used. so in this case: create temporary table t1 select substring(a,1,10) a; But how do we force all users to use column aliases?