Product grid blocks database error

Resolved Francisco
(@fmarconi)

7 months, 3 weeks ago

Hello!
All of my sites have an issue with last Woocommerce update 6.0.0
Although everything works, all the ranked products grid blocks throws the database error message copied below.
I have a fresh install only StoreFront and WooCommerce test site with debug mode enabled here
Regards,
Francisco
[28-Dec-2021 18:21:56 UTC] WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') AND meta_key LIKE 'attribute\\_%'' at line 1 for query SELECT post_id as variation_id, meta_key as attribute_key, meta_value as attribute_value FROM test_s_w_postmeta WHERE post_id IN () AND meta_key LIKE 'attribute\\_%' made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/storefront/page.php'), get_template_part, locate_template, load_template, require('/themes/storefront/content-page.php'), do_action('storefront_page'), WP_Hook->do_action, WP_Hook->apply_filters, storefront_page_content, the_content, apply_filters('the_content'), WP_Hook->apply_filters, do_blocks, render_block, WP_Block->render, Automattic\WooCommerce\Blocks\BlockTypes\AbstractBlock->render_callback, Automattic\WooCommerce\Blocks\BlockTypes\AbstractProductGrid->render, Automattic\WooCommerce\Blocks\BlockTypes\AbstractProductGrid->get_products, Automattic\WooCommerce\Blocks\BlockTypes\AbstractProductGrid->prime_product_variations

The page I need help with: [log in to see the link]

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

Plugin Support

(@rainfallnixfig)

Let’s see if updating and verifying the database helps. Please go to WooCommerce > Status > Tools and run these commands:

Link to image: https://i.imgur.com/JE28XH3.png

Make sure to save a full backup before running the tools.

If further assistance is required it might be a good idea to share a copy of your site’s System Status. You can find it via WooCommerce > Status. Select “Get system report” and then “Copy for support”. Once you’ve done that, paste it here in your response.

Thanks.

Thread Starter

Francisco

(@fmarconi)

7 months, 3 weeks ago

Hi @rainfallnixfig , thanks for your help.
Updating and verifying the database didn’t help. :C
Here’s my system report (a fresh install):


### WordPress Environment ###

WordPress address (URL): https://web-pyme.cl/test-storefront-woocommerce
Site address (URL): https://web-pyme.cl/test-storefront-woocommerce
WC Version: 6.0.0
REST API Version: ✔ 6.0.0
WC Blocks Version: ✔ 6.3.3
Action Scheduler Version: ✔ 3.3.0
WC Admin Version: ✔ 2.9.4
Log Directory Writable: ✔
WP Version: 5.8.2
WP Multisite: –
WP Memory Limit: 256 MB
WP Debug Mode: ✔
WP Cron: ✔
Language: en_US
External object cache: –

### Server Environment ###

Server Info: LiteSpeed
PHP Version: 7.4.27
PHP Post Max Size: 200 MB
PHP Time Limit: 3000
PHP Max Input Vars: 10000
cURL Version: 7.71.0
OpenSSL/1.1.1d

SUHOSIN Installed: –
MySQL Version: 5.5.5-10.3.32-MariaDB
Max Upload Size: 200 MB
Default Timezone is UTC: ✔
fsockopen/cURL: ✔
SoapClient: ✔
DOMDocument: ✔
GZip: ✔
Multibyte String: ✔
Remote Post: ✔
Remote Get: ❌ wp_remote_get() failed. Contact your hosting provider.

### Database ###

WC Database Version: 6.0.0
WC Database Prefix: test_s_w_
Total Database Size: 3.82MB
Database Data Size: 2.40MB
Database Index Size: 1.42MB
test_s_w_woocommerce_sessions: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
test_s_w_woocommerce_api_keys: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
test_s_w_woocommerce_attribute_taxonomies: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
test_s_w_woocommerce_downloadable_product_permissions: Data: 0.02MB + Index: 0.06MB + Engine InnoDB
test_s_w_woocommerce_order_items: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
test_s_w_woocommerce_order_itemmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
test_s_w_woocommerce_tax_rates: Data: 0.02MB + Index: 0.06MB + Engine InnoDB
test_s_w_woocommerce_tax_rate_locations: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
test_s_w_woocommerce_shipping_zones: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
test_s_w_woocommerce_shipping_zone_locations: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
test_s_w_woocommerce_shipping_zone_methods: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
test_s_w_woocommerce_payment_tokens: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
test_s_w_woocommerce_payment_tokenmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
test_s_w_woocommerce_log: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
test_s_w_actionscheduler_actions: Data: 0.02MB + Index: 0.13MB + Engine InnoDB
test_s_w_actionscheduler_claims: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
test_s_w_actionscheduler_groups: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
test_s_w_actionscheduler_logs: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
test_s_w_commentmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
test_s_w_comments: Data: 0.02MB + Index: 0.09MB + Engine InnoDB
test_s_w_links: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
test_s_w_options: Data: 1.47MB + Index: 0.03MB + Engine InnoDB
test_s_w_postmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
test_s_w_posts: Data: 0.06MB + Index: 0.06MB + Engine InnoDB
test_s_w_termmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
test_s_w_terms: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
test_s_w_term_relationships: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
test_s_w_term_taxonomy: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
test_s_w_usermeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
test_s_w_users: Data: 0.02MB + Index: 0.05MB + Engine InnoDB
test_s_w_wc_admin_notes: Data: 0.05MB + Index: 0.00MB + Engine InnoDB
test_s_w_wc_admin_note_actions: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
test_s_w_wc_category_lookup: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
test_s_w_wc_customer_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
test_s_w_wc_download_log: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
test_s_w_wc_order_coupon_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
test_s_w_wc_order_product_lookup: Data: 0.02MB + Index: 0.06MB + Engine InnoDB
test_s_w_wc_order_stats: Data: 0.02MB + Index: 0.05MB + Engine InnoDB
test_s_w_wc_order_tax_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
test_s_w_wc_product_meta_lookup: Data: 0.02MB + Index: 0.09MB + Engine InnoDB
test_s_w_wc_rate_limits: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
test_s_w_wc_reserved_stock: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
test_s_w_wc_tax_rate_classes: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
test_s_w_wc_webhooks: Data: 0.02MB + Index: 0.02MB + Engine InnoDB

### Post Type Counts ###

attachment: 1
customize_changeset: 1
nav_menu_item: 8
page: 13
post: 2
product: 3
revision: 6

### Security ###

Secure connection (HTTPS): ✔
Hide errors from visitors: ❌Error messages should not be shown to visitors.

### Active Plugins (1) ###

WooCommerce: by Automattic – 6.0.0

### Inactive Plugins (2) ###

Akismet Anti-Spam: by Automattic – 4.2.1
Hello Dolly: by Matt Mullenweg – 1.7.2

### 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: #23 - /shop/
Cart: #24 - /cart/
Checkout: #25 - /checkout/
My account: #26 - /my-account/
Terms and conditions: ❌ Page not set

### Theme ###

Name: Storefront
Version: 3.9.1
Author URL: https://woocommerce.com/
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: 6
Oldest: 2021-12-28 18:16:06 +0000
Newest: 2021-12-29 00:13:28 +0000

### Status report information ###

Generated at: 2021-12-29 00:16:15 +00:00

mother.of.code

(@imazed)

The Mother of Code

7 months, 3 weeks ago

Hey @fmarconi – have you tried removing the block, saving the page, and then re-adding the block back in?

Thread Starter

Francisco

(@fmarconi)

7 months, 3 weeks ago

@imazed Hi! Yes, I did, and the message still there. And in the editor I get Error loading block: The response is not a valid JSON response.

Plugin Support

Mirko P. woo-hc

(@rainfallnixfig)

7 months, 3 weeks ago

Hi @fmarconi,

Thanks for sending over the error message.

Could you install the standalone WooCommerce Blocks plugin and see if it helps? You can download the plugin from this URL: https://wordpress.org/plugins/woo-gutenberg-products-block/.

If you see the server responded with a status of 502 () message in the browser inspector tools, it could also be an indication that it has something to do with the host. Since the error refers to the database it would be probably a good idea to get in touch with your host’s support.

Thanks.

Thread Starter

Francisco

(@fmarconi)

7 months, 2 weeks ago

EDIT:
I also solved putting the following code on child theme functions:

add_filter('query', 'return_fake_variation_id');
function return_fake_variation_id($sql){
	if (str_contains(wp_debug_backtrace_summary(),"prime_product_variations")) {
		return "SELECT '' as variation_id FROM DUAL";
	}
    return $sql;
}

(PHP 8.0)

@rainfallnixfig, thank you, I follow your indications. The WooCommerce Blocks plugin doesn’t fix the issue and my hosting provider says I’m using deprecated syntax.
I’ve found the issue is on WooCommerce repo
and follow the temporarly fix proposed on \woocommerce\packages\woocommerce-blocks\src\BlockTypes\AbstractProductGrid.php adding the follow code on line 364:

if (empty($prime_variation_ids)){
	return;
}

Regards,
Francisco

This reply was modified 7 months, 2 weeks ago by Francisco. Reason: Other solution

Plugin Support

Juan G. woo-hc

(@judagutor)

7 months, 2 weeks ago

Hi Francisco!

Glad to hear you solved it! Thanks for sharing the solution with everybody.

I’ll mark this thread as resolved now. If you have any further questions, I recommend creating a new one.

Cheers!

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

The topic ‘Product grid blocks database error’ is closed to new replies.

Product grid blocks database error

Topic Tags

Views