How to Disable MySQL Strict Mode in MariaDB and MySQL

[Sandeep B.]

learn how you can easily disable mysql strict mode in MySQL/MariaDB Server on any Linux os such as Centos/Ubuntu/RHEL/AlmaLinux and in Linux Server Control panel like CWP, HestiaCP etc.

Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. 

To Disable MySQL strict mode simply add below config in my.cnf usually in /etc/my.cnf ensure you added it under [mysqld] section see example below in this tutorial :

sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

How to find the my.cnf ? mostly my.cnf is located in this locations :

Quote

/etc/my.cnf 
/etc/mysql/my.cnf 

if there is no my.cnf files then create one in this location /etc/my.cnf with below config :

#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]

[mysqld]
sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

#
# include *.cnf from the config directory
#
!includedir /etc/my.cnf.d

**please note you need to change the permission if you’ve newly created the my.cnf to user:group i.e. : mysql:mysql