PW 3.0.165, Illegal argument error with ~= selector and mysql 8.0+ ?

[celfred]

Hello,
Not sure this is an issue with PW, but I mentionned it on the PW Talk (https://processwire.com/talk/topic/24229-contains-all-words-selector-~-triggers-an-error-since-updating-to-30165/?tab=comments#comment-205596) and was advised to maybe submit an issue here, so here's the thing :

I have updated my Ubuntu 20.04 OS and my Processwire at the same time so I am with :

• PHP 7.4.3
• MySQL 8.0.21
• PW 3.0.165

And all my localhost pages having the "~=" operator in my selectors throw an error :

PDOException #HY000
SQLSTATE[HY000]: General error: 3685 Illegal argument to a regular expression.

As I said on the forum, if I change the operator with either "^=" or "~*=", the error disappears (but then of course, I lose the ~= capabilities of whole word only selection).

Hope it helps...

[celfred]

I've just tested and it works with other fields : title, summary... No problems (apparently). As soon as I change to "name~=", the error triggers.

[ryancramerdesign]

@celfred Unfortunately this is one area where MySQL 8.x breaks compatibility with prior versions. I don't run MySQL 8 here, but have been looking for someone to test the MySQL 8.x version of that particular matching mode. If you look in the file /wire/core/PageFinder.php on this line can you change $mySQL8 = false; to $mySQL8 = true; to see if that resolves the issue there? Once we know the working version, I can substitute the true or false with a version check. Thanks.

It works with other fields (title, summary, etc.) because those use a fulltext index. But the page name field does not.

[celfred]

@ryancramerdesign I'm so glad I might be able to help !
So I have set line 2507 of /wire/core/PageFinder.php from mysql8 = false; to mysql8 = true; and... everything seems to work fine ! 👍

[ryancramerdesign]

@celfred Thanks for testing it out. Glad to hear it works. The tricky part now is detecting the MySQL version because MySQL comes in more than one flavor (i.e. MariaDB), and they use different version numbers. But as far as I can tell, MariaDB doesn't have a version that uses the UCU regex engine, so hopefully the version detection I added will suffice for now, but please let me know if you find the update doesn't resolve it. Thanks.

[celfred]

@ryancramerdesign Thank YOU for all your work that made it possible for me to have a fun website for my middle school students, to have fun learning development (with my low-skills), and to to meet a wonderful community on the forum !

Here's what I did : I have copied and pasted the PageFinder.php file in place of my own (with your last patch), and everything seems to work fine on my localhost. So I guess we can close the issue 😄
Thanks a lot for your replies and as I sais, I'm glad I could help a little.
PS : I'll mention this as closed in the forum.

[ryancramerdesign]

@celfred Great and thanks! If you can't replace your /wire/ directory, you may need to also update your WireDatabasePDO.php file in /wire/core/, as I think the PageFinder.php update calls a new method from it.