How to disable MySQL strict mode in WHM/cPanel

AlmightyGreg

Member
Registered
Joined
Mar 22, 2015
Messages
16
Points
3
How can I disable MySQL strict mode in WHM/cPanel or do I have to do so via ssh? I want to disables this to make a couple of applications work properly. How can I do that?
 

EcommIndiaCloudIT

Member
Registered
Joined
Dec 28, 2016
Messages
64
Points
0

AlmightyGreg

Member
Registered
Joined
Mar 22, 2015
Messages
16
Points
3
AlmightyGreg
I visited this url through Google search but there are many answers there, not sure which is the best solution.
After disabled MySQL strict mode, I also want to check its status is ON or OFF on my hosting? how can your link answer that?
 

LJSHost

Well-known member
Hosting Provider
Registered
Joined
Jul 5, 2016
Messages
1,031
Points
63
I'm sorry but please make an effort and do a google search before asking these kind of questions. Please don't post everything that comes to your mind.
Thank you for your comments but we like to help members here instead of referring them to the documentation. Members are free to post any question they wish.

I will let this post slide. OP you can find what you need in the link provided.
 

24x7CSM

Well-known member
Registered
Joined
Sep 27, 2016
Messages
263
Points
28
Before you disable mysql strict mode , make sure you have working backups of mysql DBs and mysql configurations.


To disable mysql strict mode in mysql on a cPanel server, first ssh in to the server

Code:
cp -rp /etc/my.cnf /etc/my.cnf.backup
cp -rp /usr/my.cnf /usr/my.cnf.backup
Then edit the main mysql configuration file like so:

Code:
vim /etc/my.cnf
Then there may or may not be content in this file already, if there is look for the section that starts with:

Code:
[mysqld]
If that line is not there at all, just add it near the top of the file. Then if there is a line that starts with sql_mode”, edit it so it matches this line:

Code:
sql_mode=”NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”
then save the file by pressing the Escape key then typing:

Code:
:wq
Then press the Enter to Save the file. If you have mysql 5.6 make sure to make a similar change in the /usr/my.cnf file as well:

Code:
vim /usr/my.cnf
by default it usually shows:

Code:
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
So change that to:

Code:
sql_mode=”NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”
Save the file again by pressing the escape key and typing :wq” then Enter again. Now restart mysql using:

Code:
service mysql restart
Now use the command below to check if everything was done good

Code:
 mysql -e 'select @@GLOBAL.sql_mode;'
if the output is NO_AUTO_CREATE_USER and other parameters with NO_**_**

You are done.
 

EcommIndiaCloudIT

Member
Registered
Joined
Dec 28, 2016
Messages
64
Points
0
Thank you for your comments but we like to help members here instead of referring them to the documentation.
Oh I'm all for helping and you would know that if you look at the history of my posts. What I clearly meant was for questions that have answers which are couple of lines long and thoroughly well documented already for several years now, its easier to find answers by a simple search. If OP had the questions as "I tried to disable it this way and it didn't work", then that's a different story and obviously anyone is free to post any question they have but in the long run it reduces the quality of the forum. My apologies if you or the OP felt offended in any way.

I also want to check its status is ON or OFF on my hosting? how can your link answer that?
Your question only asked how to do it, not how to check post doing it.

There isn't a mechanism directly built into cPanel to do this check for you. Most of the configuration of any SQL setting, there is nothing that will tell you if its enabled or not unless you look at the config file or have a <?php info()?> file to pull the output or directly run the SQL command in SSH console.

If you have CSF firewall installed on your cPanel, you can run a security check and it will tell you if MySQL strict mode is enabled or disabled as its one of the parameters it checks.
 
Older Threads
Replies
2
Views
3,286
Replies
20
Views
6,732
Replies
25
Views
10,577
Newer Threads
Recommended Threads
Replies
3
Views
2,362
Replies
8
Views
4,974
Replies
35
Views
9,402

Latest Hosting OffersNew Reviews

Sponsors

Tag Cloud

You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.

Top