Issue with MySQL >= 8.0.4 and solution

  • Resolved Robert

    (@robertinfigo)


    4 years, 3 months ago

    Cze??,
    “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.

    Maybe you can adapt the plugin to use both, depending on MySQL version?

    The conflicting boundaries can be found in acf-better-search/app/Search/Where.php

    Pozdrawiam

Viewing 4 replies - 1 through 4 (of 4 total)
  • Plugin Author Mateusz Gbiorczyk

    (@mateuszgbiorczyk)

    Cze?? @robertinfigo,

    Thank you for your message and your commitment!

    Can you replace this line:
    if (self::$config['whole_words']) $list[] = 'a.meta_value REGEXP \'[[:<:]]' . $word . '[[:>:]]\'';

    with the following:
    if (self::$config['whole_words']) $list[] = '(a.meta_value REGEXP \'[[:<:]]' . $word . '[[:>:]]\' OR a.meta_value REGEXP \'\\b' . $word . '\\b\')';

    Has it helped?

    Thread Starter Robert

    (@robertinfigo)

    Not exactly.

    I’ve changed all occurences of ‘[[:<:]] and ‘[[:>:]] to ‘\\\\b as you need double the backslash because MySQL interprets it as the escape character within strings.

    But that didn’t help, another error was that d.post_title is unknown. So I’ve changed ‘d.post_title’ to just ‘post_title’. And now it works!

    For a fix best would be to determine version of MySQL for example by:
    $mysqlVersion = $wpdb->db_version();
    Mine is 8.0.22 and then serve right word boundaries.

    Plugin Author Mateusz Gbiorczyk

    (@mateuszgbiorczyk)

    @robertinfigo, thank you very much for your help. Henry Spencer’s implementation also works on the older version of the database, so I added it permanently to the plugin.

    And if someone had problems, then filters were prepared to update the SQL query.

    I have released update 3.7.0 which fixes the problems you have reported. Please download it and confirm that everything works for you. Thanks again for your cooperation.

    Plugin Author Mateusz Gbiorczyk

    (@mateuszgbiorczyk)

    @robertinfigo, I had to go back to my previous regular expressions in my SQL query with a few requests. However, the implementation by Henry Spencer is still available in the plugin, you only need to activate this option in the plugin settings.

    I am sorry for the inconvenience.

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Issue with MySQL >= 8.0.4 and solution’ is closed to new replies.