• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Elasticsearch Error on Rebuild - Invalid UTF-8 sequence in argument

#1
Hello!
I XenForo 1.2.1, Enhanced Search 1.0.2
I do a lot of times but still failel

display error
Code:
ErrorException: json_encode(): Invalid UTF-8 sequence in argument - library/XenES/Api.php:96
Generated By: admin, Hôm nay lúc 06:06
Stack Trace
#0 [internal function]: XenForo_Application::handlePhpError(2, 'json_encode(): ...', '/home/website/dom...', 96, Array)
#1 /home/website/public/library/XenES/Api.php(96): json_encode(Array)
#2 /home/website/public/library/XenES/Search/SourceHandler/ElasticSearch.php(50): XenES_Api::indexBulk('mydatabase', 'post', Array)
#3 /home/website/public/library/XenForo/Search/Indexer.php(44): XenES_Search_SourceHandler_ElasticSearch->insertIntoIndex('post', 5185303, '', 'up', 1349435975, 56030, 265224, Array)
#4 /home/website/public/library/XenForo/Search/DataHandler/Post.php(61): XenForo_Search_Indexer->insertIntoIndex('post', 5185303, '', 'up', 1349435975, 56030, 265224, Array)
#5 /home/website/public/library/XenForo/Search/DataHandler/Abstract.php(238): XenForo_Search_DataHandler_Post->_insertIntoIndex(Object(XenForo_Search_Indexer), Array, Array)
#6 /home/website/public/library/XenForo/Search/DataHandler/Post.php(133): XenForo_Search_DataHandler_Abstract->insertIntoIndex(Object(XenForo_Search_Indexer), Array, Array)
#7 /home/website/public/library/XenForo/Search/DataHandler/Post.php(103): XenForo_Search_DataHandler_Post->quickIndex(Object(XenForo_Search_Indexer), Array)
#8 /home/website/public/library/XenForo/Deferred/SearchIndex.php(72): XenForo_Search_DataHandler_Post->rebuildIndex(Object(XenForo_Search_Indexer), 5182523, 3000)
#9 /home/website/public/library/XenForo/Model/Deferred.php(198): XenForo_Deferred_SearchIndex->execute(Array, Array, 9.6194531917572, '')
#10 /home/website/public/library/XenForo/Model/Deferred.php(321): XenForo_Model_Deferred->runDeferred(Array, 9.6194531917572, '', false)
#11 /home/website/public/library/XenForo/Model/Deferred.php(274): XenForo_Model_Deferred->_runInternal(Array, 9.6194560527802, '', false)
#12 /home/website/library/XenForo/ViewRenderer/Abstract.php(352): XenForo_Model_Deferred->run(true, 9.6194560527802)
#13 /home/website/public/library/XenForo/ViewRenderer/Json.php(147): XenForo_ViewRenderer_Abstract::hasManualDeferredToRun()
#14 /home/website/public/library/XenForo/ViewRenderer/Json.php(185): XenForo_ViewRenderer_Json::_addDefaultParams(Array)
#15 /home/website/public/library/XenForo/ViewAdmin/Error/ServerError.php(47): XenForo_ViewRenderer_Json::jsonEncodeForOutput(Array)
#16 /home/website/public/library/XenForo/ViewRenderer/Abstract.php(227): XenForo_ViewAdmin_Error_ServerError->renderJson()
#17 /home/website/public/library/XenForo/ViewRenderer/Json.php(88): XenForo_ViewRenderer_Abstract->renderViewObject('XenForo_ViewAdm...', 'Json', Array, 'error_server_er...')
#18 /home/website/public/library/XenForo/FrontController.php(572): XenForo_ViewRenderer_Json->renderView('XenForo_ViewAdm...', Array, 'error_server_er...', NULL)
#19 /home/website/public/library/XenForo/FrontController.php(158): XenForo_FrontController->renderView(Object(XenForo_ControllerResponse_View), Object(XenForo_ViewRenderer_Json), Array)
#20 /home/website/public/admin.php(13): XenForo_FrontController->run()
#21 {main}
Request State
array(3) {
  ["url"] => string(49) "http://website/admin.php?tools/run-deferred"
  ["_GET"] => array(1) {
    ["tools/run-deferred"] => string(0) ""
  }
  ["_POST"] => array(5) {
    ["execute"] => string(1) "1"
    ["_xfRequestUri"] => string(24) "/admin.php?tools/rebuild"
    ["_xfNoRedirect"] => string(1) "1"
    ["_xfToken"] => string(8) "********"
    ["_xfResponseType"] => string(4) "json"
  }
}
similar to this case , in this topic
http://xenforo.com/community/threads/elasticsearch-running-but-xf-wont-reindex.26173/page-2#post-

hope to receive help !
please give me the solution? , Thank you very much

PS .
1 year ago I converted from phpbb to XenForo
and
'post', 5185303, the data on the phpbb , any solution to fix database ?

Sory my english poor
 
Last edited:
#6
Take the post number from the error, and search for that in the database via phpmyadmin, then edit the relevent data.
I can not remove manually, because the data I have so many non-utf8 character ,
Can you help me write a function , cleaning the string before passing it to json_encode ?
I found a function in php
http://php.net/manual/en/function.iconv.php
example
$s= iconv('UTF-8','UTF-8//IGNORE', $s);

Can you help me? I will thank you very much
 
Last edited:

Mike

XenForo developer
Staff member
#7
That (or similar) would have been done when you converted in the past.

I'm not totally sure how this comes up - the data should be validated as UTF-8 by MySQL, by the data conversion, and by our own input functions. Only thing I could think of is valid UTF-8 structure but not necessarily a valid character.

Unfortunately, what Slavik has recommended is really the only course we could recommend.
 
#8
That (or similar) would have been done when you converted in the past.

I'm not totally sure how this comes up - the data should be validated as UTF-8 by MySQL, by the data conversion, and by our own input functions. Only thing I could think of is valid UTF-8 structure but not necessarily a valid character.

Unfortunately, what Slavik has recommended is really the only course we could recommend.
@Mike
take a look at the characters, I took out from phpmyadmin
non-utf8 character
Here is the letter written by nokia mobile phone feature phone (T9 keypad nokia, Vietnam language )
Game giống game đẩy teng trong siêu thị đó mua bỏ teng vào rớt ra teng đ s40 mh 240x320
đang thích đây
Code:
Game giống game đẩy teng trong siêu thị đó mua bỏ teng vào rớt ra teng đó s40 mh 240x320
đang thích đây
in my database with billions of such characters. I can not make removal from the solution by Slavik

ps .
My forum is 50% using nokia feature phones
please give me a correct solution
thank you
 
Last edited:

Mike

XenForo developer
Staff member
#9
There's nothing not inherently UTF-8 there - it just looks like the combining diacritics aren't necessarily handled as expected in the default XF font. If they weren't UTF-8, none of the accents would be correct. You can see them correctly in the code tag as a different font is used. You can just change the message font.

This is likely separate from the original issue you're reporting.
 
#10
There's nothing not inherently UTF-8 there - it just looks like the combining diacritics aren't necessarily handled as expected in the default XF font. If they weren't UTF-8, none of the accents would be correct. You can see them correctly in the code tag as a different font is used. You can just change the message font.

This is likely separate from the original issue you're reporting.
Thank you very much !!!
Me the implementation replaced of the character for keyboard nokia feature phone and succeeded.
This character is from data the phpbb
Code:
UPDATE xf_post SET message = REPLACE(message, 'á', 'á');
UPDATE xf_post SET message = REPLACE(message, 'ả', 'ả');
UPDATE xf_post SET message = REPLACE(message, 'à', 'à');
UPDATE xf_post SET message = REPLACE(message, 'ã', 'ã');
UPDATE xf_post SET message = REPLACE(message, 'ạ', 'ạ');
UPDATE xf_post SET message = REPLACE(message, 'ắ', 'ắ');
UPDATE xf_post SET message = REPLACE(message, 'ẳ', 'ẳ');
UPDATE xf_post SET message = REPLACE(message, 'ằ', 'ằ');
UPDATE xf_post SET message = REPLACE(message, 'ẵ', 'ẵ');
UPDATE xf_post SET message = REPLACE(message, 'ặ', 'ặ');
UPDATE xf_post SET message = REPLACE(message, 'ấ', 'ấ');
UPDATE xf_post SET message = REPLACE(message, 'ẩ', 'ẩ');
UPDATE xf_post SET message = REPLACE(message, 'ầ', 'ầ');
UPDATE xf_post SET message = REPLACE(message, 'ẫ', 'ẫ');
UPDATE xf_post SET message = REPLACE(message, 'ậ', 'ậ');
UPDATE xf_post SET message = REPLACE(message, 'é', 'é');
UPDATE xf_post SET message = REPLACE(message, 'ẻ', 'ẻ');
UPDATE xf_post SET message = REPLACE(message, 'è', 'è');
UPDATE xf_post SET message = REPLACE(message, 'ẽ', 'ẽ');
UPDATE xf_post SET message = REPLACE(message, 'ẹ', 'ẹ');
UPDATE xf_post SET message = REPLACE(message, 'ế', 'ế');
UPDATE xf_post SET message = REPLACE(message, 'ể', 'ể');
UPDATE xf_post SET message = REPLACE(message, 'ề', 'ề');
UPDATE xf_post SET message = REPLACE(message, 'ễ', 'ễ');
UPDATE xf_post SET message = REPLACE(message, 'ệ', 'ệ');
UPDATE xf_post SET message = REPLACE(message, 'ó', 'ó');
UPDATE xf_post SET message = REPLACE(message, 'ỏ', 'ỏ');
UPDATE xf_post SET message = REPLACE(message, 'ò', 'ò');
UPDATE xf_post SET message = REPLACE(message, 'õ', 'õ');
UPDATE xf_post SET message = REPLACE(message, 'ọ', 'ọ');
UPDATE xf_post SET message = REPLACE(message, 'ố', 'ố');
UPDATE xf_post SET message = REPLACE(message, 'ổ', 'ổ');
UPDATE xf_post SET message = REPLACE(message, 'ồ', 'ồ');
UPDATE xf_post SET message = REPLACE(message, 'ỗ', 'ỗ');
UPDATE xf_post SET message = REPLACE(message, 'ộ', 'ộ');
UPDATE xf_post SET message = REPLACE(message, 'ớ', 'ớ');
UPDATE xf_post SET message = REPLACE(message, 'ở', 'ở');
UPDATE xf_post SET message = REPLACE(message, 'ờ', 'ờ');
UPDATE xf_post SET message = REPLACE(message, 'ỡ', 'ỡ');
UPDATE xf_post SET message = REPLACE(message, 'ợ', 'ợ');
UPDATE xf_post SET message = REPLACE(message, 'ú', 'ú');
UPDATE xf_post SET message = REPLACE(message, 'ủ', 'ủ');
UPDATE xf_post SET message = REPLACE(message, 'ù', 'ù');
UPDATE xf_post SET message = REPLACE(message, 'ũ', 'ũ');
UPDATE xf_post SET message = REPLACE(message, 'ụ', 'ụ');
UPDATE xf_post SET message = REPLACE(message, 'ứ', 'ứ');
UPDATE xf_post SET message = REPLACE(message, 'ử', 'ử');
UPDATE xf_post SET message = REPLACE(message, 'ừ', 'ừ');
UPDATE xf_post SET message = REPLACE(message, 'ữ', 'ữ');
UPDATE xf_post SET message = REPLACE(message, 'ự', 'ự');
UPDATE xf_post SET message = REPLACE(message, 'ý', 'ý');
UPDATE xf_post SET message = REPLACE(message, 'ỷ', 'ỷ');
UPDATE xf_post SET message = REPLACE(message, 'ỳ', 'ỳ');
UPDATE xf_post SET message = REPLACE(message, 'ỹ', 'ỹ');
UPDATE xf_post SET message = REPLACE(message, 'ỵ', 'ỵ');
UPDATE xf_post SET message = REPLACE(message, 'í', 'í');
UPDATE xf_post SET message = REPLACE(message, 'ỉ', 'ỉ');
UPDATE xf_post SET message = REPLACE(message, 'ì', 'ì');
UPDATE xf_post SET message = REPLACE(message, 'ĩ', 'ĩ');
UPDATE xf_post SET message = REPLACE(message, 'ị', 'ị');
UPDATE xf_post SET message = REPLACE(message, 'Á', 'Á');
UPDATE xf_post SET message = REPLACE(message, 'Ả', 'Ả');
UPDATE xf_post SET message = REPLACE(message, 'À', 'À');
UPDATE xf_post SET message = REPLACE(message, 'Ã', 'Ã');
UPDATE xf_post SET message = REPLACE(message, 'Ạ', 'Ạ');
UPDATE xf_post SET message = REPLACE(message, 'Ắ', 'Ắ');
UPDATE xf_post SET message = REPLACE(message, 'Ẳ', 'Ẳ');
UPDATE xf_post SET message = REPLACE(message, 'Ằ', 'Ằ');
UPDATE xf_post SET message = REPLACE(message, 'Ẵ', 'Ẵ');
UPDATE xf_post SET message = REPLACE(message, 'Ặ', 'Ặ');
UPDATE xf_post SET message = REPLACE(message, 'Ấ', 'Ấ');
UPDATE xf_post SET message = REPLACE(message, 'Ẩ', 'Ẩ');
UPDATE xf_post SET message = REPLACE(message, 'Ầ', 'Ầ');
UPDATE xf_post SET message = REPLACE(message, 'Ẫ', 'Ẫ');
UPDATE xf_post SET message = REPLACE(message, 'Ậ', 'Ậ');
UPDATE xf_post SET message = REPLACE(message, 'É', 'É');
UPDATE xf_post SET message = REPLACE(message, 'Ẻ', 'Ẻ');
UPDATE xf_post SET message = REPLACE(message, 'È', 'È');
UPDATE xf_post SET message = REPLACE(message, 'Ẽ', 'Ẽ');
UPDATE xf_post SET message = REPLACE(message, 'Ẹ', 'Ẹ');
UPDATE xf_post SET message = REPLACE(message, 'Ế', 'Ế');
UPDATE xf_post SET message = REPLACE(message, 'Ể', 'Ể');
UPDATE xf_post SET message = REPLACE(message, 'Ề', 'Ề');
UPDATE xf_post SET message = REPLACE(message, 'Ễ', 'Ễ');
UPDATE xf_post SET message = REPLACE(message, 'Ệ', 'Ệ');
UPDATE xf_post SET message = REPLACE(message, 'Ó', 'Ó');
UPDATE xf_post SET message = REPLACE(message, 'Ỏ', 'Ỏ');
UPDATE xf_post SET message = REPLACE(message, 'Ò', 'Ò');
UPDATE xf_post SET message = REPLACE(message, 'Õ', 'Õ');
UPDATE xf_post SET message = REPLACE(message, 'Ọ', 'Ọ');
UPDATE xf_post SET message = REPLACE(message, 'Ố', 'Ố');
UPDATE xf_post SET message = REPLACE(message, 'Ổ', 'Ổ');
UPDATE xf_post SET message = REPLACE(message, 'Ồ', 'Ồ');
UPDATE xf_post SET message = REPLACE(message, 'Ỗ', 'Ỗ');
UPDATE xf_post SET message = REPLACE(message, 'Ộ', 'Ộ');
UPDATE xf_post SET message = REPLACE(message, 'Ớ', 'Ớ');
UPDATE xf_post SET message = REPLACE(message, 'Ở', 'Ở');
UPDATE xf_post SET message = REPLACE(message, 'Ờ', 'Ờ');
UPDATE xf_post SET message = REPLACE(message, 'Ỡ', 'Ỡ');
UPDATE xf_post SET message = REPLACE(message, 'Ợ', 'Ợ');
UPDATE xf_post SET message = REPLACE(message, 'Ú', 'Ú');
UPDATE xf_post SET message = REPLACE(message, 'Ủ', 'Ủ');
UPDATE xf_post SET message = REPLACE(message, 'Ù', 'Ù');
UPDATE xf_post SET message = REPLACE(message, 'Ũ', 'Ũ');
UPDATE xf_post SET message = REPLACE(message, 'Ụ', 'Ụ');
UPDATE xf_post SET message = REPLACE(message, 'Ứ', 'Ứ');
UPDATE xf_post SET message = REPLACE(message, 'Ử', 'Ử');
UPDATE xf_post SET message = REPLACE(message, 'Ừ', 'Ừ');
UPDATE xf_post SET message = REPLACE(message, 'Ữ', 'Ữ');
UPDATE xf_post SET message = REPLACE(message, 'Ự', 'Ự');
UPDATE xf_post SET message = REPLACE(message, 'Ý', 'Ý');
UPDATE xf_post SET message = REPLACE(message, 'Ỷ', 'Ỷ');
UPDATE xf_post SET message = REPLACE(message, 'Ỳ', 'Ỳ');
UPDATE xf_post SET message = REPLACE(message, 'Ỹ', 'Ỹ');
UPDATE xf_post SET message = REPLACE(message, 'Ỵ', 'Ỵ');
UPDATE xf_post SET message = REPLACE(message, 'Í', 'Í');
UPDATE xf_post SET message = REPLACE(message, 'Ỉ', 'Ỉ');
UPDATE xf_post SET message = REPLACE(message, 'Ì', 'Ì');
UPDATE xf_post SET message = REPLACE(message, 'Ĩ', 'Ĩ');
UPDATE xf_post SET message = REPLACE(message, 'Ị', 'Ị');
mysql2.PNG
Elasticsearch Work :)
I am really happy with Xenforo
work.PNG
s.PNG
 
Last edited:

Slavik

XenForo moderator
Staff member
#11
So an FYI @Mike and others.

If you run into this error and are unable to get the post ID you can do the following.

Code:
mysqldump -uUSER -pPASS -c -e --default-character-set=utf8 --single-transaction --skip-set-charset --add-drop-database -B DATABASENAME > dump.sql

iconv -f UTF-8 -t UTF-8//IGNORE dump.sql > dump-fixed.sql
 
mysql -u USER -p
 
use DATABASENAME;
 
SET NAMES 'utf8';
 
source /path/to/dump-fixed.sql
 

caoanh204

Active member
#12
It should be:

UPDATE xf_post SET message = REPLACE(message, 'á', 'á');
UPDATE xf_post SET message = REPLACE(message, 'ả', 'ả');
UPDATE xf_post SET message = REPLACE(message, 'à', 'à');
UPDATE xf_post SET message = REPLACE(message, 'ã', 'ã');
UPDATE xf_post SET message = REPLACE(message, 'ạ', 'ạ');
UPDATE xf_post SET message = REPLACE(message, 'ắ', 'ắ');
UPDATE xf_post SET message = REPLACE(message, 'ẳ', 'ẳ');
UPDATE xf_post SET message = REPLACE(message, 'ằ', 'ằ');
UPDATE xf_post SET message = REPLACE(message, 'ẵ', 'ẵ');
UPDATE xf_post SET message = REPLACE(message, 'ặ', 'ặ');
UPDATE xf_post SET message = REPLACE(message, 'ấ', 'ấ');
UPDATE xf_post SET message = REPLACE(message, 'ẩ', 'ẩ');
UPDATE xf_post SET message = REPLACE(message, 'ầ', 'ầ');
UPDATE xf_post SET message = REPLACE(message, 'ẫ', 'ẫ');
UPDATE xf_post SET message = REPLACE(message, 'ậ', 'ậ');
UPDATE xf_post SET message = REPLACE(message, 'é', 'é');
UPDATE xf_post SET message = REPLACE(message, 'ẻ', 'ẻ');
UPDATE xf_post SET message = REPLACE(message, 'è', 'è');
UPDATE xf_post SET message = REPLACE(message, 'ẽ', 'ẽ');
UPDATE xf_post SET message = REPLACE(message, 'ẹ', 'ẹ');
UPDATE xf_post SET message = REPLACE(message, 'ế', 'ế');
UPDATE xf_post SET message = REPLACE(message, 'ể', 'ể');
UPDATE xf_post SET message = REPLACE(message, 'ề', 'ề');
UPDATE xf_post SET message = REPLACE(message, 'ễ', 'ễ');
UPDATE xf_post SET message = REPLACE(message, 'ệ', 'ệ');
UPDATE xf_post SET message = REPLACE(message, 'ó', 'ó');
UPDATE xf_post SET message = REPLACE(message, 'ỏ', 'ỏ');
UPDATE xf_post SET message = REPLACE(message, 'ò', 'ò');
UPDATE xf_post SET message = REPLACE(message, 'õ', 'õ');
UPDATE xf_post SET message = REPLACE(message, 'ọ', 'ọ');
UPDATE xf_post SET message = REPLACE(message, 'ố', 'ố');
UPDATE xf_post SET message = REPLACE(message, 'ổ', 'ổ');
UPDATE xf_post SET message = REPLACE(message, 'ồ', 'ồ');
UPDATE xf_post SET message = REPLACE(message, 'ỗ', 'ỗ');
UPDATE xf_post SET message = REPLACE(message, 'ộ', 'ộ');
UPDATE xf_post SET message = REPLACE(message, 'ớ', 'ớ');
UPDATE xf_post SET message = REPLACE(message, 'ở', 'ở');
UPDATE xf_post SET message = REPLACE(message, 'ờ', 'ờ');
UPDATE xf_post SET message = REPLACE(message, 'ỡ', 'ỡ');
UPDATE xf_post SET message = REPLACE(message, 'ợ', 'ợ');
UPDATE xf_post SET message = REPLACE(message, 'ú', 'ú');
UPDATE xf_post SET message = REPLACE(message, 'ủ', 'ủ');
UPDATE xf_post SET message = REPLACE(message, 'ù', 'ù');
UPDATE xf_post SET message = REPLACE(message, 'ũ', 'ũ');
UPDATE xf_post SET message = REPLACE(message, 'ụ', 'ụ');
UPDATE xf_post SET message = REPLACE(message, 'ứ', 'ứ');
UPDATE xf_post SET message = REPLACE(message, 'ử', 'ử');
UPDATE xf_post SET message = REPLACE(message, 'ừ', 'ừ');
UPDATE xf_post SET message = REPLACE(message, 'ữ', 'ữ');
UPDATE xf_post SET message = REPLACE(message, 'ự', 'ự');
UPDATE xf_post SET message = REPLACE(message, 'ý', 'ý');
UPDATE xf_post SET message = REPLACE(message, 'ỷ', 'ỷ');
UPDATE xf_post SET message = REPLACE(message, 'ỳ', 'ỳ');
UPDATE xf_post SET message = REPLACE(message, 'ỹ', 'ỹ');
UPDATE xf_post SET message = REPLACE(message, 'ỵ', 'ỵ');
UPDATE xf_post SET message = REPLACE(message, 'í', 'í');
UPDATE xf_post SET message = REPLACE(message, 'ỉ', 'ỉ');
UPDATE xf_post SET message = REPLACE(message, 'ì', 'ì');
UPDATE xf_post SET message = REPLACE(message, 'ĩ', 'ĩ');
UPDATE xf_post SET message = REPLACE(message, 'ị', 'ị');
UPDATE xf_post SET message = REPLACE(message, 'Á', 'Á');
UPDATE xf_post SET message = REPLACE(message, 'Ả', 'Ả');
UPDATE xf_post SET message = REPLACE(message, 'À', 'À');
UPDATE xf_post SET message = REPLACE(message, 'Ã', 'Ã');
UPDATE xf_post SET message = REPLACE(message, 'Ạ', 'Ạ');
UPDATE xf_post SET message = REPLACE(message, 'Ắ', 'Ắ');
UPDATE xf_post SET message = REPLACE(message, 'Ẳ', 'Ẳ');
UPDATE xf_post SET message = REPLACE(message, 'Ằ', 'Ằ');
UPDATE xf_post SET message = REPLACE(message, 'Ẵ', 'Ẵ');
UPDATE xf_post SET message = REPLACE(message, 'Ặ', 'Ặ');
UPDATE xf_post SET message = REPLACE(message, 'Ấ', 'Ấ');
UPDATE xf_post SET message = REPLACE(message, 'Ẩ', 'Ẩ');
UPDATE xf_post SET message = REPLACE(message, 'Ầ', 'Ầ');
UPDATE xf_post SET message = REPLACE(message, 'Ẫ', 'Ẫ');
UPDATE xf_post SET message = REPLACE(message, 'Ậ', 'Ậ');
UPDATE xf_post SET message = REPLACE(message, 'É', 'É');
UPDATE xf_post SET message = REPLACE(message, 'Ẻ', 'Ẻ');
UPDATE xf_post SET message = REPLACE(message, 'È', 'È');
UPDATE xf_post SET message = REPLACE(message, 'Ẽ', 'Ẽ');
UPDATE xf_post SET message = REPLACE(message, 'Ẹ', 'Ẹ');
UPDATE xf_post SET message = REPLACE(message, 'Ế', 'Ế');
UPDATE xf_post SET message = REPLACE(message, 'Ể', 'Ể');
UPDATE xf_post SET message = REPLACE(message, 'Ề', 'Ề');
UPDATE xf_post SET message = REPLACE(message, 'Ễ', 'Ễ');
UPDATE xf_post SET message = REPLACE(message, 'Ệ', 'Ệ');
UPDATE xf_post SET message = REPLACE(message, 'Ó', 'Ó');
UPDATE xf_post SET message = REPLACE(message, 'Ỏ', 'Ỏ');
UPDATE xf_post SET message = REPLACE(message, 'Ò', 'Ò');
UPDATE xf_post SET message = REPLACE(message, 'Õ', 'Õ');
UPDATE xf_post SET message = REPLACE(message, 'Ọ', 'Ọ');
UPDATE xf_post SET message = REPLACE(message, 'Ố', 'Ố');
UPDATE xf_post SET message = REPLACE(message, 'Ổ', 'Ổ');
UPDATE xf_post SET message = REPLACE(message, 'Ồ', 'Ồ');
UPDATE xf_post SET message = REPLACE(message, 'Ỗ', 'Ỗ');
UPDATE xf_post SET message = REPLACE(message, 'Ộ', 'Ộ');
UPDATE xf_post SET message = REPLACE(message, 'Ớ', 'Ớ');
UPDATE xf_post SET message = REPLACE(message, 'Ở', 'Ở');
UPDATE xf_post SET message = REPLACE(message, 'Ờ', 'Ờ');
UPDATE xf_post SET message = REPLACE(message, 'Ỡ', 'Ỡ');
UPDATE xf_post SET message = REPLACE(message, 'Ợ', 'Ợ');
UPDATE xf_post SET message = REPLACE(message, 'Ú', 'Ú');
UPDATE xf_post SET message = REPLACE(message, 'Ủ', 'Ủ');
UPDATE xf_post SET message = REPLACE(message, 'Ù', 'Ù');
UPDATE xf_post SET message = REPLACE(message, 'Ũ', 'Ũ');
UPDATE xf_post SET message = REPLACE(message, 'Ụ', 'Ụ');
UPDATE xf_post SET message = REPLACE(message, 'Ứ', 'Ứ');
UPDATE xf_post SET message = REPLACE(message, 'Ử', 'Ử');
UPDATE xf_post SET message = REPLACE(message, 'Ừ', 'Ừ');
UPDATE xf_post SET message = REPLACE(message, 'Ữ', 'Ữ');
UPDATE xf_post SET message = REPLACE(message, 'Ự', 'Ự');
UPDATE xf_post SET message = REPLACE(message, 'Ý', 'Ý');
UPDATE xf_post SET message = REPLACE(message, 'Ỷ', 'Ỷ');
UPDATE xf_post SET message = REPLACE(message, 'Ỳ', 'Ỳ');
UPDATE xf_post SET message = REPLACE(message, 'Ỹ', 'Ỹ');
UPDATE xf_post SET message = REPLACE(message, 'Ỵ', 'Ỵ');
UPDATE xf_post SET message = REPLACE(message, 'Í', 'Í');
UPDATE xf_post SET message = REPLACE(message, 'Ỉ', 'Ỉ');
UPDATE xf_post SET message = REPLACE(message, 'Ì', 'Ì');
UPDATE xf_post SET message = REPLACE(message, 'Ĩ', 'Ĩ');
UPDATE xf_post SET message = REPLACE(message, 'Ị', 'Ị');
 

Karelke

Active member
#13
Alternatively you could write a simple php script:

PHP:
error_reporting(-1);

$db = new PDO('<server settings>');

$sql = 'SELECT * FROM xf_post';
$update = 'UPDATE xf_post SET message = :message WHERE post_id = :id';

$prepare = $db->prepare($update);

foreach ($db->query($sql) as $row) {
    $prepare->execute(array(
        'message' => mb_convert_encoding($row['message'], '<source encoding>', 'UTF-8'),
        'id' => $row['thread_id'],
    ));
}