Select Page

[:de]Ubuntu Upgrade von 14 Trusty auf 16 Xenial, MySQL-Probleme beheben[:en]Ubuntu upgrade from 14 Trusty to 16 Xenial, solve MySQL issues[:]

by | Apr 29, 2017 | Administration, Linux, MySQL | 0 comments

This entry has been published on 2017-04-29 and may be out of date.

Last Updated on 2017-04-29.

[:en] 

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:

  1. If the server is a virtual appliance, create a snapshot when the system is shut down. Otherwise create a full backup.
  2. Within Ubuntu v14, do an “apt-get update && apt-get upgrade” and check if everything is running fine.
  3. Create a snapshot again.
  4. Edit /etc/mysql/my.cnf. In my case I had to delete variables
    1. table_cache
    2. log-slow-queries
    3. (long query time)
  5. Run “do-release-upgrade” to v16 Xenial.
  6. 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.
  7. 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”).
    1. 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.
  8. 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.
  9. Check if phpMyAdmin works. If Dbcommon failed during the upgrade, run “dpkg-reconfigure phpmyadmin”.

 

Additions:

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.

LimitNOFILE=infinity
LimitMEMLOCK=infinity

 

* 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

Run afterwards:

systemctl daemon-reload
service mysql restart

Check the currently used max_connections value e.g. with phpMyAdmin or MySQL console.

Update 2017-05-04:

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):

sql_mode=NO_ENGINE_SUBSTITUTION

 

 

 

Reference

Reference[:]

Submit a Comment