From: Date: October 1 2008 11:48am Subject: bzr commit into mysql-5.1-bugteam branch (ramil:2687) Bug#39182 List-Archive: http://lists.mysql.com/commits/54891 X-Bug: 39182 Message-Id: <200810010948.m919mv87005518@localhost.localdomain> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit #At file:///home/ram/mysql/mysql-5.0-5.1.29-rc/ 2687 Ramil Kalimullin 2008-10-01 Fix for bug#39182: Binary log producing incompatible character set query from stored procedure. Problem: we replace all references to local variables in stored procedures with NAME_CONST(name, value) logging to the binary log. However, if the value's collation differs we might get an 'illegal mix of collation' error as we don't pass the collation to the function. Fix: pass the value's collation to NAME_CONST(). Note: actually we should pass to NAME_CONST() the value's derivation as well. It's impossible without the parser modifying. Now we always set the derivation to DERIVATION_IMPLICIT, the same as local variables have. modified: mysql-test/r/binlog.result mysql-test/r/ctype_cp932_binlog.result mysql-test/r/rpl_sp.result mysql-test/t/binlog.test sql/item.cc sql/sp_head.cc per-file messages: mysql-test/r/binlog.result Fix for bug#39182: Binary log producing incompatible character set query from stored procedure. - test result. mysql-test/r/ctype_cp932_binlog.result Fix for bug#39182: Binary log producing incompatible character set query from stored procedure. - results adjusted. mysql-test/r/rpl_sp.result Fix for bug#39182: Binary log producing incompatible character set query from stored procedure. - results adjusted. mysql-test/t/binlog.test Fix for bug#39182: Binary log producing incompatible character set query from stored procedure. - test case. sql/item.cc Fix for bug#39182: Binary log producing incompatible character set query from stored procedure. - allow NAME_CONST() to get _charset'foo' COLLATE 'bar' strings (see Item_func_set_collation). sql/sp_head.cc Fix for bug#39182: Binary log producing incompatible character set query from stored procedure. - pass the value's collation to NAME_CONST(). === modified file 'mysql-test/r/binlog.result' --- a/mysql-test/r/binlog.result 2008-02-08 10:55:55 +0000 +++ b/mysql-test/r/binlog.result 2008-10-01 09:48:47 +0000 @@ -582,4 +582,35 @@ master-bin.000001 4 Format_desc 1 98 Ser master-bin.000001 98 Query 1 219 use `test`; create table t1 (a bigint unsigned, b bigint(20) unsigned) master-bin.000001 219 Query 1 343 use `test`; insert into t1 values (9999999999999999,14632475938453979136) master-bin.000001 343 Query 1 419 use `test`; drop table t1 +CREATE DATABASE bug39182 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; +USE bug39182; +CREATE TABLE t1 (a VARCHAR(255) COLLATE utf8_unicode_ci) +DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +CREATE PROCEDURE p1() +BEGIN +DECLARE s1 VARCHAR(255); +SET s1= "test"; +CREATE TEMPORARY TABLE tmp1 +SELECT * FROM t1 WHERE a LIKE CONCAT("%", s1, "%"); +SELECT +COLLATION(NAME_CONST('s1', _utf8'test')) c1, +COLLATION(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) c2, +COLLATION(s1) c3, +COERCIBILITY(NAME_CONST('s1', _utf8'test')) d1, +COERCIBILITY(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) d2, +COERCIBILITY(s1) d3; +DROP TEMPORARY TABLE tmp1; +END// +CALL p1(); +c1 c2 c3 d1 d2 d3 +utf8_general_ci utf8_unicode_ci utf8_unicode_ci 2 2 2 +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 +SELECT * FROM t1 WHERE a LIKE CONCAT("%", NAME_CONST('s1',_utf8'test' COLLATE 'utf8_unicode_ci'), "%") +master-bin.000001 1483 Query 1 1575 use `bug39182`; DROP TEMPORARY TABLE tmp1 +DROP PROCEDURE p1; +DROP TABLE t1; +DROP DATABASE bug39182; +USE test; End of 5.0 tests === modified file 'mysql-test/r/ctype_cp932_binlog.result' --- a/mysql-test/r/ctype_cp932_binlog.result 2008-05-15 23:13:24 +0000 +++ b/mysql-test/r/ctype_cp932_binlog.result 2008-10-01 09:48:47 +0000 @@ -40,6 +40,6 @@ IN ind DECIMAL(10,2)) BEGIN INSERT INTO t4 VALUES (ins1, ins2, ind); END -master-bin.000001 777 Query 1 988 use `test`; INSERT INTO t4 VALUES ( NAME_CONST('ins1',_latin1 0x466F6F2773206120426172), NAME_CONST('ins2',_cp932 0xED40ED41ED42), NAME_CONST('ind',47.93)) -master-bin.000001 988 Query 1 1077 use `test`; DROP PROCEDURE bug18293 -master-bin.000001 1077 Query 1 1156 use `test`; DROP TABLE t4 +master-bin.000001 777 Query 1 1044 use `test`; INSERT INTO t4 VALUES ( NAME_CONST('ins1',_latin1 0x466F6F2773206120426172 COLLATE 'latin1_swedish_ci'), NAME_CONST('ins2',_cp932 0xED40ED41ED42 COLLATE 'cp932_japanese_ci'), NAME_CONST('ind',47.93)) +master-bin.000001 1044 Query 1 1133 use `test`; DROP PROCEDURE bug18293 +master-bin.000001 1133 Query 1 1212 use `test`; DROP TABLE t4 === modified file 'mysql-test/r/rpl_sp.result' --- a/mysql-test/r/rpl_sp.result 2008-05-16 15:26:29 +0000 +++ b/mysql-test/r/rpl_sp.result 2008-10-01 09:48:47 +0000 @@ -502,7 +502,7 @@ master-bin.000001 # Query 1 # use `test` master-bin.000001 # Query 1 # use `test`; CREATE TABLE t1(col VARCHAR(10)) master-bin.000001 # Query 1 # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`(arg VARCHAR(10)) INSERT INTO t1 VALUES(arg) -master-bin.000001 # Query 1 # use `test`; INSERT INTO t1 VALUES( NAME_CONST('arg',_latin1'test')) +master-bin.000001 # Query 1 # use `test`; INSERT INTO t1 VALUES( NAME_CONST('arg',_latin1'test' COLLATE 'latin1_swedish_ci')) master-bin.000001 # Query 1 # use `test`; DROP PROCEDURE p1 master-bin.000001 # Query 1 # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() SET @a = 1 @@ -841,7 +841,7 @@ CREATE DEFINER=`root`@`localhost` PROCED INSERT INTO t1 VALUES(arg) /*!*/; SET TIMESTAMP=t/*!*/; -INSERT INTO t1 VALUES( NAME_CONST('arg',_latin1'test')) +INSERT INTO t1 VALUES( NAME_CONST('arg',_latin1'test' COLLATE 'latin1_swedish_ci')) /*!*/; SET TIMESTAMP=t/*!*/; DROP PROCEDURE p1 === modified file 'mysql-test/t/binlog.test' --- a/mysql-test/t/binlog.test 2008-02-08 10:55:55 +0000 +++ b/mysql-test/t/binlog.test 2008-10-01 09:48:47 +0000 @@ -123,4 +123,42 @@ drop table t1; --replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ /Server ver: [^,]*,/Server version,/ show binlog events from 0; + +# +# Bug #39182: Binary log producing incompatible character set query from +# stored procedure. +# +CREATE DATABASE bug39182 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; +USE bug39182; +CREATE TABLE t1 (a VARCHAR(255) COLLATE utf8_unicode_ci) + DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; + +DELIMITER //; + +CREATE PROCEDURE p1() +BEGIN + DECLARE s1 VARCHAR(255); + SET s1= "test"; + CREATE TEMPORARY TABLE tmp1 + SELECT * FROM t1 WHERE a LIKE CONCAT("%", s1, "%"); + SELECT + COLLATION(NAME_CONST('s1', _utf8'test')) c1, + COLLATION(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) c2, + COLLATION(s1) c3, + COERCIBILITY(NAME_CONST('s1', _utf8'test')) d1, + COERCIBILITY(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) d2, + COERCIBILITY(s1) d3; + DROP TEMPORARY TABLE tmp1; +END// + +DELIMITER ;// + +CALL p1(); +SHOW BINLOG EVENTS FROM 1285; + +DROP PROCEDURE p1; +DROP TABLE t1; +DROP DATABASE bug39182; +USE test; + --echo End of 5.0 tests === modified file 'sql/item.cc' --- a/sql/item.cc 2008-09-18 12:55:36 +0000 +++ b/sql/item.cc 2008-10-01 09:48:47 +0000 @@ -1217,10 +1217,12 @@ Item_name_const::Item_name_const(Item *n if (!(valid_args= name_item->basic_const_item() && (value_item->basic_const_item() || ((value_item->type() == FUNC_ITEM) && - (((Item_func *) value_item)->functype() == - Item_func::NEG_FUNC) && + ((((Item_func *) value_item)->functype() == + Item_func::COLLATE_FUNC) || + ((((Item_func *) value_item)->functype() == + Item_func::NEG_FUNC) && (((Item_func *) value_item)->key_item()->type() != - FUNC_ITEM))))) + FUNC_ITEM))))))) my_error(ER_WRONG_ARGUMENTS, MYF(0), "NAME_CONST"); Item::maybe_null= TRUE; } === modified file 'sql/sp_head.cc' --- a/sql/sp_head.cc 2008-08-11 16:10:00 +0000 +++ b/sql/sp_head.cc 2008-10-01 09:48:47 +0000 @@ -123,6 +123,9 @@ sp_get_item_value(THD *thd, Item *item, if (cs->escape_with_backslash_is_dangerous) buf.append(' '); append_query_string(cs, result, &buf); + buf.append(" COLLATE '"); + buf.append(item->collation.collation->name); + buf.append('\''); str->copy(buf); return str;