XF 2.1 Problems enabling emojis

[TWOKam]

Hi,

I ran the 'php cmd.php xf:convert-utf8mb4 ' command however it didn't complete:

The following tables failed to convert:
* xf_search_index: xf_search_index: MySQL query error [1366]: Incorrect string value: '\x88\x01\x000...' for column 'metadata' at row 8434
You should contact the table creator for guidance. Failure to correct this may lead to unexpected behavior.

Conversion complete, but with errors!

Looking through threads here, I saw someone with a similar issue and they were advised to "truncate the xf_search_index table and run the command again. You’ll need to rebuild your search index afterwards though." I got my hosts to do this as I wasn't confident doing this myself. The hosts did this, when I tried running the first command again, this time I saw 'No convertable tables found. No action required.' - so that looks like that part is done. I then rebuild the search index.

I then added the below text to my src/config.php file:

$config['fullUnicode'] = true;

When I view the forums, I can see the extra emoji options when making a post. When I go to make a post using one of the emoji's I get the below error. I click OK, refresh the forum, the message and emoji are posted...however each time anyone uses an emoji this error will popup:

XF\Db\Exception: MySQL query error [1366]: Incorrect string value: '\xF0\x9F\x92\xAA' for column 'message' at row 1 in src/XF/Db/AbstractStatement.php at line 217
XF\Db\AbstractStatement->getException() in src/XF/Db/Mysqli/Statement.php at line 196
XF\Db\Mysqli\Statement->getException() in src/XF/Db/Mysqli/Statement.php at line 77
XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 94
XF\Db\AbstractAdapter->query() in src/XF/Db/AbstractAdapter.php at line 221
XF\Db\AbstractAdapter->insert() in src/XF/Search/Source/MySqlFt.php at line 96
XF\Search\Source\MySqlFt->index() in src/XF/Search/Search.php at line 40
XF\Search\Search->index() in src/XF/Behavior/Indexable.php at line 46
XF\Behavior\Indexable->XF\Behavior\{closure}() in src/XF.php at line 290
XF::triggerRunOnce() in src/XF/Mvc/Dispatcher.php at line 143
XF\Mvc\Dispatcher->dispatchLoop() in src/XF/Mvc/Dispatcher.php at line 50
XF\Mvc\Dispatcher->run() in src/XF/App.php at line 2177
XF\App->run() in src/XF.php at line 390
XF::runApp() in index.php at line 20

Is there something I'm missing - any suggestions on what could be going wrong?

 

[Chris D]

Not sure how it happens exactly, but I think we've seen it once before. It looks like there are one or more records in your search index which contain invalid characters or similar and it trips up the conversion. I recommend following these steps:

1. Set this back to false first:

$config['fullUnicode'] = false;

2. Perform the following query on your database (or use PhpMyAdmin or similar to empty/truncate the table):

TRUNCATE xf_search_index;

3. Run the php cmd.php xf:convert-utf8mb4 command again.

4. Re-enable full unicode:

$config['fullUnicode'] = true;

5. Go to Admin CP > Tools > Rebuild caches > Rebuild search index

Rebuilding the search index will take a while, and searching will be disrupted while it takes place, but once it is all done that should hopefully be all working.

 

[TWOKam]

I tried the above...still get the same message.

Is there anything else I can try?

EDIT - Just checking the error message in the control panel, it said:

Stack trace

INSERT INTO xf_search_index (content_type, content_id, title, message, metadata, item_date, user_id, discussion_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE title = VALUES(title), message = VALUES(message), metadata = VALUES(metadata), item_date = VALUES(item_date), user_id = VALUES(user_id), discussion_id = VALUES(discussion_id)

#0 src/XF/Db/Mysqli/Statement.php(196): XF\Db\AbstractStatement->getException('MySQL query err...', 1366, 'HY000')
#1 src/XF/Db/Mysqli/Statement.php(77): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1366, 'HY000')
#2 src/XF/Db/AbstractAdapter.php(94): XF\Db\Mysqli\Statement->execute()
#3 src/XF/Db/AbstractAdapter.php(221): XF\Db\AbstractAdapter->query('INSERT INTO `x...', Array)
#4 src/XF/Search/Source/MySqlFt.php(96): XF\Db\AbstractAdapter->insert('xf_search_index', Array, false, 'title = VALUES(...')
#5 src/XF/Search/Search.php(40): XF\Search\Source\MySqlFt->index(Object(XF\Search\IndexRecord))
#6 src/XF/Behavior/Indexable.php(46): XF\Search\Search->index('thread', Object(XF\Entity\Thread), false)
#7 src/XF.php(290): XF\Behavior\Indexable->XF\Behavior\{closure}()
#8 [internal function]: XF::triggerRunOnce()
#9 {main}

Is the below correct?

​

Looking at THIS THREAD he mentions:

Hi,
After upgrade to XF2.1 from XF1.5, I get below error.

When member try to change thread title included emoji

• XF\Db\Exception: MySQL query error [1366]: Incorrect string value: '\xF0\x9F\x98\x90 v...' for column xf_sv_user_log.content_title at row 1
• src/XF/Db/AbstractStatement.php:217

I have run below query to solve my problem FYI

ALTER TABLE xf_sv_user_log CHANGE content_title content_title VARCHAR(200) CHARACTER SET utf8mb4_general_ci

Should I be setting a type and collation to those headers which are unassigned?