| Bug #42851 | Spurious "Statement is not safe to log in statement format." warnings | ||
|---|---|---|---|
| Submitted: | 13 Feb 23:27 | Modified: | 3 Dec 16:13 |
| Reporter: | Vincent Rivellino | ||
| Status: | Duplicate | ||
| Category: | Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.1.31 | OS: | Linux (Fedora Core 9) |
| Assigned to: | Daogang Qu | Target Version: | 5.1+ |
| Tags: | sbr, warning, statement, replication, log, limit | ||
| Triage: | Triaged: D3 (Medium) | ||
[13 Feb 23:27]
Vincent Rivellino
[13 Feb 23: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 12: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 19: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 11: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 11:55]
Jon Stephens
Commit updating docs: http://lists.mysql.com/commits/66620
[17 Feb 17: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 17:39]
Jon Stephens
This no longer appears to be a Docs issue; changing category to Server:Replication and lead to Lars.
[22 Feb 20:33]
Jon Stephens
Removing myself as assignee since this is no longer a documentation issue.
[22 Feb 20: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 20: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 9:15]
Sveta Smirnova
Thank you for the feedback. Having warning with order by and limit verified as replication bug.
[26 Feb 4: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 21: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 11: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 12: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 12:18]
Luís Soares
See also: BUG#42415.
[5 Jun 10:17]
Luís Soares
Another reference: BUG#34768.
[5 Jun 13: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 13: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 15: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 13:21]
Luís Soares
Pushed patch 1 to 5.1-bugteam and mysql-pe.
[8 Jul 15: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 9: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 10: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 13: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 13: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 13:51]
Jon Stephens
Back to NDI; see my previous comment.
[18 Jul 18: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 13: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 13: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 12: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 14: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 14:52]
Domas Mituzas
the bug hasn't been fixed - disabling warnings entirely doesn't seem to be a solution
[5 Aug 15:24]
Jon Stephens
Discussed with Davi. The changes show above were pushed, setting back to Documenting.
[5 Aug 15:42]
Jon Stephens
Documented in the 5.1.37 and 5.4.4 changelogs, as stated previously.
[8 Aug 13:52]
Domas Mituzas
Resetting it back to 'verified' per agreement.
[13 Aug 0:05]
Paul DuBois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[15 Aug 0:55]
Paul DuBois
Ignore previous comment about 5.4.2.
[26 Aug 15: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 15: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 15: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)
[27 Aug 1: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 18: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 15:11]
Paul DuBois
The 5.4 fix has been pushed to 5.4.2.
[11 Nov 7: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 9: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 3: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 9: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 10: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.
