| 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: | |
| 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
[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 !
