Bug #46265 Can not disable warning about unsafe statements for binary logging
Submitted: 17 Jul 2009 14:54 Modified: 20 May 2010 2:26
Reporter: Mark Leith Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:5.1.36 OS:Any
Assigned to: Davi Arnaut CPU Architecture:Any

[17 Jul 2009 14:54] Mark Leith
Description:
If you are using statement based replication, and using UDFs, you continually get messages such as:

090717 14:30:05 [Warning] Statement is not safe to log in statement format. Statement: update ....

There is no way to disable this currently, and with high throughput on these kinds of statements, the error log soon gets flooded.

How to repeat:
Read this from THD::binlog_query() in sql_class.cc:

  /*
    If we are in statement mode and trying to log an unsafe statement,
    we should print a warning.
  */
  if (sql_log_bin_toplevel && lex->is_stmt_unsafe() &&
      variables.binlog_format == BINLOG_FORMAT_STMT)
  {
   /*
     A warning can be elevated a error when STRICT sql mode.
     But we don't want to elevate binlog warning to error here.
   */
    push_warning(this, MYSQL_ERROR::WARN_LEVEL_NOTE,
                 ER_BINLOG_UNSAFE_STATEMENT,
                 ER(ER_BINLOG_UNSAFE_STATEMENT));
    if (!(binlog_flags & BINLOG_FLAG_UNSAFE_STMT_PRINTED))
    {
      sql_print_warning("%s Statement: %.*s",
                        ER(ER_BINLOG_UNSAFE_STATEMENT),
                        MYSQL_ERRMSG_SIZE, query_arg);
      binlog_flags|= BINLOG_FLAG_UNSAFE_STMT_PRINTED;
    }

Suggested fix:
Take in to account the log-warnings variable as well, perhaps add "&& (global_system_variables.log_warnings > 0)" to the "if (!(binlog_flags & BINLOG_FLAG_UNSAFE_STMT_PRINTED))" check?
[20 Jul 2009 11:55] Mark Leith
This is a duplicate of Bug#42851.
[23 Jul 2009 11:53] Mark Leith
Re-opening following comments from Bug#42851
[23 Jul 2009 12:12] Luis Soares
Taking up the bug work is related to BUG#42851.
[27 Jul 2009 2:14] 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/79304

3043 Luis Soares	2009-07-27
      BUG#46265: Can not disable warning about unsafe statements for 
      binary logging
      
      Several warnings are printed when using statement based logging
      and unsafe operations are logged to the binlog. For example, this
      is the case for statements using LIMIT + ORDER BY PK. As a
      consequence, this would rapidly increase mysqld error log size,
      in some cases to several gigabytes, causing a maintenance
      nightmare.
            
      This patch proposes a mechanism to selectively and voluntarily
      suppress warnings from mysqld error log. It adds a dynamic server
      variable "suppress_log_warnings", which can be used to set/reset
      warning filtering from mysqld error log. Its details are the
      following:
            
        - Default: empty => no suppressions from error log.
            
        - Accepted values: list of tuples separated by commas, eg:
            
          <LEVEL>:<CODE>,<LEVEL>:<CODE>
            
          where LEVEL is one of: 'warning' or 'note, and code is the
          numerical representation of the warning code to suppress. In
          the case of unsafe statement warnings the code is 1592. As
          such, to suppress such warnings one could use:
               
          --suppress_log_warnings=warning:1592
            
          or
              
          mysql>SET @@global.suppress_log_warnings=warning:1592
            
        - Multiple suppressions can be done by adding other tuples
          <LEVEL>:<CODE> and separating each with a comma.
            
      Every time the suppress_log_warnings variable is set, the
      internal structure (instance of class Suppress_Log_Warnings) is
      updated and new rules are computed based on the
      suppress_log_warnings variable content. 
            
      Finally, in this patch we only address the suppressing of unsafe
      warnings from error log, however, for this mechanism to work with
      other warnings, one needs to refactor part of the source code so
      that calls to sql_print_warnings get replaced with
      sql_print_or_suppress_warnings referencing the warning code
      whenever possible.
     @ mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result
        Result file update. Notice the extra warning for unsafe statements that are 
        not suppressed. These are intended and guarded by DBUG_EXECUTE_IF.
     @ mysql-test/suite/binlog/t/binlog_stm_unsafe_warning-master.opt
        Added option to suppress unsafe warning.
     @ mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test
        Added test case for explicit suppressions through new server variable:
        suppress_log_warning.
     @ sql/log.cc
        Added function that conditionally prints to the error log, based on
        the rules established by the contents of suppress_log_warnings filtering
        rules.
     @ sql/mysql_priv.h
        Added references to sql_print_or_suppress_warning and 
        opt_suppress_log_warnings.
     @ sql/mysqld.cc
        Added handling of command line arguments for option: suppress_log_warnings.
        If options are given, then it initializes the Suppress_Log_Warnings instance
        with the given argument.
     @ sql/set_var.cc
        Added new servr system variable suppress_log_warnings. Everytime, the
        variable is reset, the suppress_log_warnings filter gets updated.
     @ sql/set_var.h
        Exported system variable suppress_log_warnings.
     @ sql/sql_class.cc
        Replaced the sql_print_warning, for unsafe statements, with 
        sql_print_or_suppress_warning.
     @ sql/sql_error.cc
        Created a class named Suppress_Log_Warnings. It holds warning filters for
        mysqld's error log. At its core is a Hash that maps error code into information
        on which levels the error code is to be suppressed. The class interface provides
        methods to set list of suppressions and check if a code is to be suppressed for 
        a specific level. Apart from these, it also contains private utility methods.
     @ sql/sql_error.h
        Added interface for class Suppress_Log_Warnings.
[31 Jul 2009 4:18] 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/79740

3052 Davi Arnaut	2009-07-31
      Bug#46265: Can not disable warning about unsafe statements for binary logging
      
      If using statement based replication (SBR), repeatedly calling
      statements which are unsafe for SBR will cause a warning message
      to be written to the error for each statement. This might lead
      to filling up the error log and there is no way to disable this
      behavior.
      
      The solution is to only log these message (about statements unsafe
      for statement based replication) if the log_warnings option is set.
      
      For example:
      
      SET GLOBAL LOG_WARNINGS = 0;
      INSERT INTO t1 VALUES(UUID());
      SET GLOBAL LOG_WARNINGS = 1;
      INSERT INTO t1 VALUES(UUID());
      
      In this case the message will be printed only once:
      
      [Warning] Statement may not be safe to log in statement format.
                Statement: INSERT INTO t1 VALUES(UUID())
     @ mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result
        Add test case result for Bug#46265
     @ mysql-test/suite/binlog/t/binlog_stm_unsafe_warning-master.opt
        Make log_error value available.
     @ mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test
        Add test case for Bug#46265
     @ sql/sql_class.cc
        Print warning only if the log_warnings is enabled.
[31 Jul 2009 13:01] 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/79772

3055 Davi Arnaut	2009-07-31
      Bug#46265: Can not disable warning about unsafe statements for binary logging
      
      If using statement based replication (SBR), repeatedly calling
      statements which are unsafe for SBR will cause a warning message
      to be written to the error for each statement. This might lead
      to filling up the error log and there is no way to disable this
      behavior.
      
      The solution is to only log these message (about statements unsafe
      for statement based replication) if the log_warnings option is set.
      
      For example:
      
      SET GLOBAL LOG_WARNINGS = 0;
      INSERT INTO t1 VALUES(UUID());
      SET GLOBAL LOG_WARNINGS = 1;
      INSERT INTO t1 VALUES(UUID());
      
      In this case the message will be printed only once:
      
      [Warning] Statement may not be safe to log in statement format.
                Statement: INSERT INTO t1 VALUES(UUID())
     @ mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result
        Add test case result for Bug#46265
     @ mysql-test/suite/binlog/t/binlog_stm_unsafe_warning-master.opt
        Make log_error value available.
     @ mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test
        Add test case for Bug#46265
     @ sql/sql_class.cc
        Print warning only if the log_warnings is enabled.
[31 Jul 2009 13:05] Davi Arnaut
Queued to 5.1-bugteam
[4 Aug 2009 19:51] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090804194615-h40sa098mx4z49qg) (version source revid:davi.arnaut@sun.com-20090731131549-ilfwbh31gz451vll) (merge vers: 5.4.4-alpha) (pib:11)
[4 Aug 2009 20:45] Bugs System
Pushed into 5.1.38 (revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (version source revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (merge vers: 5.1.38) (pib:11)
[8 Aug 2009 1:02] Paul DuBois
Noted in 5.1.38, 5.4.4 changelogs.

With statement-based logging (SBL), repeatedly calling statements
that are unsafe for SBL caused a warning message to be written to the
error log for each statement, and there was no way to disable this
behavior. Now the server logs messages about statements that are
unsafe for statement-based logging only if the log_warnings variable
is greater than 0.
[9 Aug 2009 23:37] Paul DuBois
Correction:

... only if the global value of the log_warnings variable is greater than 0.
[12 Aug 2009 21:38] Paul DuBois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[14 Aug 2009 22:37] Paul DuBois
Ignore previous comment about 5.4.2.
[1 Oct 2009 5:59] Bugs System
Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)
[1 Oct 2009 7:25] Bugs System
Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)
[1 Oct 2009 13:25] Bugs System
Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)
[5 Oct 2009 10:50] Bugs System
Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)
[5 Oct 2009 18:09] Paul DuBois
This fix now has been pushed into 5.4.2.
[20 May 2010 2:26] Roel Van de Paar
To clarify: the warning always shows in the client (CLI), and:

log_warnings=0 turns off the warning for the error log
log_warnings=1 turns on  the warning for the error log
log_warnings=2 turns on  the warning for the error log
[21 May 2010 5:20] Roel Van de Paar
The fix is in 5.1.38+, 5.4.2+