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
    | 53090 points


  • | 884 points

    @leokhoa That also did not help.
    I had to make the following query in phpMyAdmin:
    SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';
    After that it worked.


  • administrators
    | 53090 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
    STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_...
    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
    SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;
    in phpMyAdmin.

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


  • administrators
    | 53090 points

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


  • | 884 points


  • administrators
    | 53090 points

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

    [mysqld]
    ...
    ...
    #innodb_lock_wait_timeout = 50
    #sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
    sql-mode = "NO_ENGINE_SUBSTITUTION"
    #show_compatibility_56 = on
    datadir= "C:/laragon/data"
    

    Here is my result:

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


    Note:

    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
    | 53090 points

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


Log in to reply
 

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