XF 2.1 MySQL error when posting smilies

[Karelke]

MariaDB 10.3.16 (not running in strict mode).

Inserting a random smiley like this:



Results into the following error:

XF\Db\Exception: MySQL query error [1366]: Incorrect string value: '\xF0\x9F\x91\x8D' for column [ICODE]xenforo[/ICODE].[ICODE]xf_draft[/ICODE].[ICODE]message[/ICODE] at row 1
src/XF/Db/AbstractStatement.php:217

INSERT  INTO [ICODE]xf_draft[/ICODE] ([ICODE]user_id[/ICODE], [ICODE]draft_key[/ICODE], [ICODE]message[/ICODE], [ICODE]extra_data[/ICODE], [ICODE]draft_id[/ICODE], [ICODE]last_update[/ICODE]) VALUES (?, ?, ?, ?, ?, ?)

------------


#0 src/XF/Db/Mysqli/Statement.php(196): XF\Db\AbstractStatement->getException('MySQL query err...', 1366, '22007')
#1 src/XF/Db/Mysqli/Statement.php(77): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1366, '22007')
#2 src/XF/Db/AbstractAdapter.php(94): XF\Db\Mysqli\Statement->execute()
#3 src/XF/Db/AbstractAdapter.php(218): XF\Db\AbstractAdapter->query('INSERT  INTO `x...', Array)
#4 src/XF/Mvc/Entity/Entity.php(1452): XF\Db\AbstractAdapter->insert('xf_draft', Array, false)
#5 src/XF/Mvc/Entity/Entity.php(1184): XF\Mvc\Entity\Entity->_saveToSource()
#6 src/XF/Draft.php(44): XF\Mvc\Entity\Entity->save()
#7 src/XF/ControllerPlugin/Draft.php(29): XF\Draft->save()
#8 src/XF/Pub/Controller/Thread.php(275): XF\ControllerPlugin\Draft->updateMessageDraft(Object(XF\Draft), Array)
#9 src/XF/Mvc/Dispatcher.php(321): XF\Pub\Controller\Thread->actionDraft(Object(XF\Mvc\ParameterBag))
#10 src/XF/Mvc/Dispatcher.php(244): XF\Mvc\Dispatcher->dispatchClass('XF:Thread', 'Draft', Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\Thread), NULL)
#11 src/XF/Mvc/Dispatcher.php(100): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\Thread), NULL)
#12 src/XF/Mvc/Dispatcher.php(50): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#13 src/XF/App.php(2177): XF\Mvc\Dispatcher->run()
#14 src/XF.php(390): XF\App->run()
#15 index.php(20): XF::runApp('XF\\Pub\\App')
#16 {main}

array(4) {
  ["url"] => string(32) "/forum/threads/test.282986/draft"
  ["referrer"] => string(51) "/forum/threads/test.282986/"
  ["_GET"] => array(0) {
  }
  ["_POST"] => array(9) {
    ["message_html"] => string(198) "<p><img class="smilie smilie--emoji fr-fic fr-dii" alt="👍" title="Thumbs up    (y)" src="https://cdn.jsdelivr.net/emojione/assets/4.5/png/64/1f44d.png" data-shortname="(y)"><br></p>"
    ["attachment_hash"] => string(32) "6e9cca62d75c35735d18cb33bc89d43f"
    ["attachment_hash_combined"] => string(88) "{"type":"post","context":{"thread_id":282986},"hash":"6e9cca62d75c35735d18cb33bc89d43f"}"
    ["last_date"] => string(10) "1476350154"
    ["last_known_date"] => string(10) "1561388432"
    ["_xfRequestUri"] => string(27) "/forum/threads/test.282986/"
    ["_xfWithData"] => string(1) "1"
    ["_xfToken"] => string(8) "[B]****[/B]"
    ["_xfResponseType"] => string(4) "json"
  }
}

 

[Karelke]

There's a similar error in another table:

XF\Db\Exception: MySQL query error [1366]: Incorrect string value: '\xF0\x9F\x98\x81' for column [ICODE]xenforo[/ICODE].[ICODE]xf_post[/ICODE].[ICODE]message[/ICODE] at row 1 src/XF/Db/AbstractStatement.php:217

UPDATE  [ICODE]xf_post[/ICODE] SET [ICODE]message[/ICODE] = ?, [ICODE]edit_count[/ICODE] = ? WHERE [ICODE]post_id[/ICODE] = 2727599

#0 src/XF/Db/Mysqli/Statement.php(196): XF\Db\AbstractStatement->getException('MySQL query err...', 1366, '22007')
#1 src/XF/Db/Mysqli/Statement.php(77): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1366, '22007')
#2 src/XF/Db/AbstractAdapter.php(94): XF\Db\Mysqli\Statement->execute()
#3 src/XF/Db/AbstractAdapter.php(322): XF\Db\AbstractAdapter->query('UPDATE  `xf_pos...', Array)
#4 src/XF/Mvc/Entity/Entity.php(1457): XF\Db\AbstractAdapter->update('xf_post', Array, '[ICODE]post_id[/ICODE] = 272...')
#5 src/XF/Mvc/Entity/Entity.php(1184): XF\Mvc\Entity\Entity->_saveToSource()
#6 src/XF/Service/Post/Editor.php(187): XF\Mvc\Entity\Entity->save(true, false)
#7 src/XF/Service/ValidateAndSavableTrait.php(40): XF\Service\Post\Editor->_save()
#8 src/XF/Pub/Controller/Post.php(135): XF\Service\Post\Editor->save()
#9 src/XF/Mvc/Dispatcher.php(321): XF\Pub\Controller\Post->actionEdit(Object(XF\Mvc\ParameterBag))
#10 src/XF/Mvc/Dispatcher.php(244): XF\Mvc\Dispatcher->dispatchClass('XF:Post', 'Edit', Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\Post), NULL)
#11 src/XF/Mvc/Dispatcher.php(100): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\Post), NULL)
#12 src/XF/Mvc/Dispatcher.php(50): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#13 src/XF/App.php(2177): XF\Mvc\Dispatcher->run()
#14 src/XF.php(390): XF\App->run()
#15 index.php(20): XF::runApp('XF\\Pub\\App')
#16 {main}

array(4) {
  ["url"] => string(25) "/forum/posts/2727599/edit"
  ["referrer"] => string(73) "/forum/threads/test.74767/page-100"
  ["_GET"] => array(0) {
  }
  ["_POST"] => array(8) {
    ["message_html"] => string(640) "<p><img src="/forum/attachments/naamloos-png.41831/" data-attachment="full:41831" alt="Naamloos.png" class="fr-fic fr-dii"></p><p>aaaahhhhhh,,,,,</p><p>Windows Vista Home 64-bit<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite1 fr-fic fr-dii" alt=":)" title="Smile&nbsp; &nbsp; :)" data-shortname=":)"></p><p><br></p><p>Test test test <img class="smilie smilie--emoji fr-fic fr-dii" alt="😁" title="Beaming face with smiling eyes    :grin:" src="https://cdn.jsdelivr.net/emojione/assets/4.5/png/64/1f601.png" data-shortname=":grin:">&nbsp;</p>"
    ["_xfInlineEdit"] => string(1) "1"
    ["attachment_hash"] => string(32) "8156f94dd5457feb29969dafa1eff8b2"
    ["attachment_hash_combined"] => string(87) "{"type":"post","context":{"post_id":2727599},"hash":"8156f94dd5457feb29969dafa1eff8b2"}"
    ["_xfToken"] => string(8) "[B]****[/B]"
    ["_xfRequestUri"] => string(49) "/forum/threads/test.74767/page-100"
    ["_xfWithData"] => string(1) "1"
    ["_xfResponseType"] => string(4) "json"
  }
}

 

[Chris D]

You seem to have enabled the following in your src/config.php file:

$config['fullUnicode'] = true;

But you haven't actually converted your database to support utf8mb4.

The steps to fix this are covered in the manual:

Emoji support | Manual | XenForo

End-user documentation for XenForo

xenforo.com

 

[Karelke]

Thank you @Chris D .

Converting to utf8mb4 fixes the issue indeed. Somehow I overlooked this step when we migrated from 1.5 to 2.1