Solved Backup error - WP database

P

patrickkasie

Verified User
Joined
Sep 21, 2021
Messages
244
Location
Een echte Hollander
Dear DirectAdmin forum,

When my automatic backups failed just this night, I've immediately started searching far and wide for the solution online. Lo and behold, it was there, telling me to check my /tmp folder and to see if the disk has been filled up. They are not! Perhaps it's something that's going on in the middle of the night that prevents it all from moving forward by themselves, so after another attempt at a backup just now, I get the same result, though in the terminal. It is only for 2 specific users out of like 30 or so, but nothing has changed in regards to moving from MDB version.

Code: 
# da taskq
2024/01/04 09:21:52  info executing task            task=action=backup&append_to_path=nothing&database_data_aware=yes&email_data_aware=yes&local_path=%2Fhome%2Fadmin%2Fadmin_backups&owner=admin&select0=userhere&trash_aware=yes&type=admin&value=multiple&when=now&where=local
2024/01/04 09:24:10 error running backup task       data=map[action:[backup] append_to_path:[nothing] database_data_aware:[yes] email_data_aware:[yes] local_path:[/home/admin/admin_backups] owner:[admin] select0:[userhere] trash_aware:[yes] type:[admin] value:[multiple] when:[now] where:[local]] error=error code 1: Error while backing up database userhere_wp
mysqldump error output: mysqldump: Couldn't execute 'show create table `wp_e_events`': Table 'userhere_wp.wp_e_events' doesn't exist in engine (1932)

Backup sizes of both users are 1.2GB, with the largest succesful user being 26GB
Available 66GB
CentOS7
MariaDB 10.1 (I never changed this, even when it got phased out)
 
Richard G said:
This is causing the error, might be database issue/problem. Check this site and kindlike for solutions.
Click to expand...

I have tried all solutions, the last one seems to be the only one that gave a diagnostic that somewhat resembles what I need an answer to:

Code: 
vuurwerkbr_db.wp_wfhits
note     : The storage engine for the table doesn't support repair
vuurwerkbr_db.wp_wfhoover
Warning  : Tablespace is missing for table 'vuurwerkbr_db/wp_wfhoover'
Error    : Table 'vuurwerkbr_db.wp_wfhoover' doesn't exist in engine
status   : Operation failed
vuurwerkbr_db.wp_wfissues
note     : The storage engine for the table doesn't support repair
vuurwerkbr_db.wp_wfknownfilelist
note     : The storage engine for the table doesn't support repair
 
You could try this:


PHPMyAdmin: If you have access to PHPMyAdmin, you can repair the table through the graphical interface:

  • Login to PHPMyAdmin.
  • Select your database.
  • Click on the table that’s crashed (in this case table_name).
  • Click on the Operations tab.
  • Under ‘Table maintenance’, click the Repair table button.
 
Merely clicking the table gives the following error:
The second screenshot says -in use- - Edited for visibility

chrome_rpurwtyZeJ.png
chrome_M5fEx7BJy2.png
 
Last edited:
Can you try creating a native MariaDB backup and see if the error persists?
That is via the mysqldump command.
mysqldump -uusername -ppassword databasename >/backup/dump.sql

Then remove the database from DA.
And then see if restore via the:
mysql -uUSERNAME -p DATABASENAME < /path/to/the/backup.sql
commnd fixes anything.

If not, we somebody with better mysql knowledge if this is even fixable.
 
Richard G said:
Can you try creating a native MariaDB backup and see if the error persists?
That is via the mysqldump command.
mysqldump -uusername -ppassword databasename >/backup/dump.sql

Then remove the database from DA.
And then see if restore via the:
mysql -uUSERNAME -p DATABASENAME < /path/to/the/backup.sql
commnd fixes anything.

If not, we somebody with better mysql knowledge if this is even fixable.
Click to expand...
I have performed the mysqldump command, just as you've provided in the earlier link by dba ninja
mysqldump -u root -p vuurwerkbr_db > /mariadb/backups/mybackup.sql
Result:
Code: 
# mysqldump -u root -p vuurwerkbr_db > /mariadb/backups/mybackup.sql
Enter password:
mysqldump: Got error: 1932: "Table 'vuurwerkbr_db.wp_wfhoover' doesn't exist in engine" when using LOCK TABLES
I choose not to continue deleting a database that can't be backed up
 
patrickkasie said:
I choose not to continue deleting a database that can't be backed up
Click to expand...
Ofcourse not indeed. You don't have any correct working, maybe older backup?

Most likely problem is that this table you're missing, is existing in the database itself but not in the ibdata1 file. Which causes the missing table error and ofcourse also the lock error.

Now I'm no mysql/mariadb guru, but maybe there is a workaround. Copy your backup file so you have one to play with.

1.) Create a totally new database
2.) Import the backup you have

In fact it should give no problems then, because tables will be newly created.

If it has the same problem again, edit the backup file and remove the wp_wfhoover table from it and it contents.
Then try importing again.

In fact, the wfhoover is from Wordfence plugin if I'm correct. So a possible solution could also be to uninstall wordfence from the current installation if possible and then create a backup and install the wordfence plugin again.
 
Richard G said:
Ofcourse not indeed. You don't have any correct working, maybe older backup?

Most likely problem is that this table you're missing, is existing in the database itself but not in the ibdata1 file. Which causes the missing table error and ofcourse also the lock error.

Now I'm no mysql/mariadb guru, but maybe there is a workaround. Copy your backup file so you have one to play with.

1.) Create a totally new database
2.) Import the backup you have

In fact it should give no problems then, because tables will be newly created.

If it has the same problem again, edit the backup file and remove the wp_wfhoover table from it and it contents.
Then try importing again.

In fact, the wfhoover is from Wordfence plugin if I'm correct. So a possible solution could also be to uninstall wordfence from the current installation if possible and then create a backup and install the wordfence plugin again.
Click to expand...

I don't have an older backup file of the database itself, but I do have the hosting package itself which, while failing to complete the database backup, I do have access to. I've restored that on another server. These are the results:

Backup succesfully restored
Can login to PMA
Can open the database
The database records ends with the very next table being the broken table

You probably could have guessed that yourself, but I just want to ensure I'm not missing any steps. I would assume it's going to be the same result if I'd import the database. I'm not sure what to do at this point.

Edit: I also mean that I don't have the backup in /usr/local/directadmin/custombuild/mysql_backups

chrome_kyShni5XFx.png
 
Last edited:
patrickkasie said:
I don't have an older backup file of the database itself,
Click to expand...
You have a problem then if you only have the crippled backup and no older working backup.

I don't now if tables can be fixed in the ibdata1 file, most likely that is not possible.

At this moment, is the site still working or not.
So like, it works correctly but only does not make a correct database backup?
Or is it not working at all?

If it still works correctly, then as said, uninstall the WordFence plugin and try to make another backup.
 
I guest, there have some weird data in the table records.

If you can test restore on other server, trying restore only Structure by yourself ( open sql text find and copy sql statememt of that's table and manual import query ).
 
The site is working.
The backup is simply incomplete with the database, but the backup itself works upon restoring for everything else but the full database.
So it's partially working.

The database structure of the hoover table is present, it's just the actual data that is missing. Did MySQL make a whoopsie here?

Code: 
151126356 -rw-rw----   1 mysql mysql 3.0K Feb 22  2021 wp_wfhits.frm
151126357 -rw-rw----   1 mysql mysql  10M Feb  5 11:12 wp_wfhits.ibd
151135237 -rw-rw----   1 mysql mysql 1.7K Feb 22  2021 wp_wfhoover.frm
151126358 -rw-rw----   1 mysql mysql 4.2K Feb 22  2021 wp_wfissues.frm
151126359 -rw-rw----   1 mysql mysql 160K Feb  5 11:02 wp_wfissues.ibd
150999680 -rw-rw----   1 mysql mysql  995 May 18  2022 wp_wfknownfilelist.frm
150996453 -rw-rw----   1 mysql mysql  10M Feb  5 02:13 wp_wfknownfilelist.ibd

Does this mean I can safely remove the table? The data is not present anyway, so what good would it do to leave a zombie table in the database.
 
patrickkasie said:
Does this mean I can safely remove the table?
Click to expand...
The table is from Wordfence. Why did you not try to uninstall and remove the Wordfence plugin first as I already suggested a couple of times?
When you remove the table then WF will complaint anyway so you have to reinstall. Maybe with uninstalling WF the table will get removed automatically or else you can delete it because WF is gone then anyway.
 
Richard G said:
The table is from Wordfence. Why did you not try to uninstall and remove the Wordfence plugin first as I already suggested a couple of times?
When you remove the table then WF will complaint anyway so you have to reinstall. Maybe with uninstalling WF the table will get removed automatically or else you can delete it because WF is gone then anyway.
Click to expand...
I have not removed the table, but my job is not to make WordPress work. It's our customer's, and I will still suggest them to remove WordFence themselves. I don't want to mess with their hosting process, but our promise as a company is for working backups at all times. However, this did alert me to move the missing table away by using SSH (mv wp_wfhoover.frm /home/vuurwerkbr) and that did fix the backup process.

I have suggested the customer to reinstall WordFence, and if that doesn't work, we'll do it this way.
 
patrickkasie said:
I have not removed the table, but my job is not to make WordPress work. It's our customer's,
Click to expand...
Correct but your customer is having a problem now and you already want to remove a table for him. You can't keep a promise when a customer cripples things. You don't need to reinstall WordFence for them, it only needs to be removed.
I thought you were helping the customer fixing it completely.

However, if you only removed the table and that works too, then it's indeed the customers issue further on.
 
I have just noticed something very odd about my post. Initially, I was talking about `wp_e_events` and we carried on with another table called `wp_wfhoover`. The latter seems to have been fixed, even though the former one has not. It suffers from the same issue, and so I will have to let that customer know as well that they have to reinstall Elementor or take it out completely. However, the issue seems to have been fixed in the grand scheme of things.

Thank you very much for the help, this definitely moved me into the right direction.
 
Last edited:
just to add: if you want to full uninstall wordfence, then there is a checkbox field somewhere in its configuration, to choose if you want to remove it completely with all residues inkl. database tables, or not (for later reinstalling).
 
You must log in or register to reply here.
Back
Top