Failed Upgrade MySQL 5.6 --> 5.7 -- Now What?

Greetings, folks.

This has been a very stressful morning. Since the recent upgrade to WHM 78, I'd been getting nagged that our MySQL 5.6 was out of date and that the preferred version would be 5.7. This morning, after a successful backup of all the user accounts, I took the plunge.

Things did not go well.

The server was running MySQL Governor atop CLOUDLINUX 7.6. The CL command line switch to 5.7 seemed to take correctly and the subsequent install started throwing various errors. Upon completion of the install, mysql wouldn't start. Rolling back also resulted in a no-start. An attempt to upgrade to MariaDB also failed.

Finally, I uninstalled MySQL Governor, deleted MySQL PID and then ran a cPanel rpm package check to fix things. MySQL was simply not there at all now. cPanel installed the missing 5.6 rpms, but MySQL still wouldn't start. I finally solved that problem by deleting the InnoDB log files and now the server is sitting on 5.6 with my original my.cnf file. CL MySQL Governor is currently not installed.

With that long preamble out of the way, I need to ask: Are there any incompatibilities whatsoever in my.cnf between MySQL 5.6 and 5.7? What about MySQL 5.6 to MariaDB 10.3, which appears to still be the recommended upgrade path? While I recognize that it's probably time to upgrade, I really don't want a repeat of this morning's fiasco.

my.cnf:

[mysqld]
performance-schema = 0
skip-external-locking
query_cache_size = 32M
socket = "/var/lib/mysql/mysql.sock"
thread_cache_size = 8
max_allowed_packet = 268435456
key_buffer = 256M
sort_buffer_size = 1M
character_set_server = utf8
read_rnd_buffer_size = 4M
collation_server = utf8_general_ci
server-id = 1
thread_concurrency = 4
max_connections = 200
local-infile = 0
read_buffer_size = 1M
port = 3306
skip-federated
myisam_sort_buffer_size = 64M
table_open_cache = 2M
default-storage-engine = InnoDB
innodb_file_per_table = 1
open_files_limit = 50000
innodb_fast_shutdown=0

[client]
port = 3306
socket = "/var/lib/mysql/mysql.sock"

[myisamchk]
write_buffer = 2M
read_buffer = 2M
key_buffer = 128M
sort_buffer_size = 128M

[mysqldump]
quick
max_allowed_packet = 16M

[isamchk]
write_buffer = 2M
read_buffer = 2M
key_buffer = 128M
sort_buffer_size = 128M

[mysqlhotcopy]
interactive-timeout

[mysql]
no-auto-rehash
​

Thanks for any help. If I was nervous about upgrading prior to this morning, I'm definitely not looking forward to doing so now.

trane

 

Hi, Michael.

After a day of client work, I went after this upgrade again this morning. This time, without CloudLinux's MySQL Governor installed, I tried again, but decided to go with MariaDB. Today also failed, but I was able to note which option caused the failure between MariaDB 10.0 to 10.1, at which point the upgrade process failed and the server's databases were now offline.

The upgrade complained of unknown option 'skip-federated', which is listed in my.cnf above. Despite the upgrade process claiming that it would repair my.cnf to ensure all options were correct, IT DID NOT. Hence, the failure of MySQL/MariaDB to operate. So, I'm answering my own original question. Are there any incompatibilities? Yes. If you've got skip-federated in your my.cnf, remove it PRIOR to upgrading from MySQL 5.6 to 5.7 or from MariaDB 10.0 to 10.1.

The server is happily purring along with MariaDB 10.3 now. This thread can be marked as resolved.

 

Michael,

The my.cnf from my functioning MySQL 5.6 was listed in the original post. MySQL 5.6 to MariaDB 10.0 succeeded with this original file. MariaDB 10.0 to 10.1 failed with this my.cnf in place. The ONLY change required to make 10.1 through 10.3 succeed was to remove skip-federated from the [mysqld] section of my.cnf.