Support

Site Restoration

#13229 My site fails to restore in db restore section

Posted in ‘Site restoration’
This is a public ticket

Everybody will be able to see its contents. Do not include usernames, passwords or any other sensitive information.

Environment Information

PHP version
n/a
CMS Type
Other
CMS Version
n/a
Backup Tool Version
n/a
Kickstart version
n/a

user36073
Mandatory information about my setup:

Have I read the related troubleshooter articles above before posting (which pages?)? Yes
Have I searched the tickets before posting? Yes
Have I read the documentation before posting (which pages?)? Yes
Joomla! version:2.5
PHP version: (unknown)
MySQL version: (unknown)
Host: (optional, but it helps us help you)
Akeeba Backup version which took the backup: (unknown)
Kickstart version used to extract the backup: (unknown)

Description of my issue:


Hi, I get the following problem when I try to restore my site:

Error at the line 103: CREATE OR REPLACE ALGORITHM=UNDEFINED VIEW `#__gigcal_gigs_import` AS select `topfarms_findyourlocalie`.`j17_gigcal_gigs`.`id` AS `id`,concat(`topfarms_findyourlocalie`.`j17_gigcal_gigs`.`band_id`,'| ... MySQL: MySQL query failed with error 1146 (Table 'topfarms_findyourlocalie.j17_gigcal_gigs' doesn't exist). The query was:CREATE OR REPLACE ALGORITHM=UNDEFINED VIEW `fyl_gigcal_gigs_import` AS select `topfarms_findyourlocalie`.`j17_gigcal_gigs`.`id` AS `id`,concat(`topfarms_findyourlocalie`.`j17_gigcal_gigs`.`band_id`,'|',`topfarms_findyourlocalie`.`j17_gigcal_gigs`.`venue_id`,'|',`topfarms_findyourlocalie`.`j17_gigcal_gigs`.`gigdate`) AS `gigname` from `j17_gigcal_gigs`

This happens after I click next from the DB Restore screen.
I can send you the backup via dropbox if you want?

Please help me restore - this website was the culmination of months of work.
I have restored sites hundreds of times using akeebabackup and never had a problem.
kind regards,
Steve
stephen@cubeb.com

nicholas
Akeeba Staff
Manager
Hello Steve,

As I wrote in my email earlier today, I believe that your problem has to do with having the "No dependency tracking" option checked in your Akeeba Backup database dump engine configuration, or excluding a table referenced by the view but not the view itself.

I understand that you no longer have access to the original site, so we'll have to work around this issue the manual way. First, follow steps 1-3 of the Emergency restoration procedure. Before proceeding to the fourth step, please search your database dump files (.sql, .s01, ...) for
CREATE OR REPLACE ALGORITHM=UNDEFINED VIEW `#__gigcal_gigs_import` 
and remove that line from the backup file. Now continue with step 4. If you see any other VIEW not being restored, follow the same procedure. After completing step 4 you can restore your site the normal way, using Kickstart. Just make sure you use the same database table name prefix as the one you used in the manual restoration step 3.

Nicholas K. Dionysopoulos

Lead Developer and Director

🇬🇷Greek: native 🇬🇧English: excellent 🇫🇷French: basic • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

user36073
OK, database dump files: is that all the files in the sql folder of the backup?

user36073
Hi Nicholas,

thanks for your help. I have been following the steps and i found the line in one of the files.
Now I am a bit stuck with Step 4 of the emergency restoration procedure:

We did the following:

edited the joomla.sql file in the extracted version of my backup
removed the line as suggested from joomla.s15

Now I am left with 2 problems

Firstly, I don't know how to restore the database as suggested in Step 4

Secondly, you said to continue with restore using kickstart. Does that mean i have to convert the extracted version back into a JPA file. If so, how can I do that?

Kind regards,
Steve

nicholas
Akeeba Staff
Manager
Hi Steve,

You must first follow step 4. You will need to use phpMyAdmin. This is usually provided in your hosting control panel. If you at not sure please ask your host. Once in phpMyAdmn you will see an Import link at the top of the page. You can use it to restore each of the .sql, .s01 etc files.

Only after you complete all of th abov steps should you try restorIng with Kickstart again.

Nicholas K. Dionysopoulos

Lead Developer and Director

🇬🇷Greek: native 🇬🇧English: excellent 🇫🇷French: basic • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

user36073
Hi Nicholas
thanks for your reply - can you tell me something please?

Do I have to complete Step 3 (changing the table names) for each file in the SQL folder?

Because otherwise I am getting this message:


#1146 - Table 'topfarms_findyourlocalie.#__content' doesn't exist

nicholas
Akeeba Staff
Manager
Yes, it's required for each file. You may want to use a mass search and replace tool. Don't ask me for recommendations about such software, I do mass search and replace the hard way (command line) :)

Nicholas K. Dionysopoulos

Lead Developer and Director

🇬🇷Greek: native 🇬🇧English: excellent 🇫🇷French: basic • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

user36073
Thanks Nicholas,

I am using Notepad++ as per the user guide and it has a mass search and replace function which is working well thanks

Thanks again for your help - can i ask you one more thing?

After I restore the database, what file do i run with the kickstart.php?

Am i supposed to use the original jpa file? - (does that not have the old database files?)

nicholas
Akeeba Staff
Manager
Yes, after going through step 4 you can try restoring the .jpa file with Kickstart. The idea is that the VIEW creation had failed because at least one of the rerenced tables was not present in the database. By restoring the database manually (steps 1 through 4 of the manual restoration procedure) we are creating all tables. That's why the automatic restoration should now work. The only way it won't work is if that view was left behind but the tables it references are not present any more. This can happen if you uninstalled the respective component but its uninstallation forgot to remove the VIEW.

Nicholas K. Dionysopoulos

Lead Developer and Director

🇬🇷Greek: native 🇬🇧English: excellent 🇫🇷French: basic • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

Support Information

Working hours: Typically we work Monday to Friday, 9am to 7pm Cyprus timezone (EEST). Support is provided by the same developers writing the software, all of which live in Europe. You can still file tickets, but we cannot respond to them, outside of our working hours.

Support policy: We would like to kindly inform you that when using our support you have already agreed to the Support Policy which is part of our Terms of Service. Thank you for your understanding and for helping us help you!

Summer vacations: Our support will be closed for replies and new tickets from August 6th to August 21st, 2022 due to summer vacations.