Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

MySQL 5.5 to 5.7

Discussion in 'Database Discussion' started by fullfatdesigns, Mar 1, 2019.

  1. fullfatdesigns

    fullfatdesigns Well-Known Member

    Joined:
    Aug 1, 2014
    Messages:
    72
    Likes Received:
    11
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    Hi

    I've received the message about needing to update mySQL 5.5

    I have 20+ websites on our server with databases. Is updating mySQL like updating php where each account needs to be fully checked after updating, or should sites that worked in 5.5 still work in 5.7? Or are there certain things I should look out for?

    Regards
    Wayne
     
  2. GOT

    GOT Get Proactive! PartnerNOC

    Joined:
    Apr 8, 2003
    Messages:
    1,478
    Likes Received:
    185
    Trophy Points:
    193
    Location:
    Chesapeake, VA
    cPanel Access Level:
    DataCenter Provider
    By and large this is pretty seamless. It's not as iffy as updating php.

    However 5.7 will enable strict mode by default and some sites may have issues with that. Mostly related to custom coded stuff though. Wordpress for example would not have any issues.

    You can disable strict mode easily by editing the my.cnf.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. fullfatdesigns

    fullfatdesigns Well-Known Member

    Joined:
    Aug 1, 2014
    Messages:
    72
    Likes Received:
    11
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    Great, thank you.

    I'll give it a try over the weekend. Don't fancy doing this at 4 on a Friday ;)
     
    cPanelMichael likes this.
  4. cPanelMichael

    cPanelMichael Technical Support Community Manager Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    47,331
    Likes Received:
    2,161
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello @fullfatdesigns,

    Let us know if you encounter any issues.

    Thanks!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  5. fullfatdesigns

    fullfatdesigns Well-Known Member

    Joined:
    Aug 1, 2014
    Messages:
    72
    Likes Received:
    11
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    After doing quite a bit of checking of our sites, I can't be 100% sure the coding will be compatible as some is a few years old. I've searched around about disabling strict mode all with different ways to achieve it. Is there a preferred whm/cpanel method? Can this be done in whm? Or should I be using SSH?
     
  6. keat63

    keat63 Well-Known Member

    Joined:
    Nov 20, 2014
    Messages:
    1,266
    Likes Received:
    86
    Trophy Points:
    28
    cPanel Access Level:
    Root Administrator
    Having just gone through updating from 5.6 to 5.7, I asked the same question and had the same concerns.
    My update only took about 10 minutes at the most.

    As for strict mode disable strict mode

    I was also concerned about the implications of the update failing and being stuck between versions.

    Backup all MySQL Databases?
     
  7. fullfatdesigns

    fullfatdesigns Well-Known Member

    Joined:
    Aug 1, 2014
    Messages:
    72
    Likes Received:
    11
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    Thanks keat63

    Thats good info and glad to know I'm not the only one worrying about updating services.

    Regarding the my.cnf file. How do you connect to the file in /etc for the whole server? I can access the root of each cPanel account in ftp, but how to I access the one at the root of the server?
     
  8. keat63

    keat63 Well-Known Member

    Joined:
    Nov 20, 2014
    Messages:
    1,266
    Likes Received:
    86
    Trophy Points:
    28
    cPanel Access Level:
    Root Administrator
    I ftp'd in to root, using the root login account, I copied the file off, edit it and ftp'd it back up.
    As root admin, I assume you can FTP as the root user ?

    When I ftp as the root user, my ftp client dumps me in the root folder.
    Jump back one level where you can gain access to the etc folder.

    CSF also offer a free explorer plugin, which will allow you to edit and copy files via WHM.
    Its a bit clumsy, but can be useful at times.
     
    #8 keat63, Mar 4, 2019
    Last edited: Mar 4, 2019
  9. fullfatdesigns

    fullfatdesigns Well-Known Member

    Joined:
    Aug 1, 2014
    Messages:
    72
    Likes Received:
    11
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    I'm viewing using the CSF file view, but I can't see my.cnf in /etc - Should it be in there? I wonder if I should do the SSH route to do this. I've looked on the main cPanel site, but I can't find any info on the steps to take...
     
  10. Infopro

    Infopro cPanel Sr. Product Evangelist Staff Member

    Joined:
    May 20, 2003
    Messages:
    16,907
    Likes Received:
    484
    Trophy Points:
    583
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    That's a bad idea. If you're not comfortable with command line you might try CSE from ConfigServer.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  11. keat63

    keat63 Well-Known Member

    Joined:
    Nov 20, 2014
    Messages:
    1,266
    Likes Received:
    86
    Trophy Points:
    28
    cPanel Access Level:
    Root Administrator
    @Infopro Can you explain why it's a bad idea please.
     
  12. keat63

    keat63 Well-Known Member

    Joined:
    Nov 20, 2014
    Messages:
    1,266
    Likes Received:
    86
    Trophy Points:
    28
    cPanel Access Level:
    Root Administrator
    Are you looking at the user account /etc folder or the root account /etc folder.
     
  13. Infopro

    Infopro cPanel Sr. Product Evangelist Staff Member

    Joined:
    May 20, 2003
    Messages:
    16,907
    Likes Received:
    484
    Trophy Points:
    583
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  14. fullfatdesigns

    fullfatdesigns Well-Known Member

    Joined:
    Aug 1, 2014
    Messages:
    72
    Likes Received:
    11
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    I was looking at the root folder in whm. I've googled about editing via SSH, but I'm finding lots of different ways and I can't find any info on cPanel site (unless I'm looking in the wrong place?)

    I've used SSH before, but find I need to follow step-by-step instructions to help
     
  15. fullfatdesigns

    fullfatdesigns Well-Known Member

    Joined:
    Aug 1, 2014
    Messages:
    72
    Likes Received:
    11
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    My mistake, found the file. I didn't see it in the top list (which was directories) and it was in the list below.
     
  16. fullfatdesigns

    fullfatdesigns Well-Known Member

    Joined:
    Aug 1, 2014
    Messages:
    72
    Likes Received:
    11
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    OK, I've updated. Thanks everyone for helping.

    Just to confirm, I've checked with the command;

    Code:
    mysql -e "SELECT @@sql_mode;"
    and got this response;

    Code:
    @@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
    Would that suggest disabling Strict mode was successful?
     
  17. cPanelMichael

    cPanelMichael Technical Support Community Manager Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    47,331
    Likes Received:
    2,161
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello @fullfatdesigns,

    Here's the entry you should add to disable Strict Mode under the [mysqld] section of the /etc/my.cnf file:

    Code:
    sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Remember to restart MySQL after making the change with the following command:

    Code:
    /scripts/restartsrv_mysql
    The below command should not return any output once strict mode is disabled:

    Code:
    mysql -i -BN -e 'SELECT @@sql_mode' | grep -E 'ONLY_FULL_GROUP_BY|STRICT_TRANS_TABLES'
    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  18. fullfatdesigns

    fullfatdesigns Well-Known Member

    Joined:
    Aug 1, 2014
    Messages:
    72
    Likes Received:
    11
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    Hi cPanelMichael

    Sorry, I should have explained all the steps I made.

    I added to my my.cnf file;

    Code:
    sql_mode=NO_ENGINE_SUBSTITUTION
    Then did

    Code:
    mysql -e "SELECT @@sql_mode;"
    To check if it had worked.

    As I've done the top bit, do I also need to do the steps you mentioned in your last post?
     
  19. fullfatdesigns

    fullfatdesigns Well-Known Member

    Joined:
    Aug 1, 2014
    Messages:
    72
    Likes Received:
    11
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    I've now re-booted mySQL and re-checked and get this response;

    Code:
    @@sql_mode             |
    +------------------------+
    | NO_ENGINE_SUBSTITUTION
    Does that mean its worked?
     
  20. cPanelMichael

    cPanelMichael Technical Support Community Manager Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    47,331
    Likes Received:
    2,161
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello @fullfatdesigns,

    You could change the existing sql_mode mode entry in your /etc/my.cnf with the one I noted in my last response:

    Code:
    sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Or, you could leave the original entry as well. Both entries will disable strict mode, it's just a preference of what other modes you want to enable/disable. The following MySQL document is worth reading if you want to know what each mode means:

    MySQL :: MySQL 5.7 Reference Manual :: 5.1.10 Server SQL Modes

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice