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

[Trane Francks]

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

 

[cPanelMichael]

Hello @Trane Francks,

Can you open a support ticket so we can take a closer look at your system? You can post the ticket number here and I'll link this thread to it.

Thank you.

 

[Trane Francks]

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.

 

[cPanelMichael]

Hello @Trane Francks,

Thank you for the additional information, and I'm glad to see you were able to solve the issue.

I attempted to reproduce this issue by adding the "skip-federated" line to the /etc/my.cnf file prior to upgrading from MySQL version 5.7 to MariaDB version 10.3. However, the existence of this line did not prevent the MySQL upgrade from succeeding, and MariaDB starts well after the upgrade. Do you happen to remember the exact entry that you removed from /etc/my.cnf when you encountered the reported issue?

Thank you.

 

[Trane Francks]

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.

 

[cPanelMichael]

Hello @Trane Francks,

I encourage you to submit a defect report using the following link if you'd like us to take a closer look and open an internal case to prevent this from happening in the future:

cPanel Customer Portal

Let me know the ticket number once it's opened and I'll link this thread to it.

Thank you.