Duplicate Can't Merge User

[Grumpy Squid]

Affected version

2.1.11

When we attempt to merge two users together in XF 2.1.11 the process fails and we get hit with the following error:

XF\Db\Exception: 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

Stack trace

            SELECT change_key, group_ids
            FROM xf_user_group_change
            WHERE user_id = ?
                AND change_key REGEXP '^warning_[0-9]+$'
        
------------

#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 chan...', 226795)
#5 src/XF/Service/User/ContentChange.php(384): XF\Db\AbstractAdapter->fetchPairs('
            SELECT chan...', 226795)
#6 src/XF/MultiPartRunnerTrait.php(48): XF\Service\User\ContentChange->stepApplyWarningGroupChanges(NULL, G)
#7 src/XF/Service/User/ContentChange.php(198): XF\Service\User\ContentChange->runLoop(G)
#8 src/XF/Service/User/Merge.php(137): XF\Service\User\ContentChange->apply(G)
#9 src/XF/MultiPartRunnerTrait.php(48): XF\Service\User\Merge->stepReassignContent(NULL, G)
#10 src/XF/Service/User/Merge.php(88): XF\Service\User\Merge->runLoop(G)
#11 src/XF/Job/UserMerge.php(39): XF\Service\User\Merge->merge(G)
#12 src/addons/SV/ConversationEssentials/XF/Job/UserMerge.php(21): XF\Job\UserMerge->run(G)
#13 src/addons/SV/UserEssentials/XF/Job/UserMerge.php(16): SV\ConversationEssentials\XF\Job\UserMerge->run(G)
#14 src/XF/Job/Manager.php(253): SV\UserEssentials\XF\Job\UserMerge->run(G)
#15 src/XF/Job/Manager.php(195): XF\Job\Manager->runJobInternal(Array, G)
#16 src/XF/Job/Manager.php(111): XF\Job\Manager->runJobEntry(Array, G)
#17 src/XF/Admin/Controller/Tools.php(120): XF\Job\Manager->runByIds(Array, 8)
#18 src/XF/Mvc/Dispatcher.php(350): XF\Admin\Controller\Tools->actionRunJob(Object(XF\Mvc\ParameterBag))
#19 src/XF/Mvc/Dispatcher.php(257): XF\Mvc\Dispatcher->dispatchClass('XF:Tools', 'RunJob', Object(XF\Mvc\RouteMatch), Object(XF\Admin\Controller\Tools), NULL)
#20 src/XF/Mvc/Dispatcher.php(113): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(XF\Admin\Controller\Tools), NULL)
#21 src/XF/Mvc/Dispatcher.php(55): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#22 src/XF/App.php(2191): XF\Mvc\Dispatcher->run()
#23 src/XF.php(391): XF\App->run()
#24 admin.php(13): XF::runApp('XF\\Admin\\App')
#25 {main}

Anyone know a fix for this?

Edit: The stack trace makes me think it might actually be one of our add-ons causing the issue. Will also reach out to add-on creator.

 

[Paul B]

It looks like the database collation may be incorrect.

Can you check if it is actually utf8mb4_0900_ai_ci?

It should be utf8mb4_unicode_ci.

Edit: Although on further reading, utf8mb4_0900_ai_ci looks like it may be the default collation for MySQL 8.0..1 and later.

 

[Rasmus Vind]

I sense it has to do with ConversationEssentials. You can see it in the stack trace.

 

[Grumpy Squid]

It looks like the database collation may be incorrect.

Can you check if it is actually utf8mb4_0900_ai_ci?

It should be utf8mb4_unicode_ci.

Edit: Although on further reading, utf8mb4_0900_ai_ci looks like it may be the default collation for MySQL 8.0..1 and later.

Here is the coalition, so is this incorrect? Everything else works fine, just the merge user.

I sense it has to do with ConversationEssentials. You can see it in the stack trace.

Yeah, I didn't catch that until after I posted. I asked the add-on creator for some advice, I figured I wouldn't ask for the removal of this thread though just in case it isn't the add-ons fault for some reason.

 

[Mike]

This particular issue was fixed in 2.2.2. It's a specific incompatibility with MySQL 8.0.22. See:

Fixed - MySQL 8.0.22+ - xf_phrase rlike compatibility

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

xenforo.com

(Slightly different location it manifests, but it's the same issue.)