Showing posts with label Mysql sql_mode. Show all posts
Showing posts with label Mysql sql_mode. Show all posts

Tuesday, 6 November 2018

Mysql: Disable ONLY_FULL_GROUP_BY

I accidentally enabled ONLY_FULL_GROUP_BY mode like this:
SET sql_mode = 'ONLY_FULL_GROUP_BY';
How do I disable it?

 Answers


Solution 1: Remove ONLY_FULL_GROUP_BY from mysql console
mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
you can read more here
Solution 2: Remove ONLY_FULL_GROUP_BY from phpmyadmin
  • Open phpmyadmin & select localhost
  • Click on menu Variables & scroll down for sql mode
  • Click on edit button to change the values & remove ONLY_FULL_GROUP_BY & click on save.


Be careful using
SET sql_mode = ''
This actually clears all the modes currently enabled. If you don't want to mess with other settings, you'll want to do a
SELECT @@sql_mode
first, to get a comma-separated list of the modes enabled, then SET it to this list without the ONLY_FULL_GROUP_BY option.


    mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
    mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
    mysql> exit;


Thanks to @cwhisperer. I had the same issue with Doctrine in a Symfony app. I just added the option to my config.yml:
doctrine:
    dbal:
        driver:   pdo_mysql
        options:
            # PDO::MYSQL_ATTR_INIT_COMMAND
            1002: "SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))"
This worked fine for me.


On:
  • Ubuntu 14.04
  • mysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper
Do:
$ sudo nano /etc/mysql/conf.d/mysql.cnf
Copy and paste:
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
To the bottom of the file
$ sudo service mysql restart


I have noticed that @Eyo Okon Eyo solution works as long as MySQL server is not restarted, then defaults settings are restored. Here is a permanent solution that worked for me:
To remove particular SQL mode (in this case ONLY_FULL_GROUP_BY), find the current SQL mode:
SELECT @@GLOBAL.sql_mode;
copy the result and remove from it what you don't need (ONLY_FULL_GROUP_BY)
e.g.:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
to
STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
create and open this file:
/etc/mysql/conf.d/disable_strict_mode.cnf
and write and past into it your new SQL mode:
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
restart MySQL:
sudo service mysql restart
Or you can use ANY_VALUE() to suppress ONLY_FULL_GROUP_BY value rejection, you can read more about it here


I'm using doctrine and I have added the driverOptions in my doctrine.local.php :
return array(
'doctrine' => array(
    'connection' => array(
        'orm_default' => array(
            'driverClass' => 'Doctrine\DBAL\Driver\PDOMySql\Driver',
            'params' => array(
                'host' => 'localhost',
                'port' => '3306',
                'user' => 'myusr',
                'password' => 'mypwd',
                'dbname' => 'mydb',
                'driverOptions' => array(
                    PDO::MYSQL_ATTR_INIT_COMMAND => "SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))"
                ),
            ),
        ),
    ),
));
In phpmyadmin the user needs SUPER activated in the privileges.


You can disable it using the config file my.cnf :
$ mysql --verbose --help | grep my.cnf
So in macOS 10.12, it's at usr/local/etc/my.cnf. You can edit sql_mode here:
# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"


This is a permanent solution for MySql 5.7+ on Ubuntu 14+:
$ sudo bash -c "echo -e \"\nsql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION\"  >> /etc/mysql/mysql.conf.d/mysqld.cnf"
$ sudo service mysql restart
# Check if login attempt throws any errors
$ mysql -u[user] -p # replace [user] with your own user name
If you are able to login without errors - you should be all set now.

Adding or removing individual SQL modes in MySQL's sql_mode variable

Oracle recently published the MySQL 5.7.5 Development Milestone release, a pre-production release providing numerous improvements to the MySQL server. You can download the release here: http://dev.mysql.com/downloads/mysql/5.7.html
This release carries some incompatible changes, as explained in the release notes . During my work in the Server QA team I have experienced some of these changes first hand already, and we have had to modify some tests and tools to adapt to some of it.
One very big change (well, some may not notice at all, while others may need to adjust their tools and applications) is the new default value of sql_mode:
Old default (5.7.4):
mysql> SELECT @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)
New default (5.7.5):
mysql> SELECT @@sql_mode;
+---------------------------------------------------------------+
| @@sql_mode                                                    |
+---------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
As you can see, the big news here is the addition of the following sql_modes:
ONLY_FULL_GROUP_BY
Non-deterministic grouping queries will be rejected.
STRICT_TRANS_TABLES
Invalid and missing-value data exchanging statements will result in an error instead of a warning.
Above is my own very brief summary of these SQL modes. I recommend reading the 5.7 docs on the subject and the release notes for official detailed information.
It is also worth mentioning that the ONLY_FULL_GROUP_BY mode is greatly improved in 5.7.5. This is a welcome change by many, including myself :)
Now, down to business...
We have found that one old way of setting SQL mode is no longer considered "safe" for us. We want to test the default values of the server as much as possible, because that is likely what most customers will use. However, in some cases we need to set certain sql_modes in order to get the desired behavior - either for testing that specific behavior, or because this behavior helps making testing other things easier.
The sql_mode variable for a session can be set like this, from a MySQL client:
SET sql_mode=<value>

for example:
SET sql_mode='NO_ENGINE_SUBSTITUTION'
to ensure that the server will not revert to a different storage engine for a table "behind our back".
This works fine as long as you are sure that you do not want any other sql_modes, or as long as the old value of sql_mode is empty. However, now that the default contains no less than three SQL modes, some more care needs to be taken to make sure we are not disabling an sql_mode that we actually want to keep.
The sql_mode variable does not work the same way as e.g. optimizer_switch, where you can set individual flags and the others will remain the same. With sql_mode, what you set is exactly what you get.
It would be nice to be able to easily add or remove a single mode to/from the sql_mode variable, wouldn't it? Here are a couple of hopefully helpful tricks for setting and removing individual modes from the sql_mode variable, without risking the removal of modes that are not mentioned in your statement:
Adding a mode to sql_mode:
SET sql_mode=(SELECT CONCAT(@@sql_mode,',<mode_to_add>'));
Removing a mode from sql_mode:
SET sql_mode=(SELECT REPLACE(@@sql_mode,'<mode_to_remove>',''));


Example: Add ONLY_FULL_GROUP_BY to sql_mode:
mysql> SELECT @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

mysql> SET sql_mode=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY'));
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@sql_mode;
+-------------------------------------------+
| @@sql_mode                                |
+-------------------------------------------+
| ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------+
1 row in set (0.00 sec)

-- It works even if sql_mode is empty:

mysql> SET sql_mode='';
Query OK, 0 rows affected (0.32 sec)

mysql> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.06 sec)

mysql> SET sql_mode=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY'));
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT @@sql_mode;
+--------------------+
| @@sql_mode         |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+
1 row in set (0.00 sec)
NOTE: If the mode is already set in the sql_mode variable, no change is made, and no error is given, which is quite neat.

Example: Remove ONLY_FULL_GROUP_BY from sql_mode:
mysql> SELECT @@sql_mode;
+---------------------------------------------------------------+
| @@sql_mode                                                    |
+---------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)
If the specified mode is not actually set in sql_mode, no change is made and no error is given.

Subscribe to: Posts (Atom)