CODEWITHSUNDEEP
mysqlcpanelwhmcsstrict-mode
Hamed Atae
Hamed Atae

Reputation: 213

How to find and disable MySQL strict mode?

Hello everybody I wanted to find this mysql strict mode for whmcs and disable it but i didn't had a progress I'm a newbie so sry if I didn't mention other things Can anyone help me? I'm working on WHM/Cpanel

Upvotes: 19

Views: 77141

Answers (6)

Vu Van Dung
Vu Van Dung

Reputation: 21

I think it is impossible to change *.cnf file in recent version Instead of, you can change mysql.service file

/lib/systemd/system/mysql.service
ExecStart=/usr/sbin/mysqld --sql-mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

Upvotes: 2

Faisal
Faisal

Reputation: 4765

First, verify which mode(s) MYSQL is running with the below command:

 mysql -u root -p -e "SHOW VARIABLES LIKE 'sql_mode';"

You'll need to replace root with whatever username has superuser permissions on your server (but, it's usually just root). You'll also be prompted for the password.

To Disable Strict Mode via SQL command line:

You can disable strict mode on your MySQL server by running the following command on your command line:

mysql -u root -p -e "SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';"

Upvotes: 1

Shane Dolim
Shane Dolim

Reputation: 131

I currently run a Centos 6.9 server with MariaDB and a multi-cpanel configuration, so for clients, they were unable to disable strict on their own without getting a lack of permission error. This is how you disable it globally:

Open SSH/Console and verify the current mode:

# mysql -e "SELECT @@sql_mode;"

+----------------------------------------------------------------------+
| @@sql_mode                                                           |
+----------------------------------------------------------------------+
|STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, |
|NO_ENGINE_SUBSTITUTION                                                |
+----------------------------------------------------------------------+

Locate and edit the "my.cnf" file

vim /etc/my.cnf

Find and change the following or add the line if it's not present

Strict Mode Enabled:

sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Strict Mode Disabled:

sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Restart the MySQL Service

/etc/init.d/mysql restart

Verify strict mode has been disabled:

# mysql -e "SELECT @@sql_mode;"

+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+

Done :)

Upvotes: 13

Coz
Coz

Reputation: 1983

To turn off (or on) mysql strict access from cpanel.

1, search for 'phpmyadmin', in the search box, click on it

2, once phpmyadmin is loaded up, click on the 'variables' tab

3, search for 'sql mode'

then

to turn strict mode on enter

STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

to turn strict mode off, enter

NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

If you have control of the script you'll be using then it's a good idea to leave strict mode on, it'll alert you to any bugs in your code before they become an issue down the line.

Upvotes: 25

Shirish Bari
Shirish Bari

Reputation: 2722

Open my-default.ini file and remove the STRICT_TRANS_TABLES parameter from sql_mode value.

[Ex: In localhost xampp the file is at xampp/mysql/my-default.ini]

default value of sql_mode is as below 

sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

change it to 

 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

This will disable the strict mode in MYSQL

Upvotes: 1

Kailash Aghera
Kailash Aghera

Reputation: 549

First of all, to disable MySQL strict mode, you need server's root access. If you don't have it, you can't do it. If you have just cPanel access, that means, you are not the administrator of this server or you don't have root access.

To edit your mysql configuration file my.cnf, you will have to access your server via SSH (with root user or sudo access) and then edit /etc/my.cnf file and add following line if it is not present:

[mysqld]

sql_mode="TRADITIONAL,NO_AUTO_CREATE_USER"

After editing this file, save it and restart mysql service. Again, you need full root access for your server to change this.

Upvotes: 5

Related Questions