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)]>
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]
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.
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.
I encountered the same error regarding the required privilege(s) to enable an event scheduler. However, after submitting a support ticket, the Digital Ocean team resolved the issue by granting me the necessary permissions.
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
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.
Hi @chrismccoy , here is a workaround for this issue: gist.github.com/nylen/abc5969a7bda...
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
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.
I encountered the same error regarding the required privilege(s) to enable an event scheduler. However, after submitting a support ticket, the Digital Ocean team resolved the issue by granting me the necessary permissions.