IBM Cloud - Compose for MySQL - Change sql_mode - to enable GROUP BY queries

Question

I'm using the Compose for MySQL DB within my IBM Cloud CF app, however the current sql_mode settings for the DB DO NOT ALLOW ME me to run standard SQL queries using the GROUP BY statement.

I understand from research that I need to change the "sql_mode" config option for the MySQL database. This is the mysql statement that I understand needs to be executed :

mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

However, when I try this on my mysql client using my Admin credentials it tells me I require SUPER user rights for this, so it seems I need ROOT login access. The credentials of root login to the DB are not available within the IBM Cloud Dashboard only an "ADMIN" users details which clearly does not have the privileges for this operation.

In Summary, my question to IBM Cloud Support is, what precisely do I need to do to enable GROUP BY queries in on my Composer for MySQL Database ? Can it be configured via the composer.json or other method etc.

PS. DON'T TRY THIS AT HOME - PHPMyAdmin - So I had PHP MyAdmin access to the MySQL DB in the IBM Cloud and so I ran the query above there and it CRASHED the DB and made it Inaccessible. I had to restore !

---- DEPLOYMENT DETAILS ------------ Platform : IBM Cloud (aka Bluemix) Product : Compose for MySQL Deployment Details: Type MySQL (5.7.20)

Answer 1

Although you can't globally change the sql_mode (as it would impact all users of the database), you can change the setting on a per session basis.

Here's a short example...

mysql> SELECT name, address, MAX(age) FROM example GROUP BY name;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY 
clause and contains nonaggregated column 'compose.example.address' 
which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by

This isn't allowed as per SQL92. And if we check...

mysql> select @@SESSION.sql_mode;
 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

1 row in set (0.02 sec)

Ok, so let's disable that...

mysql> SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Query OK, 0 rows affected (0.02 sec)

mysql> select @@SESSION.sql_mode;
 STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
1 row in set (0.03 sec)

And now we can try again....

mysql> SELECT name, address, MAX(age) FROM example GROUP BY name;               
Empty set (0.03 sec)

And now we can run the command. You'll have to put the...

SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

at the start of any session where you want to use this style of SQL99-later command.