Problem with View When Restoring a MySQL Backup

Question

I have a database (InnoDB) with some tables, views and routines...

Periodically I do a backup using the command line:

mysqldump -u user -ppassword --routines db_name > backup.sql

But I have a problem also using workbench to create the backup

Btw the backup process terminate without problems or warining...

After restoration I got some problems, the restore process ends well without errors, but some views are corrupted

• if I try to alter it or view the structure I got an empty/blank result
• if I perform a select on the view I receive the correct results (like the view is not corrupted)...

I cannot alter the view itself..

How can I fix it? And why do I have this problem?

I get a blank/empty result using MySQL Workbench 5.2.35 CE....

using plain sql (describe view_name) I got the following error:

Error Code: 1356. View 'db.view_name' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

But the select * from view_name still return the expected result

SHOW CREATE VIEW view_name

Error Code: 1356. View 'view_name' references invalid table(s) or column(s) or function(s)> or definer/invoker of view lack rights to use them

SELECT * FROM INFORMATION_SCHEMA.TABLES where table_schema = 'db_name' and table_name = 'view_name'

TABLE_CATALOG def 
TABLE_SCHEMA db_name 
TABLE_NAME view_name 
TABLE_TYPE VIEW
ENGINE VERSION NULL
ROW_FORMAT NULL
TABLE_ROWS NULL
AVG_ROW_LENGTH NULL
DATA_LENGTH NULL
MAX_DATA_LENGTH NULL
INDEX_LENGTH NULL
DATA_FREE NULL
AUTO_INCREMENT NULL
CREATE_TIME NULL
UPDATE_TIME NULL
CHECK_TIME NULL
TABLE_COLLATION NULL
CHECKSUM NULL
CREATE_OPTIONS NULL
TABLE_COMMENT 'View ''db_name.view_name'' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them'

This view is a view generated by others views joined each other.

Answer 1

The definition of the View must be contained within the dump file.

To see the definitions, run the following command on the dumpfile:

cat backup.sql | grep -A 3 "CREATE ALGORITHM" > backup_views.sql
cat backup_views.sql

You could then drop the offending view(s) and recreate them manually.

If you have another server with the views established already, you should mysqldump the schema only. It will contain the views as well. @DTest and I wrote earlier posts on this.

Views are always processed last (after all tables) in a mysqldump.

This problem may also occur if one view references another, and the order the views are created doesn't take account of that.

Answer 2

Actually, as error reports, this can be related with view definer/invoker. Typically happens when you backup db1 where view definer is user1 and then you restore this backup under a different name, say, db2, so that user1 doesn't permissions on this new database.

In such case you can either map user1 to db2 or replace user1 with user2 in backup.sql file before running restore (assuming user2 has necessary permissions on db2 - usual case when you make production database backup and then restore it as test database).

You can read more details in this answer.