Bug #56353 Confusing ERROR 1449 on VIEW creation
Submitted: 29 Aug 2010 23:26
Reporter: Roel Van de Paar Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:5.1.49 OS:Any
Assigned to: CPU Architecture:Any

[29 Aug 2010 23:26] Roel Van de Paar
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.