MySQL Bugs: #57873: Backtick in savepoint name causes replication failure

Bug #57873	Backtick in savepoint name causes replication failure
Submitted:	31 Oct 2010 14:28	Modified:	10 Feb 2011 6:31
Reporter:	Elena Stepanova	Email Updates:
Status:	In progress	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1, 5.5	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
If a savepoint name contains a backtick, it breaks replication.

This is a tiny leftover of the fix bug#55961 (not a regression, as it would break in the previous versions also). 

The original savepoint name can contain a backtick, either given as `na``me`, or as "na`me" (in ANSI_QUOTES mode). 
After the bugfix bug#55961, a savepoint name in the binary log is always enclosed with backticks; however, internal backticks in the name are not duplicated, so the binlog in both cases is written as `na`me`, which makes the slave SQL stop with a syntax error.

How to repeat:
--source include/master-slave.inc

--disable_warnings
DROP TABLE IF EXISTS t;
--enable_warnings
CREATE TABLE t ( i INT );
BEGIN;
INSERT INTO t VALUES (1);
SAVEPOINT `s``p`;
COMMIT;

--sync_slave_with_master

# cleanup
--connection master
DROP TABLE t;
--sync_slave_with_master

--exit

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/122865

3551 Alfranio Correia	2010-11-04
      BUG#57873 Backtick in savepoint name causes replication failure
      
      Savepoint name in the binary log is always enclosed with backticks;
      however, internal backticks in the name are not duplicated, so ids
      like `xx`yy` make the slave SQL to stop with a syntax error.
      
      To fix the problem, we duplicate internal backtics, e.g. `xx``yy`.
     @ sql/log.cc
        Call MYSQL_BIN_LOG::get_ansi_id().
     @ sql/log.h
        Added get_ansi_id() that doubles any backtick in "id" and stores
        the result string in "ansi_id".

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/122866

3551 Alfranio Correia	2010-11-04
      BUG#57873 Backtick in savepoint name causes replication failure
      
      Savepoint name in the binary log is always enclosed with backticks;
      however, internal backticks in the name are not duplicated, so ids
      like `xx`yy` make the slave SQL to stop with a syntax error.
      
      To fix the problem, we duplicate internal backtics, e.g. `xx``yy`.
     @ sql/log.cc
        Call MYSQL_BIN_LOG::get_ansi_id().
     @ sql/log.h
        Added get_ansi_id() that doubles any backtick in "id" and stores
        the result string in "ansi_id".

LOAD DATA has the same problem.

Here is a test
--------------
source include/master-slave.inc;
source include/have_binlog_format_statement.inc;

CREATE TABLE `t``1`(a int, b int);
LOAD DATA INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE `t``1`;
SHOW BINLOG EVENTS;
sync_slave_with_master;

DROP TABLE has the same problem.
Here is a test.
-----------------------
source include/master-slave.inc;
#source include/have_binlog_format_statement.inc;
source include/have_binlog_format_row.inc;

CREATE TABLE `t``1`(a int, b int);
CREATE TEMPORARY TABLE t2(c1 INT);
#LOAD DATA INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE `t``1`;
DROP TABLE `t``1`, t2;
SHOW BINLOG EVENTS;
sync_slave_with_master;

mysqlbinlog has the problem too.
1. mysqlbinlog print a wrong database name in USE statement if there is a "`" in the database name.
2. mysqlbinlog print a wrong user variable name if there is a "`" in the user variable name.

CREATE TABLE IF NOT EXISTS SELECT has the problem on row mode if the table already exists.

I used "grep "`\"" *.cc in sql/ to find all code which writes "`" into binlog.

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/127885

3454 Alfranio Correia	2011-01-04
      BUG#57873 Backtick in savepoint name causes replication failure
      
      Backticks can be used as part of a name which may be wrongly interpreted if
      appropriated quotes are not used. This patch thus quotes names that are
      written to the binary log in the following cases:
      
        . User variable events;
        . Create database;
        . Create table;
        . Savepoint/Rollback;
        . Load data;
        . Drop table;
        . Drop database;
        . Other cases, where statements are constructed 
          and injected in the binary log.
      
      When an event is read from the binary log and used in the mysqlbinlog, the
      current database must have its name quoted before being processed. Such data
      is not quoted before being written to binary log in order to avoid problems
      with filters as they are not expecting quoted names.
     @ client/mysqldump.c
        Refactored this routine in order to avoid duplicated code.
        The code removed from here is used in the quote_str() in
        strings/strfill.c
     @ include/m_string.h
        Created quote_str() to quote strings.
     @ mysql-test/r/mysqlbinlog.result
        Updated result file.
     @ mysql-test/r/mysqlbinlog2.result
        Updated result file.
     @ mysql-test/r/user_var-binlog.result
        Updated result file.
     @ mysql-test/suite/binlog/r/binlog_base64_flag.result
        Updated result file.
     @ mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result
        Updated result file.
     @ mysql-test/suite/rpl/r/rpl_row_ignorable_event.result
        Updated result file.
     @ mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result
        Updated result file.
     @ mysql-test/suite/rpl/r/rpl_sp.result
        Updated result file.
     @ sql/binlog.cc
        Quoted "Savepoint" and "Rollback".
     @ sql/ha_ndbcluster_binlog.cc
        The name of the table is quoted before being written to the
        binary log.
     @ sql/log_event.cc
        Quoted the current database and information related to Load_log_event.
     @ sql/log_event.h
        Created a wrapper to quote_str in strings/strfill.c.
     @ sql/sql_base.cc
        The name of the table is quoted before being written to the
        binary log.
     @ sql/sql_db.cc
        The name of the table is quoted before being written to the
        binary log.
     @ sql/sql_load.cc
        The name of the table is quoted before being written to the
        binary log.
     @ sql/sql_table.cc
        Quoted "Drop table".
     @ strings/strfill.c
        Created function to quote names.

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/127886

3454 Alfranio Correia	2011-01-04
      BUG#57873 Backtick in savepoint name causes replication failure
      
      Backticks can be used as part of a name which may be wrongly interpreted if
      appropriated quotes are not used. This patch thus quotes names that are
      written to the binary log in the following cases:
      
        . User variable events;
        . Create database;
        . Create table;
        . Savepoint/Rollback;
        . Load data;
        . Drop table;
        . Drop database;
        . Other cases, where statements are constructed 
          and injected in the binary log.
      
      When an event is read from the binary log and used in the mysqlbinlog, the
      current database must have its name quoted before being processed. Such data
      is not quoted before being written to binary log in order to avoid problems
      with filters as they are not expecting quoted names.
     @ client/mysqldump.c
        Refactored this routine in order to avoid duplicated code.
        The code removed from here is used in the quote_str() in
        strings/strfill.c
     @ include/m_string.h
        Created quote_str() to quote strings.
     @ mysql-test/r/mysqlbinlog.result
        Updated result file.
     @ mysql-test/r/mysqlbinlog2.result
        Updated result file.
     @ mysql-test/r/user_var-binlog.result
        Updated result file.
     @ mysql-test/suite/binlog/r/binlog_base64_flag.result
        Updated result file.
     @ mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result
        Updated result file.
     @ mysql-test/suite/rpl/r/rpl_row_ignorable_event.result
        Updated result file.
     @ mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result
        Updated result file.
     @ mysql-test/suite/rpl/r/rpl_sp.result
        Updated result file.
     @ mysql-test/suite/rpl/t/rpl_backtick-master.opt
        Added test case.
     @ mysql-test/suite/rpl/t/rpl_backtick-slave.opt
        Added test case.
     @ mysql-test/suite/rpl/t/rpl_backtick.test
        Added test case.
     @ sql/binlog.cc
        Quoted "Savepoint" and "Rollback".
     @ sql/ha_ndbcluster_binlog.cc
        The name of the table is quoted before being written to the
        binary log.
     @ sql/log_event.cc
        Quoted the current database and information related to Load_log_event.
     @ sql/log_event.h
        Created a wrapper to quote_str in strings/strfill.c.
     @ sql/sql_base.cc
        The name of the table is quoted before being written to the
        binary log.
     @ sql/sql_db.cc
        The name of the table is quoted before being written to the
        binary log.
     @ sql/sql_load.cc
        The name of the table is quoted before being written to the
        binary log.
     @ sql/sql_table.cc
        Quoted "Drop table".
     @ strings/strfill.c
        Created function to quote names.

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/129221

3527 Alfranio Correia	2011-01-19
      BUG#57873 Backtick in savepoint name causes replication failure
      
      Backticks can be used as part of a name which may be wrongly interpreted if
      appropriated quotes are not used. This patch thus quotes names that are
      written to the binary log in the following cases:
      
        . User variable events;
        . Create database;
        . Create table;
        . Savepoint/Rollback;
        . Load data;
        . Drop table;
        . Drop database;
        . Other cases, where statements are constructed 
          and injected in the binary log.
      
      When an event is read from the binary log and used in the mysqlbinlog, the
      current database must have its name quoted before being processed. Such data
      is not quoted before being written to binary log in order to avoid problems
      with filters as they are not expecting quoted names.
     @ client/mysqldump.c
        Refactored this routine in order to avoid duplicated code.
        The code removed from here is used in the quote_str() in
        strings/strfill.c
     @ include/m_string.h
        Created quote_str() to quote strings.
     @ mysql-test/r/mysqlbinlog.result
        Updated result file.
     @ mysql-test/r/mysqlbinlog2.result
        Updated result file.
     @ mysql-test/r/user_var-binlog.result
        Updated result file.
     @ mysql-test/suite/binlog/r/binlog_base64_flag.result
        Updated result file.
     @ mysql-test/suite/binlog/r/binlog_row_mysqlbinlog_verbose.result
        Updated result file.
     @ mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result
        Updated result file.
     @ mysql-test/suite/rpl/r/rpl_backtick.result
        Added result file.
     @ mysql-test/suite/rpl/r/rpl_row_ignorable_event.result
        Updated result file.
     @ mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result
        Updated result file.
     @ mysql-test/suite/rpl/r/rpl_sp.result
        Updated result file.
     @ mysql-test/suite/rpl/t/rpl_backtick-master.opt
        Added test case.
     @ mysql-test/suite/rpl/t/rpl_backtick-slave.opt
        Added test case.
     @ mysql-test/suite/rpl/t/rpl_backtick.test
        Added test case.
     @ sql/binlog.cc
        Quoted "Savepoint" and "Rollback".
     @ sql/ha_ndbcluster_binlog.cc
        The name of the table is quoted before being written to the
        binary log.
     @ sql/log_event.cc
        Quoted the current database and information related to Load_log_event.
     @ sql/log_event.h
        Created a wrapper to quote_str in strings/strfill.c.
     @ sql/sql_base.cc
        The name of the table is quoted before being written to the
        binary log.
     @ sql/sql_db.cc
        The name of the table is quoted before being written to the
        binary log.
     @ sql/sql_load.cc
        The name of the table is quoted before being written to the
        binary log.
     @ sql/sql_show.cc
        Added a return to append_identifier as in the future you should check
        if operations do not go wrong.
     @ sql/sql_show.h
        Added a return to append_identifier as in the future you should check
        if operations do not go wrong.
     @ sql/sql_table.cc
        Quoted "Drop table".
     @ strings/strfill.c
        Created function to quote names.