Questions about MySQL 5.6 EOL update

marjwyatt

Well-Known Member
Jun 23, 2014
48
5
58
cPanel Access Level
Reseller Owner
I've read over the link recommended by cPanel about this EOL update: https://blog.cpanel.com/mysql-5-6-and-mariadb-10-1-end-of-life/

That link referred to a documentation cPanel source that resulted in a 404. I pulled the /88/ off the end of that link and found this source of information. So, my first question would be, is this the right DIY link? https://docs.cpanel.net/whm/sql-services/mysql-or-mariadb-upgrade/

Step #4 on that page says this:
"If you are upgrading your database server from MySQL version 5.7 to 8, the Upgrade Checker via MySQLShell (optional) section will appear. This utility checks whether the upgrade will work on the MySQL configuration file and table engine. To run the MySQL Upgrade Checker utility, click Install and Run Checker. A progress window will appear, and it will display the results of the upgrade check."

I'd feel much more comfortable proceeding with this upgrade if there was a utility that did some pre-update checking but that paragraph makes it seem like this only happens if one is updating MySQL 5.7 to MySQL 8.0. Am I misinterpreting the words on that page? Will the MySQL Upgrade Checker utility option be offered during a MySQL 5.6 to MySQL 5.7 update?

This warning is issued on the page:
" We strongly recommend that you back up your database before you upgrade your database or change to MariaDB. "

Does this warning suggest there is a recovery path should something go horribly wrong? I do nightly backups for all accounts and system files and move them to an S3 bucket for longer term storage. Would I have to recover system_files, all accounts, or what?

I've read in another thread that MySQL 5.7 doesn't require any particular version of PHP. I've been using Multi PHP as I transition older sites to be compatible with PHP 7.2. That transition will take more time on a couple sites but, may I safely assume that upgrading MySQL to 5.7 will permit me to keep getting cPanel security updates?

It seems like the least disruptive path, for me, would be to update MySQL 5.6 -> MySQL 5.7. I've read in other threads in this forum that, after updating to MySQL 5.7, future upgrades would be restricted to MariaDB. Is that true and, if so, why?
 

keat63

Well-Known Member
Nov 20, 2014
1,763
199
93
cPanel Access Level
Root Administrator
I went through the same deliberations.
Originally seriously considering switching to MariaDB.

From what I recall, MariaDB was the preferred path due to MYSQL not having had any updates, (or something along those lines), but that had been back tracked and was suggested to stick now with MYSQL.
I'm sure someone with more knowledge will confirm this.
I'm aware that once you upgrade to MariaDB, there is no going back to MYSQL, so I guess this decision is something not to be taken lightly.

I eventually decided to go with MYSQL 5.7.
I'm sure if you searched my posts you'll find me talking about it somewhere.
My update from 5.6 to 5.7 went without a hitch and was pretty quick.

Just one thing to be aware of is strict mode.
You may find after updating that you'll need to disable strict mode.

 
  • Like
Reactions: marjwyatt

marjwyatt

Well-Known Member
Jun 23, 2014
48
5
58
cPanel Access Level
Reseller Owner
I went through the same deliberations.
Originally seriously considering switching to MariaDB.

From what I recall, MariaDB was the preferred path due to MYSQL not having had any updates, (or something along those lines), but that had been back tracked and was suggested to stick now with MYSQL.
I'm sure someone with more knowledge will confirm this.
I'm aware that once you upgrade to MariaDB, there is no going back to MYSQL, so I guess this decision is something not to be taken lightly.

I eventually decided to go with MYSQL 5.7.
I'm sure if you searched my posts you'll find me talking about it somewhere.
My update from 5.6 to 5.7 went without a hitch and was pretty quick.

Just one thing to be aware of is strict mode.
You may find after updating that you'll need to disable strict mode.

It's good to know that your upgrade went smoothly. I'm fairly certain yours was the thread I read through before posting my question. Can you tell me whether the MySQL Upgrade Checker utility was available during your upgrade?
 

keat63

Well-Known Member
Nov 20, 2014
1,763
199
93
cPanel Access Level
Root Administrator
I'm guessing there must have been some kind of checker, as the guys on here were telling me that it wouldn't upgrade if anything were wrong.
At the time @cPanelMichael suggested " if the upgrade fails then MySQL 5.6 will remain installed "

I recall that I sat with my finger over the upgrade button for 5 minutes saying, should I or shouldn't I.
I already had backups of the databases, so my concern was really around the work involved rolling back should anything go wrong.


Much water has passed under the bridge since, but i believe that it took less than about 20 minutes.

There are a few steps to take regarding stopping services before updating.
here's my old thread.

 

ffeingol

Well-Known Member
PartnerNOC
Nov 9, 2001
390
88
328
cPanel Access Level
DataCenter Provider
A couple of suggestions before up upgrade. First check and repair all the databases on the server;

Code:
mysqlcheck --all-databases --auto-repair
More than likely you won't have any crashed tables, but you never know. The above code checks all the tables on the server and does repairs on them if necessary. If you do have crashed tables and you try to backups all the databases (with mysqldump) mysqldump will exit when it hits the crashed table (and then you'll have to fix it and then run the dump over).

Secondly (speaking as a former DBA) "try" to fix the code (if you can) instead of disabling strict. With strict off MySQL will let you put 11 characters in a 10 character column and simply truncate the 11th character. With script on it will die when you try to do that. I know you can't fix customers code, but eventually that kind of code will really come back and bite you (your customer).
 
  • Like
Reactions: marjwyatt

marjwyatt

Well-Known Member
Jun 23, 2014
48
5
58
cPanel Access Level
Reseller Owner
Secondly (speaking as a former DBA) "try" to fix the code (if you can) instead of disabling strict. With strict off MySQL will let you put 11 characters in a 10 character column and simply truncate the 11th character. With script on it will die when you try to do that. I know you can't fix customers code, but eventually that kind of code will really come back and bite you (your customer).
Are you saying that I should NOT disable strict under any circumstances?
 

ffeingol

Well-Known Member
PartnerNOC
Nov 9, 2001
390
88
328
cPanel Access Level
DataCenter Provider
Are you saying that I should NOT disable strict under any circumstances?
of course not. I'm just suggesting that if you have the choice between fixing the code and disabling strict, try to fix the code. The only place we have seen issues with strict is custom coded things. All of the main stream, open source software will have no issues.
 

keat63

Well-Known Member
Nov 20, 2014
1,763
199
93
cPanel Access Level
Root Administrator
Depends how many sites you run.
I disabled strict mode 18 months ago and have no issues.

You may be OK, you may find that your sites don't suffer and no need to disable strict mode.
Look at it this way, strict mode either doesn't exist or is disabled in 5.6, so what difference is it going to make.
 

marjwyatt

Well-Known Member
Jun 23, 2014
48
5
58
cPanel Access Level
Reseller Owner
of course not. I'm just suggesting that if you have the choice between fixing the code and disabling strict, try to fix the code. The only place we have seen issues with strict is custom coded things. All of the main stream, open source software will have no issues.
Thanks for the answer. I just ran a command to see if strict mode is currently enabled and it appears that it is not based on this output.

mysql> SHOW VARIABLES LIKE 'sql_mode';
+-----------------------+------------------------------------------+
| Variable_name | Value |
+-----------------------+------------------------------------------+
| sql_mode | NO_ENGINE_SUBSTITUTION |
+-----------------------+------------------------------------------+
1 row in set (0.00 sec)

I think I'm brave enough to upgrade after getting some answers from you helpful people. Server maintenance has been scheduled with my clients for Sunday, after the next set of weekly backups.
 

marjwyatt

Well-Known Member
Jun 23, 2014
48
5
58
cPanel Access Level
Reseller Owner
I completed the upgrade to MySQL 5.7 successfully. All accounts and websites seem to be working as they did prior to the upgrade.

The MySql variables have changed radically with the upgrade. Is this how they are supposed to be now?
mysql> SHOW VARIABLES LIKE 'sql_mode';
+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 

marjwyatt

Well-Known Member
Jun 23, 2014
48
5
58
cPanel Access Level
Reseller Owner
Just as a note for @keat63

This is the notification that popped up before I proceeded with the MySQL upgrade this morning.

"MySQL enables "strict mode" by default as of version 5.7. Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. Applications not built with strict mode enabled may cause undesired behavior; please verify applications using MySQL are compatible before upgrading. More information about strict mode is available here:
MySQL :: MySQL 5.7 Reference Manual :: 5.1.10 Server SQL Modes"

In other words, there is no reason to disable it before running the upgrade procedure but it does inform that strict mode will be enabled by default as part of the upgrade.