adam/Piwigo
1
0 Fork 0
mirror of https://github.com/Piwigo/Piwigo.git synced 2026-11-03 07:22:22 +01:00
Code Issues 657 Packages Projects Releases Wiki Activity

Quick search error (MySQL 8 and REGEXP) #974

New Issue
Closed
opened 2026-10-05 00:26:17 +02:00 by adam · 2 comments
adam commented 2026-10-05 00:26:17 +02:00
Owner
Copy link
Copy source

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:


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:

    {// 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.'\'';
    }

Per erAck here (https://piwigo.org/forum/viewtopic.php?id=29779), it's due to a change in the regular expression library in MySQL 8.0.4:

https://dev.mysql.com/doc/refman/8.0/en/regexp.html#regexp-compatibility

"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.

I used his suggested patch:

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

And that resolved the issue.

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

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: ```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: ``` 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.'\''; } ``` Per erAck here (https://piwigo.org/forum/viewtopic.php?id=29779), it's due to a change in the regular expression library in MySQL 8.0.4: https://dev.mysql.com/doc/refman/8.0/en/regexp.html#regexp-compatibility "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. I used his suggested patch: ``` $pre = ($token->modifier & QST_WILDCARD_BEGIN) ? '' : '\\\\b'; $post = ($token->modifier & QST_WILDCARD_END) ? '' : '\\\\b'; ``` And that resolved the issue. 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 <!-- github-sync-id:520684305 -->
adam closed this issue 2026-10-05 00:26:17 +02:00
adam commented 2026-10-05 00:26:18 +02:00
Author
Owner
Copy link
Copy source

With MySQL 8 getting more widespread this is an awkward error. The code probably will have to distinguish between MySQL versions to support both.

With MySQL 8 getting more widespread this is an awkward error. The code probably will have to distinguish between MySQL versions to support both. <!-- github-comment-id:573693261 -->
adam commented 2026-10-05 00:26:19 +02:00
Author
Owner
Copy link
Copy source

Yay, finally I can obsolete the patch I applied against this after every upgrade.. Thanks!

Yay, finally I can obsolete the patch I applied against this after every upgrade.. Thanks! <!-- github-comment-id:803436589 -->
Sign in to join this conversation.
No Branch/Tag Specified
Labels
No items
No labels
Milestone
No items
No Milestone
Projects
Clear projects
No projects
Assignees
Clear assignees
adam (Adam Wojcieszonek)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: adam/Piwigo#974