You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@cloudstack.apache.org by Wido den Hollander <wi...@widodh.nl> on 2017/04/10 15:00:55 UTC
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
Re: MySQL 5.7 and SQL Mode
Posted by Wido den Hollander <wi...@widodh.nl>.
> Op 10 april 2017 om 18:20 schreef Rafael Weingärtner <ra...@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 <ma...@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
Re: MySQL 5.7 and SQL Mode
Posted by Rafael Weingärtner <ra...@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.
On Mon, Apr 10, 2017 at 11:04 AM, Rene Moser <ma...@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
Re: MySQL 5.7 and SQL Mode
Posted by Rene Moser <ma...@renemoser.net>.
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�
Re: MySQL 5.7 and SQL Mode
Posted by Wido den Hollander <wi...@widodh.nl>.
> Op 12 april 2017 om 10:39 schreef Erik Weber <te...@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 <wi...@widodh.nl> wrote:
> >
> >> Op 12 april 2017 om 7:23 schreef Koushik Das <ko...@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" <wi...@widodh.nl> wrote:
> >>
> >>
> >> > Op 11 april 2017 om 10:51 schreef Rohit Yadav <ro...@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 <wi...@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.
Re: MySQL 5.7 and SQL Mode
Posted by Erik Weber <te...@gmail.com>.
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 <wi...@widodh.nl> wrote:
>
>> Op 12 april 2017 om 7:23 schreef Koushik Das <ko...@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" <wi...@widodh.nl> wrote:
>>
>>
>> > Op 11 april 2017 om 10:51 schreef Rohit Yadav <ro...@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 <wi...@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.
Re: MySQL 5.7 and SQL Mode
Posted by Wido den Hollander <wi...@widodh.nl>.
> Op 12 april 2017 om 7:23 schreef Koushik Das <ko...@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" <wi...@widodh.nl> wrote:
>
>
> > Op 11 april 2017 om 10:51 schreef Rohit Yadav <ro...@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 <wi...@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.
Re: MySQL 5.7 and SQL Mode
Posted by Koushik Das <ko...@accelerite.com>.
Hi Wido,
Check initDataSource() in TransactionLegacy.java. The connection properties are read from db.properties.
-Koushik
On 11/04/17, 10:27 PM, "Wido den Hollander" <wi...@widodh.nl> wrote:
> Op 11 april 2017 om 10:51 schreef Rohit Yadav <ro...@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 <wi...@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.
Re: MySQL 5.7 and SQL Mode
Posted by Wido den Hollander <wi...@widodh.nl>.
> Op 11 april 2017 om 10:51 schreef Rohit Yadav <ro...@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 <wi...@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
>
>
>
Re: MySQL 5.7 and SQL Mode
Posted by Rohit Yadav <ro...@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.
Regards.
________________________________
From: Wido den Hollander <wi...@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
Re: MySQL 5.7 and SQL Mode
Posted by Wei ZHOU <us...@gmail.com>.
We had this issue, and made same change as you said. We also add it to chef
cookbook.
2017年4月10日星期一，Wido den Hollander <wi...@widodh.nl> 写道：
> 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
>