Mysql – Why did error messages change after upgrading to MySQL 5.7.9

I think the problem stems from the fact that the upgrade was done trying to use the mysql schema folder of the previous version of MySQL, since the message "Can't open the mysql.plugin table" appears.

MySQL 5.1/5.5 uses a table called mysql.plugin

Prior versions (MySQL 5.1/4.x/3.x) do not use mysql.plugin.

I also noticed another oddity. The error message also said the highest file format is Barracida. The innodb_file_format of earlier versions of mysql is Antelope. You may want to export the InnoDB data in the previous version of mysql you had running. Otherwise, you may not have proper access to the InnoDB files.

Here is what you should do:

Step 1) Make a backup copy of that mysql folder. (/var/lib/mysql/mysql and /var/lib/mysql)

Step 2) You should reinstall the MySQL 5.0 version that was there before.

Step 3) Once mysql 5.0 is back up, perform a mysqldump of all databases except the mysql schema to /root/OldData.sql. Make sure you get triggers, stored procedures and views as follows:

For example, if you have 5 databases, (information_schema, db1, db2, db3, and mysql), do not include information_schema and mysql. Just mysqldump all the others.

mysqldump -hhostaddr -umyuserid -pmypassword --routines --triggers --databases db1 db2 db3 > /root/OldData.sql

Step 4) Capture the mysql grants from MySQL 5.0 as follows:

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > /root/MySQLGrants.sql

Step 5) service mysql stop

Step 6) mv /etc/my.cnf /etc/my.cnf1

Step 7) Clear away /var/lib/mysql (Make sure you did Step 1)

Step 8) Uninstall mysql 5.0

Step 9) Install mysql 5.5 (MySQL 5.5 does not auto-install anymore)

Step 10) mv /etc/my.cnf1 /etc/my.cnf

Step 11) service mysql start

Step 12) Login to mysql and from the mysql client run the scripts

Step 13) Load the two SQL scripts

mysql> source /root/MySQLGrants.sql
mysql> source /root/OldData.sql

You should fine from here.

Give it a Try !!!

This brings to mind something I have done a dozen times (fortunately with small datasets less then 10GB). When restoring a mysqldump to a new server, you must make absolutely sure the InnoDB settings on the new server is identical to the InnoDB settings.

For a new server that you have opted to restore a mysqldump to, run this command:

SHOW ENGINES;

This will display all storage engines available in in the MySQL instance. Here is a sample from a server running MySQL 5.5.9:

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

Notice that InnoDB has DEFAULT in the Support Column. There are four possible values for the Support column:

• YES
• NO
• DEFAULT
• DISABLED

One day, a server I was restoring to had mismatched configurations on the innodb_buffer_pool_size. Because I had set innodb_buffer_pool_size=512G instead of 512M (obvious typo), InnoDB would come up DISABLED. When loading the data into the new server, every CREATE TABLE defaulted to MyISAM upon execution. I didn't notice anything until I saw dozens of DB Connections that were waiting to write to the same table, which was typical of heavy writes against MyISAM. The solution was simply to start over with the correct InnoDB settings and reload.

MORAL OF THE STORY

1. Always make sure you can restore data to the same configuration as specfied in /etc/my.cnf. If the new server has less diskspace and/or less RAM than the server you mysqldump'd, they carefully configure /etc/my.cnf to the maximum settings that make the most sense for the new server you are restoring to.
2. Always run SHOW ENGINES; to make sure every storage engine is functioning.
3. Always read the error log and verify any anomalies with regard to startup, InnoDB crash recovery, fulltext stopword list declarations, MyISAM repair options, the amount of memory requested for buffers and DB Connections.
4. Always make sure ib_logfile0 and ib_logfile1 are the same size as the one specified in the old server's /etc/my.cnf.
5. Always use dev and staging DB servers to load old backups and test usability.