Database table structure

Resolved morayman
(@morayman)

3 years, 1 month ago

We have recently moved to a new WordPress hosting (WordPress 6.1.1) at GoDaddy by restoring a backup (including WooCommerce 6.0.1). However (a) existing orders no longer show the products, and (b) the shipping rules are no longer present.

Looking at the backup, it creates a table wp_wc_product_meta_lookup but looking at the database browser it appears as wp_nbq7dywchz_wc_product_meta_lookup. So, can anyone help with the internal structure – I’m assuming that the order display is trying to pull the products from wp_wc_product_meta_lookup and getting nothing, and I need to update something to use wp_nbq7dywchz_wc_product_meta_lookup instead.

I’m reluctant to rename the tables as the order detail is still working…

I’m also assuming a similar problem with the shipping rules, though I don’t know which table holds them – can anyone help with that, and also what might need updating to use the new table name.

Or is there another reason the information is not showing up?

Many thanks!

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

Igor H
(@ihereira)

3 years, 1 month ago

Hi,

Looking at the backup, it creates a table wp_wc_product_meta_lookup but looking at the database browser it appears as wp_nbq7dywchz_wc_product_meta_lookup.

I understand the database prefix is different, you will want to change it to see if that resolves the issue, to learn more please check out this web article about it: How to Change the WordPress Database Prefix

Before making any critical change, please back up your site.

I hope this helps.

Thread Starter morayman
(@morayman)

3 years, 1 month ago

Thank you for this. I’ve had a look with the tables, but cannot find the info that’s not working. Does anyone know where the the item information is obtained from when woocommerce is displaying an order? The order infomation is fine, but the contents of the order are missing.

This occurs for just historic orders (from the restore) – it works fine for new orders (in the new wp_xxxxx_ scheme)

Igor H
(@ihereira)

3 years, 1 month ago

Hello,

Can you switch the site theme to Storefront temporarily, and then disable all plugins except WooCommerce? Check again to see if the issue persists, if not, it means a conflict was affecting the site, and you can then switch back your theme to the original one, and then reactivate the rest of the plugins (checking again after each plugin reactivation), until you find the culprit. More info: https://woocommerce.com/document/how-to-test-for-conflicts/

but cannot find the info that’s not working. Does anyone know where the item information is obtained from when WooCommerce is displaying an order? The order information is fine, but the contents of the order are missing.

Can you provide a screenshot of what you are describing, please?

If you don’t already have a screenshot service installed, here are a few free options:

https://snipboard.io

http://awesomescreenshot.com/

That will help to take a closer look, thanks.

Thread Starter morayman
(@morayman)

3 years, 1 month ago

Thank you. I’ve finally got the screen shots, to explain the problem. From the reports section:

Missing Items : https://www.dropbox.com/s/ey1aorjihvku89c/Missing%20Items%20Order.jpg?dl=0

Shown Items : https://www.dropbox.com/s/d3at5pky1d531j8/Shown%20Items%20Order.jpg?dl=0

The report seems unable to pick up the items of the order from those prior to the switch to GoDaddy, but OK with those since. My guess is that the report generator is using the old wp_ table name, and if I can find where that is embedded in the database information, I can make an adjustment (maybe).
Igor H
(@ihereira)

3 years, 1 month ago
Hello,

On your database, you can run the following SQL command:
```
/* get everything from wp_woocommerce_order_items table */
SELECT * FROM wp_woocommerce_order_items;
```
[Source].

On my testing site it looks like this, you can see the item and the order ID related:

You can install a plugin like the WP php my Admin to check the database tables. What I am trying to say is that you can check if the data is present in the new site.

If that does not work for you, please 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”.

Cheers!
Thread Starter morayman
(@morayman)

3 years, 1 month ago

Thanks – that gives me what is needed. It looks like the backup failed to restore the values.

I’d like to add them in, but there have been a number of orders (say 250 items) since this new set up started, so if I restore directly, I will get numerous duplicates of order_item_id (with different data).

Will that be an issue, or do need to go through the restoring SQL to add 250 to all the order_item_id values to make them unique?

Thanks for you help.

Saif
(@babylon1999)

3 years, 1 month ago

Hello @morayman,

Will that be an issue, or do need to go through the restoring SQL to add 250 to all the order_item_id values to make them unique?

This depends on your backup plugin. For example, in Jetpack, you can restore to any point in the past or use a backup without worrying about your products and orders being affected.

If you’re importing orders with IDs that are currently occupied in the current version of the site, you can try offsetting the ID by the number of orders.

Instead of dealing with the database directly, I suggest using a plugin like Import export Suite for WooCommerce to import/export orders safely.

Also, please make sure to have a backup of the site on standby just in case anything goes wrong during the process.

Cheers!

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

The topic ‘Database table structure’ is closed to new replies.