How to disable SQL Mode: Only_Full_Group_By

  • I can disable Only_Full_group_by in phpmyadmin but once the server restarted it appeared again and need to be manually remove it.
    how can i make it permanently instead every-time keep removing it


    current using the latest stable version 1.5.3 upgraded since ver 1.4.3 keep updating (no miss) =)
    using ubuntu 16.04
    :|:|:|

  • I have use this method but imscp got got error unable to login




    Update:



    To keep your current mysql settings and disable ONLY_FULL_GROUP_BY I suggest to visit your phpmyadmin or whatever client you are using and type:
    SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','') copy_me
    next copy result to your my.ini file.
    mint: sudo nano /etc/mysql/my.cnf
    ubuntu 16 and up: sudo nano /etc/mysql/my.cnf
    ubuntu 14-16: /etc/mysql/mysql.conf.d/mysqld.cnf
    Caution! copy_me result can contain a long text which might be trimmed by default. Make sure you copy whole text!


    Edited once, last by longsyee ().

  • OK Thanks I found out the problem! that i dint use the [mysqld]


    must type in all in /etc/mysql/my.cnf


    Code
    1. [mysqld]
    2. sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"


    sudo service mysql restart

  • @longsyee


    You should really avoid changing the SQL_MODE... For instance, ONLY_FULL_GROUP_BY isn't compatible with i-MSCP because we rely on NON standard SQL. This will be solved in near future.


    See also: https://dev.mysql.com/doc/refm…en/group-by-handling.html

    badge.php?id=1239063037&bid=2518&key=1747635596&format=png&z=547451206

  • @longsyee


    You should really avoid changing the SQL_MODE... For instance, ONLY_FULL_GROUP_BY isn't compatible with i-MSCP because we rely on NON standard SQL. This will be solved in near future.


    See also: https://dev.mysql.com/doc/refm…en/group-by-handling.html

    Hello YES but this is the standard Setting after UPGRADE to I-MSCP 1.5.3 and makes also on my Software Problems!

    Pls check if I-MSCP code is working correct if everybody gets this Settings after Upgrade to 1.5.3.


    Thanks.

  • Hello YES but this is the standard Setting after UPGRADE to I-MSCP 1.5.3 and makes also on my Software Problems!

    Pls check if I-MSCP code is working correct if everybody gets this Settings after Upgrade to 1.5.3.


    Thanks.

    Yes, in fact, sql_mode is not longer set in config file.. It is set for i-MSCP only at runtime. If you need back to old behavior just do the following:


    Create the /etc/mysql/conf.d/sql_mode.cnf file with the following content:

    Code
    1. [mysqld]
    2. sql_mode=


    Then restart the service: service mysql restart


    Then, the problem should be solved:

    badge.php?id=1239063037&bid=2518&key=1747635596&format=png&z=547451206