Sql_mode=only_full_group_by

Wappler General How To
#1

I’m working on moving a project from Heroku to Digital Ocean as my host that was hosting my database decided to stop allowing remote MySQL (without telling me!!). Anywho - all is going well, except I’m having an issue with a bunch of my queries. I’m getting “this is incompatible with sql_mode=only_full_group_by”

It looks like I should be able to set sql_mode=’’ to fix this but I have no idea where I might do that.

I saw this post - Setting SET SESSION sql_mode = ''" on the connection file? - which gives a solution for PHP, but I need one for NodeJS.

TIA.

#2

Hi Heather,
In Digital Oceans control panel for your database cluster you will see the menu options for the database:

Screenshot from 2024-03-27 22-20-49
Screenshot from 2024-03-27 22-20-491396×175 9.33 KB

Click on Settings and scroll down to:

Screenshot from 2024-03-27 22-20-26
Screenshot from 2024-03-27 22-20-261364×294 20.4 KB

Then click on edit and you will see the option to add ONLY_FULL_GROUP_BY:

Screenshot from 2024-03-27 22-22-08
Screenshot from 2024-03-27 22-22-081298×636 47.3 KB

#3

I’m not using the digital ocean managed databases. It’s deployed in the droplet via docker.

#4

Can you not use something like Workbench (under the menu for Administration) to connect to the database and update the configuration? Otherwise connect via the SSH console in Wappler or Putty and try running grep to locate it and make the changes manually within the my.cnf? Apologies we never run our database within the container so not familiar with that scenario personally…

#5

Thanks for helping.

I don’t see Workbench anywhere.

I’m very new to all this so not really sure exactly what to do and a bit worried about mucking it up. Kind of hoping there’s an option to just add a line to one of the Wappler files, like in the Connections.php from that other post!

#6

No panic Heather like all things Wappler one or other of us will help you get there in the end!

:slight_smile:

Workbench is a FREE MySQL administration tool for Linux, Mac, and Windows. Very useful and surprisingly good for a freebie (offered up by Oracle):

Details: https://www.mysql.com/products/workbench/

Download: https://dev.mysql.com/downloads/workbench/

That is something I’ve no idea about to be honest but maybe someone else can help though?

1 Like
#7

I ended up getting a db cluster. Having issues deploying to DO now, but the database seems to be working as I can connect to it and use it from localhost.