mysql 5.7 [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonag

2019独角兽企业重金招聘Python工程师标准>>>

mysql 5.7 [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonag

up vote 111 down vote favorite 32

I accidentally enabled ONLY_FULL_GROUP_BY mode like this: SET sql_mode = 'ONLY_FULL_GROUP_BY'; How do I disable it? mysql mysql-workbench shareedit edited Mar 11 at 21:18 fedorqui 114k31176228 asked May 28 '14 at 20:21 ZviBar 1,01041326

7   Have you tried SET sql_mode = ''? – Tripp Kinetics May 28 '14 at 20:25 It works! You can post it as an answer. – ZviBar May 28 '14 at 20:26 1   Supportted by: mysqlfaqs.net/mysql-faqs/Client-Server-Commands/… – xQbert May 28 '14 at 20:26 Why would you want to disable a mode that makes MySQL better comply with SQL standards, and one, too, that teaches you to be more careful in writing your queries? – Andriy M May 29 '14 at 8:11 3   As of Mysql 5.7 you may, alternatively, use the ANY_VALUE(column) function to retrofit your query. See doc here – Qoheleth-Tech May 23 at 16:59 show 3 more comments

 

11 Answers

active oldest votes

 

up vote 16 down vote accepted

Give this a try: SET sql_mode = '' As per @xQbert in the comments above, check http://www.mysqlfaqs.net/mysql-faqs/Client-Server-Commands/What-is-sql-mode-in-MySQL-and-how-can-we-set-it Community Note: As pointed out in the answers below, this actually clears all the SQL modes currently enabled. That may not necessarily be what you want. shareedit edited Jun 20 at 21:41 Matt Browne 6,53712239 answered May 28 '14 at 20:27 Tripp Kinetics 2,6381023

1   Wow, '' = 'full group by is disabled'? MySQL does some pretty dumb things but that one is up there. – Aaron Bertrand May 28 '14 at 20:35 11   I think this basically disables any sql mode; – ZviBar May 28 '14 at 20:40 4   This didn't work for me on mysql 5.7.8 – ghanbari Feb 24 at 12:42 5   Before trying this answer, definitely look at Machavity's warning and if you want the changes to persist, make sure to use global in the command. – thomas88wp May 4 at 13:58 2   Every time when I come back to MySql after some time, I always meet this problem which annoys me, because I always forget what is the problem :) The feeling is just disappointing, when you think that it was working and now it doesn't because of version change. – karate Jun 9 at 13:33 show 7 more comments

 

up vote 3 down vote	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. shareedit answered Nov 7 at 14:16 Arvid 200210
	add a comment

 

up vote 2 down vote

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 shareedit answered Nov 3 at 13:53 Jadeye 7591430

Will the mysql.cnf never be overwritten by a mysql update? – cwhisperer Nov 8 at 12:00 1   @cwhisperer /etc/mysql/mysql.cnf points to 2 config folders !includedir /etc/mysql/conf.d/ + !includedir /etc/mysql/mysql.conf.d/. Same goes for /etc/mysql/my.cnf. Hence I assume that configurations files are not overridden upon update. You can read more here http://dev.mysql.com/doc/mysql/en/server-system-variables.ht‌​ml – Jadeye Nov 8 at 12:07 add a comment

 

up vote 154 down vote

The best way to go about it without emptying sql_mode out mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); it worked for me and you can read more at http://johnemb.blogspot.com.ng/2014/09/adding-or-removing-individual-sql-modes.html shareedit edited Oct 22 at 20:01 j0k 17.6k115061 answered Mar 16 at 11:11 Eyo Okon Eyo 1,719246

3   This solution work fine on mySQL 5.7.11 and should be the accepted one. The accepted answer doesn't work on new version of mySQL – Anyone_ph Apr 14 at 6:54 1   Shouldn't there be a comma as part of the seach? 'ONLY_FULL_GROUP_BY,','' – Jens Bodal May 11 at 19:57 2   Just ensured. It doesn't matter REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''), MySQL anyway removes unwanted commas from the record. OP's answer is correct. – nawfal May 21 at 3:47 2   Very good. Best solution. – Patrick Savalle May 28 at 7:40 1   This works, but when I restart the mysql server, defaults are restored... why? is there a persistent solution? Thanks! – Vincent Pazeller Nov 16 at 9:15 show 8 more comments

 

up vote 1 down vote

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. shareedit answered Oct 12 at 13:51 cwhisperer 4211922

Using the yaml notation for configuring Doctrine (as done in Symfony) you need to use "1002" instead of the constant "PDO::MYSQL_ATTR_INIT_COMMAND", but nevertheless this is what I have been looking for a few weeks ago and couldn't find out. Thanks for your solution! Worked for me. – Arvid Nov 7 at 14:20 add a comment

 

up vote 85 down vote

If you want to disable permanently error sql_mode=only_full_group_by do those steps: sudo nano /etc/mysql/my.cnf Add this to the end of the file [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" sudo service mysql restart to restart MySQL This will disable ONLY_FULL_GROUP_BY for ALL users shareedit edited Oct 10 at 5:40 LaurentG 325111 answered May 16 at 7:13 breq 6,343103352

2   this helped much more – MFAL May 19 at 8:15 1   for me, setting the mysqld tag on the same line didn't work, but is did work with a line break :) thanks – bloub Jun 21 at 14:55 Worked like butter on bread! Thanks. – Jonas Lomholdt Jul 7 at 9:59 5   For ubuntu, the file where custom config values go is /etc/mysql/mysql.conf.d/mysqld.cnf – knb Aug 23 at 12:02 add a comment

转载于:https://my.oschina.net/leblancs/blog/805664