Unable create database dump: 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, 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

• An attempt to back up a database via command-line interface fails with an error:

  # plesk db dump database > database.sql
  mysqldump: Got error: 1356: "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
  exit status 2

Cause

The database view is corrupted.

Resolution

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;"

Alternatively, restore the affected database from the valid backup as instructed here.