Tell me more ×
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.
up vote 0 down vote favorite

I have a full database dump and i restored it. But in my new restored server all the views are appeared as MyISAM tables. In early times dumps were restored perfectly. can anyone help me to trigger out the issue, please?

Regards, Praveen

share|improve this question
Have a check on mysql error logs! What do they say. – user9398 Jun 14 '12 at 9:48
Which tool have you used to take a backup? can you paste a sample SQL which shows view's statement? – Mahesh Patil Jun 14 '12 at 10:59
Please, accept some answers of your previous questions (eg this one). – dezso Jun 14 '12 at 13:17
Please run this query : SELECT IFNULL(engine,'View') engine,COUNT(1) table_count from information_schema.tables group by IFNULL(engine,'View'); show that output in the question. – RolandoMySQLDBA Jun 14 '12 at 14:40
I used below command for backup: mysqldump --user=root --password=pwd --lock-all-tables --log-error=/var/lib/mysqlback/log/backup.log --all-databases > /var/lib/mysqlback/alldatabases.sql and this for restore: mysql --user=root --password=pwd < /var/lib/mysqlback/alldatabases.sql – Praveen Jun 15 '12 at 9:27
show 3 more comments

1 Answer

up vote 1 down vote

In the backup script the views are first created as tables which are then dropped at the end of the script as each view is being created, so it seems that an error occurs while creating the views at the end of the script.

However when a view is created there is a user who is used in the DEFINER clause of the view who may not exist in your database, e.g., DEFINER=user123@192.168.1.%

I usually remove that clause in the backup script and it tends to work.

To verify add the -v option when importing the database

share|improve this answer

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.