Bug #16621 INSERTs in Stored Procedures causes data corruption in the Binary Log for 5.0.18
Submitted: 19 Jan 2006 0:13 Modified: 31 May 2006 18:47
Reporter: Chris Calender
Status: Closed
Category:Server: SP Severity:S1 (Critical)
Version:5.0.21 OS:Linux (Linux and Windows)
Assigned to: Alexander Nozdrin Target Version:

[19 Jan 2006 0: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.
[19 Jan 2006 0:41] Chris Calender 
I corrected the title from "... Binary ..." to "... Binary Log ...".
[19 Jan 2006 12: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 14:59] Alexander Nozdrin 
BUG#16378 (Stored procedure puts garbage in the binary log) was marked
as a duplicate of this bug.
[20 Jan 2006 16: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 18:19] Alexander Nozdrin 
A mistake in the previous post: currently tagged 5.0.19.
[2 Feb 2006 5: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 23: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 10: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 14:59] Alexander Nozdrin 
Merged into 5.1 tree.
[7 Feb 2006 0: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 20: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 17:45] Alexander Nozdrin 
Kayra,

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