Unable create database dump in Plesk: View 'database.table' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them when using LOCK TABLES

Symptoms

• Unable to create a database dump in Plesk, the backup creation, or migration process fails:

  View 'database.table' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them when using LOCK TABLES

• An error is received while checking the integrity of the database and its tables with the mysqlcheck utility:

  Error: Table ‘database.table’ doesn’t exist
  status: Operation failed

Cause

The database view is corrupted.

Resolution

Initially, attempt to restore the database from a valid backup as instructed here.

If restoration cannot be done, then follow these steps to drop the damaged view:

Warning: Removing the view may affect application/website functionality.

1. Log in to Plesk.

2. Go to Domains > example.com > Databases.

3. Click on Check and Repair under the affected database.

4. In the pop-up box click on See Details and Resolve > Repair Selected to find the list of affected tables.

5. Connect to the server via SSH.

6. Manually remove the affected database view using the command for each table:

# plesk db "drop view database.table;"