Search engine problem

[sergarch]

Hello there!

I moved my site to GoDaddy shared hosting and have find some weird problem with search engine.

Mainly we are using Russian language as dafault.

The problem is that vBulletin's search engine on my forum do not recognize russian letters.

For example, when we enter some keywords in Russian in the search field - it returns nothng, but when we serching with English letters (such as nicknames) - it works perfectly.

Our forum using UTF-8, and all DB's is in utf8_general_ci

I rebuilded search index via vBulletin's adminCP with no results.
I uncommetted // $config['Mysqli']['charset'] = 'utf8'; and //define('DISABLE_HOOKS', true); - no luck.
I switched from custom to dafault style - no results.

I understand, that it is an encoding problem somewhere, but I don't know where and what to do.

Please advise me how to solve it! :confused:

http://www.xpam.org

[Lynne]

When you transferred the database, did you make sure the database Server Charset was the same as the old database Server Charset? That is different than the Server connection collation.

[sergarch]

Thank you for your response!
I will check it soon.

--------------- Added 1418896061 at 1418896061 ---------------

Hello again!

Just received an answer from GoDaddy:

Its possible that the CharSet is different for this server from your old server as we have a default of UTF-8 and do not give the user the ability to change it because the database is created by the hosting account and cannot be created manually. It may be possible for you to convert your data to UFT-8 however this would not be something we would be able to assist with.


As I see on PhpMyAdmin:
Server charset: UTF-8 Unicode (utf8)
Server connection collation: utf8_general_ci

What should I do now ?

[sergarch]

Nobody help?
Any suggestions?
:confused:

[Lynne]

What was the Server Charset on the old server?

Did you try rebuilding the Search Index after the server move?

[sergarch]

As I remember it was utf8_general_ci (the same)
Yes, I am rebuilded Search Index via adminCP.

[Lynne]

That isn't the server charset, that is the collation.

Did you still have access to the old server so you can try an export/import again?

Are you using vBulletin Search or Fulltext search?

If using vBulletin search, have you checked the word table and verified the field collation types within that table are utf8_general_ci ?

[sergarch]

Did you still have access to the old server so you can try an export/import again?


No.


Are you using vBulletin Search or Fulltext search?


vBulletin


If using vBulletin search, have you checked the word table and verified the field collation types within that table are utf8_general_ci ?

Every table on my DB is utf8_general_ci

I checked searchcore_text table within and have found that russian text going on abracadabra (?žб?‰ий Фо?€?ƒм instead of "Общий Форум") The source is in UTF-8, but decoding in CP1252!

I did query on my DB - SHOW VARIABLES LIKE "char%"; and received an answer:

Variable_name

Value

character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/

As noted, the character_set_server is latin1 instead of utf8. May be this is the main problem?

[Lynne]

Yes, that can cause problems.

But, within a table that is my_charset, you can have fields that are a_different_charset. So, I wanted you to check the *field* charset for the fields in the search tables, not the charset for the tables themselves.

Are you able to do a new import of the database to try the import again?

[sergarch]

Hello again!

I checked my tables and revealed all russian letters appears on abracadabra (Общий Форум) but it appears correctly to any visitors on my site.

So I have problem with vBulletin search engine only.
For now I have started to use "Google Custom Search Engine" hack to give a temporary solution for my site.

Under vBulletin's adminCP menu - diagnostic - system information I found this:

character_set_client latin1
character_set_connection latin1
character_set_database utf8
character_set_filesystem binary
character_set_results latin1
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
collation_connection latin1_swedish_ci
collation_database utf8_general_ci
collation_server latin1_swedish_ci

What are the necessary steps I have to do now?

[Lynne]

You have a mix of latin1 and utf8. I'd suggest setting up a test site and reading George's reply in this thread regarding how to go about fixing the issue - http://www.vbulletin.com/forum/forum/vbulletin-4/vbulletin-4-questions-problems-and-troubleshooting/402798-japanese-characters-not-showing-up-properly Do that first on your test site before doing it to your live site!!!

[webmastersun]

When you create a new database for the restoration from previous backups, you need to set your database as utf8_unicode_ci :)

[ozzy47]

@sergarch, did you ever get this sorted out?

[sergarch]

Dear friends!

Thank you all for your advises! :up:

First of all Marry Christmas (or Happy Hanukkah) and Happy New Year!!!

Second - I have found a solution for this problem.

Here are my steps for it:


1. Create an empty test DB (data base)
2. Download notepad++ (http://notepad-plus-plus.org)
3. Download your current DB via Sypex Dumper (https://sypex.net/) and force encoding to latin-1 (otherwise on utf8 or auto encoding it will be unreadable abracadabra in Russian)
4. Open DB in Notepad++ (DB is now readable with proper Russian letters) and manually change latin-1 to utf8 (it is in the first line) and save it
5. Upload your edited DB to the test DB on your server.
6. If you need to change collation_server latin1_swedish_ci (or any other) to utf8_general_ci here the simple query for SQL:
ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
7. Change your config.php with new DB's name and password and uncommette // $config['Mysqli']['charset'] = 'utf8';
8. If you need to zip-unzip with *.gz , please use editor like 7-Zip (http://www.7-zip.org/)


That's all ;)

Now vBulletin's search engine works perfectly without bugs :D

[ozzy47]

Glad to hear you got it sorted. Also thanks for sharing what you did to fix it, in case others run into the same situation. :)