Skip to main content

Questions about MySQL 5.6 EOL update

marjwyatt
I've read over the link recommended by cPanel about this EOL update: 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?

Comments

16 comments

Date Votes
  • 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.
    0
  • 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.
    0
  • 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.
    0
  • 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.
    0
  • 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.
    0
  • 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).
    0
  • keat63
    Lauren said "It will check" not "I will check", so you might not get a reply on that actual comment. :-)
    0
  • 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.
    0
  • 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?
    0
  • 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.
    0
  • 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.
    0
  • 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
    0
  • 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.
    0
  • 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)
    0
  • 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.
    0
  • keat63
    Glad to see the update went well.
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post