search

woocommerce / woocommerce

A customizable, open-source ecommerce platform built on WordPress. Build any commerce solution you can imagine.
https://woocommerce.com
9.42k stars 10.77k forks source link

Unknown column on MySQL 8 triggered in find_matching_product_variation #29969

Closed brandoncc closed 3 years ago

brandoncc commented 3 years ago

Prerequisites (mark completed items with an [x]):

Describe the bug When a user chooses variation options on a product page, we see this error in our logs:

[23-May-2021 16:10:11 UTC] WordPress database error Unknown column 'attribute_kit' in 'where clause' for query
                        SELECT postmeta.post_id, postmeta.meta_key, postmeta.meta_value, posts.menu_order FROM wp_postmeta as postmeta
                        LEFT JOIN wp_posts as posts ON postmeta.post_id=posts.ID
                        WHERE postmeta.post_id IN (
                                SELECT ID FROM wp_posts
                                WHERE wp_posts.post_parent = 7235
                                AND wp_posts.post_status = 'publish'
                                AND wp_posts.post_type = 'product_variation'
                        )
                         AND postmeta.meta_key IN ( "attribute_kit","attribute_year","attribute_leaf-springs","attribute_oil-pan-kit-b-c","attribute_locker-kit-c","attribute_3rd-member-kit-c" ) ORDER BY posts.menu_order ASC, postmeta.post_id ASC;

Expected behavior The user should find a matching variation, see the price, and be able to purchase it.

Actual behavior The user is presented with a message saying no products matching their selections have been found.

image

Steps to reproduce the bug (We need to be able to reproduce the bug in order to fix it.) I believe this is another issue with ANSI_QUOTES, since I'm on MySQL 8 and found other similar Github issues where that was the root cause of the bug.

To reproduce this bug, I believe you will need a variable product with two or more selectable attributes (variable products with only one attribute seem to work fine) on MySQL 8 (with ANSI_QUOTES on).

Visit the product page and select options, then view your debug log.

Isolating the problem (mark completed items with an [x]):

Proposed (verified in my system) fix I'm currently on an outdated minor version, but master contains the same code I changed, so I believe this still applies to master.

I went to wp-content/plugins/woocommerce/includes/data-stores/class-wc-product-data-store-cpt.php and changed:

$query .= ' AND postmeta.meta_key IN ( "' . implode( '","', array_map( 'esc_sql', $meta_attribute_names ) ) . '" )';

to:

$query .= " AND postmeta.meta_key IN ( '" . implode( "','", array_map( 'esc_sql', $meta_attribute_names ) ) . "' )";

After making this change, I'm able to select variations fine. You can see the exact same options selected in this screenshot as the one above:

image

You can try it yourself here

Important note: There are variation combinations which actually do not exist, so it is important to test it using the options shown in the screenshot.

WordPress Environment We use the WooCommerce System Status Report to help us evaluate the issue. Without this report we won't be able to fully evaluate this issue.

``` ### WordPress Environment ### WordPress address (URL): https://www.yotamasters.com Site address (URL): https://www.yotamasters.com WC Version: 5.1.0 REST API Version: ✔ 5.1.0 WC Blocks Version: ✔ 4.4.3 Action Scheduler Version: ✔ 3.1.6 WC Admin Version: ✔ 2.0.2 Log Directory Writable: ✔ WP Version: ❌ 5.6.4 - There is a newer version of WordPress available (5.7.2) WP Multisite: – WP Memory Limit: 2 GB WP Debug Mode: ✔ WP Cron: ✔ Language: en_US External object cache: – ### Server Environment ### Server Info: Apache PHP Version: 7.2.34 PHP Post Max Size: 16 MB PHP Time Limit: 180 PHP Max Input Vars: 1000 cURL Version: 7.68.0 OpenSSL/1.1.1f SUHOSIN Installed: – MySQL Version: 8.0.23 Max Upload Size: 16 MB Default Timezone is UTC: ✔ fsockopen/cURL: ✔ SoapClient: ✔ DOMDocument: ✔ GZip: ✔ Multibyte String: ✔ Remote Post: ✔ Remote Get: ✔ ### Database ### WC Database Version: 5.1.0 WC Database Prefix: wp_ Total Database Size: 664.82MB Database Data Size: 434.81MB Database Index Size: 230.01MB wp_woocommerce_sessions: Data: 10.02MB + Index: 1.02MB + Engine InnoDB wp_woocommerce_api_keys: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_woocommerce_attribute_taxonomies: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_woocommerce_downloadable_product_permissions: Data: 0.02MB + Index: 0.06MB + Engine InnoDB wp_woocommerce_order_items: Data: 5.52MB + Index: 1.52MB + Engine InnoDB wp_woocommerce_order_itemmeta: Data: 26.56MB + Index: 23.06MB + Engine InnoDB wp_woocommerce_tax_rates: Data: 0.02MB + Index: 0.06MB + Engine InnoDB wp_woocommerce_tax_rate_locations: Data: 0.02MB + Index: 0.05MB + Engine InnoDB wp_woocommerce_shipping_zones: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_woocommerce_shipping_zone_locations: Data: 0.02MB + Index: 0.05MB + Engine InnoDB wp_woocommerce_shipping_zone_methods: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_woocommerce_payment_tokens: Data: 0.05MB + Index: 0.02MB + Engine InnoDB wp_woocommerce_payment_tokenmeta: Data: 0.09MB + Index: 0.09MB + Engine InnoDB wp_woocommerce_log: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_actionscheduler_actions: Data: 1.02MB + Index: 0.63MB + Engine InnoDB wp_actionscheduler_claims: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_actionscheduler_groups: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_actionscheduler_logs: Data: 1.11MB + Index: 0.39MB + Engine InnoDB wp_commentmeta: Data: 1.52MB + Index: 3.03MB + Engine InnoDB wp_comments: Data: 19.55MB + Index: 16.09MB + Engine InnoDB wp_customcontactforms_field_options: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_customcontactforms_fields: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_customcontactforms_forms: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_customcontactforms_styles: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_customcontactforms_user_data: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_gd_manager: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_links: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_mail_booster: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_mail_booster_logs: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_mail_booster_meta: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_mappress_maps: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_mappress_posts: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_ngg_album: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_ngg_gallery: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_ngg_pictures: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_options: Data: 106.56MB + Index: 8.94MB + Engine InnoDB wp_postmeta: Data: 146.73MB + Index: 106.30MB + Engine InnoDB wp_posts: Data: 37.48MB + Index: 8.77MB + Engine InnoDB wp_relevanssi: Data: 23.53MB + Index: 24.50MB + Engine InnoDB wp_relevanssi_log: Data: 13.52MB + Index: 3.52MB + Engine InnoDB wp_relevanssi_stopwords: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_sm_advanced_search_temp: Data: 0.11MB + Index: 0.00MB + Engine InnoDB wp_sm_views: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_smtpmail_data: Data: 0.27MB + Index: 0.00MB + Engine InnoDB wp_term_relationships: Data: 8.52MB + Index: 4.52MB + Engine InnoDB wp_term_taxonomy: Data: 0.08MB + Index: 0.03MB + Engine InnoDB wp_termmeta: Data: 0.11MB + Index: 0.11MB + Engine InnoDB wp_terms: Data: 0.08MB + Index: 0.03MB + Engine InnoDB wp_usermeta: Data: 17.44MB + Index: 12.95MB + Engine InnoDB wp_users: Data: 0.45MB + Index: 0.44MB + Engine InnoDB wp_wc_admin_note_actions: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_wc_admin_notes: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_wc_category_lookup: Data: 0.05MB + Index: 0.00MB + Engine InnoDB wp_wc_customer_lookup: Data: 1.52MB + Index: 1.77MB + Engine InnoDB wp_wc_download_log: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_wc_order_coupon_lookup: Data: 0.13MB + Index: 0.19MB + Engine InnoDB wp_wc_order_product_lookup: Data: 4.52MB + Index: 6.06MB + Engine InnoDB wp_wc_order_stats: Data: 2.52MB + Index: 2.39MB + Engine InnoDB wp_wc_order_tax_lookup: Data: 0.41MB + Index: 0.34MB + Engine InnoDB wp_wc_product_meta_lookup: Data: 0.52MB + Index: 0.77MB + Engine InnoDB wp_wc_reserved_stock: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_wc_tax_rate_classes: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_wc_webhooks: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_woocommerce_bundled_itemmeta: Data: 0.13MB + Index: 0.13MB + Engine InnoDB wp_woocommerce_bundled_items: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_woocommerce_shipping_flat_rate_boxes: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_woocommerce_shipping_zone_shipping_methods: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_wpmm_subscribers: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_yoast_indexable: Data: 3.52MB + Index: 1.45MB + Engine InnoDB wp_yoast_indexable_hierarchy: Data: 0.25MB + Index: 0.36MB + Engine InnoDB wp_yoast_migrations: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_yoast_primary_term: Data: 0.06MB + Index: 0.03MB + Engine InnoDB wp_yoast_seo_links: Data: 0.08MB + Index: 0.03MB + Engine InnoDB ### Post Type Counts ### attachment: 9154 auto-thickbox-plus: 1 jetpack_migration: 2 jp_img_sitemap: 4 jp_img_sitemap_index: 1 jp_sitemap: 1 jp_sitemap_master: 1 nav_menu_item: 10 page: 14 post: 54 postman_sent_mail: 250 product: 1997 product_variation: 2455 revision: 1643 shop_coupon: 128 shop_order: 17692 shop_order_refund: 535 slide: 141 wooframework: 516 wpcf7_contact_form: 1 wpephpcompat_jobs: 48 ### Security ### Secure connection (HTTPS): ✔ Hide errors from visitors: ✔ ### Active Plugins (49) ### VaultPress: by Automattic – 2.1.4 Akismet Anti-Spam: by Automattic – 4.1.9 Authorize.Net CIM for WooCommerce: by Cardpay Solutions Inc. – 2.0.18 Autoptimize: by Frank Goossens (futtta) – 2.8.3 Checkout page modifications for WooCommerce: by Brandon Conway – 0.1 Extra product specific shipping options for WooCommerce: by Brandon Conway – 0.1 WooCommerce Mods: by Brandon Conway – 0.1 Category Posts Widget: by TipTopPress – 4.9.10 Contact Form 7: by Takayuki Miyoshi – 5.4 Easy Theme and Plugin Upgrades: by Chris Jean – 2.0.1 EasyRotator for WordPress: by DWUser.com – 1.0.14 Email Obfuscation for Wordpress: by Brandon Conway – 0.1 WooCommerce Gift Certificates Pro: by IgniteWoo.com – 3.6.6 IgniteWoo Updater: by IgniteWoo.com – 3.0.2 Jetpack by WordPress.com: by Automattic – 9.6.1 MapPress Maps for WordPress: by Chris Richardson – 2.62.7 Open External Links in a New Window: by WebFactory Ltd – 1.42 PHP Compatibility Checker: by WP Engine – 1.5.0 Plugin Compatibility Checker: by Dinesh Pilani – 1.1 Post Expirator: by Aaron Axelsen – 2.4.1 Post SMTP: by Yehuda Hassine – 2.0.22 Really Simple CAPTCHA: by Takayuki Miyoshi – 2.1 Regenerate Thumbnails: by Alex Mills (Viper007Bond) – 3.1.5 Relevanssi: by Mikko Saari – 4.12.5 Search By SKU - for Woocommerce: by Unroll Digital – 0.7.1 Smart Manager For WooCommerce – Stock Management, Bulk Edit & more...: by StoreApps – 5.15.0 Smarter Navigation: by scribu versusbassz – 1.4 Sucuri Security - Auditing, Malware Scanner and Hardening: by Sucuri Inc. – 1.8.26 TimThumb Vulnerability Scanner: by Peter Butler – 1.54 UpdraftPlus - Backup/Restore: by UpdraftPlus.Com DavidAnderson – 2.16.57.1 W3 Total Cache: by BoldGrid – 2.1.2 WooCommerce Advanced Free Shipping: by Jeroen Sormani – 1.1.4 WooCommerce Cart Add-Ons: by WooCommerce – 2.0.0 Print Invoice & Delivery Notes for WooCommerce: by Tyche Softwares – 4.6.2 WooCommerce PayPal Checkout Gateway: by WooCommerce – 2.1.1 WooCommerce Google Analytics Integration: by WooCommerce – 1.5.1 Woocommerce Products Per Page: by Jeroen Sormani – 1.2.6 WooCommerce Sequential Order Numbers: by SkyVerge – 1.9.5 WooCommerce Flat Rate Box Shipping: by WooCommerce – 2.0.16 WooCommerce UPS Shipping: by WooCommerce – 3.2.31 WooCommerce - ShipStation Integration: by WooCommerce – 4.1.41 (update to version 4.1.42 is available) WooCommerce: by Automattic – 5.1.0 (update to version 5.3.0 is available) Yoast SEO: by Team Yoast – 16.1.1 WP Crontrol: by John Blackbourn & crontributors – 1.10.0 WP Maintenance Mode: by Designmodo – 2.3.0 WPFront Scroll Top: by Syam Mohan – 2.0.4.04013 Yotamasters Automatic Coupons: by Brandon Conway – 0.1.0 Yotamasters Shipping Filters: by Brandon Conway – 0.2.1 Embed Plus for YouTube - Gallery, Channel, Playlist, Live Stream: by Embed Plus for YouTube Team – 13.4.2 ### Inactive Plugins (13) ### Cache Enabler: by KeyCDN – 1.7.1 Exploit Scanner: by Automattic – 1.5.2 File Un-Attach: by Hafid R. Trujillo Huizar – 1.1.3 Products Per Page WooCommerce Extension: by Brandon Conway – 0.1 Scroll Back to Top: by Joe Sexton – 1.1.3 Theme Authenticity Checker (TAC): by builtBackwards – 1.5.3 Under Construction: by Noah Kagan – 1.18 Velvet Blues Update URLs: by VelvetBlues.com – 3.2.10 WooCommerce Chained Products: by StoreApps – 2.4.4 WooCommerce Helper: by WooCommerce – 1.7.2 – Network enabled WooCommerce MSRP Pricing: by Lee Willis – 1.3 Woo Empty Cart Button: by WPCodelibrary – 1.3.1 WP Thickbox Integration: by Alain Gonzalez – 1.0.2 ### Dropin Plugins (2) ### advanced-cache.php: advanced-cache.php maintenance.php: maintenance.php ### Settings ### API Enabled: ✔ Force SSL: ✔ Currency: USD ($) Currency Position: left Thousand Separator: , Decimal Separator: . Number of Decimals: 2 Taxonomies: Product Types: bundle (bundle) 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: #5 - /shop/ Cart: #6 - /cart/ Checkout: #7 - /checkout/ My account: #9 - /my-account/ Terms and conditions: #764 - /about-us/terms-policies/ ### Theme ### Name: WooStore Child Version: 0.1 Author URL: http://www.woothemes.com Child Theme: ✔ Parent Theme Name: WooStore Parent Theme Version: 1.8.3 – 1.8.4 is available Parent Theme Author URL: http://www.woothemes.com WooCommerce Support: ✔ ### Templates ### Overrides: woostore-child/single-product/product-image.php version 2.6.3 is out of date. The core version is 3.5.1 woostore-child/single-product/product-thumbnails.php version 2.6.3 is out of date. The core version is 3.5.1 Outdated Templates: ❌ Learn how to update ### Action Scheduler ### Complete: 2,512 Oldest: 2021-04-22 16:46:27 +0000 Newest: 2021-05-23 16:32:38 +0000 ### Status report information ### Generated at: 2021-05-23 08:39:47 -08:00 ```
rodelgc commented 3 years ago

Hi @brandoncc,

Thank you for opening the issue! It requires further feedback from the WooCommerce Core team. I am adding the needs developer feedback label to this issue so that the Core team could take a look.

Please note it may take a few days for them to get to this issue. Thank you for your patience.

Konamiman commented 3 years ago

Hi @brandoncc, thanks for your report, this seems indeed like a bug that needs fixing. For reference, we fixed a similar bug a few months ago.