Gets "stuck" indexing orders

  • scmsteve
    Participant
    # 1 year, 10 months ago

    When we initially opened the settings page to do the indexing, it reported 290,347 orders to index. It gets stuck with 210,578 orders not indexed, and won’t go further. I even deleted everything and tried again, still it gets stuck at this point.

    Here is the output when DEBUG is on:

    An error or timeout occured.
    Error code: parsererror
    Error message: SyntaxError: Unexpected token P in JSON at position 0

    Posts excluded from the index:

    ******** DEBUG ACTIVATED – Beginning of new loop (batch size) *******
    ******** DEBUG ACTIVATED – Query documents from last post date *******

    Query:
    SELECT ID, post_modified, post_parent, post_type
    FROM wpscm_posts AS A
    WHERE (
    (post_modified = ‘2015-08-13 10:08:46’ AND ID > 261096)
    OR
    (post_modified > ‘2015-08-13 10:08:46’)
    )
    AND ( post_status IN (‘wc-pending’,’wc-processing’,’wc-on-hold’,’wc-completed’,’wc-cancelled’,’wc-tracking’,’wc-refunded’,’wc-failed’,’wc-wait-pickup’,’wc-pre-order’,’wc-re-opened’)
    AND
    ( post_type = ‘shop_order’ )
    )
    ORDER BY post_modified ASC, ID ASC LIMIT 50

    Last post date:
    2015-08-13 10:08:46

    Last post ID:
    261096

    And then it has the posts with JSON data. I am not pasting that here for privacy. We had the batch size set low, to 50, and so I (tediously) took each line of JSON output and pasted it into a JSON validator, and no line in that output has a problem. I don’t understand the error “Unexpected token P in JSON at position 0” because I can not find that in the JSON data output in the DEBUG dump.

    wpsolr
    Keymaster
    # 1 year, 10 months ago

    Please check if you can find some clues in https://www.wpsolr.com/forums/topic/error-when-indexing-products/

    scmsteve
    Participant
    # 1 year, 10 months ago

    Did not fix. I did get “{“acknowledged”:true}” when I ran it, but still the error happens. I removed the index and started again, it failed. I started just the orders indexing again, checking to start from the beginning but leave existing data in place, and it was right around 80,000 when it went to 0 and is just sitting there now. Maybe when it times out I will find an error, but the number before I started said there were 210583 orders left to index, the last time it was 210578 so it seems there is something right around that point (whatever that point is) that is causing problems.

    scmsteve
    Participant
    # 1 year, 10 months ago

    Actually, *this* thread seems more relevant, but I am not running PHP 7.4:

    Error when indexing products

    Going to try debug on with batch of 1 to see if I can find the order and maybe find what is wrong…

    scmsteve
    Participant
    # 1 year, 10 months ago

    This seems to be orders that were imported from another system earlier than our WooCommerce store:

    “_order_number_i”: [
    64698707615563
    ],
    “_order_number_str”: [
    64698707615563
    ],

    The error after importing one row at a time was:
    {“nb_results”:0,”status”:0,”message”:”failed to parse field [_order_number_i] of type [integer] in document with id ‘261149’. Preview of field’s value: ‘64698707615563’”,”indexing_complete”:false}

    I guess that number is too large for an integer? I will change the data section so that the order_number is text (sortable) instead of integer, that should fix it. There are too many that were imported with numbers larger than will fit in an integer.

    This may be my fault, I don’t think the default for the order_number was integer, I didn’t know there were values that would overflow it. 🙂

    wpsolr
    Keymaster
    # 1 year, 9 months ago

    Well done.

    I’m impressed, never heard of 30O thousand orders until now. The orders search being able to look in customer’s details, like address fields, it should fly and be much more relevant now.

    scmsteve
    Participant
    # 1 year, 9 months ago

    Had one more error, due to a long field, the order items field where they had a VERY LARGE order, over 32766 chars in that field. So I changed that type to the text that is too large to fit in other types and it seems to be indexing past that point now as well. 🙂

    Not quite “plug and play”, much to learn, but I think this is going to give us a huge improvement once done.

    wpsolr
    Keymaster
    # 1 year, 9 months ago

    Well done.

    I agree on the not “plug & play”. It’s on purpose, as we want to keep as many options from the search engine as possible. This is the difference with a pure hosted solution, where most of the settings are hidden.

    Just for my sake, how much long was your admin order search without WPSOLR?

    scmsteve
    Participant
    # 1 year, 9 months ago

    Strange, I thought I had answered this. Built-In search was extremely slow, but I still don’t have this working right to compare. I have “results” autocompleting in that box, but having a hard time getting *relevant* results, it is bringing up pages/posts not orders. Strange. I’ll pursue that in a separate thread if needed.

    But this saga continues… While I had it complete an index, I made some changes and, for testing, re-indexed everything and now it is hitting an error I didn’t see the first time:

    {“nb_results”:0,”status”:0,”message”:”failed to parse field [categories] of type [text] in document with id ‘250804’. Preview of field’s value: ‘{tax_class=null, line_subtotal_tax=null, type=null, item_meta=null, line_total=null, line_tax_data=null, variation_id=null, line_subtotal=null, qty=null, product_id=null, name=null, line_tax=null, account=null}'”,”indexing_complete”:false}

    That is an order, should it have a “category”? Or does it mean category on the product in the order? If so, the order only has one item which is in a category of “Other”.

    scmsteve
    Participant
    # 1 year, 9 months ago

    Oh, I had updated to the interim release 21.9 for the WooCommerce 4.4.1 release, maybe that added this problem.

    wpsolr
    Keymaster
    # 1 year, 9 months ago

    Had one more error, due to a long field, the order items field where they had a VERY LARGE order, over 32766 chars in that field. So I changed that type to the text that is too large to fit in other types and it seems to be indexing past that point now as well.

    Your new error could be related to your previous change. Can you try with the order items field disabled in tab 2.2?

    scmsteve
    Participant
    # 1 year, 9 months ago

    I don’t believe it is related at all, as with the 21.8 release it indexed the whole table, only with 21.9 is it not indexing it. But strange, as the contents of the _order_items field are NOT the order items.

    “_order_items_t”: [
    {
    “account”: null,
    “item_meta”: null,
    “line_subtotal”: null,
    “line_subtotal_tax”: null,
    “line_tax”: null,
    “line_tax_data”: null,
    “line_total”: null,
    “name”: null,
    “product_id”: null,
    “qty”: null,
    “tax_class”: null,
    “type”: null,
    “variation_id”: null
    }

    And the “categories” field has this, which also seems to make no sense:

    “categories”: [
    “redacted first and last name and email”,
    “redacted email”,
    “redacted email”,
    “Redacted First Name”,
    “Redacted Last Name”,
    “2015-06-08T13:32:32Z”,
    “redacted ip address”,
    “181”,
    {
    “account”: null,
    “item_meta”: null,
    “line_subtotal”: null,
    “line_subtotal_tax”: null,
    “line_tax”: null,
    “line_tax_data”: null,
    “line_total”: null,
    “name”: null,
    “product_id”: null,
    “qty”: null,
    “tax_class”: null,
    “type”: null,
    “variation_id”: null
    },

    Since it is complaining about the categories, it makes sense that the latter is what it is having problems with, maybe because it expects an array of strings not an embedded array? I don’t know why there is one in there, what 181 means, and the fact that the other entries in there are not categories to begin with.

    scmsteve
    Participant
    # 1 year, 9 months ago

    Also, the content has the same fields as the categories array, just in string form, with the array as 181. Array. 0. 2015-06-08T13:31:41Z. ….
    I’ll blow away the index and go back to 21.8, even though it is known to have issues with WooCommerce 4.4.1 and WordPress 5.5 (5.5.1 installed here).

    wpsolr
    Keymaster
    # 1 year, 9 months ago

    With WooCommerce 4.4.1, I do not see “_order_items” in tab 2.2. Do you know where this custom field comes from?

    scmsteve
    Participant
    # 1 year, 9 months ago

    Very good question, I did not think of where it was coming from, I just know that it was there. I did a grep through our entire plugin directory and only found it in two places.

    WooCommerce has it in an old wc_update_200_line_items() where it migrated from that format and put them into the newer line item format. So this must be very old data that never was migrated. There are also a lot of _order_items_old which was done as part of that conversion. I will check with the site owner, but I think this can be deleted now probably. Strangely enough, of the existing (717) _order_items metadata entries are either blank (19), for the same product (698), or (19) that contain that product and others. It seems that that product, for whatever reason, failed conversion when it was done, but these can go away.

    I will put 21.9 back in, exclude that field now knowing it is not relevant, and report back. Thank you for your help to get me to look into this further, I just saw the data in there and assumed (bad move) that it was standard WooCommerce data.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic.