Backup/mysqldump of one of my mysql/mariadb databases fails repeatedly

Hi team,
since about two weeks the backup dump of one of my databases fails with the following error message:

"Failed to make backup of database c1leaderclubnetwork, because mysqldump failed"

I already increased the "wait_timeout" of mariadb to 86400 but still the mysqldump fails for this database.

Important detail: Yes, the website and the database are very large in the meantime (filedump = 30GB, mysqldump = 125MB)

What could I do to solve this?

Thank you very much for your help in advance.

Best,
Johannes

 

That's what the mysql error log said:

mysqldump: Got error: 1045: "Access denied for user 'root'@'localhost' (using password: NO)" when trying to connect

User time 0.00, System time 0.00
Maximum resident set size 5856, Integral resident set size 0
Non-physical pagefaults 408, Physical pagefaults 0, Swaps 0
Blocks in 0 out 8, Messages in 0 out 0, Signals 0
Voluntary context switches 2, Involuntary context switches 0

 

Set the max_allowed_packet to 500MB - no use, still same error

 

Ah, oh, sorry.

Here the new one:


mysqldump: Got error: 1356: "View 'c1leaderclubnetwork.wp_uap_action_logs_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights>

User time 0.00, System time 0.00
Maximum resident set size 5996, Integral resident set size 0
Non-physical pagefaults 422, Physical pagefaults 0, Swaps 0
Blocks in 0 out 8, Messages in 0 out 0, Signals 0
Voluntary context switches 12, Involuntary context switches 0

 

seems something is wrong with table "wp_uap_action_logs_view"

 

Never had something like this. ("Ansichten") - Can I delete this?

 

Ok, I will check google & co. and will post my findings here, so that other users, who got a similar issue might get some hints.

 

So here is the solution:

If you are getting some error from mysql-dump like: " 'some_tablename_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights " open phpMyAdmin and issue the following sql-query:

SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';
(replace "database_name" with the name of the database, which fails to mysqldump)

This shows you all "views"-tables referencing to respective other tables. A view table seems to be a table for reading out just certain fields/colums from the referenced table. If this referenced table doesn't exist anymore (because it has been deleted) but the referencing "view"-table still is there and then references to nothing, mysqldump backup will fail and ISPConfig will produce an error in the monitor-log.

Solution: search all "views"-tables with above sql-statement and delete those, who reference to missing/deleted main tables. Then the mysqldump-backup will work again.

Best,
Johannes

 

Hello
Is there any way to be clear with this command, I mean shown via a tutorial, not to plant sites, about the cleaning to be done?
Mz