11

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?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Dr.Mezo
  • 807
  • 3
  • 10
  • 25
  • 1
    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. – Bill Karwin Aug 06 '19 at 17:53
  • @BillKarwin iam using now latest version of MariaDB – Dr.Mezo Aug 06 '19 at 18:20
  • 1
    You should read https://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. – Bill Karwin Aug 06 '19 at 18:36
  • 1
    I have removed the mysql tag from your question. – Bill Karwin Aug 06 '19 at 18:36

3 Answers3

19

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

Jakub Ujvvary
  • 421
  • 4
  • 13
Dr.Mezo
  • 807
  • 3
  • 10
  • 25
5

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)
Miguel Tavares
  • 51
  • 1
  • 3
0

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", "");
Jan
  • 11
  • 3