Bug #93139 mysqldump temporary views missing definer
Submitted: 9 Nov 2018 10:44 Modified: 9 Nov 2018 12:02
Reporter: Nikolai Ikhalainen Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:5.6.42, 5.7.24, OS:Any
Assigned to: CPU Architecture:Any

[9 Nov 2018 10:44] Nikolai Ikhalainen 
Description:
I have two views:
create DEFINER=test@'%' SQL SECURITY INVOKER VIEW test.v0 AS SELECT 1;
create DEFINER=test@'%' SQL SECURITY INVOKER VIEW test.v1 AS SELECT 1 FROM test.v0;

mysql root user is removed.
Trying to import dump to a just created server and getting surprising error:
ERROR 1449 (HY000) at line 938: The user specified as a definer ('root'@'localhost') does not exist

5.5.62 - not affected, because it uses temporary myisam table
5.6.42, 5.7.24, 8.0.13
ERROR 1449 (HY000) at line 974: The user specified as a definer ('root'@'localhost') does not exist

Different versions have a slightly different line containing statement:
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`test`@`%` SQL SECURITY INVOKER */
/*!50001 VIEW `v0` AS select 1 AS `1` from `v1` */;

At this moment, temporary view defined as:
/*!50001 CREATE VIEW `v1` AS SELECT 
 1 AS `1`*/;

How to repeat:
I'm using docker & bash script:
for VER in 5.5 5.6 5.7 8.0; do docker pull mysql:$VER;docker rm -f mbug; docker run --name mbug -d -e MYSQL_ALLOW_EMPTY_PASSWORD=1 mysql:$VER;docker exec -it mbug mysqladmin ping --wait;sleep 60;docker exec -it mbug mysqladmin ping --wait;docker exec -it mbug mysql -e "SELECT VERSION();create database IF NOT EXISTS test;CREATE USER test@'%' IDENTIFIED BY '';grant all privileges on *.* to test@'%' with grant option;DROP USER root@'%'; DROP USER root@'localhost';";docker exec -it mbug mysql -u test -e "create DEFINER=test@'%' SQL SECURITY INVOKER VIEW test.v1 AS SELECT 1;create DEFINER=test@'%' SQL SECURITY INVOKER VIEW test.v0 AS SELECT 1 FROM test.v1;";docker exec -it mbug mysqldump -u test --all-databases --flush-privileges > dump.sql;docker rm -f mbug;docker run --name mbug -d -e MYSQL_ALLOW_EMPTY_PASSWORD=1 mysql:$VER;docker exec -it mbug mysqladmin ping --wait;sleep 60;docker exec -it mbug mysqladmin ping --wait;docker exec -i mbug mysql < dump.sql ; done

mysql 8.0 requires longer sleep due to slow docker container initialization.

Suggested fix:
If I'm adding definer from original view to temporary view the problem disappears:
-/*!50001 CREATE VIEW `v0` AS SELECT 
+/*!50001 CREATE DEFINER=test@'%' VIEW `v0` AS SELECT 
-/*!50001 CREATE VIEW `v1` AS SELECT 
+/*!50001 CREATE DEFINER=test@'%' VIEW `v1` AS SELECT
[9 Nov 2018 11:32] Nikolai Ikhalainen 
sorry in description incorrect views naming is used,
views selecting from another view should have a name before selected view:
create DEFINER=test@'%' SQL SECURITY INVOKER VIEW test.v1 AS SELECT 1;
create DEFINER=test@'%' SQL SECURITY INVOKER VIEW test.v0 AS SELECT 1 FROM test.v1;

because mysqldump will create
temporary view for v0
temporary view for v1

view for v0 -- and here we can get error on missing root@localhost
view for v1
[9 Nov 2018 12:02] MySQL Verification Team 
Hello Nikolai,

Thank you for the report.
Verified as described with 5.7.24 build.

regards,
Umesh