Low Search "Find and Replace" shows preview but won't complete replace

I have Low Search 5.20 installed on EE 3.5.10, PHP 7.0, MySQL 5.0.12. All search functions are working fine. However, I'm trying to use the Find and Replace feature and am having no success. When I preview the search, I quickly see all of the candidates with highlighted search phrases. But when I add the replacement string and try and run the search, all I see is "working".

I checked my SQL processes and don't see anything running, and none of the replacements happen in the database. The "working..." message will stay on the screen indefinitely. I have tried to run the Find and Replace on our staging server and (yipes!) on our live server just to ensure it wasn't a memory issue. I am trying a fairly large Find and Replace over thousands of entires, however I have tried to just have it look at a single field and it still doesn't work. Any help is appreciated.
1 person has
this problem
+1
Reply
  • Low (Official Rep) July 16, 2017 08:01
    That's probably because the Ajax call returns an error. You can use your browser's inspector to see the XHR request fired and the response it gets. What does that tell you?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • Ok, this is the 500 header response I'm getting:

    SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'edit_date' at row 1:
    UPDATE exp_channel_titles SET title = REPLACE(title, 'string_1', 'string_2'), edit_date = '20170717014349' WHERE entry_id IN (7286,7285,7284,7283,7282,7281,7280,7279,7277,7276,7275,7274,7273,7272,7271,7270,7266,7265,7264,7263,7262,7261,7260,7257,7255,7254,7253,7252,7251,7249,7248,7247,7245,7244,7243,7242,7241,7239,7238,7236,7235,7234,7233,7232,7231,7230,7229,7227,7226,7225,7224,7223,7222,7219,7218,7217,7215,7214,7212,7211,7210,7209,7208,7207,7205,7204,7202,7200,7199,7197,7195,7194,7193,7192,7191,7190,7188,7187,7186,7185,7184,7182,7180,7179,7177,7175,7174,7173,7172,7170,7169,7168,7167,7165,7162,7159,7158,7157,7155,7154);

    ee/legacy/database/drivers/mysqli/mysqli_connection.php:122
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • Low (Official Rep) July 17, 2017 10:17
    Huh. Looks like EE3 now saves its edit_date in a consistent format, rather than the odd format it used in EE2. No mention of that in their change log. It didn't throw an error in my tests, because I don't have MySQL running in Strict mode, which you probably have.

    Anyway. Open up mcp.low_search.php, and look up line #2583, which reads:

    // Add query to change edit date, which is in a STUPID FORMAT!
    $tables['channel_titles'][] = sprintf("edit_date = '%s'",
    date('YmdHis', ee()->localize->now));


    Change that to:

    // Add query to change edit date
    $tables['channel_titles'][] = sprintf("edit_date = '%s'", ee()->localize->now);


    ...and it should work.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • Yeah, after the 3.0 upgrade at some point I had to switch the db to strict -- can't recall when or why but was one of many issues I worked through. Anyway your patch does the trick. Works great!

    One more question: Is it risky to increase the search and replace past 100 entries? Do I risk running into PHP memory errors? I'm doing the work on a staging server and database is backed up.

    By the way, thank you for your excellent and quick support -- even on a weekend. Impressive!!
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • Low (Official Rep) July 18, 2017 07:03
    Yeah, there is a chance of memory (and UI) issues. You can try by setting the PREVIEW_LIMIT constant in the mcp.low_search.php file to a higher number; make sure to backup first. If it fails, go back to batches of 100.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated