If you recently upgraded your MySQL server version, you probably didn’t know that you need to run the mysql_upgrade package on all your databases as well. The mysql_upgrade package ensures your database tables are upgraded and ensures there are no incompatibilities you’re your current version of MariaDB server.
If you have upgraded SQL server from previous versions before and never ran the mysql_upgrade package, chances are your mysql_error.log is filling up with errors below.
[Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name. Please run mysql_upgrade
[Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade
If you are having trouble running the mysql_upgrade package command, it’s likely you aren’t running it correctly. You may receive the error below.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql_upgrade' at line 1
In order to run mysql_upgrade, you need to login to MySQL Server via command line and invoke the package. The steps below are an example to run mysql_upgrade inside a MAMP Pro server environment. It is highly recommended to take a backup of your MySQL databases before running this command.
- Open Terminal and type the following command to invoke the mysql_upgrade package.
/Applications/MAMP/Library/bin/mysql_upgrade --host=localhost -uroot -proot
- You may receive the following message below. If so, type the following command below to force the upgrade.
This installation of MySQL is already upgraded to 5.7.23, use --force if you still need to run mysql_upgrade
Then run the following command:
/Applications/MAMP/Library/bin/mysql_upgrade --host=localhost -uroot -proot -–force
MySQL server will check all it’s databases. This will run for a few minutes depending how many databases you have and their size.