Cannot remove only_full_group_by from sql_mode

[anthonyma94]

I'm trying to migrate from a self-hosted database to PlanetScale. We use Objection.JS extensively throughout our app. When trying to retrieve data, I get the following error:

vttablet: rpc error: code = InvalidArgument desc = In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'jerviswebb.rfid_tags.id'; this is incompatible with sql_mode=only_full_group_by (errno 1140) (sqlstate 42000) (CallerID: planetscale-admin)

I have tried to remove only_full_group_by from sql_mode in the web console:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

However, none of the commands work. SELECT @@sql_mode and SELECT @@GLOBAL.sql_mode continues to include only_full_group_by, and I continue to get my errors. Is this a bug? I can't use this service if this error persists.

[orware]

@anthonyma94,

You are pretty close with what you shared above! The only issue is that you are unable to changes things using the GLOBAL keyword.

Just remove that and you should be set!

You can only change the sql_mode for the connection/session so it has to be changed for any connections where you will need that behavior removed.

I hope this helps!

[anthonyma94]

Thanks for the response. That's what I've done in my code to mitigate that so far. Is there a reason why I can't set the mode globally?

[orware]

It's primarily a platform limitation that is in place but I don't have the specific rationale unfortunately.

Allowing for changes at the session/connection level tends to be something that is used quite often however for a number of projects and database libraries since global defaults can be less than ideal for a variety of reasons so allowance for changing them for your current session is a happy middle ground for the majority of needs.