How do I correct InnoDB Multiple files found for the same tablespace ID?

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.

Method 1

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 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply