Mysql – Problem with View When Restoring a MySQL Backup

backupMySQLmysqldumprestoreview

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.

Best Answer

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.

Related Question