Database error unknown column

  • Resolved sturdywall12

    (@sturdywall12)


    8 months, 1 week ago

    I always find this error repeating every few hours in the error logs of nginx server. I’m running Woocommerce plugin with paid WPML.

    "PHP message: WordPress database error Unknown column 'posts.ID' in 'on clause' for query SELECT SUM( order_item_meta.meta_value ) as qty, order_item_meta_2.meta_value as product_id FROM wp_tkqbrzh17a_posts AS orders INNER JOIN wp_tkqbrzh17a_woocommerce_order_items AS order_items ON orders.ID = order_id INNER JOIN wp_tkqbrzh17a_woocommerce_order_itemmeta AS order_item_meta ON order_items.order_item_id = order_item_meta.order_item_id INNER JOIN wp_tkqbrzh17a_woocommerce_order_itemmeta AS order_item_meta_2 ON order_items.order_item_id = order_item_meta_2.order_item_id  INNER JOIN wp_tkqbrzh17a_postmeta AS currency_postmeta ON posts.ID = currency_postmeta.post_id WHERE orders.post_type IN ( 'shop_order' ) AND orders.post_status IN ( 'wc-completed','wc-processing','wc-on-hold' ) AND order_item_meta.meta_key = '_qty' AND order_item_meta_2.meta_key = '_product_id' AND orders.post_date_gmt >= '2024-07-01' AND orders.post_date_gmt <= '2024-07-19 13:27:34'  AND currency_postmeta.meta_key = '_order_currency'"

    I inspected the php table “posts” which clearly has ID (posts.ID) column available. I need guidance on this. This could be a small conflict between Woocmmerce and WPML. Yet WPML says it’s related to other plugins, I have disabled all plugins on the site and reverted to WP default theme, yet this error shows up again.

Viewing 12 replies - 1 through 12 (of 12 total)

  • Hey there, @sturdywall12! Thanks for contacting us. I’m happy to help you.

    To confirm, you disabled all plugins and used a default theme and the issue persisted, correct?
    Which theme did you use?
    Was WPML also disabled when you tested?

    Are you seeing any other issue on the website? Are you unable to do something?

    Furthermore, I’d like to understand your site properly. Please share with us the necessary information below for us to investigate the issue further:

    System Status Report which you can find via WooCommerce > Status > Get system report > Copy for support.
    Fatal error logs (if any) under WooCommerce > Status > Logs. Then you can use the selector to search for Fatal Errors.
    You could copy and paste your reply here or paste it via https://gist.github.com/ and send the link here.

    Looking forward to your reply.

    Have a wonderful day!

    Thread Starter sturdywall12

    (@sturdywall12)

    Hello @carolm29

    All plugins are disabled except Woo, Woo payment & WPML.

    I only see the error popping up on /var/log/nginx/error.log every now and then. Around 5-10 times per day.

    As I said in my first post, the error is reported by PHP and is only posted to the error logs of the server.

    Here’s what you need:

    1. System Status Report: https://gist.github.com/ubut12d/14291fc6ee51b0890e0c644605b35a18
    2. Nothing to be found. No Fatal Errors.

    Thanks!

    Plugin Support Zubair Zahid (woo-hc)

    (@doublezed2)

    Hello sturdywall12

    Thank your for your reply.

    I checked the Database tables of your site and noticed that the database prefix is wp_tkqbrzh17a_
    This means all the tables should start with “wp_tkqbrzh17a_”.

    Then I also noticed that there are two posts tables.
    1- wp_tkqbrzh17a_posts
    2- wp_posts

    Your current site is set up to use the tables that have the prefix wp_tkqbrzh17a_.
    Which I think is working fine without any problems.
    This is why you are not encountering any issues on the site.

    Another WordPress site may be set up to use the same database.
    That site is using wp_ prefix tables.

    There are no WooCommerce tables in the tables with the wp_ prefix.
    This suggests WooCommerce is not installed on that site.

    Could you check your server of other sites?
    You may contact your host and ask them.

    Looking forward to your response. ??

    Best regards.

    Thread Starter sturdywall12

    (@sturdywall12)

    @doublezed2

    I’m running the site on sole VPS. There are no other sites using the database. We had it emigrated from Cloudways as the droplet/cloud was too slow for wordpress. It wasn’t easy to emigrate the website until we decided to buy pro version of duplicator.

    Me as web developer of the site would say the database the config file has this entry “$table_prefix = ‘wp_tkqbrzh17a_’;” which clearly shows the prefix is coming from the config.

    These wp_ tables might be leftovers from emigration process. It honestly looks like the usual classic wp_ prefix WordPress uses on the very first installation.

    what do you suggest for now?

    Plugin Support omarfpg a11n

    (@omarfpg)

    Hi @sturdywall12,

    We had it emigrated from Cloudways as the droplet/cloud was too slow for wordpress.

    These wp_ tables might be leftovers from emigration process. It honestly looks like the usual classic wp_ prefix WordPress uses on the very first installation.

    That’s correct. This in particular sounds like a migration issue. It sounds like Duplicator Pro didn’t clear up the original tables from the new site, and now you have two.

    In this case I’d suggest reaching out to Duplicator Pro’s support here: https://duplicator.com/my-account/support/.

    Me as web developer of the site would say the database the config file has this entry “$table_prefix = ‘wp_tkqbrzh17a_’;” which clearly shows the prefix is coming from the config.

    This is also correct, your site is using the prefix defined in wp_config and the SSR shows this is the case.

    Now, back to your original issue: this doesn’t seem to be a migration issue or a databse issue, rather than a query issue. If we format the query properly for easier reading, we can spot the issue right away:

    Direct link to the image: https://snipboard.io/ydvZgw.jpg

    The next step is identifying where this query is coming from. By the looks of it, it sounds like this is for reporting or analytics. I’m looking at your list of active plugins, and I’m not identifying anything that would work for such cases at first glance. Are you trying to use WooCommerce’s analytics and reports by any chance? Maybe a tool like Query Monitor can help you identify the source.

    Other test would be trying to work on a Staging site and disabling plugins until the issue goes away, then you can reenable them one by one until the issue presents itself to learn which is the culprit. This may take a while since the issue only shows about 5 times a day, and there’s no specific action you’re taking to cause it, right? If you can identify which action triggers this issue that can speed things up in troubleshooting.

    I hope this helps so far.
    -OP

    Thread Starter sturdywall12

    (@sturdywall12)

    Hello.

    I’m installing the plugin and will observe the queries. However, after further observation to the logs. I figure the code I reported comes when I access the wp-admin after authorization, visitor checking a product and another visitor visiting the main page. For now I have disabled all analytics. However, the fatal error in sql queries is being reported in multiple pages and plugins pages such as google site kit. Please check the following code:

    2024/07/27 09:15:36 [error] 162989#162989: *621 FastCGI sent in stderr: "PHP message: WordPress database error Unknown column 'posts.ID' in 'on clause' for query SELECT SUM( order_item_meta.meta_value ) as qty, order_item_meta_2.meta_value as product_id FROM wp_tkqbrzh17a_posts AS orders INNER JOIN wp_tkqbrzh17a_woocommerce_order_items AS order_items ON orders.ID = order_id INNER JOIN wp_tkqbrzh17a_woocommerce_order_itemmeta AS order_item_meta ON order_items.order_item_id = order_item_meta.order_item_id INNER JOIN wp_tkqbrzh17a_woocommerce_order_itemmeta AS order_item_meta_2 ON order_items.order_item_id = order_item_meta_2.order_item_id  INNER JOIN wp_tkqbrzh17a_postmeta AS currency_postmeta ON posts.ID = currency_postmeta.post_id WHERE orders.post_type IN ( 'shop_order' ) AND orders.post_status IN ( 'wc-completed','wc-processing','wc-on-hold' ) AND order_item_meta.meta_key = '_qty' AND order_item_meta_2.meta_key = '_product_id' AND orders.post_date_gmt >= '2024-07-01' AND orders.post_date_gmt <= '2024-07-27 09:15:36'  AND currency_postmeta.meta_key = '_order_currency'" while reading response header from upstream, client: 172.69.222.221, server: mp-performance.ae, request: "GET /wp-admin/index.php HTTP/1.1", upstream: "fastcgi://unix:/var/run/php/php8.2-fpm.sock:", host: "mp-performance.ae", referrer: "https://mp-performance.ae/wp-admin/admin.php?page=wc-admin&path=%2Fgoogle%2Fproduct-feed"
    2024/07/27 09:26:04 [error] 163472#163472: *4 FastCGI sent in stderr: "PHP message: WordPress database error Unknown column 'posts.ID' in 'on clause' for query SELECT SUM( order_item_meta.meta_value ) as qty, order_item_meta_2.meta_value as product_id FROM wp_tkqbrzh17a_posts AS orders INNER JOIN wp_tkqbrzh17a_woocommerce_order_items AS order_items ON orders.ID = order_id INNER JOIN wp_tkqbrzh17a_woocommerce_order_itemmeta AS order_item_meta ON order_items.order_item_id = order_item_meta.order_item_id INNER JOIN wp_tkqbrzh17a_woocommerce_order_itemmeta AS order_item_meta_2 ON order_items.order_item_id = order_item_meta_2.order_item_id  INNER JOIN wp_tkqbrzh17a_postmeta AS currency_postmeta ON posts.ID = currency_postmeta.post_id WHERE orders.post_type IN ( 'shop_order' ) AND orders.post_status IN ( 'wc-completed','wc-processing','wc-on-hold' ) AND order_item_meta.meta_key = '_qty' AND order_item_meta_2.meta_key = '_product_id' AND orders.post_date_gmt >= '2024-07-01' AND orders.post_date_gmt <= '2024-07-27 09:26:04'  AND currency_postmeta.meta_key = '_order_currency'" while reading response header from upstream, client: 172.69.222.125, server: mp-performance.ae, request: "GET /wp-admin/index.php HTTP/1.1", upstream: "fastcgi://unix:/var/run/php/php8.2-fpm.sock:", host: "mp-performance.ae", referrer: "https://mp-performance.ae/wp-admin/admin.php?page=wc-admin&path=%2Fgoogle%2Fproduct-feed"
    2024/07/27 09:28:49 [error] 163472#163472: *101 FastCGI sent in stderr: "PHP message: PHP Warning:  Attempt to read property "post_date" on null in /var/www/html/wordpress/wp-content/plugins/woocommerce/includes/rest-api/Controllers/Version3/class-wc-rest-product-categories-controller.php on line 61; PHP message: PHP Warning:  Attempt to read property "post_date_gmt" on null in /var/www/html/wordpress/wp-content/plugins/woocommerce/includes/rest-api/Controllers/Version3/class-wc-rest-product-categories-controller.php on line 62; PHP message: PHP Warning:  Attempt to read property "post_modified" on null in /var/www/html/wordpress/wp-content/plugins/woocommerce/includes/rest-api/Controllers/Version3/class-wc-rest-product-categories-controller.php on line 63; PHP message: PHP Warning:  Attempt to read property "post_modified_gmt" on null in /var/www/html/wordpress/wp-content/plugins/woocommerce/includes/rest-api/Controllers/Version3/class-wc-rest-product-categories-controller.php on line 64" while reading response header from upstream, client: 172.69.222.221, server: mp-performance.ae, request: "GET /wp-json/wc-analytics/products/categories?per_page=100&_locale=user HTTP/1.1", upstream: "fastcgi://unix:/var/run/php/php8.2-fpm.sock:", host: "mp-performance.ae", referrer: "https://mp-performance.ae/wp-admin/admin.php?page=wc-admin&path=%2Fanalytics%2Fproducts"
    2024/07/27 09:29:57 [error] 163472#163472: *395 FastCGI sent in stderr: "PHP message: PHP Warning:  Attempt to read property "post_date" on null in /var/www/html/wordpress/wp-content/plugins/woocommerce/includes/rest-api/Controllers/Version3/class-wc-rest-product-categories-controller.php on line 61; PHP message: PHP Warning:  Attempt to read property "post_date_gmt" on null in /var/www/html/wordpress/wp-content/plugins/woocommerce/includes/rest-api/Controllers/Version3/class-wc-rest-product-categories-controller.php on line 62; PHP message: PHP Warning:  Attempt to read property "post_modified" on null in /var/www/html/wordpress/wp-content/plugins/woocommerce/includes/rest-api/Controllers/Version3/class-wc-rest-product-categories-controller.php on line 63; PHP message: PHP Warning:  Attempt to read property "post_modified_gmt" on null in /var/www/html/wordpress/wp-content/plugins/woocommerce/includes/rest-api/Controllers/Version3/class-wc-rest-product-categories-controller.php on line 64" while reading response header from upstream, client: 172.69.222.220, server: mp-performance.ae, request: "GET /wp-json/wc-analytics/products/categories?per_page=100&_locale=user HT
    Thread Starter sturdywall12

    (@sturdywall12)

    This might give us some insight, managed to trigger the fatal error:

    https://share.zight.com/eDuKddZj

    Plugin Support Shameem R. a11n

    (@shameemreza)

    Hi @sturdywall12

    Thanks for sharing additional information.

    Could you tell me how you managed to reproduce the error? Would you mind outlining the steps? Also, did you conduct a conflict test? What was the result? Also, did you contact Duplicator Pro’s support as suggested by @omarfpg? They may be able to provide more insights into whether the migration process could have caused these issues.

    From what I can see, there is an issue with the SQL query where it’s referencing a posts.ID column in the on clause of the query, but it seems like the posts table alias is not defined anywhere in the query, which is why it’s throwing an error.

    The PHP warnings indicate that a piece of code is attempting to read properties on a null object, which means the object it’s trying to access does not exist or is not initialized.

    In the meantime, I recommend reaching out to a developer or a service like Codeable.io for further assistance. They can help debug this issue further and provide a solution.

    Looking forward to hearing from you!

    Plugin Support Shameem R. a11n

    (@shameemreza)

    Hi @sturdywall12

    We’ve not heard back from you in a while, so I’m marking this thread as resolved. Hopefully, you were able to find a solution to your problem!

    If you have further questions, please feel free to open a new topic.

    Thanks!

    @sturdywall12 My site has the same error, have you solved it?

    Thread Starter sturdywall12

    (@sturdywall12)

    @wpshushu nope. We kinda forgot about it. I might as well download the database and try to add the missing table.

    will do that this week and will let you know if I managed to fix it.

    I’m very disappointed that WP community has no competent members who can fix such simple missing tables issues.

    @sturdywall12 If WPML is involved in your case. I found this https://wpml.org/forums/topic/php-message-wordpress-database-error-unknown-column-posts-id/ and am confirming with WPML support if they have inclued the patch in latest WC Multilingual. If you are interested.

Viewing 12 replies - 1 through 12 (of 12 total)
  • You must be logged in to reply to this topic.