MySQL upgrade fails on Ubuntu 16.04

Question

I have Ubuntu 16.04 running (clean install - no upgrade). MySQL is running fine - but I get this error when I try to update MySQL:

apt-get install
Reading package lists... Done
Building dependency tree       
Reading state information... Done
0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded.
2 not fully installed or removed.
After this operation, 0 B of additional disk space will be used.
Setting up mysql-server-5.7 (5.7.16-0ubuntu0.16.04.1) ...
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
The sys schema is already up to date (version 1.5.1).
Checking databases.

ALL DATABASES RETURNED OK

Error occurred: Error during call to mysql_check.
mysql_upgrade failed with exit status 4
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
E: Sub-process /usr/bin/dpkg returned an error code (1)

Although it's running, I dont like errors. Do you guys have any idea how to fix this issue?

Running dpkg -l | grep mysql gives:

dpkg -l | grep mysql 
ii  libdbd-mysql-perl                  4.033-1ubuntu0.1                                     amd64        Perl5 database interface to the MySQL database
ii  libmysqlclient20:amd64             5.7.16-0ubuntu0.16.04.1                              amd64        MySQL database client library
ii  mysql-client                       5.7.16-0ubuntu0.16.04.1                              all          MySQL database client (metapackage depending on the latest version)
ii  mysql-client-5.7                   5.7.16-0ubuntu0.16.04.1                              amd64        MySQL database client binaries
ii  mysql-client-core-5.7              5.7.16-0ubuntu0.16.04.1                              amd64        MySQL database core client binaries
ii  mysql-common                       5.7.16-0ubuntu0.16.04.1                              all          MySQL database common files, e.g. /etc/mysql/my.cnf
iU  mysql-server                       5.7.16-0ubuntu0.16.04.1                              all          MySQL database server (metapackage depending on the latest version)
iF  mysql-server-5.7                   5.7.16-0ubuntu0.16.04.1                              amd64        MySQL database server binaries and system database setup
ii  mysql-server-core-5.7              5.7.16-0ubuntu0.16.04.1                              amd64        MySQL database server binaries
ii  php-mysql                          1:7.0+45+deb.sury.org~xenial+1                       all          MySQL module for PHP [default]
ii  php5.6-mysql                       5.6.27-1+deb.sury.org~xenial+1                       amd64        MySQL module for PHP
ii  php7.0-mysql                       7.0.12-1+deb.sury.org~xenial+1                       amd64        MySQL module for PHP

Thanks

Answer 1

I've run into this myself and I believe it has to do with how the upgrade process stops/starts/restarts MySQL.

Here is my workaround that I do after an attempted upgrade of MySQL fails.

1. Start MySQL normally, typically "service mysql start" as root.
2. As root execute "mysql_upgrade --defaults-file=/etc/mysql/debian.cnf". Hopefully it should complete with no errors or indicate that MySQL is already upgraded.
3. Edit the file "/var/lib/dpkg/info/mysql-server-5.7.postinst" with your favorite editor. Around line 320 (depending on version) find the line "mysql_upgrade --defaults-file=/etc/mysql/debian.cnf || result=$?". Comment that line out (prepend line with '#'), if should look like "#mysql_upgrade --defaults-file=/etc/mysql/debian.cnf || result=$?". Save the file and exit from the editor.
4. Rerun the upgrade process and it should indicate that MySQL is now upgraded.

At some point I will dig further to see why the "mysql_upgrade ..." invocation fails during the upgrade but not alone from the command line. But I've not gone through a few iterations of upgrades without issue using this method.

Hope this helps.

'Grip

Answer 2

I've had this issue before, the root cause for this issue was changing the db configuration. I changed lower_case_table_names = 1 and set the value to one, and when I tried to upgrade it failed with the error you shared in your question.

To solve it, I simple set the value back to 0, restarted the db, and the upgrade worked well for me.

First edit the config:

sudo nano /etc/mysql/my.cnf

change this line:

lower_case_table_names = 0

restart the database

sudo /etc/init.d/mysql restart 

Then run the upgrade command

Answer 3

NOTE: This is for upgrading MySQL with "mysql_secure_installation" fix.

Had problems with upgrading MySQL Server 5.7 in Ubuntu 16.04 and I finally located my problem. I enabled "mysql_secure_installation" during my first installation that causes the upgrade script to fail due to password creation error (unsatisfied special character requirement on the automatically generated password in the upgrade script).

While running 'sudo apt upgrade' or 'sudo apt install -f', the error can be traced using this command:

tail -f /var/log/mysql/error.log

In my case, the log shows:

[ERROR] 1819 Your password does not satisfy the current policy requirements

To fix this, login to mysql and temporarily disable MySQL's validate password plugin:

mysql -u root -p -h 127.0.0.1

mysql> uninstall plugin validate_password;

mysql> exit

make sure to stop the previous upgrade/install and rerun the upgrade/install. Once done, login back to mysql and enable MySQL's validate password plugin;

mysql -u root -p -h 127.0.0.1

mysql> install plugin validate_password SONAME 'validate_password.so';

mysql> exit

Answer 4

always check /var/log/mysqld.log first