cloudstack-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Wido den Hollander <w...@widodh.nl>
Subject Re: MySQL 5.7 and SQL Mode
Date Wed, 12 Apr 2017 08:55:41 GMT

> Op 12 april 2017 om 10:39 schreef Erik Weber <terbolous@gmail.com>:
> 
> 
> Instead of hacking mysql settings, wouldn't it be better to fix the query?
> 

I agree completely. But CloudStack does a lot of SQL queries and I can't tell for sure which
still work or which do not.

For now I suggest to change MySQL back to a different SQL mode and then attempt to fix this.

It currently prevents users from deploying on Ubuntu 16.04 with MySQL 5.7 very easily.

Wido

> -- 
> Erik
> 
> On Wed, Apr 12, 2017 at 9:56 AM, Wido den Hollander <wido@widodh.nl> wrote:
> >
> >> Op 12 april 2017 om 7:23 schreef Koushik Das <koushik.das@accelerite.com>:
> >>
> >>
> >> Hi Wido,
> >>
> >> Check initDataSource() in TransactionLegacy.java. The connection properties
are read from db.properties.
> >
> > Thanks! After looking into this I found that you can just add this to db.cloud.url.params
> >
> > sessionVariables=sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
> >
> > My line now looks like this in db.properties:
> >
> > db.cloud.url.params=prepStmtCacheSize=517&cachePrepStmts=true&sessionVariables=sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
> >
> > Created a issue: https://issues.apache.org/jira/browse/CLOUDSTACK-9871
> >
> > I created a PR to include this into db.properties by default: https://github.com/apache/cloudstack/pull/2037
> >
> > Wido
> >
> >>
> >> -Koushik
> >>
> >> On 11/04/17, 10:27 PM, "Wido den Hollander" <wido@widodh.nl> wrote:
> >>
> >>
> >>     > Op 11 april 2017 om 10:51 schreef Rohit Yadav <rohit.yadav@shapeblue.com>:
> >>     >
> >>     >
> >>     > Hi Wido,
> >>     >
> >>     >
> >>     > You're right, MySQL 5.7 has by default strict(er) sql mode enabled.
To make CloudStack work with MySQL 5.7, changing the sql mode makes MySQL 5.7 behave like
5.6 with which the mgmt server/usage server should work.
> >>     >
> >>
> >>     Yes, but a client can do this as well. It doesn't have to be server wide.
> >>
> >>     Do you know where the MySQL client is initiated by CloudStack? A few lines
of code there should/might be enough.
> >>
> >>     Wido
> >>
> >>     >
> >>     > Regards.
> >>     >
> >>     > ________________________________
> >>     > From: Wido den Hollander <wido@widodh.nl>
> >>     > Sent: 10 April 2017 20:30:55
> >>     > To: dev@cloudstack.apache.org
> >>     > Subject: MySQL 5.7 and SQL Mode
> >>     >
> >>     > 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?
> >>     >
> >>     > Thank you,
> >>     >
> >>     > Wido
> >>     >
> >>     > rohit.yadav@shapeblue.com
> >>     > www.shapeblue.com
> >>     > 53 Chandos Place, Covent Garden, London  WC2N 4HSUK
> >>     > @shapeblue
> >>     >
> >>     >
> >>     >
> >>
> >>
> >>
> >>
> >>
> >> DISCLAIMER
> >> ==========
> >> This e-mail may contain privileged and confidential information which is the
property of Accelerite, a Persistent Systems business. It is intended only for the use of
the individual or entity to which it is addressed. If you are not the intended recipient,
you are not authorized to read, retain, copy, print, distribute or use this message. If you
have received this communication in error, please notify the sender and delete all copies
of this message. Accelerite, a Persistent Systems business does not accept any liability for
virus infected mails.

Mime
View raw message