Bug #16621 INSERTs in Stored Procedures causes data corruption in the Binary Log for 5.0.18
Submitted: 18 Jan 2006 23:13 Modified: 31 May 2006 16:47
Reporter: Chris Calender Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S1 (Critical)
Version:5.0.21 OS:Linux (Linux and Windows)
Assigned to: Alexander Nozdrin

[18 Jan 2006 23:13] Chris Calender
Description:
When you do an insert from a stored procedure, it instantly corrupts the data in the binary log.

How to repeat:
CREATE TABLE t1 ( a VARCHAR(10) );

CREATE PROCEDURE p1 ( a VARCHAR(10) ) INSERT INTO t1 (a) VALUES (a); CALL p1('test');

Then Examine the binary log - you'll see the corruption.

Suggested fix:
Not sure.
[18 Jan 2006 23:41] Chris Calender
I corrected the title from "... Binary ..." to "... Binary Log ...".
[19 Jan 2006 11:28] Alexander Nozdrin
This bug is related with BUG#15588 (String overrun during sp-vars.test).
The fix for BUG#15588 fixes also this bug.
[19 Jan 2006 13:59] Alexander Nozdrin
BUG#16378 (Stored procedure puts garbage in the binary log) was marked
as a duplicate of this bug.
[20 Jan 2006 15:00] Alexander Nozdrin
Patch for BUG#15588 and test case for this bug have been pushed into 5.0-tree,
currently tagged 5.0.18.
[20 Jan 2006 17:19] Alexander Nozdrin
A mistake in the previous post: currently tagged 5.0.19.
[2 Feb 2006 4:38] Mike Hillyer
When will this be pushed into 5.1? We really should have it pushed so we can create a changelog entry for both versions.
[2 Feb 2006 22: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/2104
[3 Feb 2006 9:14] Alexander Nozdrin
The patch has been copied into 5.1-release so that it get into 5.1.6.

The patch is still not merged into the main 5.1 tree. It will get there
after resolving 5.0 -> 5.1 merge logjam.
[6 Feb 2006 13:59] Alexander Nozdrin
Merged into 5.1 tree.
[6 Feb 2006 23:50] Mike Hillyer
Documented 5.1.6, 5.0.19:

    <listitem>
        <para>
          An <literal>INSERT</literal> statement in a stored procedure
          corrupted the binary log. (Bug #16621)
        </para>
      </listitem>
[24 Mar 2006 19:26] Kayra Otaner
Well,

I've installed 5.0.19 couple of days ago and I believe this problem is still there. But this time it fails because of ' (single quote). 
Stored procedure needs to insert a string similar to "O'taner". Single quote character brokes replication while same SP  works fine on the master. Please see below. (I've changed table name and var names with ZZZ) :

                 Last_Errno: 1064
                 Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TANER Z') )
        )
        ;' at line 11' on query. Default database: 'global'. Query: 'INSERT INTO ZZZ
        (
           ZZ
        ,  ZZ
        ,  ZZ
        )
        VALUES
        (
            NAME_CONST('ZZ',15277186)
        ,   NAME_CONST('ZZ',5)
        ,  LTRIM(  NAME_CONST('ZZ',_latin1'O'TANER, Z') )
        )
        ;'
[3 Apr 2006 15:45] Alexander Nozdrin
Kayra,

you hit related, but another issue -- it was reported as BUG##18293:
Values in stored procedure written to binlog unescaped.