Disable Strict mode on MariaDB database

  • | 884 points

    I have an old app written for MySQL. After using the app with Laragon and MariaDB I got errors saying "Field x has no default value" when I tried to insert something into the database.

    To fix this, and use the old MySQL behaviour I had to set SET @@global.sql_mode= '' in phpMyAdmin.
    Adding sql-mode= '' to the my.ini did not help.

  • administrators
    | 123538 points

  • | 884 points

    @leokhoa That also did not help.
    I had to make the following query in phpMyAdmin:
    After that it worked.

  • administrators
    | 123538 points

    @jmartsch : Glad to know it worked with your solution. Cheer!

  • | 884 points

    @leokhoa Today is a new day, and what do I have to see?
    The MySQL (MariaDB) settings have been reset to
    it seems, that Laragon (or whatever) does not remember the setting I applied in phpMyAdmin and resets the sql_mode every time I start the MySQL service.

    You can see what SQL mode is set if you enter
    in phpMyAdmin.

    Please help. It is very annoying that I have to set the SQL Mode every day for new.

  • administrators
    | 123538 points

    @jmartsch : Oh, what is your Laragon's version?
    I'll check and get back to you soon.

  • | 884 points

  • administrators
    | 123538 points

    @jmartsch : You must set it in my.ini (Menu > MySQL > my.ini)
    Please check if your my.ini looks like this:

    #innodb_lock_wait_timeout = 50
    #show_compatibility_56 = on
    datadir= "C:/laragon/data"

    Here is my result:

    MariaDB [(none)]> SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;
    1 row in set (0.00 sec)


    1. Remember to delete or comment out the line sql_mode = "STRICT_...."
    2. Restart MySQL after changing

  • | 884 points

    Hello @leokhoa, thanks for your support.
    I do a facepalm to myself, because the sql_mode was overwritten waaaay down in the my.ini file so my setting that was set almost at the top was overwritten.
    Sorry for bothering you. Now everything works fine.

  • administrators
    | 123538 points

    @jmartsch : You're welcome. I'm glad to help. Many times we learn a lot about issues like this :relaxed:

  • | 61 points

    @leokhoa thanks!

    It helped a lot.

    I had this bug on MariaDB 10.5:

    Erro ao cadastrar: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: '' for column

    Now its works perfectly!

Log in to reply

Looks like your connection to Laragon was lost, please wait while we try to reconnect.