cloudstack-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rafael Weingärtner <rafaelweingart...@gmail.com>
Subject Re: MySQL 5.7 and SQL Mode
Date Mon, 10 Apr 2017 16:20:56 GMT
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 <mail@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

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message