Bug #42851 Spurious "Statement is not safe to log in statement format." warnings
Submitted: 13 Feb 2009 22:27 Modified: 3 Dec 2009 15:13
Reporter: Vincent Rivellino Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.1.31 OS:Linux (Fedora Core 9)
Assigned to: Assigned Account CPU Architecture:Any
Tags: limit, log, replication, sbr, statement, warning

[13 Feb 2009 22:27] Vincent Rivellino
Description:
Since upgrading a development system to 5.1.31, I have noticed the following kinds of warnings in the error log:

090213 16:58:54 [Warning] Statement is not safe to log in statement format. ...

I am pretty sure I can ignore them, as they aren't affecting replication, I was curious as to why.  I was able to generate a simple table to reproduce.  It appears directly related to the LIMIT optimization.

mysql> select * from t1;
+-------+--------+---------+---------------------+
| recNo | string | inUseBy | tsLastUpdated       |
+-------+--------+---------+---------------------+
|     2 | one    |         | 2009-02-13 17:25:13 | 
|     4 | two    |         | 2009-02-13 17:25:13 | 
|     6 | three  |         | 2009-02-13 17:25:13 | 
+-------+--------+---------+---------------------+
3 rows in set (0.00 sec)

mysql> UPDATE t1 SET inUseBy='me' WHERE inUseBy='' LIMIT 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------+
| Level   | Code | Message                                           |
+---------+------+---------------------------------------------------+
| Warning | 1592 | Statement is not safe to log in statement format. | 
+---------+------+---------------------------------------------------+
1 row in set (0.00 sec)

There are no triggers for this table, and no user defined functions/stored-procedures are being used.

This appears to be a spurious warning message.

How to repeat:
-- create table
DROP TABLE IF EXISTS t1;
CREATE TABLE `t1` (
  `recNo` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `string` varchar(64) NOT NULL,
  `inUseBy` varchar(38) NOT NULL DEFAULT '',
  `tsLastUpdated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`recNo`),
  KEY `tsLastUpdated` (`tsLastUpdated`),
  KEY `inUseBy` (`inUseBy`)
);
-- insert test data
INSERT INTO t1 SET string='one';
INSERT INTO t1 SET string='two';
INSERT INTO t1 SET string='three';
-- grab one record
UPDATE t1 SET inUseBy='me' WHERE inUseBy='' LIMIT 1;
-- above query will produce a warning
SHOW WARNINGS;
-- the follow queries do not
UPDATE t1 SET inUseBy='me' WHERE inUseBy='';
UPDATE t1 SET inUseBy='' WHERE inUseBy='me';
[13 Feb 2009 22:33] Vincent Rivellino
Fixed OS details.

Also, I'm using the precompiled binaries available from mysql.com.

mysql-5.1.31-linux-x86_64-icc-glibc23
[14 Feb 2009 11:43] Sveta Smirnova
Thank you for the report.

Please read about replication formats at http://dev.mysql.com/doc/refman/5.1/en/replication-formats.html

But I agree it should be clear which statements are safe in the binary log format STATEMENT and which are not. So I change category of the report to "Documentation".
[14 Feb 2009 18:53] Vincent Rivellino
I'm not convinced this is a documentation issue.  I've read through much of it, and I don't see anything that indicates that LIMIT is non-deterministic.  (I assume that the "Statement is not safe to log in statement format" message is for statements considered to be non-deterministic.)

I'm really concerned about the log volume we're going to see when we upgrade to 5.1 on our production systems.  We're currently using statement based replication, and I'm not sure when we'll switch to mixed replication.
[17 Feb 2009 10:27] MySQL Verification Team
mysql> UPDATE t1 SET inUseBy='me' WHERE inUseBy='' LIMIT 1;

there is no ORDER BY clause which includes all unique/pk columns, so stmt based replication might cause an inconsistent slave.  perhaps this fact could be documented?
[17 Feb 2009 10:34] Jon Stephens
This is not a bug, but rather documented behaviour.

Per http://dev.mysql.com/doc/refman/5.1/en/replication-features-limit.html:

[being]

 Statement-based replication of LIMIT clauses in DELETE, UPDATE, and INSERT ... SELECT statements is unsafe since the order of the rows affected is not defined. Beginning with MySQL 5.1.24, when such a statement is encountered:

    * when using STATEMENT mode, a warning that the statement is not safe for statement-based replication is now issued 

...

[end]

In other words: To make statements using LIMIT safe for SBR, you need an ORDER BY as well.

I am adding a statement to this effect to the page in question.
[17 Feb 2009 10:55] Jon Stephens
Commit updating docs: http://lists.mysql.com/commits/66620
[17 Feb 2009 16:11] Vincent Rivellino
Ok, that documentation makes sense.

However, I still see the warning.  Using the above table as an example:

mysql> UPDATE t1 SET inUseBy='me' WHERE inUseBy='' ORDER BY recNO ASC LIMIT 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------+
| Level   | Code | Message                                           |
+---------+------+---------------------------------------------------+
| Warning | 1592 | Statement is not safe to log in statement format. | 
+---------+------+---------------------------------------------------+
1 row in set (0.00 sec)
[17 Feb 2009 16:39] Jon Stephens
This no longer appears to be a Docs issue; changing category to Server:Replication and lead to Lars.
[22 Feb 2009 19:33] Jon Stephens
Removing myself as assignee since this is no longer a documentation issue.
[22 Feb 2009 19:36] Jon Stephens
Note: My commit from the 17th added the following new section to the Manual, which covers this situation explicitly:

http://dev.mysql.com/doc/refman/5.1/en/replication-features-limit.html
[22 Feb 2009 19:39] Jon Stephens
Note to submitter: You should be able to get rid of the warnings by using --binlog_format=MIXED or --binlog_format=ROW. Is there some reason why you can't used the mixed or row-based format?

(However, I agree that LIMIT plus ORDER BY should also get rid of the warning, and I consider the fact that it does not to be a bug. For the time being, I'm noting this behaviour in the docs as a known issue.)
[25 Feb 2009 8:15] Sveta Smirnova
Thank you for the feedback.

Having warning with order by and limit verified as replication bug.
[26 Feb 2009 3:52] Nicolae Namolovan
Hi, for some reason my error logs are now full of "090226  5:42:04 [Warning] Statement is not safe to log in statement format. Statement: ..", the problem is that this table is not even writed to binary logs since I have binlog-do-db=somedb and this table is in another database what is not replicated. And you call this GA ? And maybe there is a option to stop these stupid warnings filling my logs. Why did you introduce this only in GA, I did test a lot of 5.1.. I'm sorry I'm too lazy to fill a new bug..
[8 Apr 2009 19:34] Ren Sauceda
My server is also not configured for replication and never has been. My version is 5.1.30-community-log.

I have tried changing binlog_format to MIXED and to then to ROW and I still get these errors in my log. The log is really getting out of control. It is impossible to look for other warnings without grepping out the thousands of lines of these errors (48748621 as of right now). My log has grown to almost 9G since Oct. 

I was considering rolling back since it doesn't look like this bug has been fixed, but I don't know how far back I'd have to go short of returning to 5.0.x. Then again I haven't tested this app against 5.0 either, though I haven't seen this error on my other 5.0 servers.
[14 May 2009 9:57] Domas Mituzas
Code comment:

/*
Statement-based replication of INSERT ... SELECT ... LIMIT is not safe
as order of rows is not defined, so in mixed mode we go to row-based.

Note that we may consider a statement as safe if ORDER BY primary_key
is present or we SELECT a constant. However it may confuse users to
see very similiar statements replicated differently.
*/
if (lex->current_select->select_limit)
{
lex->set_stmt_unsafe();
thd->set_current_stmt_binlog_row_based_if_mixed();
}

So, even that we know that statement is safe, we deliberately return a warning. 
It wouldn't be much of an issue, but as this is filling error logs with useless crap, I'd like to bump this up a bit.
[14 May 2009 10:07] Domas Mituzas
Rationale for re-triage:

1. I3 - more customers get hit by that
2. D3 - error log gets flooded with messages on high-performance systems (where every update statement will generate a warning with all the statement information) - thus yielding error logs not usable/maintainable, as well as file system filling...
[2 Jun 2009 10:18] Luis Soares
See also: BUG#42415.
[5 Jun 2009 8:17] Luis Soares
Another reference: BUG#34768.
[5 Jun 2009 11:12] 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/75681

2917 Luis Soares	2009-06-05
      BUG#42851: Spurious "Statement is not safe to log in statement
                 format." warnings
      
      Despite the fact that a statement would be filtered out from binlog, a
      warning would still be thrown if it was issued with the LIMIT.
      
      This patch addresses this issue by checking the filtering rules before
      printing out the warning.
     @ mysql-test/suite/binlog/t/limit_warning-master.opt
        Parameter to filter out database: "b42851".
     @ mysql-test/suite/binlog/t/limit_warning.test
        Added a new test case.
     @ sql/sql_class.cc
        Added filtering rules check to condition used to decide whether to
        printout warning or not.
[8 Jun 2009 11:02] 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/75830

2918 Luis Soares	2009-06-08
      BUG#42851: Spurious "Statement is not safe to log in statement
      format." warnings
      
      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 addresses this issue by adding a variable that enables the
      user to choose whether the warnings should be issued or not. The
      variable has SESSION and GLOBAL scope and is dynamically changeable.
      
      NOTE: this is an incremental patch, that builds on top of previous fix
            for this bug, the one that handles spurious warnings for filtered
            out statements.
     @ mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result
        Renamed test file and added test results for new variable.
     @ mysql-test/suite/binlog/t/binlog_stm_unsafe_warning-master.opt
        Added CLI variable setting for suppressing statmenent unsafe warnings.
     @ mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test
        Added testing to assess that new variable is indeed working as
        expected.
     @ sql/mysqld.cc
        Added new option: binlog-stmt-suppress-unsafe-warnings.
     @ sql/set_var.cc
        Added binlog_stmt_suppress_unsafe_warnings variable.
     @ sql/sql_class.cc
        Added check to thd->variables.binlog_stmt_suppress_unsafe_warnings 
        before printing out the warning.
     @ sql/sql_class.h
        Added binlog_stmt_suppress_unsafe_warnings to the system_variables
        structure.
[27 Jun 2009 13:19] 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/77391

2981 Luis Soares	2009-06-27
      BUG#42851: Spurious "Statement is not safe to log in statement
                 format." warnings
            
      Despite the fact that a statement would be filtered out from binlog, a
      warning would still be thrown if it was issued with the LIMIT.
            
      This patch addresses this issue by checking the filtering rules before
      printing out the warning.
     @ mysql-test/suite/binlog/t/binlog_stm_unsafe_warning-master.opt
        Parameter to filter out database: "b42851".
     @ mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test
        Added a new test case.
     @ sql/sql_class.cc
        Added filtering rules check to condition used to decide whether to
        printout warning or not.
[29 Jun 2009 11:21] Luis Soares
Pushed patch 1 to 5.1-bugteam and mysql-pe.
[8 Jul 2009 13:30] Bugs System
Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:luis.soares@sun.com-20090629110058-hiz09z07ypp897n2) (merge vers: 5.1.37) (pib:11)
[9 Jul 2009 7:36] Bugs System
Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:luis.soares@sun.com-20090629110058-hiz09z07ypp897n2) (merge vers: 5.1.37) (pib:11)
[9 Jul 2009 8:36] Domas Mituzas
Luis,

I'd like to point out, that the nature of escalation/re-triage was growing log file, not filtering issues. 

Patch #1 alone does not fix the issue (it just fixes the issues reported in one side comment in this bug).
Patch #2 is needed to properly close this bug, which is why it got retriaged...
[9 Jul 2009 11:24] Jon Stephens
Following review of bug report and discussion of current fix with Domas, I've set this to Need Doc Info pending review by dev team.
[10 Jul 2009 11:20] Bugs System
Pushed into 5.4.4-alpha (revid:anozdrin@bk-internal.mysql.com-20090710111017-bnh2cau84ug1hvei) (version source revid:luis.soares@sun.com-20090629111348-xtjbbh515g5w890w) (merge vers: 5.4.4-alpha) (pib:11)
[13 Jul 2009 11:51] Jon Stephens
Back to NDI; see my previous comment.
[18 Jul 2009 16:53] 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/78967

3024 Luis Soares	2009-07-18
      BUG#42851: Spurious "Statement is not safe to log in statement
      format." warnings
            
      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.
[23 Jul 2009 11:23] Lars Thalmann
This bug is about two problems:

1. Filtered events still cause warnings in error log, 
2. Warnings in error log make error log grow too large.

The first one has already been fixed as part of this bug report.

Please create a separate bug report for the 2nd problem and ask Serg
and Davi to review the patch.

(This bug report can remain open until the 2nd bug report is closed.)
[23 Jul 2009 11:54] Mark Leith
I had already opened a similar bug report for the second problem, but closed it as a duplicate of this one. I've just re-opened it again so we can use that for the second issue:

http://bugs.mysql.com/bug.php?id=46265
[5 Aug 2009 10:58] Jon Stephens
Documented bugfix in the 5.1.38 and 5.4.4 changelogs, as follows:

      Statements using LIMIT generated spurious -Statement is not safe 
      to log in statement format- warnings in the error log, causing the 
      log to grow rapidly in size.
[5 Aug 2009 12:38] Jon Stephens
I've set this back to Verified for handling and reverted the changelog entry.

What actually happened to the commit for this?

If nothing was committed, there's nothing for me to document.

If this is a duplicate, please mark it as such.
[5 Aug 2009 12:52] Domas Mituzas
the bug hasn't been fixed - disabling warnings entirely doesn't seem to be a solution
[5 Aug 2009 13:24] Jon Stephens
Discussed with Davi. The changes show above were pushed, setting back to Documenting.
[5 Aug 2009 13:42] Jon Stephens
Documented in the 5.1.37 and 5.4.4 changelogs, as stated previously.
[8 Aug 2009 11:52] Domas Mituzas
Resetting it back to 'verified' per agreement.
[12 Aug 2009 22:05] 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:55] Paul DuBois
Ignore previous comment about 5.4.2.
[26 Aug 2009 13:46] Bugs System
Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[26 Aug 2009 13:46] Bugs System
Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)
[26 Aug 2009 13:48] Bugs System
Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)
[26 Aug 2009 23:53] Siva Nookala
mysql connector throws "Statement is not safe to log in statement format" warning as an error. To workaround this problem wrap the select statement as a sub select 

For example if you have a SQL statement as below 

insert into visits
(search_query, visit_count)
select search_query, visit_count
from raw_visits 
where search_engine_id = 1
order by visit_count desc
limit 0, 25

change it to 

insert into visits
(search_query, visit_count)
select search_query, visit_count
from
(select search_query, visit_count
from raw_visits 
where search_engine_id = 1
order by visit_count desc
limit 0, 25) top
[27 Aug 2009 16:32] Bugs System
Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[7 Oct 2009 13:11] Paul DuBois
The 5.4 fix has been pushed to 5.4.2.
[11 Nov 2009 6:47] 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/90047

3192 Dao-Gang.Qu@sun.com	2009-11-11
      Bug #42851  	Spurious "Statement is not safe to log in statement format." warnings
      
      Warnings in error log make error log grow too large.
      
      The problem can be resolved by limiting the rate of messages that are 
      written to the log. A volume of messages that is less than or equal to 
      the specified rate is written to the log, whereas the volume of messages 
      that exceeds the rate is discarded.
      
      For example,
      
      log-warnings-ratelimit-interval = 10
      log-warnings-ratelimit-burst = 5
      
      This allows 5 log messages per 10 seconds. The sixth (and posterior)
      attempts to write a log message within a 10 seconds interval are
      discarded.
     @ mysql-test/suite/rpl/r/rpl_ratelimit_warnings.result
        Test Result for BUG#42851.
     @ mysql-test/suite/rpl/t/rpl_ratelimit_warnings.test
        Added the test file to verify if the rate limit works fine.
     @ sql/log.cc
        Added the implementation of the 'Rate_limit' class.
     @ sql/log.h
        Added the definition of the 'Rate_limit' class.
     @ sql/mysqld.cc
        Added OPT_LOG_WARNINGS_RATELIMIT_INTERVAL and OPT_LOG_WARNINGS_RATELIMIT_BURST 
        options for rate limit.
[23 Nov 2009 8:44] 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/91255

3192 Dao-Gang.Qu@sun.com	2009-11-23
      Bug #42851  	Spurious "Statement is not safe to log in statement format." warnings
      
      Warnings in error log make error log grow too large.
      
      The problem can be resolved by limiting the rate of messages that are 
      written to the log. A volume of messages that is less than or equal to 
      the specified rate is written to the log, whereas the volume of messages 
      that exceeds the rate is discarded.
      
      For example,
      
      log-warnings-ratelimit-interval = 10
      log-warnings-ratelimit-burst = 5
      
      This allows 5 log messages per 10 seconds. The sixth (and posterior)
      attempts to write a log message within a 10 seconds interval are
      discarded.
     @ mysql-test/suite/rpl/r/rpl_ratelimit_warnings.result
        Test Result for BUG#42851.
     @ mysql-test/suite/rpl/t/rpl_ratelimit_warnings.test
        Added the test file to verify if the rate limit works fine.
     @ sql/log.cc
        Added the implementation of the 'Rate_limit' class.
     @ sql/log.h
        Added the definition of the 'Rate_limit' class.
     @ sql/mysqld.cc
        Added OPT_LOG_WARNINGS_RATELIMIT_INTERVAL and OPT_LOG_WARNINGS_RATELIMIT_BURST 
        options for rate limit.
     @ sql/set_var.cc
        Added interface for supporting to set '@@global.log_warnings_ratelimit_interval' 
        and '@@global.log_warnings_ratelimit_interval' to update the object of 
        Rate_limit class on runtime.
[3 Dec 2009 2:32] 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/92579

3233 Dao-Gang.Qu@sun.com	2009-12-03
      Bug #42851  	Spurious "Statement is not safe to log in statement format." warnings
      
      The unsafe warnings in error log make error log grow too large.
      
      To fix the problem, the unsafe warnings only will be written to error log, 
      when the level of --log-warnings option >= 2.
     @ mysql-test/suite/rpl/r/rpl_unsafe_warnings.result
        Test Result for BUG#42851
     @ mysql-test/suite/rpl/t/rpl_unsafe_warnings.test
        Added the test file to verify the unsafe warnings will be written to error log
        when the level of --log-warnings option >= 2, and the 'SHOW WARNINGS' is not 
        controlled by --log-warnings option.
[3 Dec 2009 8:36] 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/92596

3233 Dao-Gang.Qu@sun.com	2009-12-03
      Bug #42851  	Spurious "Statement is not safe to log in statement format." warnings
      
      The unsafe warnings in error log make error log grow too large.
      
      To fix the problem, the unsafe warnings only will be written to error log, 
      when the level of --log-warnings option >= 2.
     @ mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result
        Test Result for BUG#42851.
     @ mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test
        Added the test file to verify the unsafe warnings will be written to error log
        when the level of --log-warnings option >= 2, and the 'SHOW WARNINGS' is not 
        controlled by --log-warnings option.
[3 Dec 2009 9:32] 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/92615

3233 Dao-Gang.Qu@sun.com	2009-12-03
      Bug #42851  	Spurious "Statement is not safe to log in statement format." warnings
      
      The unsafe warnings in error log make error log grow too large.
      
      To fix the problem, the unsafe warnings will only be written to error log, 
      when --log-warnings is set to 2 or higher.
     @ mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result
        Test Result for BUG#42851.
     @ mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test
        Added the test file to verify the unsafe warnings will be written to error log
        when --log-warnings is set to 2 or higher, and the 'SHOW WARNINGS' is not 
        controlled by --log-warnings option.
[3 Dec 2009 9:48] Sergei Golubchik
The original bug was fixed in Bug#46265
The DoS problem is moved to a new bug#49392
[25 Mar 2010 17:34] Rick James
We, too, are getting a flood of incorrect Warnings in the error log.  Here are some details, which are slightly different:

100325 17:06:02 [Warning] Statement may not be safe to log in statement format.
Statement: UPDATE `sched_work_queue`
SET `work_status`  ='assigned',
    `work_updated` =NOW(),
    `assigned_host`='tempus1.eah.media.ac4.yahoo.com',
    `assigned_pid` ='28691'
WHERE `work_status` IN ('pending','stalled') AND `work_group` IN ('cache
resolvedScheduleSet-full','cache resolvedScheduleSet-key')
ORDER BY `work_updated` ASC, `work_group` ASC, `work_id` ASC LIMIT 1

CREATE TABLE `sched_work_queue` (
  `work_group` varchar(63) COLLATE utf8_bin NOT NULL DEFAULT '',
  `work_id` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `work_status` enum('pending','assigned','success','failure','stalled')
COLLATE utf8_bin DEFAULT NULL,
  `work_updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `assigned_host` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `assigned_pid` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  UNIQUE KEY `primary_idx` (`work_group`,`work_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Version: 5.1.41

Note that the entire PRIMARY KEY is included in the ORDER BY, so it is guaranteed to be consistent.  But it is not only the PK.  We only need work_updated, but added work_group,work_id after getting the Warning, so as to make it unambiguous.
[19 May 2010 7:53] Roel Van de Paar
There are some unaddressed issues here:

#1 [26 Feb 2009 4:52] Nicolae Namolovan comment:
   ...for some reason my error logs are now full of "090226  5:42:04 [Warning] Statement is not safe to log in statement format. Statement: ..", the problem is that this table is not even writed to binary logs since I have binlog-do-db=somedb and this table is in another database what is not replicated.

   Tested. This is not reproducible in 5.1.46. Maybe fixed or maybe an incorrect use of USE statement.
   See manual at: http://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html#option_mysqld_b...
   
#2 [8 Apr 2009 21:34] Ren Sauceda comment:
   I have tried changing binlog_format to MIXED and to then to ROW and I still get these errors in my log. 

   Tested. This is not reproducible in 5.1.46. Maybe fixed or maybe incorrect use of SESSION vs. GLOBAL binlog_format setting

#3 Some messages are spurious, some are not. An example:

mysql> SHOW CREATE TABLE a\G
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `id` int(11) NOT NULL DEFAULT '0',
  `p` char(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> DELETE FROM a ORDER BY id LIMIT 1000;
Query OK, 1000 rows affected, 1 warning (0.01 sec)

mysql> SHOW WARNINGS;
+-------+------+-------------------------------------------------------+
| Level | Code | Message                                               |
+-------+------+-------------------------------------------------------+
| Note  | 1592 | Statement may not be safe to log in statement format. | < false/spurious
+-------+------+-------------------------------------------------------+
1 row in set (0.00 sec)

mysql> DELETE FROM a LIMIT 1000;
Query OK, 1000 rows affected, 1 warning (0.07 sec)

mysql> SHOW WARNINGS;
+-------+------+-------------------------------------------------------+
| Level | Code | Message                                               |
+-------+------+-------------------------------------------------------+
| Note  | 1592 | Statement may not be safe to log in statement format. | < fully correct, row order not guaranteed
+-------+------+-------------------------------------------------------+
1 row in set (0.00 sec)

So, in implementing a "log_warnings" solution, we are eliminating both *good* messages and *bad* (incorrect/spurious) messages.

As soon as someone sets "log_warnings" for getting rid of incorrect messages, they have also eliminated perfectly fine ones.
[7 Oct 2010 17:34] Rick James
In other words, the code needs to be smarter about when to issue the warning.

Tentatively, this may suffice:  If the query ends with
   ORDER BY <unique key>
   LIMIT n
then the warning can be suppressed.
[12 Aug 2011 23:42] John Ventimiglia
FYI - the LIMIT trick does NOT work for me in version 5.1.56

Also, INSERT DELAYED causes the warning. Removing DELAYED cures it. I see no reference to DELAYED in the docs.

insert delayed into ebay_api_errors (c   reate_time, action, code, ssb_id, user, dbuser_id, country_code) values ('666', 'VerifyAddItem', 0, 43237, 'rhughes1196', 58860,    'us')
[15 Aug 2011 10:48] Sven Sandberg
John, INSERT DELAYED is unsafe and will generate a warning, as documented at http://dev.mysql.com/doc/refman/5.1/en/replication-rbr-safe-unsafe.html

LIMIT is safe or unsafe depending on the conditions (e.g., clauses like "WHERE primary_key = constant" or "ORDER BY primary_key" will make it safe). However, there is currently a warning for all statements that contain LIMIT. It would be better if we only printed a warning for unsafe LIMITs and no warning for safe LIMITs. That will be handled in BUG#42415. The present bug is a duplicate.
[21 Jul 2012 13:48] Marcelo Gomes
Usando: MySQL Ver 14.14 Distrib 5.1.46, for slackware-linux-gnu (x86_64) using readline 5.1

Olhando as variaveis de ambiente, percebi que a configuracao do arquivo my.cnf era ignorada 'binlog_format=mixed', pois a variavel estava "binlog format STATEMENT", portanto nessa versao faz diferenca a questao do 'sensitive case', o correto e 'binlog_format=MIXED'.

Resumindo: nao precisa alterar mais nada, basta trocar o mixed para MIXED !