All we need is an easy explanation of the problem, so here it is.
Some time ago after an update of Ubuntu 20.10, I discovered that MySQL was not running. When I tried to restart it, the error message suggested that it had started, failed and stopped. A check of the error log contained "InnoDB Multiple files found for the same tablespace ID" and listed 25 such instances, for example
Tablespace ID: 92 = ['mercury/pickupcity.ibd', 'mycity/wp_mycity_eventlog.ibd']
How do I correct InnoDB Multiple files found for the same tablespace ID?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
After reading up on what a tablespace was, it became clear that they were tables in the databases. I noticed that one database recurred in each of the 25 lines, whether it was first in the order or second. Based on that I concluded that that database was linked to the problem and so deleted it using
sudo rm -R /var/lib/mysql/mercury in the console.
Next I started MySQL with
sudo systemctl start mysql and after an unusual delay, the server started.
Luckily I have the sql file to recreate the deleted database.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂