> Op 10 april 2017 om 18:20 schreef Rafael Weingärtner > <rafaelweingart...@gmail.com>: > > > 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.
I am not so familiar with our ORM, but SQL Mode is something which the client can set as a connection parameter as well. Somewhere in the code we should be able to set this. Does anybody know where the actually initiate the SQL connection? Wido > > > 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