I noticed this problem on CentOS 7. It seems that by default the mysql 5.6, comes with sql_mode = "" (at least in my Ubuntu installation); while on 5.7, it comes with quite some restrictions. I set it to the default of 5.6, it would be nice though to see if we can improve our SQLs to comply with those restrictions, or at least to remove only a few of them.
On Mon, Apr 10, 2017 at 11:04 AM, Rene Moser <m...@renemoser.net> wrote: > Hi Wido > > On 04/10/2017 05:00 PM, Wido den Hollander wrote: > > Hi, > > > > While testing with Ubuntu 16.04 and CloudStack 4.10 (from master) I've > ran into this error on the management server: > > > > com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression > #1 of SELECT list is not in GROUP BY clause and contains nonaggregated > column 'cloud.i.id' which is not functionally dependent on columns in > GROUP BY clause; this is incompatible with sql_mode=only_full_group_by > > at sun.reflect.GeneratedConstructorAccessor50.newInstance(Unknown > Source) > > at sun.reflect.DelegatingConstructorAccessorImpl.newInstance( > DelegatingConstructorAccessorImpl.java:45) > > at java.lang.reflect.Constructor.newInstance(Constructor.java:423) > > at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) > > at com.mysql.jdbc.Util.getInstance(Util.java:387) > > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939) > > at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) > > at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) > > > > I was able to fix this to add this to my my.cnf: > > > > [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" > > > > Should we maybe set the SQL Mode as a connection parameter when > connecting to the DB? This prevents users from having to set this manually > in their MySQL configuration. > > > > Did somebody else run into this with MySQL 5.7? > > Yes, I also run into this while testing and switched back to mysql 5.6. > > Regards > René > -- Rafael Weingärtner
