fullfatdesigns

Well-Known Member
Aug 1, 2014
72
11
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
 

GOT

Get Proactive!
PartnerNOC
Apr 8, 2003
1,543
208
343
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.
 

fullfatdesigns

Well-Known Member
Aug 1, 2014
72
11
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?
 

fullfatdesigns

Well-Known Member
Aug 1, 2014
72
11
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?
 

keat63

Well-Known Member
Nov 20, 2014
1,387
108
93
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.
 
Last edited:

fullfatdesigns

Well-Known Member
Aug 1, 2014
72
11
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...
 

fullfatdesigns

Well-Known Member
Aug 1, 2014
72
11
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
 

fullfatdesigns

Well-Known Member
Aug 1, 2014
72
11
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?
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,911
2,234
363
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.
 

fullfatdesigns

Well-Known Member
Aug 1, 2014
72
11
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?
 

fullfatdesigns

Well-Known Member
Aug 1, 2014
72
11
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?
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,911
2,234
363
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.