Last Updated on 2017-04-29.
Upgrading Ubuntu from v14 to 16 LTS can lead to MySQL server issues (MySQL is upgraded from v5.6 to 5.7 during the upgrade process).
E.g. if you use deprecated variables in /etc/mysql/my.cnf, MySQL server will fail to start after the upgrade is finished.
My recommendations for the upgrade:
- If the server is a virtual appliance, create a snapshot when the system is shut down. Otherwise create a full backup.
- Within Ubuntu v14, do an “apt-get update && apt-get upgrade” and check if everything is running fine.
- Create a snapshot again.
- Edit /etc/mysql/my.cnf. In my case I had to delete variables
- (long query time)
- Run “do-release-upgrade” to v16 Xenial.
- If you are using phpMyAdmin, the dbcommon upgrade procedure might fail during the upgrade process (“cannot access mysqld.sock” or similar). Simply ignore it for now.
- When the upgrade is finished, check if MySQL is running (“ps ax”) and if not, try to start it manually (e.g. “service mysql restart”).
- If MySQL server is unable to start, look at /var/log/mysql/error.log, maybe you have to modify my.cnf again, e.g. remove further certain variables. In this case, I recommend to note the vars and start from the latest snapshot again.
- If you have huge MySQL databases, watch /var/log/mysql/error.log and/or mysql.log. It might take several minutes as some DB types might be converted to newer formats in all tables.
- Check if phpMyAdmin works. If Dbcommon failed during the upgrade, run “dpkg-reconfigure phpmyadmin”.
It turned out that more modifications are necessary:
MySQL 5.7 seems to ignore max_connections value. If it is e.g. set to 600 in my.cnf, it still uses value 214.
To solve this issue, we have to do a few more adaptions.
* soft nofile 1024000 * hard nofile 1024000 * soft nproc 10240 * hard nproc 10240
open_files_limit = 1024000 table_open_cache = 500
Optionally, also decrease the timeout values:
wait_timeout = 5000 interactive_timeout = 5000
systemctl daemon-reload service mysql restart
Check the currently used max_connections value e.g. with phpMyAdmin or MySQL console.
If you encounter further MySQL errors like “ERROR 1366 Incorrect integer value: ” for column ‘xx’ at row xx, you might want to change sql_mode’s value to the default one from v5.6 (if modifying the SQL query is not an option):