With mySQL >= 8.0 various DB queries fails due to obsolete regexp (patch included)

[pms967]

DocDB has several issues when used with an up to date version of mySQL (>=8.0). One such a problem affects the search function:

AH01215: DBD::mysql::st execute failed: Illegal argument to a regular expression. at /var/www/docdb/cgi.pro/Search.pm line 283.: /var/www/docdb/cgi/Search

this error in turn cause the query to fail -> search does not work.

I've traced back this problem to an obsolete regexp format defined in the file "SearchAtoms.pm".

From: https://stackoverflow.com/questions/59998409/error-code-3685-illegal-argument-to-a-regular-expression

According to MySQL 8.0 Reference Manual, "MySQL implements regular expression support using International Components for Unicode (ICU)."

According to MySQL 5.6 Reference Manual , "MySQL uses Henry Spencer's implementation of regular expressions."

Therefore, with MySQL 8.0, rather than using [[:<:]] and [[:>:]], you must now use \b.

Here follows the trivial patch which fixes the problem:

--- ../../DocDB-8.8.9/cgi/SearchAtoms.pm    2018-06-26 20:19:08.000000000 +0200
+++ ./SearchAtoms.pm    2023-08-19 17:37:53.275499659 +0200
@@ -113,13 +122,15 @@
   }

   if ($RequireWord) {
-    $RegExpAtom .= '[[:<:]]';
+    # $RegExpAtom .= '[[:<:]]'; # does not work with mySQL >= 8.0 !
+    $RegExpAtom .= '\b';
   }
   $RegExpAtom .= '(';
   $RegExpAtom .= join '|', @RegExpParts;
   $RegExpAtom .= ')';
   if ($RequireWord) {
-    $RegExpAtom .= '[[:>:]]';
+    # $RegExpAtom .= '[[:>:]]';  # does not work with mySQL >= 8.0 !
+    $RegExpAtom .= '\b';
   }

   my $SafeAtom = $dbh->quote($RegExpAtom);