Piwigo.org

windracer · 2019-11-10 18:45:55

I've run into a problem with quick search after upgrading to MySQL 8 (and PHP 7.3) as part of the Ubuntu 19.10 release. When searching for "short" (less than 5 chars?) terms, it errors out:

Code:

Warning: mysqli::query(): (HY000/3685): Illegal argument to a regular expression. in /usr/local/piwigo/include/dblayer/functions_mysqli.inc.php on line 141

Warning:  [mysql error 3685] Illegal argument to a regular expression.
SELECT * FROM tags
WHERE (name REGEXP '[[:<:]]amy[[:>:]]'
 OR MATCH(name) AGAINST( 'amies' IN BOOLEAN MODE)) in /usr/local/piwigo/include/dblayer/functions_mysqli.inc.php on line 856

Fatal error: Uncaught Error: Call to a member function fetch_assoc() on boolean in /usr/local/piwigo/include/dblayer/functions_mysqli.inc.php:220 
Stack trace: #0 /usr/local/piwigo/include/functions_search.inc.php(1051): pwg_db_fetch_assoc(false) 
#1 /usr/local/piwigo/include/functions_search.inc.php(1383): qsearch_get_tags(Object(QExpression), Object(QResults)) 
#2 /usr/local/piwigo/include/functions_search.inc.php(1301): get_quick_search_results_no_cache('amy jeremy terr...', Array) 
#3 /usr/local/piwigo/include/functions_search.inc.php(1474): get_quick_search_results('amy jeremy terr...', Array) 
#4 /usr/local/piwigo/include/section_init.inc.php(350): get_search_results('1079', NULL) 
#5 /usr/local/piwigo/index.php(12): include('/usr/local/piwi...') 
#6 {main} thrown in /usr/local/piwigo/include/dblayer/functions_mysqli.inc.php on line 220

From what I can tell, it's something to do with the REGEXP prefix/suffix being generated in functions_search.inc.php (starting on line 934) which is used when a search term is too short:

Code:

    if (!$use_ft)
    {// odd term or too short for full text search; fallback to regex but unfortunately this is diacritic/accent sensitive
      $pre = ($token->modifier & QST_WILDCARD_BEGIN) ? '' : '[[:<:]]';
      $post = ($token->modifier & QST_WILDCARD_END) ? '' : '[[:>:]]';
      foreach( $fields as $field)
        $clauses[] = $field.' REGEXP \''.$pre.addslashes(preg_quote($variant)).$post.'\'';
    }

I'm not a regex expert, but are those "wildcard" syntaxes above still valid?

Piwigo 2.10.1
Operating system: Linux
PHP: 7.3.11-0ubuntu0.19.10.1
MySQL: 8.0.17-0ubuntu2
Graphics Library: External ImageMagick 6.9.10-23

erAck · 2019-11-10 20:21:11

See https://dev.mysql.com/doc/refman/8.0/en … patibility
"Prior to MySQL 8.0.4, MySQL used the Henry Spencer regular expression library to support regular expression operations, rather than International Components for Unicode (ICU)."
The [[:<:]] and [[:>:]] word boundary markers are only supported by the Spencer library, for ICU it would be \b instead.

Piwigo needs to be adjusted to adapt to that and support both, depending on MySQL version.

You could try to patch the code to

Code:

      $pre = ($token->modifier & QST_WILDCARD_BEGIN) ? '' : '\\\\b';
      $post = ($token->modifier & QST_WILDCARD_END) ? '' : '\\\\b';

(hopefully, untested). I hope I got the escapement of the backslash right here; MySQL itself expects two backslashes to escape one literal, and the additional layer of PHP again needs an escapement. So PHP of \\\\b should pass \\b to MySQL..

windracer · 2019-11-10 21:18:18

That worked, thanks! Do you want me to file an issue in Github?

erAck · 2019-11-11 00:12:26

Sure, would be best. Thanks!

windracer · 2019-11-11 01:37:46

Done!

[Github] Piwigo issue #1118

windracer · 2021-02-19 23:20:47

Just realized this hasn't been fixed so it came back when I upgraded to Piwigo 11. Had to apply the patch to functions_search.inc.php again.

Piwigo.org

Announcement

#1 2019-11-10 18:45:55

Quick search error (MySQL 8 and REGEXP?)

Code:

Code:

#2 2019-11-10 20:21:11

Re: Quick search error (MySQL 8 and REGEXP?)

Code:

#3 2019-11-10 21:18:18

Re: Quick search error (MySQL 8 and REGEXP?)

#4 2019-11-11 00:12:26

Re: Quick search error (MySQL 8 and REGEXP?)

#5 2019-11-11 01:37:46

Re: Quick search error (MySQL 8 and REGEXP?)

#6 2021-02-19 23:20:47

Re: Quick search error (MySQL 8 and REGEXP?)

Board footer