MySQL Bugs: #66953: log-warnings does not work in session level

Bug #66953	log-warnings does not work in session level
Submitted:	25 Sep 2012 3:04	Modified:	26 Sep 2012 8:50
Reporter:	vin chen	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S4 (Feature request)
Version:	5.5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	log-warnings

Description:
MySQL 5.5 prints the warnings message to error log, such as unsafe statement-based binlog SQL.

The manual said,
The server logs messages about statements that are unsafe for statement-based logging only if --log-warnings is enabled.
http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_log-warnings

And the variable is both global and session，but the session level setting does not work.

How to repeat:
open statement-based binlog.

mysql> create table t1(c1 datetime);
Query OK, 0 rows affected (0.16 sec)

mysql> set log_warnings=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'log_warnings';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings  | 0     |
+---------------+-------+
1 row in set (0.02 sec)

mysql> insert into t1 values(sysdate());
Query OK, 1 row affected, 1 warning (4.01 sec)

the error log still prints the warning message:
120925 10:55:24 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. Statement: insert into t1 values(sysdate())

Suggested fix:
modify function sql_class.cc THD::issue_unsafe_warnings(), use session variable value on thd instead

void THD::issue_unsafe_warnings()
{
  ...
  for (int unsafe_type=0;
       unsafe_type < LEX::BINLOG_STMT_UNSAFE_COUNT;
       unsafe_type++)
  {
    if ((unsafe_type_flags & (1 << unsafe_type)) != 0)
    {
      push_warning_printf(this, MYSQL_ERROR::WARN_LEVEL_NOTE,
                          ER_BINLOG_UNSAFE_STATEMENT,
                          ER(ER_BINLOG_UNSAFE_STATEMENT),
                          ER(LEX::binlog_stmt_unsafe_errcode[unsafe_type]));
      /* use thd's variable value instead */
      //if (global_system_variables.log_warnings)
      if (this->variables.log_warnings)
      {
        char buf[MYSQL_ERRMSG_SIZE * 2];
        sprintf(buf, ER(ER_BINLOG_UNSAFE_STATEMENT),
                ER(LEX::binlog_stmt_unsafe_errcode[unsafe_type]));
        sql_print_warning(ER(ER_MESSAGE_AND_STATEMENT), buf, query());
      }
    }
  }
  DBUG_VOID_RETURN;
}

a problem with allowing session value is that a user can fill up the partition on which the error log resides, with these useless error messages printed zillions of times.

@Shane .. in this case the user actually *disables* 'log_warnings' for the SESSION! 

So this case is about a user realizing that he may do stupid things (what can easily happen with automated tests executing random-generated statements and similar) and don't want to write garbage entries to the log. 

Your concern 'turns against you' IMO. 

Peter
(not a MySQL/Oracle person)

Just 'philosophicating' :-)

This could actually be considered a missing logic in the implementation of GLOBAL|SESSION variables in MySQL: There could be situations where it should be legal to SET the variable to OFF for the session if it is globally ON - but not to set in ON if it is globally OFF (or vice versa - depending on the implementation of the variable in discussion).

Related discussion:
http://bugs.mysql.com/bug.php?id=53466
(end_connection() should reference global_system_variables.log_warnings)

Ok, verifying this as a feature request.  But still, what if a DBA *really* wants to know if his binlogs/slaves could be broken due to unsafe sql?

In this case, I think a SUPER should be the only one to disable it on a session level if it is enabled globally.  And always, only SUPER to enable the warnings on session if they are globally disabled.   What do you think?

is this FR still valid for 5.6 ?   In 5.6 I see this:  " if (log_warnings) "

log_warnings is global only in 5.6...

http://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_log-warnings

mysql> set session log_warnings=2;
ERROR 1229 (HY000): Variable 'log_warnings' is a GLOBAL variable and should be set with SET GLOBAL

It is not only a feature request as far as I can see.

log-warnings is both and option and both global and session variable.  Any user can set session variables.  That is how it is documented and how "SET variable" is supposed to work currently.

Current behavior contradicts documentation!