search

woocommerce / woocommerce-admin

(Deprecated) This plugin has been merged to woocommerce/woocommerce
https://woocommerce.github.io/woocommerce-admin/#/
Other
360 stars 147 forks source link

WordPress deadlock error for `_wc_category_lookup` #4485

Closed jessLundie closed 3 years ago

jessLundie commented 4 years ago

Describe the bug Site is experiencing repeated deadlock errors since updating to WC 4.1.1 related to _wc_category_lookup.

Related ticket: 2994984-zen

WP Cron Info WP Crontrol shows that generate_category_lookup_table is running every 10 seconds, even though it says it is "non-recurring."

Running the event manually results in it returning. When you delete it, it also reappears and adds a second event:

https://d.pr/i/7xf3XK Full Image: https://d.pr/i/7xf3XK

Errors

[21-May-2020 14:33:51 UTC] WordPress database error Deadlock found when trying to get lock; try restarting transaction for query INSERT IGNORE INTO wp_wc_category_lookup (category_tree_id,category_id) VALUES (425,..... made by do_action_ref_array('generate_category_lookup_table'), WP_Hook->do_action, WP_Hook->apply_filters, Automattic\WooCommerce\Admin\CategoryLookup->regenerate
[21-May-2020 17:18:29 UTC] WordPress database error Deadlock found when trying to get lock; try restarting transaction for query
SELECT
wp_wc_category_lookup.category_tree_id as category_id, SUM(product_qty) as items_sold, SUM(product_net_revenue) AS net_revenue, COUNT(DISTINCT wp_wc_order_product_lookup.order_id) as orders_count, COUNT(DISTINCT wp_wc_order_product_lookup.product_id) as products_count
FROM
wp_wc_order_product_lookup
JOIN wp_wc_order_stats ON wp_wc_order_product_lookup.order_id = wp_wc_order_stats.order_id LEFT JOIN wp_term_relationships ON wp_wc_order_product_lookup.product_id = wp_term_relationships.object_id LEFT JOIN wp_wc_category_lookup ON wp_term_relationships.term_taxonomy_id = wp_wc_category_lookup.category_id
WHERE
1=1
AND ( wp_wc_order_stats.status NOT IN ( 'wc-trash','wc-pending','wc-failed','wc-cancelled' ) ) AND wp_wc_category_lookup.category_tree_id IS NOT NULL AND wp_wc_order_product_lookup.date_created <= '2020-05-21 23:59:59' AND wp_wc_order_product_lookup.date_created >= '2020-05-01 00:00:00'
GROUP BY
wp_wc_category_lookup.category_tree_id
ORDER BY
items_sold desc
made by require('wp-blog-header.php'), wp, WP->main, WP->parse_request, do_action_ref_array('parse_request'), WP_Hook->do_action, WP_Hook->apply_filters, rest_api_loaded, WP_REST_Server->serve_request, WP_REST_Server->dispatch, Automattic\WooCommerce\Admin\API\Leaderboards->get_items, Automattic\WooCommerce\Admin\API\Leaderboards->get_leaderboards, Automattic\WooCommerce\Admin\API\Leaderboards->get_categories_leaderboard, Automattic\WooCommerce\Admin\API\Reports\Categories\DataStore->get_data

Site Status

``` ### WordPress Environment ### WordPress address (URL): Site address (URL): WC Version: 4.1.1 REST API Version: ✔ 1.0.7 WC Blocks Version: ✔ 2.5.16 Action Scheduler Version: ✔ 3.1.6 WC Admin Version: ✔ 1.1.3 Log Directory Writable: ✔ WP Version: 5.4.1 WP Multisite: – WP Memory Limit: 584 MB WP Debug Mode: – WP Cron: ✔ Language: en_US External object cache: – ### Server Environment ### Server Info: Apache PHP Version: 7.2.31 PHP Post Max Size: 200 MB PHP Time Limit: 120 PHP Max Input Vars: 2000 cURL Version: 7.70.0 OpenSSL/1.1.1g SUHOSIN Installed: – MySQL Version: 5.5.5-10.1.20-MariaDB Max Upload Size: 200 MB Default Timezone is UTC: ✔ fsockopen/cURL: ✔ SoapClient: ❌ Your server does not have the SoapClient class enabled - some gateway plugins which use SOAP may not work as expected. DOMDocument: ✔ GZip: ✔ Multibyte String: ✔ Remote Post: ✔ Remote Get: ✔ ### Database ### WC Database Version: 4.1.1 WC Database Prefix: wp_ Total Database Size: 442.99MB Database Data Size: 309.85MB Database Index Size: 133.14MB wp_woocommerce_sessions: Data: 6.02MB + Index: 0.08MB + 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: 3.45MB + Index: 5.77MB + Engine InnoDB wp_woocommerce_order_items: Data: 1.52MB + Index: 0.28MB + Engine InnoDB wp_woocommerce_order_itemmeta: Data: 12.50MB + Index: 7.94MB + Engine InnoDB wp_woocommerce_tax_rates: Data: 0.02MB + Index: 0.06MB + Engine InnoDB wp_woocommerce_tax_rate_locations: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_woocommerce_shipping_zones: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_woocommerce_shipping_zone_locations: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_woocommerce_shipping_zone_methods: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_woocommerce_payment_tokens: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_woocommerce_payment_tokenmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_woocommerce_log: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_actionscheduler_actions: Data: 2.03MB + Index: 1.08MB + 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: 2.42MB + Index: 2.72MB + Engine InnoDB wp_apmm_custom_theme: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_commentmeta: Data: 0.13MB + Index: 0.11MB + Engine InnoDB wp_comments: Data: 9.52MB + Index: 8.95MB + Engine InnoDB wp_layerslider: Data: 0.17MB + Index: 0.00MB + Engine InnoDB wp_layerslider_revisions: Data: 0.34MB + Index: 0.00MB + Engine InnoDB wp_links: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_options: Data: 10.11MB + Index: 5.53MB + Engine InnoDB wp_pimwick_gift_card: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_pimwick_gift_card_activity: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_pmxi_files: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_pmxi_history: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_pmxi_images: Data: 1.52MB + Index: 0.00MB + Engine InnoDB wp_pmxi_imports: Data: 0.20MB + Index: 0.00MB + Engine InnoDB wp_pmxi_posts: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_pmxi_templates: Data: 0.08MB + Index: 0.00MB + Engine InnoDB wp_postmeta: Data: 164.80MB + Index: 60.05MB + Engine InnoDB wp_posts: Data: 36.55MB + Index: 10.42MB + Engine InnoDB wp_termmeta: Data: 0.11MB + Index: 0.06MB + Engine InnoDB wp_terms: Data: 0.05MB + Index: 0.03MB + Engine InnoDB wp_term_relationships: Data: 1.28MB + Index: 1.39MB + Engine InnoDB wp_term_taxonomy: Data: 0.06MB + Index: 0.03MB + Engine InnoDB wp_usermeta: Data: 22.48MB + Index: 16.77MB + Engine InnoDB wp_users: Data: 1.50MB + Index: 1.08MB + Engine InnoDB wp_wcpv_commissions: Data: 2.52MB + Index: 0.00MB + Engine InnoDB wp_wcpv_per_product_shipping_rules: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_wc_admin_notes: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_wc_admin_note_actions: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_wc_category_lookup: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_wc_customer_lookup: Data: 1.50MB + Index: 0.67MB + Engine InnoDB wp_wc_download_log: Data: 2.44MB + Index: 1.66MB + Engine InnoDB wp_wc_order_coupon_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_wc_order_product_lookup: Data: 1.52MB + Index: 1.23MB + Engine InnoDB wp_wc_order_stats: Data: 1.52MB + Index: 0.64MB + Engine InnoDB wp_wc_order_tax_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_wc_product_meta_lookup: Data: 0.19MB + Index: 0.38MB + 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_software_activations: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_woocommerce_software_licence: Data: 0.34MB + Index: 0.00MB + Engine InnoDB wp_yoast_indexable: Data: 19.55MB + Index: 5.55MB + Engine InnoDB wp_yoast_indexable_hierarchy: Data: 0.02MB + Index: 0.05MB + Engine InnoDB wp_yoast_migrations: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_yoast_primary_term: Data: 0.11MB + Index: 0.11MB + Engine InnoDB wp_yoast_seo_links: Data: 0.22MB + Index: 0.08MB + Engine InnoDB wp_yoast_seo_meta: Data: 2.50MB + Index: 0.00MB + Engine InnoDB ### Post Type Counts ### acf: 1 acf-field: 1 acf-field-group: 1 attachment: 23545 custom_css: 2 customize_changeset: 2 et_pb_layout: 6 fast_ticket: 408 fast-plugin-stack: 1 jetpack_migration: 2 nav_menu_item: 144 oembed_cache: 69 page: 25 post: 4 product: 1581 product_variation: 11 revision: 2048 shop_coupon: 83 shop_order: 9016 shop_order_refund: 32 woo_product_tab: 3 ywtm_tab: 1 ### Security ### Secure connection (HTTPS): ✔ Hide errors from visitors: ✔ ### Active Plugins (36) ### Divi Ultimate Header Plugin: by Divi Ultimate – 3.0.1 LayerSlider WP: by Kreatura Media – 6.11.1 ACF Content Analysis for Yoast SEO: by Thomas Kräftner ViktorFroberg marol87 pekz0r angrycreative Team Yoast – 2.3.0 Advanced Custom Fields: by Elliot Condon – 5.8.11 Authorize.net Payment Gateway For WooCommerce: by Eshan Varma – 5.3.3 Bulk Regenerate Download Permissions for WooCommerce Orders Pro: by WP Wham – 1.3.0-beta1 – Installed version not tested with active version of WooCommerce 4.1.1 PublishPress Capabilities: by PublishPress – 1.9.10 Classic Editor: by WordPress Contributors – 1.5 Current Username on Navigation Label: by Al-Mamun Talukder – 1.0.0 Divi Children: by Luis Alejandre – 3.0.9 Enhanced Text Widget: by Clever Widgets – 1.5 Fast Plugin: by Oxygenna – 1.15.2 FSC Vendor Activation Plugin: by Flying Circus – 1.1 FSC Vendor GetKey Plugin: by Flying Circus – 1.1 Google Website Translator: by Prisna – 1.4.3 Nav Menu Roles: by Kathy Darling – 1.10.1 Public Post Preview: by Dominik Schilling – 2.9.0 PW WooCommerce Gift Cards: by Pimwick LLC – 1.129 Regenerate Thumbnails: by Alex Mills (Viper007Bond) – 3.1.3 WooCommerce Product Gallery Slider (Twist): by codeixer – 2.1.0.1 WC Search Orders By Product: by WPHEKA – 1.4 – Installed version not tested with active version of WooCommerce 4.1.1 WPC Product Bundles for WooCommerce (Premium): by WPClever.net – 5.1.3 WooCommerce Admin: by WooCommerce – 1.1.3 WooCommerce Checkout Manager: by QuadLayers – 5.0.6 – Installed version not tested with active version of WooCommerce 4.1.1 WooCommerce Conversion Tracking: by weDevs – 2.0.7 – Installed version not tested with active version of WooCommerce 4.1.1 WooCommerce PayPal Checkout Gateway: by WooCommerce – 2.0.2 WooCommerce Product Tabs: by Nilambar Sharma – 2.0.2 WooCommerce Product Vendors: by WooCommerce – 2.1.33 WooCommerce Software License: by Nsp Code – 1.9.2.4 – Installed version not tested with active version of WooCommerce 4.1.1 WooCommerce: by Automattic – 4.1.1 Yoast SEO: by Team Yoast – 14.2 WP All Import Pro: by Soflyy – 4.6.1 WP Crontrol: by John Blackbourn & crontributors – 1.8.5 WP Mail SMTP: by WPForms – 2.0.1 WP All Import - ACF Add-On: by Soflyy – 3.2.6 WP All Import - WooCommerce Add-On Pro: by Soflyy – 3.2.2 ### Inactive Plugins (0) ### ### Must Use Plugins (1) ### ET Support Center :: Must-Use Plugins Autoloader: by Elegant Themes – ### 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) pw gift card (pw-gift-card) simple (simple) variable (variable) woosb (woosb) 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: #52 - /catalog/ Cart: #223 - /cart/ Checkout: #225 - /checkout/ My account: #229 - /my-account/ Terms and conditions: #43595 - /terms-and-conditions/ ### Theme ### Name: FlightSim.Com Store Version: 1.0 Author URL: https://store.flightsim.com/shop Child Theme: ✔ Parent Theme Name: Divi Parent Theme Version: 4.4.7 Parent Theme Author URL: http://www.elegantthemes.com WooCommerce Support: ✔ ### Templates ### Overrides: – ### Action Scheduler ### Complete: 4,160 Oldest: 2020-05-02 19:34:12 -0400 Newest: 2020-06-02 19:25:29 -0400 Pending: 1 Oldest: 2020-06-02 20:25:29 -0400 Newest: 2020-06-02 20:25:29 -0400 ```
becdetat commented 4 years ago

There is an additional error reported:

[03-Jun-2020 15:26:58 UTC] WordPress database error Table 'wp_wc_admin_notes' already exists for query
CREATE TABLE wp_wc_admin_notes (
note_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
type varchar(20) NOT NULL,
locale varchar(20) NOT NULL,
title longtext NOT NULL,
content longtext NOT NULL,
icon varchar(200) NOT NULL,
content_data longtext NULL default null,
status varchar(200) NOT NULL,
source varchar(200) NOT NULL,
date_created datetime NOT NULL default '0000-00-00 00:00:00',
date_reminder datetime NULL default null,
is_snoozable boolean DEFAULT 0 NOT NULL,
PRIMARY KEY (note_id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci made by require('wp-blog-header.php'), require_once('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), do_action('init'), WP_Hook->do_action, WP_Hook->apply_filters, Automattic\WooCommerce\Admin\Install::check_version, Automattic\WooCommerce\Admin\Install::install, Automattic\WooCommerce\Admin\Install::create_tables, dbDelta

I think what's happening is the wcadmin version isn't being updated correctly for some reason, possibly a race condition between the plugin and the core builds. This means that the wcadmin install steps are being performed on some requests, recreating the generate_category_lookup_table task many times leading to the deadlock. I'm not sure how that would be causing the table creation issue though as it should only run the deltas through dbDelta.

Still looking in to this.

becdetat commented 4 years ago

@jessLundie I can't reproduce this however I think the following may help:

  1. deactivate the WooCommerce Admin plugin
  2. execute this SQL: DELETE FROM wp_options WHERE option_name = 'woocommerce_admin_version'
  3. refresh the page
  4. activate the WooCommerce Admin plugin

This should force a run through the WooCommerce Admin install steps without a race condition, hopefully clearing up the problem.

becdetat commented 3 years ago

Hi @jessLundie, has this been resolved?

jessLundie commented 3 years ago

Hi @becdetat - thanks so much for following up on this! We never heard back from the user on this and the ticket self-closed in June. I'm hoping that means this is solved (at least for this customer).

becdetat commented 3 years ago

@jessLundie Thanks, I'll close this then :-)