• Praveenkumar Hulakund's avatar
    Bug#12601974 - STORED PROCEDURE SQL_MODE=NO_BACKSLASH_ESCAPES IGNORED AND BREAKS REPLICATION · 9af695fb
    Praveenkumar Hulakund authored
    Analysis:
    ========================
    sql_mode "NO_BACKSLASH_ESCAPES": When user want to use backslash as character input,
    instead of escape character in a string literal then sql_mode can be set to 
    "NO_BACKSLASH_ESCAPES". With this mode enabled, backslash becomes an ordinary 
    character like any other. 
    
    SQL_MODE set applies to the current client session. And while creating the stored 
    procedure, MySQL stores the current sql_mode and always executes the stored 
    procedure in sql_mode stored with the Procedure, regardless of the server SQL 
    mode in effect when the routine is invoked.  
    
    In the scenario (for which bug is reported), the routine is created with 
    sql_mode=NO_BACKSLASH_ESCAPES. And routine is executed with the invoker sql_mode
    is "" (NOT SET) by executing statement "call testp('Axel\'s')".
    Since invoker sql_mode is "" (NOT_SET), the '\' in 'Axel\'s'(argument to function)
    is considered as escape character and column "a" (of table "t1") values are 
    updated with "Axel's". The binary log generated for above update operation is as below,
    
      set sql_mode=XXXXXX (for no_backslash_escapes)
      update test.t1 set a= NAME_CONST('var',_latin1'Axel\'s' COLLATE 'latin1_swedish_ci');
    
    While logging stored procedure statements, the local variables (params) used in
    statements are replaced with the NAME_CONST(var_name, var_value) (Internal function) 
    (http://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_name-const)
    
    On slave, these logs are applied. NAME_CONST is parsed to get the variable and its
    value. Since, stored procedure is created with sql_mode="NO_BACKSLASH_ESCAPES", the sql_mode
    is also logged in. So that at slave this sql_mode is set before executing the statements
    of routine.  So at slave, sql_mode is set to "NO_BACKSLASH_ESCAPES" and then while
    parsing NAME_CONST of string variable, '\' is considered as NON ESCAPE character
    and parsing reported error for "'" (as we have only one "'" no backslash). 
    
    At slave, parsing was proper with sql_mode "NO_BACKSLASH_ESCAPES".
    But above error reported while writing bin log, "'" (of Axel's) is escaped with
    "\" character. Actually, all special characters (n, r, ', ", \, 0...) are escaped
    while writing NAME_CONST for string variable(param, local variable) in bin log 
    Airrespective of "NO_BACKSLASH_ESCAPES" sql_mode. So, basically, the problem is 
    that logging string parameter does not take into account sql_mode value.
    
    Fix:
    ========================
    So when sql_mode is set to "NO_BACKSLASH_ESCAPES", escaping  characters as 
    (n, r, ', ", \, 0...) should be avoided. To do so, added a check to not to
    escape such characters while writing NAME_CONST for string variables in bin 
    log. 
    And when sql_mode is set to NO_BACKSLASH_ESCAPES, quote character "'" is
    represented as ''.
    http://dev.mysql.com/doc/refman/5.6/en/string-literals.html (There are several 
    ways to include quote characters within a string: )
    9af695fb
binlog_sql_mode.test 4.58 KB