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
nginx 404 error on all wordpress posts and pages except homepage Question Question
Running a web-based video game on a droplet Question Question

Question

Managed MySQL database error: Unknown column in 'where clause'

Posted September 13, 2020 7.1k views
WordPressOpenLiteSpeed WordPress 1-ClickDigitalOcean Managed MySQL Database

Hello,

I am using OpenLiteSpeed WordPress 1-Click from the marketplace and a Managed MySQL 8 Database.

There’s a specific and well known plugin I need to run the website: Easy Digital Downloads. On a fresh install, with only that plugin active I am getting a database error:

WordPress database error: [Unknown column 'edd_payment_note' in 'where clause']
SELECT comment_approved, COUNT( * ) AS num_comments FROM wp_comments WHERE comment_type != "edd_payment_note" GROUP BY comment_approved

The error seems present only with the Managed DB. Using the provided MariaDB 10.4 from the droplet the plugin works just fine.

The Global SQL mode has the default settings:

ANSI_QUOTES 
ERROR_FOR_DIVISION_BY_ZERO 
IGNORE_SPACE 
NO_ENGINE_SUBSTITUTION 
NO_ZERO_DATE 
NO_ZERO_IN_DATE 
ONLY_FULL_GROUP_BY 
PIPES_AS_CONCAT 
REAL_AS_FLOAT 
STRICT_ALL_TABLES

What could be the difference between the databases that’s causing that issue? and how may I be able to fix it?

Add a comment
PSNick
By:
PSNick

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
nginx 404 error on all wordpress posts and pages except homepage Question Question
Running a web-based video game on a 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
1 answer
moisey October 9, 2020

The error that you are receiving is that the specific column edd_payment_note that you are requesting isn’t found in the DB/table that you are requesting it from.

You would need to login to your Managed DB and confirm that you have the correct database there, that it has the correct tables, and the correct fields.

There aren’t any differences between the two, but perhaps there was a part of a setup that was missed which is why a field or table maybe missing between the two.

You can use the describe function in MySQL to show what columns are in a database. 

MYSQL> use database_name;
MYSQL> describe table_name;

Do that on both servers and compare them to ensure that they match.

Reply Report
  • PSNick October 19, 2020

    Hi moisey, thanks for the reply!

    It looks like it was a problem with the default Global SQL Mode. I had to remove the following (and replace the last one for STRICT_TRANS_TABLES), which did the trick.

    ANSI_QUOTES 
IGNORE_SPACE 
PIPES_AS_CONCAT 
REAL_AS_FLOAT 
STRICT_ALL_TABLES

    I think technically removing just ANSI_QUOTES would’ve worked, but I made the rest of the changes to have the same settings as the default MySQL install which WordPress has no problems with.

    Reply Report

Related

Looking for something else?

Ask a new question Search for more help