| 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: | |
| Category: | MySQL Server: Errors | Severity: | S3 (Non-critical) |
| Version: | 5.1.36 | OS: | Any |
| Assigned to: | Davi Arnaut | CPU Architecture: | Any |
[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+
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?