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
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.