Bug #45832 Incorrect error message when creating unsafe view
Submitted: 29 Jun 2009 16:53 Modified: 19 Aug 2015 13:56
Reporter: Sven Sandberg Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:5.1+ OS:Any
Assigned to: CPU Architecture:Any
Tags: unsafe, VIEW, warning
Triage: Triaged: D4 (Minor)

[29 Jun 2009 16:53] Sven Sandberg
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].
[30 Jun 2009 5:02] Sveta Smirnova
Thank you for the report.

Verified as described.
[19 Aug 2015 13:56] Paul Dubois
Fixed in 5.5.46, 5.6.27, 5.7.9, 5.8.0. Changes for test suite. No changelog entry needed.