Description:
Statements may be marked "unsafe", meaning that writing them to the binlog in statement format may cause the slave to diverge. If binlog_format=MIXED, then unsafe statements are logged in row format. If binlog_format=STATEMENT, then unsafe statements produce a warning.
If a stored function, procedure, trigger, or prepared statement contains more than one unsafe sub-statement, then more than one warning is printed to the client. It would be better if only one was printed.
(All warnings are also printed to the error log, but that is good because the error log contains not only the warning, but also the specific sub-query that caused the warning. So in the error log, the warnings look different.)
It is the intent of the current code to only print one warning, but it fails to do that. Here is a piece of THD::binlog_query() in sql_class.cc:
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;
}
Note that it sets the BINLOG_FLAG_UNSAFE_STMT_PRINTED flag the first time it prints the warning, and then it tries not to print the warning again if the flag is set. However, BINLOG_FLAG_UNSAFE_STMT_PRINTED is 0, so binlog_flags is always 0. This is from sql_class.h:
enum enum_binlog_flag {
BINLOG_FLAG_UNSAFE_STMT_PRINTED,
BINLOG_FLAG_COUNT
};
How to repeat:
See, e.g., binlog_unsafe.result, which contains more than one warning for some statements.
Suggested fix:
I'll fix this when I fix BUG#39934.
Description: Statements may be marked "unsafe", meaning that writing them to the binlog in statement format may cause the slave to diverge. If binlog_format=MIXED, then unsafe statements are logged in row format. If binlog_format=STATEMENT, then unsafe statements produce a warning. If a stored function, procedure, trigger, or prepared statement contains more than one unsafe sub-statement, then more than one warning is printed to the client. It would be better if only one was printed. (All warnings are also printed to the error log, but that is good because the error log contains not only the warning, but also the specific sub-query that caused the warning. So in the error log, the warnings look different.) It is the intent of the current code to only print one warning, but it fails to do that. Here is a piece of THD::binlog_query() in sql_class.cc: 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; } Note that it sets the BINLOG_FLAG_UNSAFE_STMT_PRINTED flag the first time it prints the warning, and then it tries not to print the warning again if the flag is set. However, BINLOG_FLAG_UNSAFE_STMT_PRINTED is 0, so binlog_flags is always 0. This is from sql_class.h: enum enum_binlog_flag { BINLOG_FLAG_UNSAFE_STMT_PRINTED, BINLOG_FLAG_COUNT }; How to repeat: See, e.g., binlog_unsafe.result, which contains more than one warning for some statements. Suggested fix: I'll fix this when I fix BUG#39934.