MySQL Bugs: #108975: loadDump fails on view that references a view whose definer does not yet exist

Bug #108975	loadDump fails on view that references a view whose definer does not yet exist
Submitted:	2 Nov 2022 20:04	Modified:	15 Nov 2022 13:01
Reporter:	Duke Lee	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Shell Dump & Load	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If dumpInstance is used on an instance that contains a view (dbA.viewA) that references a view from a separate database (dbB.viewB) which has a definer other than 'root'@'localhost', loadDump will fail when using this data. The load process complains that the definer does not exist.

This error occurs specifically when there's a dependency against a separate database's view.

Normally, the load process would (should?) create placeholder views, load users, then recreate views, but the load process skips to the final view definition creation if the above scenario exists.

I would consider this a relatively serious bug as it could catch people off guard and render their instance dump data unusable.

How to repeat:
create user 'user'@'localhost' identified by 'user';
create database db1;
use db1;
create table db1_table (id int not null);
insert into db1_table values (1);
create definer='user'@'localhost' view db1_view as select * from db1_table;
grant select on db1.db1_table to 'user'@'localhost';
create database db2;
use db2;
create view db2_view as select * from db1.db1_view;

mysqlsh -h localhost -u root -p -P 33060 -- util dumpInstance "blah" --compatibility=strip_tablespaces --excludeUsers='root'

In a separate/fresh instance:

mysqlsh --verbose=4 -h localhost -u root -p -P33060 -- util loadDump "blah" --loadUsers=true

Error output:

verbose: 2022-11-02T09:50:02Z: Executing view DDL...
verbose: 2022-11-02T09:50:02Z: Begin loading view DDL
verbose: 2022-11-02T09:50:02Z: Fetching view DDL for db1.db1_view
verbose: 2022-11-02T09:50:02Z: Fetching view DDL for db2.db2_view
verbose: 2022-11-02T09:50:02Z: Executing DDL script for view `db1`.`db1_view`
verbose: 2022-11-02T09:50:02Z: Executing DDL script for view `db2`.`db2_view`
verbose: 2022-11-02T09:50:02Z: util.loadDump(): tid=13: MySQL Error 1449 (HY000): The user specified as a definer ('user'@'localhost') does not exist, SQL: /*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db2_view` AS select `db1`.`db1_view`.`id` AS `id` from `db1`.`db1_view` */
verbose: 2022-11-02T09:50:02Z: Error executing SQL: MySQL Error 1449 (HY000): The user specified as a definer ('user'@'localhost') does not exist:
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db2_view` AS select `db1`.`db1_view`.`id` AS `id` from `db1`.`db1_view` */
ERROR: Error executing DDL script for view `db2`.`db2_view`: MySQL Error 1449 (HY000): The user specified as a definer ('user'@'localhost') does not exist: /*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db2_view` AS select `db1`.`db1_view`.`id` AS `id` from `db1`.`db1_view` */
Executing view DDL - done       
verbose: 2022-11-02T09:50:02Z: Executing view DDL - done, duration: 0.029916 seconds

Suggested fix:
Not exactly sure why this is happening. I would think that loadDump would:

1) Create placeholder schemas/views
2) Create users
3) Apply grants
4) Recreate views with actual view definitions

but it seems as if it skips from 1 to 4 if the above "dependency chain" exists.

Potential workaround:

mysqlsh --force -h localhost -u root -p -P33060 -- util loadDump "blah" --loadData=false --loadDdl=true  --loadUsers=false --loadIndexes=false
mysqlsh --force -h localhost -u root -p -P33060 -- util loadDump "blah" --loadData=false --loadDdl=false --loadUsers=true
mysqlsh --force -h localhost -u root -p -P33060 -- util loadDump "blah" --loadData=false --loadDdl=true  --loadUsers=false --loadIndexes=false
mysqlsh --force -h localhost -u root -p -P33060 -- util loadDump "blah" --loadData=true  --loadDdl=false --loadUsers=false --deferTableIndexes=all
perl -n -e '/^\/\* (.+) \*\/;$/ && print $1 . ";\n"' @.users.sql | mysql -u root -p

The above loads DDL (which should have errors,) then users, DDL again (should succeed,) then loads data. The last perl line loads "missing" grants (due to bug #108974)

This has not been fully tested to see if it produces a precise/complete restoration.

Correction to the potential workaround:

mysqlsh --force -h localhost -u root -p -P33060 -- util loadDump "blah" --loadData=false --loadDdl=true  --loadUsers=false --loadIndexes=false
mysqlsh --force -h localhost -u root -p -P33060 -- util loadDump "blah" --loadData=false --loadDdl=false --loadUsers=true  --loadIndexes=false
mysqlsh --force -h localhost -u root -p -P33060 -- util loadDump "blah" --loadData=false --loadDdl=true  --loadUsers=false --loadIndexes=false
mysqlsh --force -h localhost -u root -p -P33060 -- util loadDump "blah" --loadData=true  --loadDdl=false --loadUsers=false --deferTableIndexes=all
perl -n -e '/^\/\* (.+) \*\/;$/ && print $1 . ";\n"' blah/@.users.sql | mysql -u root -p

Hi Mr. Lee,

Thank you for your bug report.

However, this is not a bug.

There has to be a user specified as definer. Which is why you get a proper error. It is not a bug in our shell, nor in our server. This is expected behaviour. Our database server has to abide by security standards set out for SQL.

Not a bug.

I'm not sure what you mean by "There has to be a user specified as a definer." Are you referring to db2.db2_view lacking a DEFINER? In MySQL documentation, the definer field is optional, which should be defaulting to whoever is running the create statement (in this case, root@localhost,) so db2.db2_view does have a definer at creation time, which is root@localhost...

Even then, that's not the problem described. The reproduction steps I've listed have valid SQL statements that produce no errors, followed by mysqlsh util.dumpInstance, and then mysqlsh util.loadDump. The reproduction steps all run successfully up until util.loadDump fails, which means there's a bug with util.loadDump

I've even tested this against a replica of our production server where we have such situations where db1.db1_view relies on db2.db2_view. I ran mysqlsh util.dumpInstance and attempted to load the data into a test server with util.loadDump, and the load process failed as described above.

Hi again,

I'd like to note a couple of things:

Firstly, I'd like to reiterate that lack of an explicit definer in db2.db2_view should not be the cause of the problem because it's a valid (My)SQL statement. e.g. If you run mysql -u root -p -e "CREATE DATABASE test; CREATE VIEW test.test_view AS SELECT 1;" the view's definer will be set to 'root'@'localhost'

Secondly, if you run the reproduction steps above, have it run into the DEFINER error, and if you login to the mysql server and view what's in it, you'll find that there exists:

db1 database
db1.db1_table
db1.db1_view (with definer='user'@'localhost')
db2 database

- db2.db2_view fails to create due to the definer error
- If you run SELECT user,host FROM mysql.user; there will be no user@localhost
- If you run SHOW CREATE VIEW db1.db1_view; it will show that its DEFINER='user'@'localhost' even though the user does not exist in the users table (how does this happen?)

Thirdly, I've managed to somehow "trick" mysqlsh's util.loadDump to finally load the example schemas that normally produce the error:

-- same structure as before
create user 'user'@'localhost' identified by 'user';
create database db1;
use db1;
create table db1_table (id int not null);
insert into db1_table values (1);
create definer='user'@'localhost' view db1_view as select * from db1_table;
grant select on db1.db1_table to 'user'@'localhost';
create database db2;
use db2;
create view db2_view as select * from db1.db1_view;

Dump the data:
mysqlsh -h localhost -u root -p -P 33060 -- util dumpInstance "blah" --compatibility=strip_tablespaces --excludeUsers='root'

In a fresh instance:
mysqlsh -h localhost -u root -p -P 33060 -- util loadDump "blah" --loadUsers=true --skipBinlog=true
mysqlsh -h localhost -u root -p -P 33060 -- util loadDump "blah" --loadUsers=true --skipBinlog=true --resetProgress
mysql -u root -p -e "DROP DATABASE db2; DROP DATABASE db1;"
mysqlsh -h localhost -u root -p -P 33060 -- util loadDump "blah" --loadUsers=true --skipBinlog=true --resetProgress

Note the presence of --skipBinlog=true in all three commands
The first command will produce the DEFINER error as mentioned previously.
The second command will attempt to reload the data but complain that the structures already exist and tells you to DROP them.
The third command drops the structures (only db1 and db2 needs to be dropped. user@localhost does not yet exist.)
The fourth command re-executes and SUCCESSFULLY loads the data for reasons unknown.

The presence of the --skipBinlog=true flag in all 3 mysqlsh commands allows the final command to successfully load the data. I've tried this _without_ the --skipBinlog=true flag, and the final command just repeats the same definer error as the first command.

I'm not sure why --skipBinlog=true would change the behavior of the schema loading process.

Hi Mr. Lee,

Thank you for your last efforts.

We managed to reproduce them.

Verified as reported.

Posted by developer:
 
Added the following note to the MySQL Shell 8.0.32 release notes:

loadDump failed when loading a view which referenced another view whose DEFINER did not yet exist.
As of this release, users are loaded after objects and view placeholders are created, but before the placeholders are
replaced by the views.

Thank you, Edward .....