Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

EDIT

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

EDIT 2

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

share|improve this question
    
What UI are you using to look at/alter the view definitions? –  Phil May 2 '12 at 11:03
    
I updated the question with other information about the UI and the sql query results... hope this helps.. –  Marcx May 2 '12 at 12:02
    
What version of MySQL ? Can you run SHOW CREATE VIEW view_name\G ? Can you run SELECT * FROM information_schema.views ? –  RolandoMySQLDBA May 2 '12 at 12:25
    
edit the question.. thank for your help :) –  Marcx May 2 '12 at 12:34
add comment

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

share|improve this answer
    
I'm not sure if this can affect(someone correct me if I'm wrong), but check the order of the tables are restored, if the system try to restore a view before of the real table it will through an error –  jcho360 May 11 '12 at 13:36
1  
@jcho360 views are always processed last (after all tables) in a mysqldump. –  RolandoMySQLDBA May 11 '12 at 14:15
    
I wasn't sure, and I read it somewhere, thanks anyway –  jcho360 May 11 '12 at 14:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.