DEV Community

loading...

Discussion on: How To Install WordPress with a Managed Database on Ubuntu 18.04

Collapse
chrismccoy profile image
chrismccoy

You will run into issues with the new DigitalOcean Platform with WordPress sites.

It uses Mysql 8 with Strict Mode enabled, which WordPress has issues with.

And Since you dont have a true admin account, you cant set the sql_mode value.

The Cluster has ONLY_FULL_GROUP_BY which WordPress doesn't like.

You Will get errors like

[Expression #1 of ORDER BY clause is not in SELECT list, references column 'wordpress.wp_posts.post_date' which is not in SELECT list; this is incompatible with DISTINCT]

I have contacted DigitalOcean to see if they will alter the sql_mode for me since the "doadmin" isnt really an admin account.

To disable Strict with doadmin you will get

MySQL [(none)]> SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
MySQL [(none)]>

Since "doadmin" is not a true admin account.

Collapse
nylen profile image
James Nylen

Hi @chrismccoy , here is a workaround for this issue: gist.github.com/nylen/abc5969a7bda...

Collapse
chrismccoy profile image
chrismccoy

thanks, I ended up moving my databases to aws, if i go back to DO at any time i will add this to my MU plugins, still a pain for every site.

Collapse
anraiki profile image
Anri • Edited

After migrating the database, I've been getting errors after errors. If it continues, I may just pop up a separate droplet only to setup a mysql instance.

WordPress database error: [Incorrect datetime value: '0000-00-00 00:00:00' for column 'post_date_gmt' at row 1]

INSERT INTO `wp_posts` (`post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_content_filtered`, `post_title`, `post_excerpt`, `post_status`, `post_type`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_parent`, `menu_order`, `post_mime_type`, `guid`) VALUES (1, '2019-11-20 20:28:19', '0000-00-00 00:00:00', '', '', 'AUTO-DRAFT', '', 'auto-draft', 'product', 'closed', 'closed', '', '', '', '', '2019-11-20 20:28:19', '0000-00-00 00:00:00', 0, 0, '', '')

As to why we are encountering this error, the sql_mode setting for this manage database includes NO_ZERO_DATE.

I found this code with my modification to take care of this error if you run into it.

add_action( 'init', 'mysql_set_sql_mode_traditional', -1);
function mysql_set_sql_mode_traditional() {
global $wpdb;
$wpdb->query("SET SESSION sql_mode = 'TRADITIONAL'");
$wpdb->query("SET SESSION sql_mode = 'NO_ZERO_DATE'");
$wpdb->query("SET SESSION sql_mode = 'NO_ZERO_IN_DATE'");
}

Add it into your function.php theme file or a file in mu-plugin

Collapse
mdrakedo profile image
Mark Drake Author

Hello @chrismccoy , apologies for my extreme delay in responding. We've just launched a new feature for DigitalOcean Managed MySQL Databases that allows users to set the global SQL mode, which should be helpful in resolving this error. I've also added a warning at the end of the final step in this tutorial that provides some context on SQL modes and lets readers know they may need to modify their database's global SQL modes.

Again, I'm sorry for the radio silence on my part as well as any frustration that this issue caused.

Collapse
chrismccoy profile image
chrismccoy

ill try if i move back, but already moved to aws for mysql

Collapse
raguzman profile image
Rafael Guzman • Edited

In your DigitalOcean database settings, edit Global SQL mode and simply remove "ANSI (Shorthand)", this should also resolve any plugin issues making MySQL query requests using double quotes. This fixed the issues I was having with MemberPress and the Users Insights plugin. Hope this helps.