MySQL Bugs: #39182: Binary log producing incompatible character set query from stored procedure.

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

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.

This obviously breaks replication and any recovery using binary logs!

Thank you for the report.

Verified as described.

Bug was introduced in versions: 5.1.24, 5.0.60 and 6.0.6

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.

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.

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.

Pushed into 5.0.72  (revid:ramil@mysql.com-20081001094847-krd414zoj0btxuvh) (version source revid:kgeorge@mysql.com-20081007152425-u1erltc3icobby3y) (pib:4)

Pushed into 5.1.30  (revid:ramil@mysql.com-20081001094847-krd414zoj0btxuvh) (version source revid:ramil@mysql.com-20081002055607-mt97wtrvsu1ugmnw) (pib:4)

Noted in 5.0.72 changelog.

Setting report to NDI pending push into 6.0.x.

Pushed into 6.0.8-alpha  (revid:ramil@mysql.com-20081001094847-krd414zoj0btxuvh) (version source revid:kgeorge@mysql.com-20081007153644-uypi14yjgque9obc) (pib:5)

Noted in 6.0.8 changelog.

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)

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)

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)