While downstaging site, process fails with the error that database tables couldn't be dropped

Issue

While downstaging sites from Prod to Non-Prod, sometimes the process fails with the error - The database clearing process has encountered an error.

Resolution

Basically the issue is, any subsequent staging will fail on a site that has views. So if you want to restage a site that has mysql views, you need to manually drop those from the database first.

On checking the WIP logs for downstage failed task, in some scenarios, we get this error -

[ded-1234|2985] - The database clearing process has encountered an error. Completed in 18 seconds. Exit code: 1. Standard out: // Do you really want to drop all tables in the database xyzdb57657?:
// yes.; Standard error: In SqlCommands.php line 125:

Unable to drop all tables. Rerun with --debug to see any error message.

If you also see such errors, then you need to drop the views from the database first and then downstage again.

Cause

During staging, our process drops the whole database from the destination site and reimports it. However, there is an issue (most likely a bug with mysql) that prevents us from dropping the whole db when there are VIEWS in the database which is getting deleted.