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. This is the case for, e.g., views that contain the unsafe sub-statement "SELECT UUID()".
However, a view that contains "SELECT...LIMIT" is not marked as unsafe. No warning is printed if binlog_format=STATEMENT, and the statement is logged in statement format even if binlog_format=MIXED.
(A related bug is that, for other unsafe statements, both the CREATE VIEW statement itself and subsequent statements that execute it produce a warning if the view is unsafe. It is not correct to produce a warning for the CREATE VIEW. I will report that separately.)
How to repeat:
source include/have_binlog_format_statement.inc;
CREATE TABLE t1 (a VARCHAR(1000));
CREATE TABLE t2 (a VARCHAR(1000));
INSERT INTO t1 VALUES ('table 1');
INSERT INTO t2 VALUES ('table 2');
CREATE VIEW v1 AS SELECT * FROM t1 LIMIT 1;
CREATE VIEW v2 AS SELECT UUID();
SELECT * FROM v1;
SELECT * FROM v2;
# Does not give a warning
INSERT INTO t2 SELECT * FROM v1;
# Gives a warning
INSERT INTO t2 SELECT * FROM v2;
SET binlog_format = MIXED;
DROP VIEW v1, v2;
CREATE VIEW v1 AS SELECT * FROM t1 LIMIT 1;
CREATE VIEW v2 AS SELECT UUID();
SELECT * FROM v1;
SELECT * FROM v2;
# Logged in statement format
INSERT INTO t2 SELECT * FROM v1;
# Logged in row format
INSERT INTO t2 SELECT * FROM v2;
source include/show_binlog_events.inc;
DROP TABLE t1, t2;
DROP VIEW v1, v2;
exit;
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. When a view contains an unsafe sub-statement, any subsequent statement reading from the view shall be unsafe. This is the case for, e.g., views that contain the unsafe sub-statement "SELECT UUID()". However, a view that contains "SELECT...LIMIT" is not marked as unsafe. No warning is printed if binlog_format=STATEMENT, and the statement is logged in statement format even if binlog_format=MIXED. (A related bug is that, for other unsafe statements, both the CREATE VIEW statement itself and subsequent statements that execute it produce a warning if the view is unsafe. It is not correct to produce a warning for the CREATE VIEW. I will report that separately.) How to repeat: source include/have_binlog_format_statement.inc; CREATE TABLE t1 (a VARCHAR(1000)); CREATE TABLE t2 (a VARCHAR(1000)); INSERT INTO t1 VALUES ('table 1'); INSERT INTO t2 VALUES ('table 2'); CREATE VIEW v1 AS SELECT * FROM t1 LIMIT 1; CREATE VIEW v2 AS SELECT UUID(); SELECT * FROM v1; SELECT * FROM v2; # Does not give a warning INSERT INTO t2 SELECT * FROM v1; # Gives a warning INSERT INTO t2 SELECT * FROM v2; SET binlog_format = MIXED; DROP VIEW v1, v2; CREATE VIEW v1 AS SELECT * FROM t1 LIMIT 1; CREATE VIEW v2 AS SELECT UUID(); SELECT * FROM v1; SELECT * FROM v2; # Logged in statement format INSERT INTO t2 SELECT * FROM v1; # Logged in row format INSERT INTO t2 SELECT * FROM v2; source include/show_binlog_events.inc; DROP TABLE t1, t2; DROP VIEW v1, v2; exit; Suggested fix: I'll fix this when I fix BUG#39934.