How to change MySQL mode permanently in MySQL Managed Database?

I found the work around. Just simply put mode change query first when you connected to database. mean that every connection have to change mode before you do things. I know it is not good solution but it help me out for now.

Hi there, our sincere apologies for the delay in response. For Mysql managed DBaaS, you won’t be able to change the SQL mode yourself because it requires superuser privileges, and our admins are the only super users. Please open a ticket with us and we will have this taken care of for you.

Thanks, Peace

It would be nice if DO could make it tweakable so we don’t need to create a ticket for this every time we spin a new Managed DB

I came across this solution online by Nathaniel Hammond: https://gist.github.com/nfourtythree

The implementation is swift and takes just a couple of seconds or minutes:

1. Log into your server as root
2. Get script wget https://gist.githubusercontent.com/nfourtythree/90fb8ef5eeafdf478f522720314c60bd/raw/disable-strict-mode.sh
3. Make is executable chmod +x disable-strict-mode.sh
4. Run the script ./disable-strict-mode.sh

You can keep the script where you downloaded it for future mods or references or you can rm -rf disable-strict-mode.sh and delete the shell script.

You will then have MySQL 5.7 without strict mode.

DISCLAIMER: Not sure if it works with version 8.0

Hi Kalin,

Unfortunately, this does not work for managed service. Any update from the Digital Ocean team regarding this would be highly appreciated. Maybe I will open the ticket with Digital Ocean and post the reply here.

Thanks, Sanjay

Hi @ardencod3,

You’ll need to edit your my.cnf file. To find where it’s stored first type

locate my.cnf

Once you’ve found it, use your preferred text editor, add the sql-mode variable and specify required SQL modes on a new line under the [mysqld] section:

vim /path/to/file/my.cnf

sql-mode = 'sql-mode-name1,sql-mode-name2'

Where sql-mode is an MYSQL MODE

One done, restart your mysql service.

Kind regards, Kalin D.