4

I have an application that runs on node.js and queries the database hosted on Azure MySQL server, I have encountered an error message during a query with group by

"...this is incompatible with sql_mode=only_full_group_by".

Subsequently, I have tried to change my SQL user privilege with

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

but got an error message:

Error Code: 1227. Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

HUGO-DEV
  • 897
  • 8
  • 20
  • change your sql query to fit the requirements., add columns to your group by or check the cloumns that doesn't work in group by and add aggregation fuctions. – nbk Jan 19 '20 at 06:01
  • @nbk Thanks for your reply, but I have too many queries. It's costly to change. If there another method? – HUGO-DEV Jan 19 '20 at 06:16
  • you can check this out https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html – nbk Jan 19 '20 at 12:10
  • In case using drupal 8 check this out https://stackoverflow.com/a/65503455/7995302 – Naser Nikzad Dec 30 '20 at 07:39

2 Answers2

7

You can do this from the "server parameters" in azure portal. You can get the option of sql_mode there. Just change that and save it

https://learn.microsoft.com/en-us/azure/mysql/howto-server-parameters

user13632948
  • 71
  • 1
  • 2
2

Open Azure portal , navigate to your MySQL server instance , and select "Server Parameters" :

  1. Look for "sql_mode" parameter
  2. Click on the dropdown menu
  3. Unselect "ONLY_FULL_GROUP_BY"

Azure portal1

rugby2312
  • 1,056
  • 1
  • 10
  • 15