wige2480

A fix, ho. But the cause is that the query I mention above is not compatible with MySQL 8. Its a simple version incompatibility that they introduced in version 4.5 of the application. The only workaround I have found is to revert to the latest version 4.4 release.

• This reply was modified 5 years, 5 months ago by wige2480.

Ok, that got me results. It shows that there is a syntax error in the SQL Query.

My search term is “taxi”

This is the query:

SELECT DISTINCT posts.ID as product_id, posts.post_parent as parent_id
FROM wpdb_2_posts posts
LEFT JOIN wpdb_2_wc_product_meta_lookup wc_product_meta_lookup
ON posts.ID = wc_product_meta_lookup.product_id
LEFT JOIN wpdb_2_wc_product_meta_lookup parent_wc_product_meta_lookup
ON posts.post_type = ‘product_variation’
AND parent_wc_product_meta_lookup.product_id = posts.post_parent
WHERE posts.post_type IN (‘product’,’product_variation’)
AND ( ( ( posts.post_title LIKE ‘%taxi%’)
OR ( posts.post_excerpt LIKE ‘%taxi%’)
OR ( posts.post_content LIKE ‘%taxi%’ )
OR ( wc_product_meta_lookup.sku LIKE ‘%taxi%’ )
OR ( wc_product_meta_lookup.sku = “”
AND parent_wc_product_meta_lookup.sku LIKE ‘%taxi%’ ) ))
ORDER BY posts.post_parent ASC, posts.post_title ASC

I believe the quotes in bold should be single quotes not double quotes?

Also, post_title being in the ORDER_BY is invalid for a SELECT DISTINCT query if post_title is not also in the SELECT portion.

I am unsure why you were unable to replicate the issue, as it appears to be the SQL query itself that is at fault, as far as I can tell.

The caller is identified as: WC_Product_Data_Store_CPT->search_products()

Sorry, yes, forgot to mention that the inventory was copied over via the WooCommerce CSV exporter and importer utility.

Creating a fresh site on a new network, and manually adding four test products produced the same result, none show up in the results.

And I will add, downgrading to WooCommerce 4.4.1 causes it to start working immediately.

The only thing I can see in common between the two test sites is that they both use MySQL 8 Managed Databases instead of local databases, but I can’t think of any reason why that would cause one admin side script to fail.

I have updated to the latest woocommerce, and have updated the product lookup tables, and upgraded to the latest database version. I am still having the same issue.

This is the output of the System Status:

### WordPress Environment ###

WordPress address (URL): http://redfish.incredibledev.net
Site address (URL): http://redfish.incredibledev.net
WC Version: 4.6.1
REST API Version: ✔ 4.6.1
WC Blocks Version: ✔ 3.4.0
Action Scheduler Version: ✔ 3.1.6
WC Admin Version: ✔ 1.6.2
Log Directory Writable: ✔
WP Version: 5.5.1
WP Multisite: ✔
WP Memory Limit: 256 MB
WP Debug Mode: –
WP Cron: ✔
Language: en_US
External object cache: –

### Server Environment ###

Server Info: Apache/2.4.41 (Ubuntu)
PHP Version: 7.4.3
PHP Post Max Size: 8 MB
PHP Time Limit: 30
PHP Max Input Vars: 1000
cURL Version: 7.68.0
OpenSSL/1.1.1f

SUHOSIN Installed: –
MySQL Version: 8.0.20
Max Upload Size: 1 MB
Default Timezone is UTC: ✔
fsockopen/cURL: ✔
SoapClient: ✔
DOMDocument: ✔
GZip: ✔
Multibyte String: ✔
Remote Post: ✔
Remote Get: ✔

### Database ###

WC Database Version: 4.6.1
WC Database Prefix: wpdb_2_
Total Database Size: 13.36MB
Database Data Size: 7.76MB
Database Index Size: 5.60MB
wpdb_2_woocommerce_sessions: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpdb_2_woocommerce_api_keys: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpdb_2_woocommerce_attribute_taxonomies: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpdb_2_woocommerce_downloadable_product_permissions: Data: 0.02MB + Index: 0.06MB + Engine InnoDB
wpdb_2_woocommerce_order_items: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpdb_2_woocommerce_order_itemmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpdb_2_woocommerce_tax_rates: Data: 0.02MB + Index: 0.06MB + Engine InnoDB
wpdb_2_woocommerce_tax_rate_locations: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpdb_2_woocommerce_shipping_zones: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wpdb_2_woocommerce_shipping_zone_locations: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpdb_2_woocommerce_shipping_zone_methods: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wpdb_2_woocommerce_payment_tokens: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpdb_2_woocommerce_payment_tokenmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpdb_2_woocommerce_log: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpdb_2_actionscheduler_actions: Data: 0.02MB + Index: 0.11MB + Engine InnoDB
wpdb_2_actionscheduler_claims: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpdb_2_actionscheduler_groups: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpdb_2_actionscheduler_logs: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpdb_2_commentmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpdb_2_comments: Data: 0.02MB + Index: 0.09MB + Engine InnoDB
wpdb_2_gdpr_cc_options: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wpdb_2_links: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpdb_2_mailchimp_carts: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wpdb_2_mailchimp_jobs: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wpdb_2_options: Data: 1.48MB + Index: 0.06MB + Engine InnoDB
wpdb_2_postmeta: Data: 3.52MB + Index: 3.45MB + Engine InnoDB
wpdb_2_posts: Data: 1.52MB + Index: 0.34MB + Engine InnoDB
wpdb_2_term_relationships: Data: 0.14MB + Index: 0.11MB + Engine InnoDB
wpdb_2_term_taxonomy: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpdb_2_termmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpdb_2_terms: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpdb_2_wc_admin_note_actions: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpdb_2_wc_admin_notes: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wpdb_2_wc_category_lookup: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wpdb_2_wc_customer_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpdb_2_wc_download_log: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpdb_2_wc_order_coupon_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpdb_2_wc_order_product_lookup: Data: 0.02MB + Index: 0.06MB + Engine InnoDB
wpdb_2_wc_order_stats: Data: 0.02MB + Index: 0.05MB + Engine InnoDB
wpdb_2_wc_order_tax_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpdb_2_wc_product_meta_lookup: Data: 0.13MB + Index: 0.30MB + Engine InnoDB
wpdb_2_wc_reserved_stock: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wpdb_2_wc_tax_rate_classes: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpdb_2_wc_webhooks: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpdb_blogmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpdb_blogs: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpdb_registration_log: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpdb_signups: Data: 0.02MB + Index: 0.06MB + Engine InnoDB
wpdb_site: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpdb_sitemeta: Data: 0.05MB + Index: 0.03MB + Engine InnoDB
wpdb_usermeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpdb_users: Data: 0.02MB + Index: 0.05MB + Engine InnoDB

### Post Type Counts ###

attachment: 624
custom_css: 1
customize_changeset: 1
elementor_library: 4
nav_menu_item: 36
page: 16
post: 15
product: 893
product_variation: 57
revision: 2
wpcf7_contact_form: 2

### Security ###

Secure connection (HTTPS): ❌
					Your store is not using HTTPS. Learn more about HTTPS and SSL Certificates.
Hide errors from visitors: ✔

### Active Plugins (12) ###

Contact Form 7: by Takayuki Miyoshi – 5.3
Mailchimp for WooCommerce: by Mailchimp – 2.4.7 – Installed version not tested with active version of WooCommerce 4.6.1
Spiraclethemes Site Library: by SpiracleThemes – 1.0.1
WooCommerce: by Automattic – 4.6.1
Elementor: by Elementor.com – 3.0.12
Akismet Anti-Spam: by Automattic – 4.1.7
Jetpack by WordPress.com: by Automattic – 9.0.2
Add From Server: by Dion Hulse – 3.4.4
WP Super Cache: by Automattic – 1.7.1
Autoptimize: by Frank Goossens (futtta) – 2.7.8
WP Rollback: by Impress.org – 1.7.1
GDPR Cookie Compliance (CCPA, PIPEDA ready): by Moove Agency – 4.3.5

### Inactive Plugins (7) ###

Facebook for WooCommerce: by Facebook – 2.0.5 – Installed version not tested with active version of WooCommerce 4.6.1
Google Ads & Marketing by Kliken: by Kliken – 1.0.6 – Installed version not tested with active version of WooCommerce 4.6.1
One Click Demo Import: by ProteusThemes – 2.6.1
Really Simple SSL: by Really Simple Plugins – 3.3.5
Smush: by WPMU DEV – 3.7.1
WP Extra File Types: by Davide Airaghi – 0.4.4.1
YITH WooCommerce Quick View: by YITH – 1.4.5

### Dropin Plugins (1) ###

advanced-cache.php: advanced-cache.php

### Settings ###

API Enabled: –
Force SSL: –
Currency: USD ($)
Currency Position: left
Thousand Separator: ,
Decimal Separator: .
Number of Decimals: 2
Taxonomies: Product Types: external (external)
grouped (grouped)
simple (simple)
variable (variable)

Taxonomies: Product Visibility: exclude-from-catalog (exclude-from-catalog)
exclude-from-search (exclude-from-search)
featured (featured)
outofstock (outofstock)
rated-1 (rated-1)
rated-2 (rated-2)
rated-3 (rated-3)
rated-4 (rated-4)
rated-5 (rated-5)

Connected to WooCommerce.com: –

### WC Pages ###

Shop base: #6 - /shop/
Cart: #7 - /cart/
Checkout: #8 - /checkout/
My account: #9 - /my-account/
Terms and conditions: ❌ Page not set

### Theme ###

Name: Twenty Twenty
Version: 1.5
Author URL: https://ww.wp.xz.cn/
Child Theme: ❌ – If you are modifying WooCommerce on a parent theme that you did not build personally we recommend using a child theme. See: How to create a child theme
WooCommerce Support: ✔

### Templates ###

Overrides: –

### Action Scheduler ###

Complete: 26
Oldest: 2020-09-26 21:28:18 -0400
Newest: 2020-10-23 19:30:04 -0400

More information…

Apparently, I was on 4.4.1. So, I deleted WC 4.5.1 and installed 4.4.1 from GitHub, and it works fine. Then, I installed 4.5.0 from GitHub over it, and same problem. So it looks like the problem is in the update from 4.4.1 to 4.5.1.

Also, I am on a WordPress Network/Multisite. Maybe this is something specific to multisite installs?

For the moment I am going to run 4.4.1 because my client needs this functionality, but hopefully you will find a solution and I can upgrade to stay current.

I added that plugin and there was no change. I also deactivated and reactivated WooCommerce entirely and that did not fix it either.

I did note in the URL, the search appears to just be the normal blog search filtered on post_type:
/wp-admin/edit.php?s=a&post_status=all&post_type=product&action=-1&product_cat&product_type&stock_status&paged=1&action2=-1

If I replace “product” with “post” in the bolded spot, it returns the blog posts containing the letter “a”. This tells me the problem is most likely with the database rather than something client side in the page?

Also, if I remove ‘post_type=product&’ from the url, I get the same result, all the blog posts, no products.

During the update, WooCommerce made some changes to the database structure. Could this have broken a table link or something?

• This reply was modified 5 years, 9 months ago by wige2480.

I am using a custom paid theme right now, but even if I switch to one of the default themes, I still only have the one option for standard pages.

I currently have about 15 categories on the categories screen, and almost 60 tags, but no box on the menu screen for either.