Support archive | go to Low

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

rodgustafson 16 Jul 2017 02:25 problem, pending

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.

Replies

Low 16 Jul 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?
rodgustafson 17 Jul 2017 07:00

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
Low 17 Jul 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.
rodgustafson 17 Jul 2017 16:19

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!!
Low 18 Jul 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.