How i can convert a mixed collation database to UTF-8 ?

[Sarge]

Have you removed "SET NAMES greek" from index.php?

What happens if you upload the Greek (not utf8) pack and switch to that in your profile?

[Yngwiedis]

Have you removed "SET NAMES greek" from index.php?

Yes. Right after the conversion.

What happens if you upload the Greek (not utf8) pack and switch to that in your profile?

Let me upload it first...

[Yngwiedis]

I upload the Greek language pack.
I select it from my profile, and i see only garbage characters like this: �κ�ακ�Ρ� Ρ�γα�ίΡ�

With this language pack the Ajax functions are working...

[Yngwiedis]

Forget this problem.
I just re upload the Greek UTF8 pack and now Ajax are working fine.

Just answer me this because i want to convert 2 more forums.

The option "Convert HTML-entities to UTF-8 characters" is something i need to use after the conversion ?
The conversion change all the fields collation but not the tables collation. The collation of the tables is latin1_swedish_ci again.
Is this correct ?

[Yngwiedis]

Forget this problem.
I just re upload the Greek UTF8 pack and now Ajax are working fine.

No is not working fine...

With the default Greek UTF8 files the Ajax is working fine.
But when i add the text strings of the mods i have installed then the Ajax is not working.
I save the files as UTF8 after i add the text strings, so i dont think i am doing something wrong

[Yngwiedis]

I think i found what file create the error.
Modifications.greek-utf8.php
But i dont see something wrong.
Can you check it please ?
The file is attached.

[agridoc]

It might well be caused by BOM in this file. BOM, Byte Order Mark, is a prefix (EF BB BF in hexadecimal) and gives information that it's a UTF-8 file.

BOM has been reported to cause some problems. By mistake I had saved the UTF-8 TP translation with BOM, I had used a new editor and didn't notice that I had to uncheck BOM in options. This resulted in optical recognition not working and some other minor problems.

The uploaded file contains BOM.

ο»Ώ<?php

Remove the first 3 "strange" chars and try.

[Yngwiedis]

Sorry agridoc but my editor dont show me any characters before

Code Select Expand

<?php

[agridoc]

It's a matter of editor. I also was not seeing BOM when I made, or opened for editing, the TP UTF-8 translation but they were saved that way.

Give it a try.

[Yngwiedis]

Heyyy.... Is working
One more time that i learn new things
Thank you very very much agridoc
Can you suggest me a better editor now that i will be envolved with UTF8 ?


So now i just need answer to my 2 questions :
The option "Convert HTML-entities to UTF-8 characters" is something i need to use after the conversion ?
The conversion change all the fields collation but not the tables collation. The collation of the tables is latin1_swedish_ci again.
Is this correct ?

[Sarge]

Heyyy.... Is working
One more time that i learn new things
Thank you very very much agridoc
Can you suggest me a better editor now that i will be envolved with UTF8 ?

I was also going to point to the same thing, but I had to rush to work. It's always good to see that community members get involved and help each other, even if they beat me in the process

So now i just need answer to my 2 questions :
The option "Convert HTML-entities to UTF-8 characters" is something i need to use after the conversion ?

It's not strictly necessary, but yes, it's best to run it every once in a while. It helps a lot with 1) database size and 2) search results. Once every few days should be enough, I guess.

However, I strongly suggest getting a database backup before running the option the first time. Just in case, you know.

The conversion change all the fields collation but not the tables collation. The collation of the tables is latin1_swedish_ci again.
Is this correct ?

First of all, check out the database collation and change it to utf8_general_ci if it's not. Only do this if the SMF tables are the only ones in the database. I guess the reason why the conversion to UTF-8 doesn't touch the database collation is because it's possible that other scripts have added their own tables and expect a character set different from the one used by SMF.

I'm not sure if the conversion to UTF-8 should have already changed the SMF table charsets to utf8 (and its default collation, which is usually utf8_general_ci), but I suggest that you change the collations manually. Let us know if you also need help with that.

And remember: always get backups before trying major operations.

[Yngwiedis]

Thank you very very much Sarge for all your help 
I am going to convert the other 2 forums now.
Wish me luck

[Sarge]

You're welcome!

Let us know how it goes. I won't mark this topic as solved until your two other forums are also converted fine.

[Yngwiedis]

Is there any fast way to convert all the tables collation at once ?

[Sarge]

Is there any fast way to convert all the tables collation at once ?

I will check about it tomorrow and will let you know.

[Yngwiedis]

I begin the conversion of the second forum.
I did all the things we said for the first forum.
I delete the data from smf_log_search tables and i disable the index for search.

But... i get this error

Σφάλμα βάσης δεδομένων: Column 'item_html' cannot be part of FULLTEXT index
Αρχείο: /home/alliance/public_html/community/Sources/Admin.php
Γραμμή: 1329

Its something wrong with the search index ?

[Sarge]

Σφάλμα βάσης δεδομένων: Column 'item_html' cannot be part of FULLTEXT index
Αρχείο: /home/alliance/public_html/community/Sources/Admin.php
Γραμμή: 1329

List of mods installed?

Can you attach the database structure (only for smf_ tables)?

I know that the solution is to simply go to that table in phpMyAdmin and remove the FULLTEXT index based on 'item_html' (not remove the 'item_html' column itself) and you should be able to recreate the index after converting. However, I'm not sure how the mod would be affected, so that's why I'm asking the two questions above.

[Yngwiedis]

Σφάλμα βάσης δεδομένων: Column 'item_html' cannot be part of FULLTEXT index
Αρχείο: /home/alliance/public_html/community/Sources/Admin.php
Γραμμή: 1329

List of mods installed?

E-Arcade      2.4.2
E-Arcade      2.5.0
TinyPortal      0.983

Can you attach the database structure (only for smf_ tables)?

There are a lot of tables from old installations of mods...

Tables structure is attached.

[Sarge]

The item_html column and the corresponding FULLTEXT index are in the smf_wowitem_cache table. If you're not using any WoW-related mod, delete that index (or even the whole table):

[Yngwiedis]

I dont need it right now so i will delete it