Related

WordPress database error: [Expression #1 of ORDER BY clause is not in SELECT list... Question Question
Private connection to Managed DB not working from within same region droplet. Question Question

Question

Which SQL mode values to use for WordPress?

Posted January 18, 2020 2.5k views
DigitalOcean Managed MySQL Database

I have a WordPress site running on a DO droplet and WordPress uses a localhost MySQL db. I want to migrate the db to a DO managed MySQL db. I have created the new db and completed migrating the db data, however, I have run into a problem… I found a warning in the DO documentation (the warning is in a red box near the bottom of page https://www.digitalocean.com/community/tutorials/how-to-install-wordpress-managed-database-ubuntu-18-04) that basically states WordPress will not work with a DO managed MySQL db unless changes are made to the global SQL mode setting. Unfortunately, the warning does not state what changes are needed. It provides links to MySQL documentation that provides details about SQL modes, but the MySQL documentation says nothing about which SQL mode values are incompatible with WordPress.

So, my question is: What is a typical, safe set of values to use for a DO managed MySQL db’s global SQL mode setting to ensure it will work properly with WordPress?

Add a comment
By:
JigsawBob

Related

WordPress database error: [Expression #1 of ORDER BY clause is not in SELECT list... Question Question
Private connection to Managed DB not working from within same region droplet. Question Question

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
4 answers
Zehubert January 18, 2020

DO managed DBs do work with WP, I ran many tests with them. You might bump into a couple of issues:

In the DO GUI DB settings, you need to set STRICTTRANSTABLES

You might want to check this: https://www.digitalocean.com/community/questions/sql_mode-only_full_group_by?answer=54522

Reply Report
  • JigsawBob January 18, 2020
    [deleted]
    Reply Report
  • JigsawBob January 19, 2020

    Thank you, Zehubert, I have made the suggested change!

    Unfortunately, I’ve run into a more fundamental issue: I changed wp-config.php to reference the new DO managed MySQL db, but now when I try to access my WordPress site via the browser I get a page with the message “Error establishing a database connection” (no other text).

    I triple checked my wp-config.php db settings to ensure their correctness. Eventually I figured out the problem is that the MySQL authentication method changed between MySQL 5.7 (version of my localhost on Ubuntu 16.04) and MySQL 8.0 (version used by DO’s managed db). All the solutions I’ve seen online involve some variation of altering the db user to use authentication method mysql_native_password, instead of the new default method auth_socket. However, the “doadmin” managed db user does not seem to have authorization to make that change.

    Did you experience this problem? If so, how did you resolve it?

    Reply Report
    • JigsawBob January 19, 2020
      Reply Report
      • Zehubert January 19, 2020

        With super-user login execute this query: ALTER USER USERNAME IDENTIFIED WITH mysqlnativepassword BY ’PASSWORD’;

        Also – I noticed that I had to use the Public ip of the managed instance, not the private IP.

        Reply Report
        • JigsawBob January 20, 2020

          Thanks, Zehubert! I successfully applied the ALTER command, but unfortunately, I’m still seeing the following WordPress error logs, even when using the public IP:

          [20-Jan-2020 02:40:56 UTC] PHP Warning:  mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in /var/www/.../wp-includes/wp-db.php on line 1633
[20-Jan-2020 02:40:56 UTC] PHP Warning:  mysqli_real_connect(): (HY000/2054): The server requested authentication method unknown to the client in /var/www/.../wp-includes/wp-db.php on line 1633

          I’ll keep working on it though… I noticed the post I linked to stated there may be problems if you’re not running on at least PHP 7.2, so I’m going to update PHP and see if that helps.

          Reply Report
          • Zehubert January 20, 2020

            That would mean that your modification didn’t work, perhaps because you tried with a user that does not have elevated-enough permission, or that you are using another dbuser in wpconfig.php

            On DO, the “doadmin” user should have enough privileges to do it.

            You can go to PHPMYadmin or any other to to check the current wpdbadmin user status in your managed MYSQL.

            Reply Report
          • Zehubert January 20, 2020

            Oh, and if you have redis or other in-memory caching, you might want to flush all caches, just in case.

            Reply Report
          • JigsawBob January 20, 2020

            Thanks for the tips, Zehubert! I went through all the steps carefully a couple more times but still no go. I’ve confirmed via the mysql shell that the db user does indeed now use mysql_native_password, and I’ve checked the wp-config.php credentials yet again.

            A couple of people in the other post stated this solution didn’t work until they updated to PHP 7.2, so I’m going to try that Tuesday.

            Reply Report
          • JigsawBob January 29, 2020

            Update: I upgraded my droplet from Ubuntu 16.04 to 18.04 and it solved the managed db connection problem, presumably because it upgraded PHP to version 7.2 which is mentioned as necessary in the link I provided earlier.

            Zehubert, I should also mention that the private link to the managed db works well for me, and I didn’t need to do anything special. Upgrading to Ubuntu 18.04 caused both the public and private db links to work equally well. Thanks again for the help you provided!

            Reply Report
Zehubert January 19, 2020

Oh yes! That’s right, I forgot about this. You have to change the auth method as your link says.

Reply Report
Zehubert January 29, 2020

businessbcbdbe0eab0de5765a, thanks for the update. Much appreciated ^^

Reply Report
giggs August 2, 2020

I’ve managed to connect to the remote MySql but the connection is way too slow..someone with the same issue?

Reply Report

Related

Looking for something else?

Ask a new question Search for more help