> Op 12 april 2017 om 10:39 schreef Erik Weber <terbol...@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 <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.
