Support » Plugin: WooCommerce » Product grid blocks database error

  • Resolved Francisco

    (@fmarconi)


    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 Mirko P. woo-hc

    (@rainfallnixfig)

    Hi @fmarconi,

    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)

    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

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

    Thread Starter Francisco

    (@fmarconi)

    @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)

    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)

    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)

    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.