Description:
mysql> CREATE ALGORITHM=UNDEFINED DEFINER=`user1`@`%` SQL SECURITY DEFINER VIEW `v2` AS SELECT id AS `a` FROM v1 ORDER BY id;
ERROR 1449 (HY000): The user specified as a definer ('nonexist'@'%') does not exist
Should say:
mysql> CREATE ALGORITHM=UNDEFINED DEFINER=`user1`@`%` SQL SECURITY DEFINER VIEW `v2` AS SELECT id AS `a` FROM v1 ORDER BY id;
ERROR 1449 (HY000): The user specified as a definer ('nonexist'@'%') for view 'test1.v1' does not exist
So that is is clear that the error message relates to the underlying view being selected from instead of the view being created (leading to confusion).
Just like (reference example):
mysql> CREATE ALGORITHM=UNDEFINED DEFINER=`user1`@`%` SQL SECURITY DEFINER VIEW `v2` AS SELECT id AS `a` FROM v1 ORDER BY id;
ERROR 1356 (HY000): View 'test1.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
How to repeat:
========== Original issue
DROP DATABASE IF EXISTS test1;
CREATE DATABASE test1;
USE test1;
DROP USER user1;
CREATE USER user1;
CREATE TABLE a (id INT); INSERT INTO a VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
CREATE TABLE b (id INT); INSERT INTO b VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
CREATE TABLE c (id INT); INSERT INTO c VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
CREATE ALGORITHM=UNDEFINED DEFINER=`nonexist`@`%` SQL SECURITY DEFINER VIEW `v1` AS SELECT b.id AS `b` FROM (b LEFT JOIN c ON((b.id = c.id)));
CREATE ALGORITHM=UNDEFINED DEFINER=`user1`@`%` SQL SECURITY DEFINER VIEW `v2` AS SELECT id AS `a` FROM v1 ORDER BY id;
==========
========== Reference example of better error message (ERROR 1356) which specifies what view the issue was from
DROP DATABASE IF EXISTS test1;
CREATE DATABASE test1;
USE test1;
DROP USER user1; /* Error if user does not exist, no IF EXISTS for DROP USER */
CREATE USER user1;
CREATE TABLE a (id INT);
CREATE TABLE b (id INT);
CREATE TABLE c (id INT);
CREATE ALGORITHM=UNDEFINED DEFINER=`user1`@`%` SQL SECURITY DEFINER VIEW `v1` AS SELECT b.id AS `b` FROM (b LEFT JOIN c ON((b.id = c.id)));
CREATE ALGORITHM=UNDEFINED DEFINER=`user1`@`%` SQL SECURITY DEFINER VIEW `v2` AS SELECT id AS `a` FROM v1 ORDER BY id;
==========
Suggested fix:
ERROR 1449 (HY000): The user specified as a definer ('nonexist'@'%') for view 'test1.v1' does not exist
i.e. specify the view.