Junk in database after CSV import, even after removing all products

  • Resolved peterjot

    (@peterjot)


    7 years ago

    I’m preparing to launch my shop using WooCommerce. This is really great product, congratulations for team. I’ve found however some issue with lot of database junk created during testing and importing products from csv files.

    Even after deleting all products, I found lot of junk entries in wp_posts and wp_postmeta tables. Their title is “Import placeholder for *” with SKU, post status is “publish”. There’s 2082 entries in ‘wp_posts’ table and around 72870 records in ‘wp_postmeta’. This is creating already performance issues with WooCommerce.

    I don’t see any option to remove such orphan entries from database.

    They’re created by https://github.com/woocommerce/woocommerce/blob/master/includes/import/class-wc-product-csv-importer.php, line 173
    (comment from code: “If we’re not updating existing posts, we may need a placeholder product to map to.”)

    My question is:
    – Is there a script or hidden function in CP to remove all products related data and start from 0, without clearing all settings made in WooCommerce?
    – Do someone has experience in removing this stuff? Is script removing such entries from ‘wp_posts’ table and entries from ‘wp_postmeta’ with matching post_id will be enough, or something else may be needed?
    – Do this function really needed to work that way in WooCommerce? This is creating huge everhead in database. If so, maybe some function would be nice to clear stuff after deleting or importing products?

    I will gently suggest that a) restoring database from backup and b) get better server answers are not favorable options for now ??

    Thank you,
    Peter

Viewing 6 replies - 1 through 6 (of 6 total)
  • Caleb Burks

    (@icaleb)

    Automattic Happiness Engineer

    Are you importing products with an ID column, or with a SKU? I’m curious which code path it’s taking.

    Cleanup happens here: https://github.com/woocommerce/woocommerce/blob/869fb52927b675bd4c200cf3480a8813c9465a28/includes/admin/class-wc-admin-importers.php#L233-L244

    It’s removing the temp products, as they should have the importing status.

    Thread Starter peterjot

    (@peterjot)

    Hi Caleb,

    Thanks for your reply.

    SKU. I don’t have any problems until yesterday, where I got time-out, have trouble removing products, and after that I found unusual amount of records in those tables.

    When checking dates, those items were created during last 9 days, probably since i started importing products, and happened many times. I’ve created csv files myself, but syntax should be ok, as products were imported without issues. I had at the begining some problems with product images field with | as delimeter, what was not working due to error in WooCommerce documentation.

    One thing unusual, I several times used option to “Update existing products”, having more products in csv (around 90 x 4 variations) than in shop (12 products with 4 variations each), but according to documentation it should skip not matched records.

    Caleb Burks

    (@icaleb)

    Automattic Happiness Engineer

    So if you didn’t finish the import or if it timed out due to server resources, then that would explain why it couldn’t clean up after itself. The above code / query is what you’ll need to run to clean up – though it should run after the next successful import.

    …but according to documentation it should skip not matched records.

    That’s correct, it should update existing records and add new ones at the same time. Existing records will match by SKU or ID.

    Thread Starter peterjot

    (@peterjot)

    I’ve done before full import thinkting it would clean up, but probably this status prevented this.

    This query looks complicated to run outside wordpress to delete all records, so i think that safest and easiest way would be change manually status to ‘importing’ for those entries in wp_posts.

    I’ll update this thread if this work, maybe someone will have similar issue.

    Thread Starter peterjot

    (@peterjot)

    Yes, it worked, all orphan records has been removed from tables. Import working really fast now.

    Magick query:
    UPDATE wp_posts SET post_status='importing' WHERE post_title LIKE 'Import placeholder%'

    And then run any import to allow cleaning of tables.

    Thanks for help, Caleb!

    Best regards,
    Piotr

    • This reply was modified 7 years ago by peterjot.
    • This reply was modified 7 years ago by peterjot.
    Caleb Burks

    (@icaleb)

    Automattic Happiness Engineer

    Awesome, glad we were able to get to the bottom of that ??

    I’ll check a bit later to see if there’s something that would cause these temp products to have the status changed like that. But I’m thinking right now it might have just been an accidental export then import.

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘Junk in database after CSV import, even after removing all products’ is closed to new replies.