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

Discussion in 'ISPConfig 3 Priority Support' started by decentris, Dec 8, 2020.

  1. decentris

    decentris Member HowtoForge Supporter

    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
     
  2. Th0m

    Th0m ISPConfig Developer Staff Member ISPConfig Developer

  3. till

    till Super Moderator Staff Member ISPConfig Developer

    Did you try to do a manual backup of the database using mysqldump on the shell to see if it fails there as well? Might be that it fails due to a crashed table or another kind of database corruption.
     
    Th0m likes this.
  4. decentris

    decentris Member HowtoForge Supporter

    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
     
  5. till

    till Super Moderator Staff Member ISPConfig Developer

    You have to provide a correct user and password, when using mysqldump. Example:

    mysqldump -u root -p databasename > dumpfile.sql

    The command will then ask you for the MySQL root password.
     
  6. decentris

    decentris Member HowtoForge Supporter

  7. decentris

    decentris Member HowtoForge Supporter

    Ah, oh, sorry. :rolleyes:

    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
     
  8. decentris

    decentris Member HowtoForge Supporter

    seems something is wrong with table "wp_uap_action_logs_view"
     
  9. decentris

    decentris Member HowtoForge Supporter

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

    Attached Files:

  10. till

    till Super Moderator Staff Member ISPConfig Developer

    I can't say that for sure as I never had that issue. Try to find occurrences of this issue by searching for the error message at e.g. google. In the end, you probably have to remove it, most likely the view references a table that does not exist anymore or something similar.
     
  11. decentris

    decentris Member HowtoForge Supporter

    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.
     
    till likes this.
  12. decentris

    decentris Member HowtoForge Supporter

    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
     
    till likes this.
  13. misterm

    misterm Member

    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
     

Share This Page