Bug #39182 Binary log producing incompatible character set query from stored procedure.
Submitted: 2 Sep 2008 10:42 Modified: 17 Oct 2008 18:12
Reporter: Daniel Fiske Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0.67, 5.0, 5.1, 6.0 bzr OS:Any (Linux, Windows)
Assigned to: Ramil Kalimullin CPU Architecture:Any
Tags: binary log, Illegal mix of collations, regression, replication

[2 Sep 2008 10:42] Daniel Fiske
Description:
MySQL produces SQL in the binary log that is not repeatable. This is similar in nature to Bug #21505 when placed in a stored procedure.

How to repeat:
1. Switch on binary logging.
2. 

CREATE DATABASE `bug_test` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;

USE bug_test;

CREATE TABLE `tbl_test_bug` (
                `Test` varchar(255) collate utf8_unicode_ci default NULL
              ) 
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_Test`()
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
	DECLARE _someString VARCHAR(255);
	SET _someString = "test";
	CREATE TEMPORARY TABLE tmp_test
	SELECT 
		* 
	from 
		tbl_test_bug
	WHERE
		Test like CONCAT("%",_someString,"%");
	DROP TEMPORARY TABLE tmp_test;
END$$

DELIMITER ;

CALL sp_Test(); -- This does not throw an error

3. Run mysqlbinlog.exe on the latest binary log. At the end you should see.

CREATE TEMPORARY TABLE tmp_test
        SELECT
                *
        from
                tbl_test_bug
        WHERE
                Test like CONCAT("%", NAME_CONST('_someString',_utf8'test'),"%")

/*!*/;

...This is a non-repeatable query which results in

ERROR 1267 (HY000): Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation 'like' 

Suggested fix:
none at this stage.
[2 Sep 2008 10:43] Daniel Fiske
This obviously breaks replication and any recovery using binary logs!
[2 Sep 2008 15:10] Sveta Smirnova
Thank you for the report.

Verified as described.
[2 Sep 2008 15:13] Sveta Smirnova
Bug was introduced in versions: 5.1.24, 5.0.60 and 6.0.6
[22 Sep 2008 15:26] 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/54457

2687 Ramil Kalimullin	2008-09-22
      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.
[1 Oct 2008 9:49] 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/54891

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.
[7 Oct 2008 19:55] Paul DuBois
Noted in 5.1.29 changelog.

References to local variables in stored procedures are replaced with 
NAME_CONST(name, value) when written to the binary log. However, an
illegal mix of collation error might occur when executing the log
contents if the value's collation differed from that of the variable.
Now information about the variable collation is written as well.

Leaving report status unchanged; this is early documentation of an upcoming push into 5.1.29.
[9 Oct 2008 17:25] Bugs System
Pushed into 5.0.72  (revid:ramil@mysql.com-20081001094847-krd414zoj0btxuvh) (version source revid:kgeorge@mysql.com-20081007152425-u1erltc3icobby3y) (pib:4)
[9 Oct 2008 17:55] Bugs System
Pushed into 5.1.30  (revid:ramil@mysql.com-20081001094847-krd414zoj0btxuvh) (version source revid:ramil@mysql.com-20081002055607-mt97wtrvsu1ugmnw) (pib:4)
[9 Oct 2008 18:43] Paul DuBois
Noted in 5.0.72 changelog.

Setting report to NDI pending push into 6.0.x.
[17 Oct 2008 16:41] Bugs System
Pushed into 6.0.8-alpha  (revid:ramil@mysql.com-20081001094847-krd414zoj0btxuvh) (version source revid:kgeorge@mysql.com-20081007153644-uypi14yjgque9obc) (pib:5)
[17 Oct 2008 18:12] Paul DuBois
Noted in 6.0.8 changelog.
[28 Oct 2008 21:02] Bugs System
Pushed into 5.1.29-ndb-6.2.17  (revid:ramil@mysql.com-20081001094847-krd414zoj0btxuvh) (version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)
[28 Oct 2008 22:20] Bugs System
Pushed into 5.1.29-ndb-6.3.19  (revid:ramil@mysql.com-20081001094847-krd414zoj0btxuvh) (version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)
[1 Nov 2008 9:45] Bugs System
Pushed into 5.1.29-ndb-6.4.0  (revid:ramil@mysql.com-20081001094847-krd414zoj0btxuvh) (version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)