5

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

  • 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
  • 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 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
8

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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