Related

Join the DigitalOcean Community Community

Join 1M+ other developers and:

  • Get help and share knowledge in Q&A
  • Subscribe to topics of interest
  • Get courses & tools that help you grow as a developer or small business owner
 Join Now
Change innodb_buffer_pool_size in a managed database cluster? Question Question
Why are snapshots so slow Question Question

Question

How to change MySQL mode permanently in MySQL Managed Database?

Posted August 29, 2019 6.7k views
MySQLDigitalOceanDatabases

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.

Add a comment
ardencod3
By:
ardencod3

Related

Join the DigitalOcean Community Community

Join 1M+ other developers and:

  • Get help and share knowledge in Q&A
  • Subscribe to topics of interest
  • Get courses & tools that help you grow as a developer or small business owner
 Join Now
Change innodb_buffer_pool_size in a managed database cluster? Question Question
Why are snapshots so slow Question Question
Add a comment
1 comment

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
Submit an Answer
6 answers
ardencod3 August 30, 2019

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.

Reply Report
  • zherebcovsergey September 20, 2019

    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='...';");
    Reply Report
sanjay70e58ac6b7638a2ba2da October 20, 2019

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

Reply Report
peace October 31, 2019

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

Reply Report
KFSys August 29, 2019

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.

Reply Report
  • ardencod3 August 30, 2019

    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.

    Reply Report
  • Clintre September 30, 2019

    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.

    Reply Report
Archie22is November 26, 2019

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

Reply Report
Zehubert January 1, 2020
Show answer This answer has been marked as resolved by Zehubert.

Related

Looking for something else?

Ask a new question Search for more help