Instead of hacking mysql settings, wouldn't it be better to fix the query? -- Erik
On Wed, Apr 12, 2017 at 9:56 AM, Wido den Hollander <w...@widodh.nl> wrote: > >> Op 12 april 2017 om 7:23 schreef Koushik Das <koushik....@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" <w...@widodh.nl> wrote: >> >> >> > Op 11 april 2017 om 10:51 schreef Rohit Yadav >> <rohit.ya...@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 <w...@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.ya...@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.
