Questions about MySQL 5.6 EOL update

[marjwyatt]

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]

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.

disable strict mode

I think it's about time that I took the plunge and updated MYSQL to 5.7. However, I understand that strict mode may need to be disabled. I've done some searching but there are so many different methods, comments or threads, i'm losing the plot. For fear of making a massive blunder, could anyone...

forums.cpanel.net

 

[marjwyatt]

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.

disable strict mode

I think it's about time that I took the plunge and updated MYSQL to 5.7. However, I understand that strict mode may need to be disabled. I've done some searching but there are so many different methods, comments or threads, i'm losing the plot. For fear of making a massive blunder, could anyone...

forums.cpanel.net

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]

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.

Backup all MySQL Databases?

I'm about to update MYSQL at the weekend. Is there a way I can perform a backup of just the databases. I can't do this as an export through PHP MyAdmin, as I suffer timeouts or memory space issues.

forums.cpanel.net

 

[cPanelLauren]

It will check to ensure that you can move from one version to another without issue and it won't proceed if it cannot.

 

[marjwyatt]

It will check to ensure that you can move from one version to another without issue and it won't proceed if it cannot.

Thanks for looking into it, @cPanelLauren. I'll wait for your response before taking any action.

 

[ffeingol]

A couple of suggestions before up upgrade. First check and repair all the databases on the server;

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

 

[keat63]

Lauren said "It will check" not "I will check", so you might not get a reply on that actual comment.

 

[marjwyatt]

Lauren said "It will check" not "I will check", so you might not get a reply on that actual comment.

Thanks. I guess I misread that.

 

[marjwyatt]

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]

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]

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.

 

[ffeingol]

Here is an explanation of what "strict" does from the MySQL 5.6 documentation: MySQL :: MySQL 5.6 Reference Manual :: 5.1.10 Server SQL Modes

 

[marjwyatt]

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]

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]

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.

 

[keat63]

Glad to see the update went well.