| Bug #35515 | Aliases of variables in binary log are ignored with NAME_CONST | ||
|---|---|---|---|
| Submitted: | 24 Mar 2008 16:42 | Modified: | 6 May 2009 14:22 |
| Reporter: | Alexander Belokapov | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.1.23-rc, 5.0.56 | OS: | Linux |
| Assigned to: | Zhenxing He | CPU Architecture: | Any |
| Tags: | alias, binary log, name_const, replication, stored procedure | ||
[24 Mar 2008 19:19]
Sveta Smirnova
Thank you for the report. I can not repeat described behavior with current development sources. Please indicate accurate version of MySQL you use.
[26 Mar 2008 14:50]
Alexander Belokapov
I'm sorry, I have submitted a wrong setup for reproducing the bug. There needs to be an insert statement in the stored procedure for the problem to occur.
How to repeat:
drop database if exists testdb;
create database testdb;
use testdb;
create table table1 (id int);
-- create a procedure that has a column alias in a subquery
delimiter $$
drop procedure if exists test_procedure$$
create procedure test_procedure(_id int)
begin
insert into table1(id)
select a.id
from
( select _id as id ) a;
end$$
delimiter ;
-- enable the binary log, then call the procedure:
call test_procedure(1234);
-- the binary log should now contain the following:
insert into table1(id)
select a.id
from
( select NAME_CONST('_id',123) as id ) a;
-- the query will fail during replication, because the alias "id" is ignored, the subquery "a" having a single column called "_id"
[31 Mar 2008 18:32]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described.
[5 Mar 2009 20:19]
be bo
Going on a year old, any update on this? Work-arounds? Thanks.
[17 Mar 2009 11:29]
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/69410 2714 Leonard Zhou 2009-03-17 BUG#35515 Aliases of variables in binary log are ignored with NAME_CONST. When add an aliase name after NAME_CONST, the aliase name will be ignored. Our solution is that set the column to aliase name if the column name is not autogenerated. That means if we have an aliase name after NAME_CONST, we will set aliase name.
[19 Mar 2009 3:06]
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/69672 2714 Leonard Zhou 2009-03-19 BUG#35515 Aliases of variables in binary log are ignored with NAME_CONST. When add an aliase name after NAME_CONST, the aliase name will be overwrite. NAME_CONST will set the field's name to aliase name if there is an aliase.
[19 Mar 2009 10:12]
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/69735 2714 Leonard Zhou 2009-03-19 BUG#35515 Aliases of variables in binary log are ignored with NAME_CONST. When add an aliase name after NAME_CONST, the aliase name will be overwrite. NAME_CONST will re-sets the field's name only if there is not an aliase in the function fix-fields(). If there is an aliase, NAME_CONST doesn't re-set the field's name and keeps the old name.
[26 Mar 2009 7:45]
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/70475 2714 Leonard Zhou 2009-03-26 BUG#35515 Aliases of variables in binary log are ignored with NAME_CONST. When add an aliase name after NAME_CONST, the aliase name will be overwrite. NAME_CONST will re-set the field's name only if there isn't an aliase in the function fix-fields(). If there is an aliase, NAME_CONST doesn't re-set the field's name and keeps the old name. @ mysql-test/r/func_misc.result Test result. @ mysql-test/r/rpl_name_const.result Test case. @ mysql-test/t/func_misc.test Add NAME_CONST test. @ mysql-test/t/rpl_name_const.test Test result. @ sql/item.cc Re-set field's name if the name is autogenerated, that mean without aliase.
[27 Mar 2009 3:40]
li zhou
Pushed into 5.0-bugteam, 5.1-bugteam, 6.0-bugteam
[5 May 2009 18:53]
Bugs System
Pushed into 5.0.82 (revid:davi.arnaut@sun.com-20090505184158-dvmedh8n472y8np5) (version source revid:davi.arnaut@sun.com-20090505184158-dvmedh8n472y8np5) (merge vers: 5.0.82) (pib:6)
[5 May 2009 19:41]
Bugs System
Pushed into 5.1.35 (revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (version source revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (merge vers: 5.1.35) (pib:6)
[6 May 2009 11:33]
Jon Stephens
Documented bugfix in the 5.0.82 and 5.1.35 changelogs as follows:
Column alises used inside subqueries were ignored in the binary log.
Status->NDI: waiting for 6.0 merge/version info.
[6 May 2009 14:08]
Bugs System
Pushed into 6.0.12-alpha (revid:svoj@sun.com-20090506125450-yokcmvqf2g7jhujq) (version source revid:horst@mysql.com-20090327184517-25eq077q2beocs6y) (merge vers: 6.0.11-alpha) (pib:6)
[6 May 2009 14:22]
Jon Stephens
Fix also noted in the 6.0.12 changelog. Closed.
[15 Jun 2009 8:28]
Bugs System
Pushed into 5.1.35-ndb-6.3.26 (revid:jonas@mysql.com-20090615074202-0r5r2jmi83tww6sf) (version source revid:jonas@mysql.com-20090615070837-9pccutgc7repvb4d) (merge vers: 5.1.35-ndb-6.3.26) (pib:6)
[15 Jun 2009 9:07]
Bugs System
Pushed into 5.1.35-ndb-7.0.7 (revid:jonas@mysql.com-20090615074335-9hcltksp5cu5fucn) (version source revid:jonas@mysql.com-20090615072714-rmfkvrbbipd9r32c) (merge vers: 5.1.35-ndb-7.0.7) (pib:6)
[15 Jun 2009 9:48]
Bugs System
Pushed into 5.1.35-ndb-6.2.19 (revid:jonas@mysql.com-20090615061520-sq7ds4yw299ggugm) (version source revid:jonas@mysql.com-20090615054654-ebgpz7elwu1xj36j) (merge vers: 5.1.35-ndb-6.2.19) (pib:6)
Description: When logging stored procedures in the Binary log, MySQL automatically substitutes the values of variables with an expression like this: select _id; becomes: select NAME_CONST('_id',1234); However, if column aliases are used, they are ignored, which leads to unexpected column names and the queries might not be valid any more. For example, the following query in a stored procedure: select a.id from ( select _id as id ) a; becomes in the binary log: select a.id from ( select NAME_CONST('_id',1234) as id ) a; In this case the subquery "a" ends up with a single column "_id", rather than "id", as expected, and the query fails. In my case this stops the replication process with an error 1054 ("Error 'Unknown column 'a.id' in 'field list'' on query...."). How to repeat: drop database if exists testdb; create database testdb; use testdb; -- create a procedure that has a column alias in a subquery delimiter $$ drop procedure if exists test_procedure$$ create procedure test_procedure(_id int) begin select a.id from ( select _id as id ) a; end$$ delimiter ; -- enable the binary log then call the procedure: call test_procedure(1234); -- the binary log should now contain the following: select a.id from ( select NAME_CONST('_id',1234) as id ) a; -- the query will fail during replication, because the alias "id" is ignored, the subquery "a" having a single column called "_id"