Disable "sql-mode=only_full_group_by" option

Question

I installed mysql in my note and am using Linux Mint 18.2. When I run the query:

SELECT *,SUM(ValorTotalProdutos) AS ValorTotal, SUM(QtdProdutos) AS QtdFinal FROM tabela WHERE SESSIONID = '77c2c1de02e94581a459ed2d4fa76370' AND StatusCompras = 'A'

Error appears:

#1140 - In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'banco.tabela.IDCarrinho'; this is incompatible with sql_mode=only_full_group_by

I opened the file my.cnf

sudo nano /etc/mysql/my.cnf

And include at the end of the file:

sql-mode=""

I restarted mysql:

sudo service mysql restart

It was a mistake. Then I deleted the above command and included another line:

sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

I restarted and the same error appeared. The error that appeared in both is:

sudo service mysql restart
Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

How do I disable sql-mode?

Answer 1

The options to change SQL MODE are these:

Using this on the command line when starting the server:

--sql-mode="modes"

Or in the configuration file:

sql-mode="modes"

In _Untime:

SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';

It is important to make sure of all options, as you may have some of higher priority by overriding the one you manually changed (or have changed in one file, and the option being set in another).

For example, even if you specify the option on my.cnf or equivalent, if the script that initializes the server makes the command line option, this will prevail.

It is worth noting that the default this option was changed in Mysql version 5.7.5.

Still, remember to change only the mode that is affecting your use, keeping the other options the way they are to avoid side effects.

Now, if possible, review the logic of query, possibly be a more definitive and portable solution to the problem.

More details in the manual:

https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by

Answer 2

You can perform database settings at runtime in PHP. It is more practical, because if you need to switch to another server you will not need to redo the settings again.

If you are using PDO run this query right after instantiating the PDO object:

$Pdo->exec("SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));");

Take the example:

<?php
namespace lib;
class Dao
{
    private static $Dao;
    private static $Pdo;

    final private function __construct()
    {
        try {
            self::$Pdo = new \PDO("mysql:host=" . CONEXAO['host'] . ";dbname=" . CONEXAO['database'], CONEXAO['user'], CONEXAO['password']);
            self::$Pdo->exec("SET TIME_ZONE = '-03:00'");
            self::$Pdo->exec("set names " . APP_CHARSET);
            self::$Pdo->exec("SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));");
            self::$Pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
            self::$Pdo->query("SET group_concat_max_len = " . GROUP_CONCAT_MAX_LEN);
        } catch (\PDOException $ex) {
            throw new SystemExceptions($ex->getMessage());
        }
    }

    //...

}