wc_product_attributes_lookup table missing primary key

orgiz commented 2 years ago

Prerequisites

[X] I have carried out troubleshooting steps and I believe I have found a bug.
[X] I have searched for similar bugs in both open and closed issues and cannot find a duplicate.

Describe the bug

When updating WooCommerce to 6.3 we are getting this error:

Database tables missing
One or more tables required for WooCommerce to function are missing, some features may not work as expected. Missing tables: wp_wc_product_attributes_lookup

We are running Wordpress on a SQL-cluster with sql_require_primary_key = 1; and I think this is the main problem. I think this prevents this table from being created in the first place during the database migration when updating to 6.3. When you are running in cluster it is best practice to always use primary key.

I can fix this problem by creating the table manually with primary key added. But i do think there is many Hosting companies out there that are forcing primary key to be present in the tables, so a fix for this should be much appreciated.

Expected behavior

CREATE TABLE wp_wc_product_attributes_lookup ( product_id bigint(20) NOT NULL, product_or_parent_id bigint(20) NOT NULL, taxonomy varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL, term_id bigint(20) NOT NULL, is_variation_attribute tinyint(1) NOT NULL, in_stock tinyint(1) NOT NULL, PRIMARY KEY (product_id,product_or_parent_id,taxonomy,term_id), KEY product_or_parent_id_term_id (product_or_parent_id,term_id), KEY is_variation_attribute_term_id (is_variation_attribute,term_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Actual behavior

CREATE TABLE wp_wc_product_attributes_lookup ( product_id bigint(20) NOT NULL, product_or_parent_id bigint(20) NOT NULL, taxonomy varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL, term_id bigint(20) NOT NULL, is_variation_attribute tinyint(1) NOT NULL, in_stock tinyint(1) NOT NULL, KEY product_or_parent_id_term_id (product_or_parent_id,term_id), KEY is_variation_attribute_term_id (is_variation_attribute,term_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Steps to reproduce

Update to 6.3 with SQL server setting sql_require_primary_key = 1;

WordPress Environment

WordPress address (URL): Site address (URL): WC Version: 6.3.1 REST API Version: ✔ 6.3.1 WC Blocks Version: ✔ 6.9.0 Action Scheduler Version: ✔ 3.4.0 WC Admin Version: ✔ 3.2.1 Log Directory Writable: ✔ WP Version: 5.9.2 WP Multisite: – WP Memory Limit: 512 MB WP Debug Mode: – WP Cron: ✔ Language: sv_SE External object cache: ✔

Server Environment

Server Info: Microsoft-IIS/10.0 PHP Version: 7.4.14 PHP Post Max Size: 256 MB PHP Time Limit: 1200 PHP Max Input Vars: 5000 cURL Version: 7.70.0 OpenSSL/1.1.1i

SUHOSIN Installed: – MySQL Version: 5.5.5-10.5.5-MariaDB-1:10.5.5+maria~focal Max Upload Size: 256 MB Default Timezone is UTC: ✔ fsockopen/cURL: ✔ SoapClient: ✔ DOMDocument: ✔ GZip: ✔ Multibyte String: ✔ Remote Post: ✔ Remote Get: ✔

Database

WC Database Version: 6.3.1 WC Database Prefix: wp_ Total Database Size: 92.97MB Database Data Size: 62.65MB Database Index Size: 30.32MB wp_woocommerce_sessions: Data: 0.02MB + Index: 0.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.17MB + Engine InnoDB wp_woocommerce_order_items: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_woocommerce_order_itemmeta: Data: 0.02MB + Index: 0.05MB + Engine InnoDB wp_woocommerce_tax_rates: Data: 0.02MB + Index: 0.08MB + 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.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.03MB + Engine InnoDB wp_woocommerce_payment_tokenmeta: Data: 0.02MB + Index: 0.05MB + Engine InnoDB wp_woocommerce_log: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_actionscheduler_actions: Data: 0.17MB + Index: 0.22MB + 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: 0.14MB + Index: 0.13MB + Engine InnoDB wp_aiowps_events: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_aiowps_failed_logins: Data: 1.52MB + Index: 0.00MB + Engine InnoDB wp_aiowps_global_meta: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_aiowps_login_activity: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_aiowps_login_lockdown: Data: 0.16MB + Index: 0.00MB + Engine InnoDB wp_aiowps_permanent_block: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_commentmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_comments: Data: 0.02MB + Index: 0.09MB + Engine InnoDB wp_e_submissions: Data: 0.02MB + Index: 0.27MB + Engine InnoDB wp_e_submissions_actions_log: Data: 0.02MB + Index: 0.11MB + Engine InnoDB wp_e_submissions_values: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_links: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_mailchimp_carts: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_mailchimp_jobs: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_nextend2_image_storage: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_nextend2_section_storage: Data: 0.05MB + Index: 0.06MB + Engine InnoDB wp_nextend2_smartslider3_generators: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_nextend2_smartslider3_sliders: Data: 0.05MB + Index: 0.03MB + Engine InnoDB wp_nextend2_smartslider3_sliders_xref: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_nextend2_smartslider3_slides: Data: 0.13MB + Index: 0.30MB + Engine InnoDB wp_options: Data: 5.14MB + Index: 1.08MB + Engine InnoDB wp_postmeta: Data: 32.56MB + Index: 16.03MB + Engine InnoDB wp_posts: Data: 5.52MB + Index: 4.61MB + Engine InnoDB wp_rank_math_404_logs: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_rank_math_analytics_objects: Data: 0.08MB + Index: 0.02MB + Engine InnoDB wp_rank_math_internal_links: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_rank_math_internal_meta: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_rank_math_redirections: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_rank_math_redirections_cache: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_sbi_feeds: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_sbi_feed_caches: Data: 0.11MB + Index: 0.02MB + Engine InnoDB wp_sbi_instagram_feeds_posts: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_sbi_instagram_feed_locator: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_sbi_instagram_posts: Data: 0.06MB + Index: 0.00MB + Engine InnoDB wp_sbi_sources: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_sm_advanced_search_temp: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_termmeta: Data: 0.09MB + Index: 0.13MB + Engine InnoDB wp_terms: Data: 0.05MB + Index: 0.03MB + Engine InnoDB wp_term_relationships: Data: 1.52MB + Index: 1.52MB + Engine InnoDB wp_term_taxonomy: Data: 0.05MB + Index: 0.03MB + Engine InnoDB wp_usermeta: Data: 0.06MB + Index: 0.03MB + Engine InnoDB wp_users: Data: 0.02MB + Index: 0.05MB + 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: 0.02MB + Index: 0.05MB + Engine InnoDB wp_wc_download_log: Data: 0.02MB + Index: 0.05MB + Engine InnoDB wp_wc_order_coupon_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_wc_order_product_lookup: Data: 0.02MB + Index: 0.11MB + Engine InnoDB wp_wc_order_stats: Data: 0.02MB + Index: 1.81MB + Engine InnoDB wp_wc_order_tax_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB wp_wc_product_meta_lookup: Data: 1.52MB + Index: 1.22MB + Engine InnoDB wp_wc_rate_limits: Data: 0.02MB + Index: 0.02MB + 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.03MB + Engine InnoDB wp_wdp_orders: Data: 0.02MB + Index: 0.11MB + Engine InnoDB wp_wdp_order_items: Data: 0.02MB + Index: 0.16MB + Engine InnoDB wp_wdp_persistent_rules_cache: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_wdp_rules: Data: 0.02MB + Index: 0.06MB + Engine InnoDB wp_wfblockediplog: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_wfblocks7: Data: 0.02MB + Index: 0.05MB + Engine InnoDB wp_wfconfig: Data: 1.27MB + Index: 0.00MB + Engine InnoDB wp_wfcrawlers: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_wffilechanges: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_wffilemods: Data: 6.52MB + Index: 0.00MB + Engine InnoDB wp_wfhits: Data: 1.02MB + Index: 0.05MB + Engine InnoDB wp_wfhoover: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_wfissues: Data: 0.06MB + Index: 0.06MB + Engine InnoDB wp_wfknownfilelist: Data: 2.52MB + Index: 0.00MB + Engine InnoDB wp_wflivetraffichuman: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_wflocs: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_wflogins: Data: 0.45MB + Index: 0.17MB + Engine InnoDB wp_wfls_2fa_secrets: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_wfls_settings: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_wfnotifications: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_wfpendingissues: Data: 0.02MB + Index: 0.06MB + Engine InnoDB wp_wfreversecache: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_wfsnipcache: Data: 0.02MB + Index: 0.05MB + Engine InnoDB wp_wfstatus: Data: 0.13MB + Index: 0.09MB + Engine InnoDB wp_wftrafficrates: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_wpf_filters: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_wpf_modules: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_wpf_modules_type: Data: 0.02MB + Index: 0.00MB + Engine InnoDB wp_wpf_usage_stat: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_wpr_rucss_resources: Data: 0.02MB + Index: 0.06MB + Engine InnoDB wp_wpr_rucss_used_css: Data: 0.02MB + Index: 0.05MB + Engine InnoDB wp_yoast_indexable: Data: 0.02MB + Index: 0.05MB + 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.02MB + Index: 0.03MB + Engine InnoDB wp_yoast_seo_links: Data: 0.02MB + Index: 0.02MB + Engine InnoDB wp_yoast_seo_meta: Data: 0.02MB + Index: 0.00MB + Engine InnoDB

Post Type Counts

attachment: 4381 custom_css: 3 elementor_library: 13 gp_elements: 2 jetpack_migration: 2 jp_img_sitemap: 6 jp_img_sitemap_index: 2 jp_sitemap: 2 jp_sitemap_master: 2 nav_menu_item: 59 oceanwp_library: 1 page: 19 post: 2 product: 269 product_variation: 7043 revision: 4 shop_coupon: 3 shop_order: 3 wc_conditional_fee: 1 wcpa_pt_forms: 5

Security

Secure connection (HTTPS): ✔ Hide errors from visitors: ✔

Active Plugins (34)

ManageWP - Worker: by GoDaddy – 4.9.13 Advanced Dynamic Pricing for WooCommerce: by AlgolPlus – 4.0.3 GDPR Cookie Consent: by WebToffee – 2.1.1 Disable Comments: by WPDeveloper – 2.3.3 Easy Upload Files During Checkout: by Fahad Mahmood – 2.8.2 Elementor Pro: by Elementor.com – 3.6.2 Elementor: by Elementor.com – 3.5.5 Envato Market: by Envato – 2.0.7 GP Premium: by Tom Usborne – 2.1.2 Smash Balloon Instagram Feed: by Smash Balloon – 6.0.4 Jetpack: by Automattic – 10.6 Klarna Checkout for WooCommerce: by Krokedil – 2.6.4 Klarna Order Management for WooCommerce: by klarna krokedil – 1.6.8

Livemesh SiteOrigin Widgets: by Livemesh – 2.8.3 Mailchimp for WooCommerce: by Mailchimp – 2.5.4 Ocean Extra: by OceanWP – 1.9.4 Really Simple SSL: by Really Simple Plugins – 5.2.3 Redis Object Cache: by Till Krüss – 2.0.23 reSmush.it Image Optimizer: by reSmush.it – 0.4.2 Rank Math SEO: by Rank Math – 1.0.83 Page Builder by SiteOrigin: by SiteOrigin – 2.16.4 Smart Manager For WooCommerce – Stock Management, Bulk Edit & more...: by StoreApps – 5.36.0 Smart Slider 3: by Nextend – 3.5.1.3 SiteOrigin CSS: by SiteOrigin – 1.5.2 SiteOrigin Widgets Bundle: by SiteOrigin – 1.30.1 WooCommerce Custom Product Addons (Free): by Acowebs – 2.6.0 WooSwatches - Woocommerce Color or Image Variation Swatches: by WooMatrix – 3.4.5 WooCommerce Customizer: by SkyVerge – 2.7.6 WooCommerce Shipping & Tax: by WooCommerce – 1.25.23 WooCommerce: by Automattic – 6.3.1 Woomatrix Multiple Variation Images: by woomatrix – 1.3.2 Wordfence Security: by Wordfence – 7.5.8 WP Rocket: by WP Media – 3.10.6 Custom Product Tabs for WooCommerce: by YIKES Inc. – 1.7.7

Inactive Plugins (1)

MCE Table Buttons: by Jake Goldman 10up Oomph – 3.3.1

Dropin Plugins (2)

advanced-cache.php: advanced-cache.php object-cache.php: Redis Object Cache Drop-In

Must Use Plugins (1)

ManageWP - Worker Loader: by GoDaddy –

Settings

API Enabled: – Force SSL: – Currency: SEK (kr) Currency Position: right 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: #617 - /butik/ Cart: #618 - /varukorg/ Checkout: #619 - /kassan/ My account: #620 - /mitt-konto/ Terms and conditions: #12573 - /kopvillkor/

Theme

Name: GeneratePress Child Version: 2.4.2.1593680667 Author URL: https://tomusborne.com Child Theme: ✔ Parent Theme Name: GeneratePress Parent Theme Version: 3.1.3 Parent Theme Author URL: https://tomusborne.com WooCommerce Support: ✔

Templates

Overrides: –

Klarna Checkout Request Log

No KCO errors:

Action Scheduler

Complete: 528 Oldest: 2022-02-17 11:28:06 +0100 Newest: 2022-03-14 10:51:21 +0100

Pending: 2 Oldest: 2022-03-14 10:51:22 +0100 Newest: 2022-03-17 11:32:07 +0100

Status report information

Generated at: 2022-03-14 10:51:22 +01:00 `

Isolating the problem

[X] I have deactivated other plugins and confirmed this bug occurs when only WooCommerce plugin is active.
[X] This bug happens with a default WordPress theme active, or Storefront.
[X] I can reproduce this bug consistently using the steps above.

woocommercebot commented 2 years ago

Hi @orgiz,

Thank you for opening the issue! It requires further feedback from the WooCommerce Core team.

We are 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.

peterfabian commented 2 years ago

Hi, thanks for reporting! We're already working on a fix here: https://github.com/woocommerce/woocommerce/pull/32067.

If you want to create the table yourself in the meantime, you should be able to use this query (please use the correct db prefix and collation for your db, though):

CREATE TABLE {$wpdb->prefix . 'wc_product_attributes_lookup'} (
 product_id bigint(20) NOT NULL,
 product_or_parent_id bigint(20) NOT NULL,
 taxonomy varchar(32) NOT NULL,
 term_id bigint(20) NOT NULL,
 is_variation_attribute tinyint(1) NOT NULL,
 in_stock tinyint(1) NOT NULL,
 INDEX is_variation_attribute_term_id (is_variation_attribute, term_id),
 PRIMARY KEY  ( `product_or_parent_id`, `term_id`, `product_id`, `taxonomy` )
)

peterfabian commented 2 years ago

Closing since the fix is merged and going to be shipped with WC 6.4.

woocommerce / woocommerce