Anyway to disable only_full_group_by on 5.7

[Matt Bourne]

It seems like there is no super access and although you can add flags there appears no way to remove default flags like only_full_group_by

[David Newgas]

Hi,

ONLY_FULL_GROUP_BY isn't actually a database flag, it is one of the possible parameters of the sql_mode database flag. sql_mode is one of the supported flags in Cloud SQL, which means you can change it by editing the instance properties. In order to remove ONLY_FULL_GROUP_BY but leave the other default modes you would need to set sql_mode to STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE(etc). Unfortunately it seems that our validation only allows you to set one mode. This is a bug, which I am reporting now.

In the mean time the best workaround is to use SET SESSION sql_mode='...' at the start of each session to remove the mode.

David

On Fri, Jun 3, 2016 at 6:29 AM, Matt Bourne <matt...@gmail.com> wrote:

It seems like there is no super access and although you can add flags there appears no way to remove default flags like only_full_group_by

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-d...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/b977f133-5127-4a54-9fa6-cdc7be6ac465%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Thomas Locke]

Hi David , Any update on this bug being resolved. Will the fix be days, weeks or months away do you know ?

Regards,

Thomas

On Friday, 3 June 2016 17:34:35 UTC+1, David Newgas wrote:

Hi,

ONLY_FULL_GROUP_BY isn't actually a database flag, it is one of the possible parameters of the sql_mode database flag. sql_mode is one of the supported flags in Cloud SQL, which means you can change it by editing the instance properties. In order to remove ONLY_FULL_GROUP_BY but leave the other default modes you would need to set sql_mode to STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE(etc). Unfortunately it seems that our validation only allows you to set one mode. This is a bug, which I am reporting now.

In the mean time the best workaround is to use SET SESSION sql_mode='...' at the start of each session to remove the mode.

David

On Fri, Jun 3, 2016 at 6:29 AM, Matt Bourne <matt...@gmail.com> wrote:

It seems like there is no super access and although you can add flags there appears no way to remove default flags like only_full_group_by

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.

[Michael Parsons]

David,

I'm hitting the same issue. Any updates?

Michael

[David Newgas]

It's planned, but other properties like bringing second gen to GA probably mean it won't happen for a while (I.e. quarters from now). For now I recommend making sure you find a workaround that works for you, such as session variable.

I do believe that this is valuable and will see if we can find a way to make it happen sooner. If other people are affected by this, feel free to comment on this thread with your use-case so the team can get a sense of how many users need this feature.

David

On Tue, Jul 12, 2016, 11:55 PM Michael Parsons <mic...@touchcare.com> wrote:

David,

I'm hitting the same issue. Any updates?

Michael

On Monday, June 13, 2016 at 11:26:16 AM UTC-4, Thomas Locke wrote:

Hi David , Any update on this bug being resolved. Will the fix be days, weeks or months away do you know ?

Regards,

Thomas

On Friday, 3 June 2016 17:34:35 UTC+1, David Newgas wrote:

Hi,

ONLY_FULL_GROUP_BY isn't actually a database flag, it is one of the possible parameters of the sql_mode database flag. sql_mode is one of the supported flags in Cloud SQL, which means you can change it by editing the instance properties. In order to remove ONLY_FULL_GROUP_BY but leave the other default modes you would need to set sql_mode to STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE(etc). Unfortunately it seems that our validation only allows you to set one mode. This is a bug, which I am reporting now.

In the mean time the best workaround is to use SET SESSION sql_mode='...' at the start of each session to remove the mode.

David

On Fri, Jun 3, 2016 at 6:29 AM, Matt Bourne <matt...@gmail.com> wrote:

It seems like there is no super access and although you can add flags there appears no way to remove default flags like only_full_group_by

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-d...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/b977f133-5127-4a54-9fa6-cdc7be6ac465%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-d...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/c9076355-8d64-47cc-89aa-b02945a56f6c%40googlegroups.com.

[Hajar Homayouni]

Hi,

How can I disable Strict mode in Cloud sql?

On Friday, June 3, 2016 at 10:34:35 AM UTC-6, David Newgas wrote:

Hi,

ONLY_FULL_GROUP_BY isn't actually a database flag, it is one of the possible parameters of the sql_mode database flag. sql_mode is one of the supported flags in Cloud SQL, which means you can change it by editing the instance properties. In order to remove ONLY_FULL_GROUP_BY but leave the other default modes you would need to set sql_mode to STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE(etc). Unfortunately it seems that our validation only allows you to set one mode. This is a bug, which I am reporting now.

In the mean time the best workaround is to use SET SESSION sql_mode='...' at the start of each session to remove the mode.

David

On Fri, Jun 3, 2016 at 6:29 AM, Matt Bourne <matt...@gmail.com> wrote:

It seems like there is no super access and although you can add flags there appears no way to remove default flags like only_full_group_by

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.

[David Newgas]

By default Cloud SQL leaves the sql mode at the default, which includes strict_trans_tables in 5.7 but does not in 5.5 or 5.6.  So if you are running 5.5 or 5.6 it is already off by default.

If you are running 5.7 you can turn it off globally by setting the sql_mode flag to anything else, or blank. Because of the issue outlined earlier in this thread, this will remove some of the other modes too. The only way to remove just that one flag is to set the sql_mode session variable each time you connect as outlined earlier.

David

On Wed, Aug 3, 2016 at 10:19 AM, Hajar Homayouni <homayou...@gmail.com> wrote:

Hi,

How can I disable Strict mode in Cloud sql?

On Friday, June 3, 2016 at 10:34:35 AM UTC-6, David Newgas wrote:

Hi,

ONLY_FULL_GROUP_BY isn't actually a database flag, it is one of the possible parameters of the sql_mode database flag. sql_mode is one of the supported flags in Cloud SQL, which means you can change it by editing the instance properties. In order to remove ONLY_FULL_GROUP_BY but leave the other default modes you would need to set sql_mode to STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE(etc). Unfortunately it seems that our validation only allows you to set one mode. This is a bug, which I am reporting now.

In the mean time the best workaround is to use SET SESSION sql_mode='...' at the start of each session to remove the mode.

David

On Fri, Jun 3, 2016 at 6:29 AM, Matt Bourne <matt...@gmail.com> wrote:

It seems like there is no super access and although you can add flags there appears no way to remove default flags like only_full_group_by

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-d...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/b977f133-5127-4a54-9fa6-cdc7be6ac465%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-d...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/40ef3feb-bd42-4030-bf3c-f3475b2f9d95%40googlegroups.com.

[Russell Thompson]

I'm having the same issue too. I assume the best work around is to rebuild the database in mysql v 5.6? The others suggested in this group didn't work for me.

[Matthew Daines]

This is also affecting me. Need some way to remove only_full_group_by.

[Amit Marty]

We added the sql_mode = TRADITIONAL to the mysql_flags section to get around this.

http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_traditional

On Wednesday, August 3, 2016 at 12:26:51 PM UTC-5, David Newgas wrote:

By default Cloud SQL leaves the sql mode at the default, which includes strict_trans_tables in 5.7 but does not in 5.5 or 5.6.  So if you are running 5.5 or 5.6 it is already off by default.

If you are running 5.7 you can turn it off globally by setting the sql_mode flag to anything else, or blank. Because of the issue outlined earlier in this thread, this will remove some of the other modes too. The only way to remove just that one flag is to set the sql_mode session variable each time you connect as outlined earlier.

David

On Wed, Aug 3, 2016 at 10:19 AM, Hajar Homayouni <homayou...@gmail.com> wrote:

Hi,

How can I disable Strict mode in Cloud sql?

On Friday, June 3, 2016 at 10:34:35 AM UTC-6, David Newgas wrote:

Hi,

ONLY_FULL_GROUP_BY isn't actually a database flag, it is one of the possible parameters of the sql_mode database flag. sql_mode is one of the supported flags in Cloud SQL, which means you can change it by editing the instance properties. In order to remove ONLY_FULL_GROUP_BY but leave the other default modes you would need to set sql_mode to STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE(etc). Unfortunately it seems that our validation only allows you to set one mode. This is a bug, which I am reporting now.

In the mean time the best workaround is to use SET SESSION sql_mode='...' at the start of each session to remove the mode.

David

On Fri, Jun 3, 2016 at 6:29 AM, Matt Bourne <matt...@gmail.com> wrote:

It seems like there is no super access and although you can add flags there appears no way to remove default flags like only_full_group_by

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/b977f133-5127-4a54-9fa6-cdc7be6ac465%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.

[Vincent Manapat]

Is there a way to clear the sql mode (i.e, by making it an empty string)? Alternatively, how do you "unset"  a single flag via the gcloud tool?

[Arvind Stutzen]

Thanks it worked. Will it have have any other impact on performance or functionality ?

[Chris Maresca]

This is pretty bad, there is no way to set multiple SQL_MODE flags, which can be a huge issue when migrating from 5.5 - not just ONLY_FULL_GROUP_BY but also NO_ZERO_IN_DATE and probably others.

This is a serious bug for anyone importing from a previous MySQL version.  So far it's cost us at least 30 developer hours working through all this and possibly more.  And this isn't the first time I've run into this, it was an issue when we migrated to GCP in my previous company. 

The problem is compounded by the fact that there is no setting to remove ALL the sql_flags, which seems like it would be an easy fix to have 'NONE' as an option....

Chris.

On Thursday, July 14, 2016 at 1:15:28 AM UTC-7, David Newgas wrote:

It's planned, but other properties like bringing second gen to GA probably mean it won't happen for a while (I.e. quarters from now). For now I recommend making sure you find a workaround that works for you, such as session variable.

I do believe that this is valuable and will see if we can find a way to make it happen sooner. If other people are affected by this, feel free to comment on this thread with your use-case so the team can get a sense of how many users need this feature.

David

On Tue, Jul 12, 2016, 11:55 PM Michael Parsons <mic...@touchcare.com> wrote:

David,

I'm hitting the same issue. Any updates?

Michael

On Monday, June 13, 2016 at 11:26:16 AM UTC-4, Thomas Locke wrote:

Hi David , Any update on this bug being resolved. Will the fix be days, weeks or months away do you know ?

Regards,

Thomas

On Friday, 3 June 2016 17:34:35 UTC+1, David Newgas wrote:

Hi,

ONLY_FULL_GROUP_BY isn't actually a database flag, it is one of the possible parameters of the sql_mode database flag. sql_mode is one of the supported flags in Cloud SQL, which means you can change it by editing the instance properties. In order to remove ONLY_FULL_GROUP_BY but leave the other default modes you would need to set sql_mode to STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE(etc). Unfortunately it seems that our validation only allows you to set one mode. This is a bug, which I am reporting now.

In the mean time the best workaround is to use SET SESSION sql_mode='...' at the start of each session to remove the mode.

David

On Fri, Jun 3, 2016 at 6:29 AM, Matt Bourne <matt...@gmail.com> wrote:

It seems like there is no super access and although you can add flags there appears no way to remove default flags like only_full_group_by

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/b977f133-5127-4a54-9fa6-cdc7be6ac465%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.

[Amit Chandel]

Thanks! Setting sql_mode to TRADITIONAL helped remove  ONLY_FULL_GROUP_BY from the sql_mode.

[Tianyang Hu]

Is there any update in terms of removing one a several sql_mode flags. Since our team has a use case of removing STRICT_TRANS_TABLES flag. There is no way to just remove one flag.

[Darshit Socialinfotech]

Hi,

Now you can Google Cloud Platform Console and then go to your instance detail page then you will see  above configuration -> Edit Configuration(in last after loading) press that edit and Then you will see option for "Add database flags" (4th option ) press on that so you will see "database flags"-----> select item from dropdown (if you can't see then press + add item) and select < sql_mode > and you will see another dropdown -> Select < STRICT_TRANS_TABLES > ... Then HIT "SAVE " button and it will automatic restart your SQL instance and after that fire your QUERY again you will see your result...

Regards
Darshit.