How To Fix MySQL Table mysql.plugin Doesn’t Exist

Share This:

How To Fix MySQL Table mysql.plugin Doesn't Exist 1

I recently had an issue on a MySQL Database Server where running apt-get upgrade to upgrade the MySQL package on the server would result in an error about the table ‘mysql.plugin’ doesn’t exist. I believe the issue started on this server after I changed the datadir in my.cnf and I moved the database directory to a different location on the server. In my data directory under the MySQL database folder, I could see the .frm and .ibd files for the plugin database, but the error was preventing an upgrade of the MySQL Server. The server was running fine and the websites connecting to the server were all functioning, so this issue only prevented an upgrade. It took a lot of trial and error and hours of research, but I was finally able to piece together a solution that worked for me when all others failed.

The Errors

In my case, the error I was getting was:

Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
mysql_upgrade: [ERROR] 1146: Table ‘mysql.plugin’ doesn’t exist
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
dpkg: dependency problems prevent configuration of mysql-server:
mysql-server depends on mysql-server-5.7; however:
Package mysql-server-5.7 is not configured yet.

dpkg: error processing package mysql-server (–configure):
dependency problems – leaving unconfigured
Errors were encountered while processing:
mysql-server-5.7
mysql-server

If I logged onto the MySQL server through the command line, I could do a use mysql to select the mysql database, but then if I tried running SELECT * FROM plugin; I would get an error that the table doesn’t exist, even though it would show up if I ran SHOW TABLES.

While looking in /var/logs/mysql/error.log, I would find errors like:

[Warning] InnoDB: Cannot open table mysql/plugin from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
mysqld: Table ‘mysql.plugin’ doesn’t exist
[ERROR] Can’t open the mysql.plugin table. Please run mysql_upgrade to create it.

Obviously running mysql_upgrade didn’t work because it would just generate the same errors again.

Trial And Error

I tried everything I could find online, but nothing worked for me. I backed up all of the databases and wiped them all out. In my datadir, I deleted the ibdata1, ibtmp1, and ib_logfile0 files and re-created all of the databases, but I still ran into the same errors. At one point, I think I even completely wiped out MySQL, including the /var/lib/mysql, var/logs/mysql, and /etc/mysql directories, and in the end, I still ran into the same issues.

The Solution

BACKUP BACKUP BACKUP – Before you do any of the steps below, backup your entire server and backup all of your databases. You can never be too cautious!

After much digging, I found this Launchpad Script that helped me. It was made for MySQL 5.6, but I was able to work through it and get it to work on MySQL 5.7. I had to comment out the set storage_engine=myisam; line.

Basically, I kept multiple windows open connected to my server. In one window, I would run the upgrade command to get the table that has the error.

In another window, connect to the MySQL server and use the MySQL database:

mysql -u root -p
[enter password]
use mysql
SHOW TABLES;

On the table that is causing the error, run DROP TABLE plugin; (or replace plugin with the table name).

In another window, run mysql -u root -p mysql < mysql_system_tables.sql (or use the path to where you saved the Launchpad Script. After that completes, try your upgrade command again. In my case, I got another table with the same error. I had to keep repeating the above steps for EVERY table that gave the error.

If you get the error below, I had to move the .ibd file for that table to a tmp directory I called /tmp/mysql_orphans/ then run the upgrade command again.

mysql_upgrade: [ERROR] 1813: Tablespace '`mysql`.`servers`' exists.
mysql_upgrade failed with exit status 5

After getting through the above errors for every table with an issue, I ran into this error on multiple tables while trying to upgrade:

mysql_upgrade: [ERROR] 1025: Error on rename of './mysql/#sql-3941_5' to './mysql/help_topic' (errno: 184 - Tablespace already exists)
mysql_upgrade failed with exit status 5

To resolve this error, I had to go back into my datadir for the mysql database and look for the .ibd file for that table. Even though it was just created, I renamed it to .old, then I ran the upgrade commands again and repeated the same steps for every table that gave me the error.

The complete list of tables that gave me issues were:

engine_cost
gtid_executed
help_topic
help_category
help_relation
help_keyword
innodb_index_stats
innodb_table_stats
plugin
server_cost
servers
slave_master_info
slave_relay_log_info
slave_worker_info
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type

Conclusion

After many hours of work and research, I was able to successfully upgrade my MySQL Server without errors. Based on how complicated it was for me to resolve the problem, I'm hoping there's an easier way out there for someone, but just in case there isn't and you stumble upon this article, hopefully this helps you get through the errors. If you have any suggestions, please leave a comment below. If you're trying to resolve this issue on your server and need help, please leave some details below and I'll try my best to help.


Share This:

 

6 Comments

  1. aihardin March 24, 2017
    • Robert Russell March 24, 2017
  2. NoLabel April 25, 2017
  3. djerman May 6, 2017
  4. Steve February 21, 2020
    • Rob Russell February 21, 2020

Leave a Comment