Disable strict mode on MariaDB

Question

When i run this sql in phpmyadmin

SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE; 

it shows

@@SQL_MODE STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 

@@GLOBAL.SQL_MODE STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 

How to Disable strict mode on MariaDB using phpmydmin?

Chandelle

MySQL and MariaDB have been diverging since 2010, and now they should be considered different software products (sort of like Microsoft SQL server vs. Sybase). You should tell us which one you use, and tag your question appropriately. The answer to your question will be different depending on which one you are using. – Selectivity

@BillKarwin iam using now latest version of MariaDB – Chandelle

You should read mariadb.com/kb/en/library/set including: "Changing a system variable by using the SET statement does not make the change permanently. To do so, the change must be made in a configuration file." You can't do this from the phpmyadmin user interface. You have to edit the configuration file on the MariaDB server. – Selectivity

I have removed the mysql tag from your question. – Selectivity

Answer 1

Edit via SSH

/etc/my.cnf file

Add

sql_mode=NO_ENGINE_SUBSTITUTION

restart MariaDB

and it will fix the issue

*edit - if You have error while restarting msyql service try to add "[mysqld]" above in my.cnf

Chandelle

Answer 2

This worked for me:

root@MaRs:/etc/mysql# cat my.cnf|grep -v \#

[client-server]

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION
root@MaRs:/etc/mysql# 

MariaDB [(none)]> SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE; 
+------------------------+------------------------+
| @@SQL_MODE             | @@GLOBAL.SQL_MODE      |
+------------------------+------------------------+
| NO_ENGINE_SUBSTITUTION | NO_ENGINE_SUBSTITUTION |
+------------------------+------------------------+
1 row in set (0.000 sec)

Yusem

Answer 3

you can use the following statement to disable stict mode for this session:

set session sql_mode= replace((replace((select @@sql_mode), "STRICT_ALL_TABLES", "")), "STRICT_TRANS_TABLES", "");

Feme