9

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

Improve this question
6
  • Which tool have you used to take a backup? can you paste a sample SQL which shows view's statement?
    – Mahesh Patil
    Commented Jun 14, 2012 at 10:59
  • 1
    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
    Commented Jun 14, 2012 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
    Commented Jun 15, 2012 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
    Commented Jun 15, 2012 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
    Commented Jun 20, 2012 at 23:02

2 Answers 2

Reset to default
12

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

Improve this answer
1
  • During long imports that seems like an error. Good catch!
    – Ray Foss
    Commented Apr 27, 2016 at 19:16
-2

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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