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

hungphutho · Aug 30, 2013

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

Slavik · Aug 30, 2013

No easy fix im afriad, you'll have to manually remove the non-utf8 character(s).

hungphutho · Aug 30, 2013

Slavik said:
No easy fix im afriad, you'll have to manually remove the non-utf8 character(s).

Thanks for your quick reply
manually remove the non-utf8 character ?
How to search non-utf8 character ?
I have deleted a lot of posts

Slavik · Aug 30, 2013

hungphutho said:
Thanks for your quick reply
manually remove the non-utf8 character ?
How to search non-utf8 character ?
I have deleted a lot of posts

Take the post number from the error, and search for that in the database via phpmyadmin, then edit the relevent data.

hungphutho · Aug 30, 2013

thank you!
I've made clear lot, 1.4 million post were deleted, Take the post number from the error. I started boredom

hungphutho · Aug 31, 2013

Slavik said:
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

Mike · Aug 31, 2013

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.

hungphutho · Aug 31, 2013

Mike said:
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 thị đó mua bỏ teng vào rớt ra teng đó s40 mh 240x320
đang thích đây

Code:

Game giống game đẩy teng trong siêu thị đó mua bỏ teng vào rớt ra teng đó s40 mh 240x320
đang thí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

Mike · Aug 31, 2013

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.

hungphutho · Aug 31, 2013

Mike said:
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, 'Ị', 'Ị');

Elasticsearch Work

I am really happy with Xenforo

Slavik · Jan 11, 2014

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

Cao · Sep 1, 2014

It should be:

UPDATE xf_post SET message = REPLACE(message, 'á', 'á');
UPDATE xf_post SET message = REPLACE(message, 'ả', 'ả');
UPDATE xf_post SET message = REPLACE(message, 'à', 'à');
UPDATE xf_post SET message = REPLACE(message, 'ã', 'ã');
UPDATE xf_post SET message = REPLACE(message, 'ạ', 'ạ');
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, 'é', 'é');
UPDATE xf_post SET message = REPLACE(message, 'ẻ', 'ẻ');
UPDATE xf_post SET message = REPLACE(message, 'è', 'è');
UPDATE xf_post SET message = REPLACE(message, 'ẽ', 'ẽ');
UPDATE xf_post SET message = REPLACE(message, 'ẹ', 'ẹ');
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, 'ó', 'ó');
UPDATE xf_post SET message = REPLACE(message, 'ỏ', 'ỏ');
UPDATE xf_post SET message = REPLACE(message, 'ò', 'ò');
UPDATE xf_post SET message = REPLACE(message, 'õ', 'õ');
UPDATE xf_post SET message = REPLACE(message, 'ọ', 'ọ');
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, 'ú', 'ú');
UPDATE xf_post SET message = REPLACE(message, 'ủ', 'ủ');
UPDATE xf_post SET message = REPLACE(message, 'ù', 'ù');
UPDATE xf_post SET message = REPLACE(message, 'ũ', 'ũ');
UPDATE xf_post SET message = REPLACE(message, 'ụ', 'ụ');
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, 'ý', 'ý');
UPDATE xf_post SET message = REPLACE(message, 'ỷ', 'ỷ');
UPDATE xf_post SET message = REPLACE(message, 'ỳ', 'ỳ');
UPDATE xf_post SET message = REPLACE(message, 'ỹ', 'ỹ');
UPDATE xf_post SET message = REPLACE(message, 'ỵ', 'ỵ');
UPDATE xf_post SET message = REPLACE(message, 'í', 'í');
UPDATE xf_post SET message = REPLACE(message, 'ỉ', 'ỉ');
UPDATE xf_post SET message = REPLACE(message, 'ì', 'ì');
UPDATE xf_post SET message = REPLACE(message, 'ĩ', 'ĩ');
UPDATE xf_post SET message = REPLACE(message, 'ị', 'ị');
UPDATE xf_post SET message = REPLACE(message, 'Á', 'Á');
UPDATE xf_post SET message = REPLACE(message, 'Ả', 'Ả');
UPDATE xf_post SET message = REPLACE(message, 'À', 'À');
UPDATE xf_post SET message = REPLACE(message, 'Ã', 'Ã');
UPDATE xf_post SET message = REPLACE(message, 'Ạ', 'Ạ');
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, 'É', 'É');
UPDATE xf_post SET message = REPLACE(message, 'Ẻ', 'Ẻ');
UPDATE xf_post SET message = REPLACE(message, 'È', 'È');
UPDATE xf_post SET message = REPLACE(message, 'Ẽ', 'Ẽ');
UPDATE xf_post SET message = REPLACE(message, 'Ẹ', 'Ẹ');
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, 'Ó', 'Ó');
UPDATE xf_post SET message = REPLACE(message, 'Ỏ', 'Ỏ');
UPDATE xf_post SET message = REPLACE(message, 'Ò', 'Ò');
UPDATE xf_post SET message = REPLACE(message, 'Õ', 'Õ');
UPDATE xf_post SET message = REPLACE(message, 'Ọ', 'Ọ');
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, 'Ú', 'Ú');
UPDATE xf_post SET message = REPLACE(message, 'Ủ', 'Ủ');
UPDATE xf_post SET message = REPLACE(message, 'Ù', 'Ù');
UPDATE xf_post SET message = REPLACE(message, 'Ũ', 'Ũ');
UPDATE xf_post SET message = REPLACE(message, 'Ụ', 'Ụ');
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, 'Ý', 'Ý');
UPDATE xf_post SET message = REPLACE(message, 'Ỷ', 'Ỷ');
UPDATE xf_post SET message = REPLACE(message, 'Ỳ', 'Ỳ');
UPDATE xf_post SET message = REPLACE(message, 'Ỹ', 'Ỹ');
UPDATE xf_post SET message = REPLACE(message, 'Ỵ', 'Ỵ');
UPDATE xf_post SET message = REPLACE(message, 'Í', 'Í');
UPDATE xf_post SET message = REPLACE(message, 'Ỉ', 'Ỉ');
UPDATE xf_post SET message = REPLACE(message, 'Ì', 'Ì');
UPDATE xf_post SET message = REPLACE(message, 'Ĩ', 'Ĩ');
UPDATE xf_post SET message = REPLACE(message, 'Ị', 'Ị');

Karelke · Sep 4, 2014

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'],
    ));
}

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

hungphutho

Member

Slavik

XenForo moderator

hungphutho

Member

Slavik

XenForo moderator

hungphutho

Member

hungphutho

Member

Mike

XenForo developer

hungphutho

Member

Mike

XenForo developer

hungphutho

Member

Slavik

XenForo moderator

Cao

Active member

Karelke

Well-known member

Similar threads

We value your privacy