Bug #11312 DELIMITER is not written to the binary log that causes syntax error
Submitted: 14 Jun 2005 11:53 Modified: 28 Nov 2006 12:29
Reporter: Victoria Reznichenko Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Replication Severity:S1 (Critical)
Version:5.0.23-BK, 5.0.22, 5.0.7 OS:Any (any)
Assigned to: Assigned Account CPU Architecture:Any

[14 Jun 2005 11:53] Victoria Reznichenko
Description:
DILIMITER command isn't written to the binary log and it causes syntax error if you try to restore database from binary logs.

How to repeat:
1. run test_case.sql and look into binary logs.
[27 Aug 2005 22:40] Alexander Pachev
The problem here is that DELIMITER is a mysql command line client/mysqltest command. It never gets passed to the server, so it cannot binlog it. This can be fixed in mysqlbinlog by parsing the text of the query, and guessing a good delimiter for the output. However, I am going to discuss it with the replication team to see if there might be a better way before implementing the fix.
[11 Sep 2005 3:39] Alexander Pachev
Submitted for review a patch for mysqlbinlog to connect to the server to which the backup is being restored. This eliminates the need for DELIMITER in the binary log or the output of mysqlbinlog. Note that since DELIMITER is a client command, the server has no knowledge of it, and therefore cannot binlog it. So the most reasonable place to fix the problem is mysqlbinlog.
[25 Sep 2005 2:28] Alexander Pachev
Rewrote the patch, submitted it to Elliot for review.
[22 Oct 2005 4:05] 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/internals/31345
[7 Dec 2005 2:57] 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/internals/33110
[27 Jan 2006 17:22] Alexander Pachev
fixed in 5.0.19. The problem was that mysqlbinlog was not adding delimiter to properly deal with the stored procedure syntax needed for mysql command line client to handle it.
[2 Feb 2006 5:18] Mike Hillyer
Documented in 5.0.19 changelog:

      <listitem>
        <para>
          The <command>mysqlbinlog</command> utility did not output
          <literal>DELIMITER</literal> statements, causing syntax errors
          for stored procedure creation statements. (Bug #11312)
        </para>
      </listitem>
[1 Jul 2006 9:00] Valeriy Kravchuk
Bug #20798 marked as a duplicate of this one (it is about triggers, but there is no difference). 

This bug is repeatable (with both original and that new test cases), on 5.0.22 and 5.0.23-BK. Moreover, I see no tracks of the patches in the code any more:

openxs@suse:~/work/mysql-5.0> grep -n print_open_delim client/mysqlbinlog.cc
openxs@suse:~/work/mysql-5.0> grep -n replay client/*
client/log_event.cc:1032:  only an information, it does not produce suitable que
ries to replay (for
client/log_event.cc:2284:    information to replay this transaction on the slave
; all we can do
openxs@suse:~/work/mysql-5.0> bk changes | head
ChangeSet@1.2210, 2006-07-01 09:28:41+04:00...

There is nothing about replay options in http://dev.mysql.com/doc/refman/5.0/en/mysqlbinlog.html.

So, my questions: is this bug really fixed, and, if yes, how? How to use mysqlbinlog for replaying triggers and stored procedures?
[12 Jul 2006 6:58] 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/9077
[12 Jul 2006 8:18] Greg Lehey
No, the fix mentioned on 27 January never seems to have been applied.  I'm currently merging it.

The patch just committed is a prerequisite for the work.  There's more to do.
[31 Jul 2006 18:42] Brandon Fosdick
Any progress on this?

BTW, it looks like http://bugs.mysql.com/bug.php?id=20396 is related.
[4 Aug 2006 7:57] 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/10036

ChangeSet@1.2232, 2006-08-04 17:27:12+09:30, grog@eucla.lemis.com +1 -0
  log_event.cc:
    
    BUG#11312: DELIMITER is not written to the binary log that causes
               syntax error
  
    Query_log_event::print(): Check contents of event for stored
                              procedure.  If found, emit DELIMITER
                              commands on either side.
[10 Sep 2006 17:51] Brandon Fosdick
Is this patch included in 5.0.24a? I don't see it in the list of changes at http://dev.mysql.com/doc/refman/5.0/en/news-5-0-x.html. 

If it isn't included, any idea when it will be? Replicating triggers by hand is getting old.
[28 Nov 2006 12:29] Alexander Barkov
Bug#11312 and Bug#20396 are duplicates.

A patch has been commited for #20396,
marking this one as a diplicate.