How to Disable Strict Sql_Mode on a Linux Server

This blog explains how to disable strict sql_mode on a Linux server via command line.

1.The temporary method by command line:

In order to disable or enable the strict mode, firstly need to login to MySQL server using the below command:

mysql -u root -p or mysql

Please note that “STRICT_TRANS_TABLES “ is responsible for setting MySQL strict mode. We need to check whether the strict mode is enabled or not by using the mysql command.

SHOW VARIABLES LIKE 'sql_mode';

While checking the screen shot above we could see that STRICT_TRANS_TABLES variable is present.

If the STRICT_TRANS_TABLES is present then we realized that the sql strict mode is enabled.

To disable the mode run the below command.

set sql_mode='';

Check again whether the mode is disabled or not by the below command.

SHOW VARIABLES LIKE 'sql_mode';

Permanent method via my.cnf config file.

We can disable sql strict mode by editing the mysql configuration file. This method referred as a permanent method to disable the strict mode.

The mysql configuration file location is /etc/my.cnf.

Before making any changes to the mysql configuration file we should take backup of my.cnf file as a security. Please find the below command to make the backup of my.cnf file.

cp -pr /etc/my.cnf /etc/my.cnf.org

In order to disable the strict mode first we need to find out the below entry in my.cnf config file under [mysqld] section.

sql_mode = “STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

Replace the line with the below entry. If the line is not present, create it under [mysqld] section.

sql_mode= “”

Then restart the mysql server using the below command.

service mysqld restart

3.Third method to disable strict sql_mode on a linux server

Environment:

MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}

MYSQL_DATABASE: ${MYSQL_DATABASE}

MYSQL_USER: ${MYSQL_USER}

MYSQL_PASSWORD: ${MYSQL_PASSWORD}

command: mysqld --sql_mode=""

restart: always

If you are running mysql on docker add command: mysqld - sql_mode=”” on the docker compose file

These are 3 methods for turning off strict sql_mode on a linux server.

Do you have any queries? Contact IServerSupport team, a qualified and dedicated server management company ,for any kind of server support for your business.