Can MySQL reliably restore backups containing views or not?

Question

Environment: Ubuntu 11.10, MySQL 5.1.58

I have a small database with views. When I try to reset and restore, I get

ERROR 1356 (HY000) at line 1693: View 'curation2.condition_reference_qrm_v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 

However, I can connect to the partially restored database and create the view myself. Therefore, I suspect that the error message is due to a problem not related to the view itself (but rather, as possible).

Here is a simple approach that I use to demonstrate the problem:

 MYSQL_PWD='xxx' mysqldump -u root --routines -B curation \ | perl -pe 's/`curation`/`curation2`/' \ | MYSQL_PWD='xxx' mysql -u root 

There are many other reports on the Internet about similar issues. The mysqldump man page has a cryptic note about errors with backup views, but it is written as a historical problem, not the current one.

So the question is: can MySQL reliably restore backups containing views or not? If possible, how? If not, what are people doing as a workaround?

Thanks Reece

Answer 1

I found a problem in my case. I am not sure that he solves such reports on the Internet.

This was fundamentally a resolution problem that arose as a result of attempts to copy this database to a new name. Permissions for this user and schema (locus on curation2) do not exist. I manually added "GRANT ALL ON curation2. * TO locus" (locus is the user registered in the error). After that, the above command line worked fine.

The lesson is that you must manually provide the necessary permissions for the target database and tables when creating a new database.

Answer 2

This question is a bit outdated, but I just spent a couple of hours trying to solve the exact same problem, so I think a clear explanation might come in handy for someone in the future ...

To abort the chase: the problem is the DEFINER field in your mysql dump. It looks something like this:

 /*!50013 DEFINER=`some_user`@`localhost` SQL SECURITY DEFINER */ 

The problem is that this * some_user @localhost * will always be tightly bound to the user account that was used to create the view in the source database and NOT the user that you used to export or import the database, as you might expect ( or at least I did it). And later, during import, this user will be used to recreate the view.

Thus, you can export / import as root, but if the source database is running under a different user and it does not have CREATE VIEW privileges in the new database, the import will fail.

You have two simple solutions:

• Find and replace all links to some_user @ localhost in the dump file with a new user (the one you use to import the dump, for example root @localhost)
• Or you can grant * some_user * the appropriate rights in the new database so that views can be created under its account.

In any case, the problem will be fixed, but I think that the first approach is much better and cleaner, since you do not need to worry about several users in the future.

Answer 3

What I found to solve the problem was to use the "sql security invoker" when initially creating the view.

  create or replace sql security invoker view <VIEW_NAME> as select ... 

It defines access to the view by the caller, not the qualifier.

Then, when the dump file is loaded, the view is created correctly.

With Amazon RDS:

To do this work with Amazon RDS, which does not allow super-privacy (which is necessary for the above), you can run this command in the dump file:

  # Remove DEFINER statement from VIEWS in Dump file sed -i 's/\sDEFINER=`[^`]*`@`[^`]*`//' $DUMPFILE_NAME 

Then, when the dump file is loaded into RDS, the view is created correctly.

Answer 4

A couple of things:

1.) Yes, you can create views using some kind of client. BUT, perhaps the owner of the tables is not the owner of the view, which leads to

2.) Usually, backing up views in mysql involves some “useless junk” like

 create algorithm xxx definer=<USER> sql security view <view_name> as .... 

and this user often includes the name of the IP or computer that went into the user’s login when creating the view ... SO, the view will not be created properly. Check it out, can help you.