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.
When a view contains an unsafe sub-statement, any subsequent statement reading from the view shall be unsafe. For example, after "CREATE VIEW v1 SELECT UUID();", the following statement shall be unsafe:
INSERT INTO t1 SELECT * FROM v1;
This works fine. However, the CREATE VIEW statement itself is also marked unsafe. If binlog_format=STATEMENT, the following warning message is printed:
Statement may not be safe to log in statement format.
This message is not correct: only subsequent SELECTs from the view are unsafe, not CREATE VIEW itself. CREATE VIEW will always be logged in statement format.
How to repeat:
source include/have_binlog_format_statement.inc;
CREATE TABLE t1 (a VARCHAR(1000));
CREATE VIEW v1 AS SELECT UUID();
SELECT * FROM v1;
INSERT INTO t1 SELECT * FROM v1;
DROP TABLE t1;
DROP VIEW v1;
exit;
Suggested fix:
There are two possible ways to fix this:
(1) Suppress the warning for CREATE VIEW with unsafe sub-statement.
(2) Give a better formulated warning for CREATE VIEW with unsafe sub-statement.
Something like:
View contains sub-statement that is unsafe to log in statement format.
Note: in this case, the warning shall be printed even if
binlog_format=MIXED or binlog_format=ROW, because the binlog format may
change after CREATE VIEW and before SELECT FROM [view].
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. When a view contains an unsafe sub-statement, any subsequent statement reading from the view shall be unsafe. For example, after "CREATE VIEW v1 SELECT UUID();", the following statement shall be unsafe: INSERT INTO t1 SELECT * FROM v1; This works fine. However, the CREATE VIEW statement itself is also marked unsafe. If binlog_format=STATEMENT, the following warning message is printed: Statement may not be safe to log in statement format. This message is not correct: only subsequent SELECTs from the view are unsafe, not CREATE VIEW itself. CREATE VIEW will always be logged in statement format. How to repeat: source include/have_binlog_format_statement.inc; CREATE TABLE t1 (a VARCHAR(1000)); CREATE VIEW v1 AS SELECT UUID(); SELECT * FROM v1; INSERT INTO t1 SELECT * FROM v1; DROP TABLE t1; DROP VIEW v1; exit; Suggested fix: There are two possible ways to fix this: (1) Suppress the warning for CREATE VIEW with unsafe sub-statement. (2) Give a better formulated warning for CREATE VIEW with unsafe sub-statement. Something like: View contains sub-statement that is unsafe to log in statement format. Note: in this case, the warning shall be printed even if binlog_format=MIXED or binlog_format=ROW, because the binlog format may change after CREATE VIEW and before SELECT FROM [view].