How to change MySQL mode permanently in MySQL Managed Database?

August 29, 2019 608 views
DigitalOcean Databases MySQL

How to change MySQL mode permanently in MySQL Managed Database?
I know that are no way to access as root user or access to MySQL config in managed database. but please show me how or add an option that user can change mode by themeselve.

1 comment
4 Answers

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.

  • Yeap, not good, but working solution :) Thanks, bro…

    PHP sample:

    $connect_link = mysqli_connect(...);
    mysqli_query($connect_link, "SET SESSION sql_mode='...';");
    mysqli_query($connect_link, "SET sql_mode='...';");
    

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.

  • Thank Kalin. the solution you gave is the best way to permanent SQL mode change. However, MySQL Managed Database leave you with no option for access config like that.

  • Appreciate you taking the time to answer, but this is for the managed database service (DBaaS).

    I am interested in this as well as we are looking at moving over to this for our apps.

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 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

Have another answer? Share your knowledge.