1

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?

Improve this question
2
  • The first visible hint that something was gone awry was when new upgrades ended with a message that MySQL was unable to start an I had no clue it had stopped. Locally hosted Web pages depending on it became inaccessible. I wasn't doing anything related to MySQL when whatever happened.
    – Mark Lee
    Commented Jun 14, 2021 at 19:00
  • It's a MySQL problem, that just happens to be on Ubuntu. I wasn't upgrading the MySQL version. I just accepted a prompt that various upgrades were available and as usual, without checking all the applications being updated, I accepted "Update all".. So now that two files are sharing a table space, whatever caused it. I want to correct fixing the wheel that punctured and not find which hole I fell into. MySQL Ver 8.0.25-0ubuntu0.20.10.1
    – Mark Lee
    Commented Jun 14, 2021 at 21:43

2 Answers 2

Reset to default
3

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.

Improve this answer
1

https://serverfault.com/questions/954801/multiple-files-found-for-the-same-tablespace-id addresses the same issue, and presents what appears to be a more satisfactory solution for preserving data.

  • Move one of the offending files to an alternate location
  • start mysql
  • backup the other problem table
  • shut down server and swap which files are out of place
  • restart server
  • drop backed up table, restore from backup
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.