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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
    
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 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 Prasannan Jun 15 '12 at 9:27
    
I have run the above query and result set is as below: engine table_count InnoDB 48 MEMORY 12 MyISAM 124 – Praveen Prasannan Jun 15 '12 at 9:30
    
Which version of MySQL? VIEWs have not always been around. A dumb SELECT * would dump a VIEW as if it were a table. Also, mysqldump has separate args for views, triggers, etc. – Rick James Jun 20 '12 at 23:02
up vote 4 down vote accepted

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
    
During long imports that seems like an error. Good catch! – Ray Foss Apr 27 at 19:16

I replaced hostname 'localhost' with'%' and it worked... I was trying to restore database using command prompt. while dropping table it was showing error 1227 which is related to user privileges. since the data was dumped from my local machine user credentials were also dumped in the file.

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.