MySQL Bugs: #84971: Warnings for statements flagged as unsafe

Bug #84971	Warnings for statements flagged as unsafe
Submitted:	13 Feb 2017 21:11	Modified:	15 Feb 2017 7:14
Reporter:	monty solomon	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.7.13, 5.6.35,5.7.17	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The server doesn't log statements that are unsafe when using mixed-format logging. The documentation states

https://dev.mysql.com/doc/refman/5.7/en/replication-rbr-safe-unsafe.html

When using mixed-format logging, statements flagged as unsafe are logged using the row-based format; statements regarded as safe are logged using the statement-based format.

...

Each statement flagged as unsafe generates a warning.

How to repeat:
CREATE TABLE `test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` char(36) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;

mysql> set binlog_format= 'MIXED';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test set uuid=uuid();
Query OK, 1 row affected (0.01 sec)

mysql> show warnings;
Empty set (0.00 sec)

Suggested fix:
Log warnings for unsafe statements when using mixed-format logging.

A server running with RBR could be switched to MBR to determine if it was safe to switch to SBR by checking the logs for unsafe statements.

The following statement in the documentation implies that warnings will always be logged for unsafe statements and not just in the case where SBR is enabled.

18.2.1.1 Advantages and Disadvantages of Statement-Based and Row-Based Replication
https://dev.mysql.com/doc/refman/5.7/en/replication-sbr-rbr.html

Statements that cannot be replicated correctly using statement-based replication are logged with a warning like the one shown here:

        [Warning] Statement is not safe to log in statement format.

The warning in the logs differs from the example in that section of the documentation.

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.

mysql> select @@log_warnings, @@log_error_verbosity, @@log_statements_unsafe_for_binlog\G
*************************** 1. row ***************************
                    @@log_warnings: 2
             @@log_error_verbosity: 3
@@log_statements_unsafe_for_binlog: 1
1 row in set, 1 warning (0.00 sec)

6.1.4 Server Command Options
https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_log-warnings

--log-warnings[=level]

 The server logs messages about statements that are unsafe for statement-based logging if the value is greater than 0.

Hello Monty,

Thank you for the report.
Observed this with 5.6.35 and 5.7.17.

Thanks,
Umesh