re: How To Install WordPress with a Managed Database on Ubuntu 18.04 VIEW POST

FULL DISCUSSION
 

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.

 
 

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.

 

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

 

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.

 

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

code of conduct - report abuse