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.