After 6.3.0 – Missing base tables: wp_wc_product_attributes_lookup

  • Resolved shopro

    (@shopro)


    2 years, 8 months ago

    After upgrading Woocommerce from 6.2.1 to 6.3.0 we are getting an error:
    “Missing base tables: wp_wc_product_attributes_lookup. Some WooCommerce functionality may not work as expected.”

    I have tried reinstalling Woocommerce, rolling back etc but the problem doesn’t seem to go away. On our staging site (exact copy) the problem didn’t happen. Is there a way to manually create the missing table?

    Manually running either “Update database” didn’t fix the problem.
    Running “Verify base database tables” gives an error: “Verifying database… One or more tables are still missing: wp_wc_product_attributes_lookup”

    
### WordPress Environment ###

WordPress address (URL): https://***
Site address (URL): https://***
WC Version: 6.3.0
REST API Version: ? 6.3.0
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.1
WP Multisite: –
WP Memory Limit: 512 MB
WP Debug Mode: –
WP Cron: ?
Language: en_US
External object cache: –

### Server Environment ###

Server Info: Apache/2.4.41 (Ubuntu)
PHP Version: 7.4.3
PHP Post Max Size: 8 MB
PHP Time Limit: 300
PHP Max Input Vars: 1000
cURL Version: 7.68.0
OpenSSL/1.1.1f

SUHOSIN Installed: –
MySQL Version: 8.0.26
Max Upload Size: 2 MB
Default Timezone is UTC: ?
fsockopen/cURL: ?
SoapClient: ?
DOMDocument: ?
GZip: ?
Multibyte String: ?
Remote Post: ?
Remote Get: ?

### Database ###

WC Database Version: 6.3.0
WC Database Prefix: wp_
Total Database Size: 262.13MB
Database Data Size: 178.33MB
Database Index Size: 83.80MB
wp_woocommerce_sessions: Data: 3.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: 0.09MB + Index: 0.02MB + Engine InnoDB
wp_woocommerce_order_itemmeta: Data: 0.39MB + Index: 0.41MB + 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: 0.09MB + Index: 0.13MB + 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.08MB + Index: 0.03MB + Engine InnoDB
wp_as3cf_items: Data: 2.52MB + Index: 2.28MB + Engine InnoDB
wp_c4wp_failed_login_tracking: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_cli_cookie_scan: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_cli_cookie_scan_categories: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_cli_cookie_scan_cookies: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_cli_cookie_scan_url: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_cli_scripts: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_commentmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_comments: Data: 0.31MB + Index: 0.36MB + Engine InnoDB
wp_duplicator_pro_entities: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_duplicator_pro_packages: Data: 12.42MB + Index: 0.02MB + Engine InnoDB
wp_ewwwio_images: Data: 8.52MB + Index: 6.06MB + Engine InnoDB
wp_ewwwio_queue: Data: 0.06MB + Index: 0.05MB + Engine InnoDB
wp_failed_jobs: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_icl_content_status: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_icl_core_status: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_icl_flags: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_icl_languages: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_icl_languages_translations: Data: 0.20MB + Index: 0.14MB + Engine InnoDB
wp_icl_locale_map: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_icl_message_status: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_icl_mo_files_domains: Data: 0.09MB + Index: 0.02MB + Engine InnoDB
wp_icl_node: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_icl_reminders: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_icl_string_batches: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_icl_string_packages: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_icl_string_pages: Data: 2.52MB + Index: 5.03MB + Engine InnoDB
wp_icl_string_positions: Data: 2.52MB + Index: 1.52MB + Engine InnoDB
wp_icl_string_status: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_icl_string_translations: Data: 15.55MB + Index: 9.02MB + Engine InnoDB
wp_icl_string_urls: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_icl_strings: Data: 7.52MB + Index: 14.03MB + Engine InnoDB
wp_icl_translate: Data: 2.48MB + Index: 0.06MB + Engine InnoDB
wp_icl_translate_job: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_icl_translation_batches: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_icl_translation_downloads: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_icl_translation_status: Data: 3.52MB + Index: 0.30MB + Engine InnoDB
wp_icl_translations: Data: 2.52MB + Index: 5.92MB + 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_options: Data: 9.05MB + Index: 0.31MB + Engine InnoDB
wp_postmeta: Data: 71.48MB + Index: 11.64MB + Engine InnoDB
wp_posts: Data: 15.39MB + Index: 5.91MB + Engine InnoDB
wp_queue: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_redirection_404: Data: 0.02MB + Index: 0.05MB + Engine InnoDB
wp_redirection_groups: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_redirection_items: Data: 0.02MB + Index: 0.09MB + Engine InnoDB
wp_redirection_logs: Data: 0.02MB + Index: 0.05MB + Engine InnoDB
wp_relevanssi: Data: 12.55MB + Index: 16.50MB + Engine InnoDB
wp_relevanssi_log: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_relevanssi_stopwords: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_social_users: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_term_relationships: Data: 1.19MB + Index: 0.45MB + Engine InnoDB
wp_term_taxonomy: Data: 0.06MB + Index: 0.06MB + Engine InnoDB
wp_termmeta: Data: 0.17MB + Index: 0.22MB + Engine InnoDB
wp_terms: Data: 0.06MB + Index: 0.03MB + Engine InnoDB
wp_usermeta: Data: 0.33MB + Index: 0.28MB + Engine InnoDB
wp_users: Data: 0.02MB + Index: 0.05MB + 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.09MB + Index: 0.00MB + Engine InnoDB
wp_wc_customer_lookup: Data: 0.06MB + Index: 0.03MB + Engine InnoDB
wp_wc_download_log: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_wc_order_coupon_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_wc_order_product_lookup: Data: 0.06MB + Index: 0.06MB + Engine InnoDB
wp_wc_order_stats: Data: 0.06MB + Index: 0.05MB + Engine InnoDB
wp_wc_order_tax_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_wc_product_meta_lookup: Data: 0.36MB + Index: 0.64MB + 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.02MB + Engine InnoDB
wp_wcpdf_credit_note_number: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_wcpdf_invoice_number: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_wcpdf_packing_slip_number: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_wcpdf_proforma_number: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_woo_shippment_provider: Data: 0.08MB + Index: 0.00MB + Engine InnoDB
wp_woocommerce_shipping_table_rates: Data: 0.05MB + Index: 0.00MB + Engine InnoDB
wp_wpmailsmtp_debug_events: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_wpmailsmtp_tasks_meta: Data: 0.05MB + Index: 0.00MB + Engine InnoDB
wp_wpml_mails: Data: 1.52MB + Index: 0.00MB + Engine InnoDB
wp_yith_wcwl: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_yith_wcwl_lists: Data: 0.02MB + Index: 0.05MB + Engine InnoDB

### Post Type Counts ###

attachment: 17035
cookielawinfo: 52
custom_css: 2
iksm: 1
page: 85
post: 1
product: 2203
revision: 698
shop_coupon: 1
shop_order: 296
shop_order_refund: 15
wpcf7_contact_form: 7

### Security ###

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

### Active Plugins (39) ###

WP fail2ban: by Charles Lecklider – 4.4.0.4
WPML Multilingual CMS: by OnTheGoSystems – 4.5.5
CAPTCHA 4WP (Premium): by WP White Security – 7.0.6
WP Offload Media Lite: by Delicious Brains – 2.6.0
Conditional Fields for Contact Form 7: by Jules Colle – 2.1.2
Contact Form 7 Multilingual: by OnTheGoSystems – 1.1.0
Contact Form 7: by Takayuki Miyoshi – 5.5.6
GDPR Cookie Consent: by WebToffee – 2.1.1
Duplicator Pro: by Snap Creek – 4.5.0
EWWW Image Optimizer: by Exactly WWW – 6.4.2
F4 Shipping Phone and E-Mail for WooCommerce: by FAKTOR VIER – 1.0.14
Iks Menu: by IksStudio – 1.9.1
Kelkoogroup Sales Tracking: by Kelkoo Group – 1.0.5
Mailchimp for WooCommerce: by Mailchimp – 2.6.1
Nextend Social Login: by Nextendweb – 3.1.4
Plausible Analytics: by PlausibleHQ – 1.2.2
Redirection: by John Godley – 5.2.3
Relevanssi: by Mikko Saari – 4.15.0
User Role Editor: by Vladimir Garagulya – 4.61.2
Advanced Shipment Tracking for WooCommerce: by zorem – 3.3.1
Product Feed ELITE for WooCommerce: by AdTribes.io – 2.5.5
WooCommerce EU VAT Number: by WooCommerce – 2.3.25
WooCommerce Stripe Gateway: by WooCommerce – 6.3.0
WooCommerce Google Analytics Integration: by WooCommerce – 1.5.8
WooCommerce Multilingual: by OnTheGoSystems – 4.12.6
WooCommerce PDF Invoices & Packing Slips: by WP Overnight – 2.14.0
WooCommerce PDF Invoices & Packing Slips Professional: by Ewout Fernhout – 2.7.3
WooCommerce PDF Invoices & Packing Slips Premium Templates: by Ewout Fernhout – 2.10.1
WooCommerce Table Rate Shipping: by WooCommerce – 3.0.29
WooCommerce: by Automattic – 6.3.0
WooSidebars: by WooCommerce – 1.4.5
WP 2FA - Two-factor authentication for WordPress: by WP White Security – 2.1.0
WP Allowed Hosts: by Brandon Groves – 1.0.8
WP Cloudflare Super Page Cache: by Optimole – 4.5.8
WP Mail SMTP: by WPForms – 3.3.0
WP Rollback: by Impress.org – 1.7.1
WP Test Email: by Boopathi Rajan – 1.1.5
WPML Media: by OnTheGoSystems – 2.7.0
WPML String Translation: by OnTheGoSystems – 3.2.1

### Dropin Plugins (1) ###

advanced-cache.php: advanced-cache.php

### Settings ###

API Enabled: –
Force SSL: –
Currency: EUR (€)
Currency Position: right_space
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: #6 - /
Cart: #7 - /cart/
Checkout: #8 - /checkout/
My account: #9 - /my-account/
Terms and conditions: #13800 - /terms-and-conditions/

### Theme ###

Name: Flatsome Child
Version: 3.0
Author URL: 
Child Theme: ?
Parent Theme Name: Flatsome
Parent Theme Version: 3.15.3
Parent Theme Author URL: https://www.uxthemes.com/
WooCommerce Support: ?

### Templates ###

Overrides: flatsome/woocommerce/archive-product.php
flatsome/woocommerce/cart/cart-empty.php
flatsome/woocommerce/cart/cart-shipping.php
flatsome/woocommerce/cart/cart.php
flatsome/woocommerce/checkout/form-checkout.php
flatsome/woocommerce/checkout/form-coupon.php
flatsome/woocommerce/checkout/terms.php
flatsome/woocommerce/checkout/thankyou.php
flatsome/woocommerce/content-product-cat.php
flatsome/woocommerce/content-product.php
flatsome/woocommerce/content-single-product.php
flatsome/woocommerce/content-widget-product.php
flatsome/woocommerce/global/breadcrumb.php
flatsome/woocommerce/global/quantity-input.php
flatsome/woocommerce/global/wrapper-end.php
flatsome/woocommerce/global/wrapper-start.php
flatsome/woocommerce/loop/loop-end.php
flatsome/woocommerce/loop/loop-start.php
flatsome/woocommerce/loop/pagination.php
flatsome/woocommerce/loop/result-count.php
flatsome/woocommerce/loop/sale-flash.php
flatsome/woocommerce/myaccount/form-login.php
flatsome/woocommerce/myaccount/navigation.php
flatsome/woocommerce/notices/error.php
flatsome/woocommerce/notices/notice.php
flatsome/woocommerce/notices/success.php
flatsome/woocommerce/product-searchform.php
flatsome/woocommerce/single-product/photoswipe.php
flatsome/woocommerce/single-product/price.php
flatsome/woocommerce/single-product/product-image.php
flatsome/woocommerce/single-product/product-thumbnails.php
flatsome/woocommerce/single-product/rating.php
flatsome/woocommerce/single-product/related.php
flatsome/woocommerce/single-product/review.php
flatsome/woocommerce/single-product/sale-flash.php
flatsome/woocommerce/single-product/share.php
flatsome/woocommerce/single-product/short-description.php
flatsome/woocommerce/single-product/tabs/tabs.php
flatsome/woocommerce/single-product/title.php
flatsome/woocommerce/single-product/up-sells.php
flatsome/woocommerce/single-product-reviews.php
flatsome/woocommerce/single-product.php
flatsome/woocommerce/content-product_cat.php

### Action Scheduler ###

Complete: 415
Oldest: 2022-02-07 10:54:51 +0000
Newest: 2022-03-10 05:38:49 +0000

### Status report information ###

Generated at: 2022-03-10 05:40:49 +00:00
    • This topic was modified 2 years, 8 months ago by shopro.
    • This topic was modified 2 years, 8 months ago by shopro.
Viewing 11 replies - 1 through 11 (of 11 total)
  • Plugin Support Sandip Mondal – a11n

    (@sandipmondal)

    Hi there,

    I understand that you get this error when you updated the WooCommerce to 6.3.0. Missing base tables: wp_wc_product_attributes_lookup. Some WooCommerce functionality may not work as expected

    Can you please go to WooCommerce > Status > Tools and click on the “Regenerate” button next to the “Product lookup” table: https://d.pr/i/Mvf3Vp ?

    Let us know how it goes!

    Thread Starter shopro

    (@shopro)

    Hi,

    No errors while it ran or after that I could see relating to it not being successful. I do however see the following error below on the Status -> Tools page. Not a new error but I just clicked Dismiss before and forgot. Also tried running “Verify base database tables” and it made no difference.

    
 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.

    @shopro

    Thanks for providing more context.

    Can you try using the ‘update database’ button to see it fixes the issue?

    If that fails, please share your WooCommerce error logs.

    Error Log: Share a copy of any fatal error log found under WooCommerce > System Status > Logs (if available).`

    Thread Starter shopro

    (@shopro)

    Thank you for your reply.

    “Update database” made no difference or give any errors. Also there are no logs.

    I also noticed that our staging site (exact copy) which is working fine, under Status -> Tools has “Regenerate the product attributes lookup table” which our live site (the problem one) does not have. Maybe the migration didn’t run correctly when updating from 6.2 to 6.3? Is there a way to manually trigger the migration?

    @shopro

    I found some pointers from my colleagues response here where your host can help re-create the missing table in your database

    Thread Starter shopro

    (@shopro)

    Thank you for the hint.

    I was successfully able to create the missing table by using the table from our staging site.

    We are using Digitalocean Managed Databases and at first I couldn’t create the table due to the table not having a primary key defined and I had to use the code below to disable it for the session.

    SET SESSION sql_require_primary_key = 0;

    This got me thinking if the problem was due to the missing primary key in the table structure? Our staging site does not have this on by default. There were no errors on the WordPress side of things so I can’t check.

    Create Table

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

    Edit:
    Looks like after running “Regenerate the product attributes lookup table” the table is deleted.

    • This reply was modified 2 years, 8 months ago by shopro.
    • This reply was modified 2 years, 8 months ago by shopro.

    Hello,

    Glad to know you have created the missing table.

    I’ve read the errors received but they do not show that is missing a key but a table.

    In this case, be sure to reach out to Digitalocean support.

    I’ve found this page that explains how you can import/export a database between sites.

    I hope this helps.

    Thread Starter shopro

    (@shopro)

    @ihereira, looks like the table gets deleted at some point, most likely during the “Regenerate the product attributes lookup table” process.

    The error due to there being no primary key for the table in question comes when trying to create the table manually. Again this is due to the fact that Digitalocean’s Managed Databases have this on by default.

    Taking a quick look at the database dump from our staging site I see that every table except the wp_wc_product_attributes_lookup has a primary key defined.

    Plugin Support Paulo P – a11n

    (@paulostp)

    Hello,

    Thanks for writing back!

    looks like the table gets deleted at some point, most likely during the “Regenerate the product attributes lookup table” process

    Do you mean that the table you’ve just recreated on your live site got deleted?

    WooCommerce 6.3.0 included a database migration, specifically addressing product filtering by attributes. You can find additional context here: https://developer.woocommerce.com/2022/02/02/new-product-filtering-by-attributes-rolling-out-in-woocommerce-6-3/

    It’s possible that your database settings could have caused issues with the migration.

    Please contact us at WooCommerce.com > My Account > Support.

    You may need to create an account before you can access that page.

    Please include a link to this forum thread, so that we can keep track of what’s already been done.

    We will be able to help you further there.

    Plugin Support Grigorij S. a11n

    (@grigaswp)

    Hi folks,

    It sounds like the wc_product_attributes_lookup table doesn’t have a primary key by default which could prevent this table from being created or re-created in situations where sql_require_primary_key is set to 1.

    We’ve seen a few reports of users being able to create the table after setting sql_require_primary_key to 0 as indicated in responses above:

    SET SESSION sql_require_primary_key = 0;

    Missing primary key could potentially cause other issues too so I’ve asked our developers if this can be changed:

    https://github.com/woocommerce/woocommerce/issues/31367#issuecomment-1066128680

    There’s a chance that this will require a separate issue in GitHub, but feel free to add your thoughts!

    Thank you!

    Mirko P.

    (@rainfallnixfig)

    Hi @shopro,

    We haven’t heard from you in a while, so I’m going to mark this as resolved.

    Just an update: as of WooCommerce 6.4, which will be released soon, the product attributes lookup table will have a primary key too by default as confirmed in the Github thread.

    Feel free to start a new thread if you have any more questions.

    Cheers.

Viewing 11 replies - 1 through 11 (of 11 total)
  • The topic ‘After 6.3.0 – Missing base tables: wp_wc_product_attributes_lookup’ is closed to new replies.