Fixed MySQL 8.0.22+ - xf_phrase rlike compatibility

[Xon]

Affected version

2.1.1

The xf_phrase.title table/column is varbinary. This affects XFRM/XFMG and some add-on migration code which uses RLIKE (or just LIKE) on this column. This works in previous versions of MySQL & MariaDB but appears to not work.

Is there any reason for this column to not be varchar rather than varbinary to ensure these sorts of updates work as expected?

XF\Db\Exception: Batch install error: MySQL statement prepare error [3995]: Character set 'binary' cannot be used in conjunction with 'utf8mb4_0900_ai_ci' in call to regexp_like. src/XF/Db/AbstractStatement.php:228

@Xon This addon not compatible with MySQL version 8.0.22 because can not install in my test site.

XF\Db\Exception: Batch install error: MySQL statement prepare error [3995]: Character set 'binary' cannot be used in conjunction with 'utf8mb4_0900_ai_ci' in call to regexp_like. src/XF/Db/AbstractStatement.php:228

Generated by: Nirjonadda Oct 20, 2020 at 9:04 PM

Stack trace

                SELECT phrase_id, title
                FROM xf_phrase
                WHERE title RLIKE ?
                    AND addon_id = ''
           
------------

#0 src/XF/Db/Mysqli/Statement.php(196): XF\Db\AbstractStatement->getException('MySQL statement...', 3995, 'HY000')
#1 src/XF/Db/Mysqli/Statement.php(39): XF\Db\Mysqli\Statement->getException('MySQL statement...', 3995, 'HY000')
#2 src/XF/Db/Mysqli/Statement.php(54): XF\Db\Mysqli\Statement->prepare()
#3 src/XF/Db/AbstractAdapter.php(94): XF\Db\Mysqli\Statement->execute()
#4 src/XF/Db/AbstractAdapter.php(175): XF\Db\AbstractAdapter->query('
                SELECT phr...', '^Sedo_AdvBBcode...')
#5 src/addons/SV/StandardLib/InstallerHelper.php(227): XF\Db\AbstractAdapter->fetchPairs('
                SELECT phr...', '^Sedo_AdvBBcode...')
#6 src/addons/SV/AdvancedBbCodesPack/Setup.php(173): SV\AdvancedBbCodesPack\Setup->renamePhrases(Array)
#7 src/addons/SV/AdvancedBbCodesPack/Setup.php(49): SV\AdvancedBbCodesPack\Setup->migrateOptions()
#8 src/XF/AddOn/StepRunnerInstallTrait.php(60): SV\AdvancedBbCodesPack\Setup->installStep3(Array)
#9 src/XF/AddOn/StepRunnerInstallTrait.php(27): SV\AdvancedBbCodesPack\Setup->installStepRunner(3, Array)
#10 src/XF/Job/AddOnInstallBatch.php(293): SV\AdvancedBbCodesPack\Setup->install(Array)
#11 src/XF/Job/AddOnInstallBatch.php(89): XF\Job\AddOnInstallBatch->stepAction(Object(XF\Timer))
#12 src/XF/Job/Manager.php(258): XF\Job\AddOnInstallBatch->run(G)
#13 src/XF/Job/Manager.php(200): XF\Job\Manager->runJobInternal(Array, G)
#14 src/XF/Job/Manager.php(116): XF\Job\Manager->runJobEntry(Array, G)
#15 src/XF/Admin/Controller/Tools.php(120): XF\Job\Manager->runByIds(Array, 8)
#16 src/XF/Mvc/Dispatcher.php(350): XF\Admin\Controller\Tools->actionRunJob(Object(XF\Mvc\ParameterBag))
#17 src/XF/Mvc/Dispatcher.php(257): XF\Mvc\Dispatcher->dispatchClass('XF:Tools', 'RunJob', Object(XF\Mvc\RouteMatch), Object(XF\Admin\Controller\Tools), NULL)
#18 src/XF/Mvc/Dispatcher.php(113): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(XF\Admin\Controller\Tools), NULL)
#19 src/XF/Mvc/Dispatcher.php(55): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#20 src/XF/App.php(2300): XF\Mvc\Dispatcher->run()
#21 src/XF.php(464): XF\App->run()
#22 admin.php(13): XF::runApp('XF\\Admin\\App')
#23 {main}

Request state

array(4) {
  ["url"] => string(24) "/admin.php?tools/run-job"
  ["referrer"] => string(47) "/admin.php?tools/run-job"
  ["_GET"] => array(1) {
    ["tools/run-job"] => string(0) ""
  }
  ["_POST"] => array(3) {
    ["_xfRedirect"] => string(82) "/admin.php?add-ons/install-from-archive-complete&batch_id=7"
    ["_xfToken"] => string(8) "********"
    ["only_ids"] => string(3) "186"
  }
}

 

[XF Bug Bot]

Thank you for reporting this issue, it has now been resolved. We are aiming to include any changes that have been made in a future XF release (2.2.2).

Change log:

Fix a MySQL 8.0.22 incompatibility with the 1.x to 2.x upgrade code (related to phrase renames).

There may be a delay before changes are rolled out to the XenForo Community.

 

[Mike]

Is there any reason for this column to not be varchar rather than varbinary to ensure these sorts of updates work as expected?

This (and the similar thing in templates) is generally to ensure that the matching is case sensitive. Allowing case insensitivity here might create some inconsistent behaviors, particularly as case sensitivity behaviors may end up being mixed. For example, arrays made be keyed by phrase/template titles so we could have situations where a PHP lookup fails and a MySQL one succeeds.

Fortunately, the fix for this is really quite simple: cast the regex to binary via WHERE title RLIKE BINARY ?. The MySQL change log for this issue would make you think this isn't allowed:

Regular expression functions such as REGEXP_LIKE() yielded inconsistent results with binary string arguments. These functions now reject binary strings with an error. (Bug #31031886, Bug #98951, Bug #31031888, Bug #98950)

However, that's not entirely accurate compared to the code changes for this. Specifically, they prevent mixing binary and non-binary strings but the functions do have support for binary character sets. The binary support isn't incidental either; it's actually explicitly supported by these changes.

 

[andreyjkee]

I have same issue on xf 2.2.1 when trying to combine two users into single account.