CODEWITHSUNDEEP
mysql
mobrien118
mobrien118

Reputation: 175

MySQL System Database Corrupt

A while ago, I accidentally deleted all of my MYSQL databases. Somehow, I got them all back in place in MySQL (from an old filesystem backup, I think).

However, it has never upgraded fully since then. I mean, apt-get installs the new SQL version, but the tables are never updated. For a couple years now, MySQL has run fine, but sometimes a reboot is needed after an "apt-get upgrade" attempt (which always errors out the MySQL post-install process) to kick it back into gear.

When I run "dpkg --configure mysql-server-5.7" I get the following output:

Setting up mysql-server-5.7 (5.7.24-0ubuntu0.16.04.1) ...
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
mysql_upgrade: [ERROR] 1812: Tablespace is missing for table `mysql`.`plugin`.
mysql_upgrade failed with exit status 5
dpkg: error processing package mysql-server-5.7 (--configure):
 subprocess installed post-installation script returned error exit status 1
Errors were encountered while processing:
 mysql-server-5.7

It's pretty obvious that the system table "plugin" is not in the database file... but how do I get it back? How do I re-create it? Since it is running fine without it, I mean, could I steal one from another server? Isn't something better than nothing?

I have dozens of databases with tons of data. I really don't want to jeopardize that...

I really don't know where to start... Please help.

Thanks!!

Upvotes: 0

Views: 736

Answers (2)

mobrien118
mobrien118

Reputation: 175

So, I got this to work. Not finished completely, but for each table that was "detached" I used the following command:

alter table [error_table] import tablespace;

A couple tables threw an error that

Error Code: 1808. Schema mismatch (Table has ROW_TYPE_COMPACT row format, .ibd file has ROW_TYPE_DYNAMIC row format.)

I have not resolved this yet. When I try to change the row format, the whole database crashes. I'll update this solution once I figure that out. Otherwise, if anyone has an suggestions, I welcome them!

Upvotes: 2

PaulProgrammer
PaulProgrammer

Reputation: 17630

You might try using the mysqlcheck utility.

mysqlcheck mysql plugin

Upvotes: 0

Related Questions