Hello...
I have a very large database with 2 different characters sets and collations in various tables.
I want to make it compatible with UTF-8 because i have a lot of problems because of the difference in character sets.
I am trying to convert it trought the SMF relative function but is not working.
You now any other way to do it ?
Thank you.
Which character sets and collations?
Is just the database mixed (with some tables in one charset and other tables in another charset) or are the tables mixed too (with some fields in one charset and other fields in the other charset)?
Character Sets : latin1 and greek
Collations : latin1_general_ci and greek_general_ci
Both of the situations you said is happening...
What is the character set and collation for the database?
It should be mentioned in phpMyAdmin: select your database (but not the tables) and look at the bottom. It should show something like this:
(http://i26.tinypic.com/15dmc6u.png)
[edit]
Also click on the tables and check their collations too.
Yes the whole database is latin1_swedish_ci, and some of the tables are in latin1_swedish_ci and some are greek_general_ci
When you go in the tables that use latin1_swedish_ci, are there any fields/columns with collation greek_general_ci?
Also, when you go in the tables that use greek_general_ci, are there any fields/columns with collation latin1_swedish_ci?
Yes on both questions...
It's a bit hard to say what exactly you should do without looking at the forum and the database directly, but you can try putting the forum in maintenance mode, then open phpMyAdmin and start switching all tables and fields that are in greek_general_ci to latin1_general_ci, or vice-versa. What's important is that all table and field collations should match the collation of the data itself.
Can you post a link to your forum?
Quote from: Sarge on February 05, 2008, 01:35:00 AM
Can you post a link to your forum?
My main forum is private. Sorry about this but it has to do with a game community and i cant give you access because if i did the other admins will cut my head off.
I can give you a link to another forum with the exact same problem.
http://community.hellas-alliance.gr/
Right now the data are displaying correctly because i add this query to the index.php
Quotemysql_query("SET NAMES 'greek'");
If i remove it i see only question marks.
If this helps the problem start because another fellow admin of the forum add the query above in the index.php for a reason that i dont know...
Quote from: Sarge on February 05, 2008, 01:35:00 AMIt's a bit hard to say what exactly you should do without looking at the forum and the database directly, but you can try putting the forum in maintenance mode, then open phpMyAdmin and start switching all tables and fields that are in greek_general_ci to latin1_general_ci, or vice-versa.
If i do it with phpMyAdmin i will spend many many hours. There is not another easiest way ?
Quote from: Sarge on February 05, 2008, 01:35:00 AM
What's important is that all table and field collations should match the collation of the data itself.
Sorry but i dont think i understand exactly what you mean :-\
It's important to know how the data are saved -- in other words, whether posts etc. are saved as greek_general_ci or latin1_swedish_ci (regardless of the collation of the database, tables, fields etc).
Where exactly is mysql_query("SET NAMES 'greek'"); in index.php?
I suggest the following:
Create a test forum in a separate directory and on a separate database. For example, if your forum is in www.yourforum.com, you can create the test forum at www.yourforum.com/test/ and install SMF there, using fresh files from the SMF 1.1.4 Full Install package, without any mods.
Then export the database of your main forum and import it into the database of the test forum, overwriting the original tables for the test forum. Because of the different character sets, you need to use "SET NAMES insert_character_set_here". It's quite possible that "SET NAMES greek" will do the job for both export and import. Then see if the test forum displays everything correctly.
How exactly you do the data export and import depends on the kind of access you have on the server. Do you have shell access? If you do, you should be able to use the mysqldump and mysql commands directly, for speed. If not, you would have to use some export/import tool and specify the character set there. You could use create_backup.php and restore_backup.php, with some necessary modifications.
In any case, get backup copies of the databases before you start. Even backups might not be saved correctly, though, because of different charsets, which is why I suggest working on a test forum first.
Let us know if you need more info. :)
Quote from: Sarge on February 05, 2008, 08:50:32 AM
It's important to know how the data are saved -- in other words, whether posts etc. are saved as greek_general_ci or latin1_swedish_ci (regardless of the collation of the database, tables, fields etc).
Where exactly is mysql_query("SET NAMES 'greek'"); in index.php?
Right here :
// Connect to the MySQL database.
if (empty($db_persist))
$db_connection = @mysql_connect($db_server, $db_user, $db_passwd);
else
$db_connection = @mysql_pconnect($db_server, $db_user, $db_passwd);
mysql_query("SET NAMES 'greek'");
// Show an error if the connection couldn't be made.
if (!$db_connection || !@mysql_select_db($db_name, $db_connection))
db_fatal_error();
Quote from: Sarge on February 05, 2008, 08:50:32 AM
I suggest the following:
Create a test forum in a separate directory and on a separate database. For example, if your forum is in www.yourforum.com, you can create the test forum at www.yourforum.com/test/ and install SMF there, using fresh files from the SMF 1.1.4 Full Install package, without any mods.
Then export the database of your main forum and import it into the database of the test forum, overwriting the original tables for the test forum. Because of the different character sets, you need to use "SET NAMES insert_character_set_here". It's quite possible that "SET NAMES greek" will do the job for both export and import. Then see if the test forum displays everything correctly.
If i just copy the database to a new one with phpMyAdmin i will have a problem ?
Quote from: Sarge on February 05, 2008, 08:50:32 AM
How exactly you do the data export and import depends on the kind of access you have on the server. Do you have shell access? If you do, you should be able to use the mysqldump and mysql commands directly, for speed. If not, you would have to use some export/import tool and specify the character set there. You could use create_backup.php and restore_backup.php, with some necessary modifications.
In any case, get backup copies of the databases before you start. Even backups might not be saved correctly, though, because of different charsets, which is why I suggest working on a test forum first.
Let us know if you need more info. :)
No i dont have shell access :(
I will install the test forum now and then i will tell you news...
Quote from: Yngwiedis on February 05, 2008, 09:35:36 AM
If i just copy the database to a new one with phpMyAdmin i will have a problem ?
There's only one way to find out... Try and see. :)
I forgot to tell you before, but after you copy/import the database, you need to upload repair_settings.php (http://docs.simplemachines.org/index.php?topic=663) to the test forum directory and visit it with your browser. Check the database info and click on the blue "Recommended" links in the Paths & URLs section. Delete repair_settings.php immediately after saving, for security reasons.
Quote from: Yngwiedis on February 05, 2008, 09:35:36 AM
No i dont have shell access :(
I will install the test forum now and then i will tell you news...
OK then, let us know how copying the database goes. :)
How big is the database?
Quote from: Sarge on February 05, 2008, 10:24:51 AM
Quote from: Yngwiedis on February 05, 2008, 09:35:36 AM
If i just copy the database to a new one with phpMyAdmin i will have a problem ?
There's only one way to find out... Try and see. :)
I forgot to tell you before, but after you copy/import the database, you need to upload repair_settings.php (http://docs.simplemachines.org/index.php?topic=663) to the test forum directory and visit it with your browser. Check the database info and click on the blue "Recommended" links in the Paths & URLs section. Delete repair_settings.php immediately after saving, for security reasons.
OK i did it.
Quote from: Sarge on February 05, 2008, 10:24:51 AM
Quote from: Yngwiedis on February 05, 2008, 09:35:36 AM
No i dont have shell access :(
I will install the test forum now and then i will tell you news...
OK then, let us know how copying the database goes. :)
How big is the database?
The database is about 250 MBs...
The test forum is at : http://hellas-alliance.gr/commtest/index.php
I add again in the index.php the query to show correct the data...
Quote from: Yngwiedis on February 05, 2008, 11:36:34 AM
The test forum is at : http://hellas-alliance.gr/commtest/index.php
I add again in the index.php the query to show correct the data...
Guest access is disabled, so I can't see anything. I noticed that English is still the default language. You might want to add the Greek language pack if you use that in your main forum.
Are the collations for the database of your test forum different or the same as for your main forum? You can check the test database from phpMyAdmin.
Quote from: Sarge on February 05, 2008, 11:54:25 AM
Quote from: Yngwiedis on February 05, 2008, 11:36:34 AM
The test forum is at : http://hellas-alliance.gr/commtest/index.php
I add again in the index.php the query to show correct the data...
Guest access is disabled, so I can't see anything.
Guest access is now enabled.
Quote from: Sarge on February 05, 2008, 11:54:25 AM
I noticed that English is still the default language. You might want to add the Greek language pack if you use that in your main forum.
I will do it in 5 minutes...
Quote from: Sarge on February 05, 2008, 11:54:25 AM
Are the collations for the database of your test forum different or the same as for your main forum? You can check the test database from phpMyAdmin.
The default collation of the test database is : latin1_swedish_ci
All the tables inside the test database have collation : latin1_swedish_ci
Some fields inside the tables hae mixed collations latin1_swedish_ci and greek_general_ci
After installing the Greek language pack as the forum default language, make a backup copy of the database and try to convert it to UTF-8.
Choose windows-1253 from the list for "Data character set".
I did it and i receive the following error :
QuoteDuplicate entry '4549' for key 1
Αρχείο: /home/alliance/public_html/commtest/Sources/Admin.php
Γραμμή: 1347
Is "Enable error logging" selected in Admin > Features and Options? If it is, check Admin > Forum Error Log. What do you see there?
If error logging is not enabled, enable it, then copy the backup database over to the test database (you may have to delete the test database first) and try the conversion to UTF-8 again. When you get the error again, go to the error log and tell us what do you see there.
Error Logging it was enabled.
The error from there is :
QuoteΕφαρμογή φίλτρου: Προβολή μόνο των ίδιων λαθών
Σφάλμα βάσης δεδομένων: Duplicate entry '4549' for key 1
Αρχείο: /home/alliance/public_html/commtest/Sources/Admin.php
Γραμμή: 1347
P.S. Do you want to translate it for you ?
Can you check in phpMyAdmin and see which table is the last one converted to UTF-8?
I think it's one of the smf_log_ tables...
You mean which table's field is the latest one that is converted, because the tables itself have again latin1_swedish_ci
So i am not exactly sure. Lets begin from the top to bottom
smf_log_online is the last table with utf8_general_ci inside the fields.
smf_log_polls dont have any field collation.
smf_log_search_messages dont have any field collation.
smf_log_search_results dont have any field collation.
smf_log_search_subjects dont have any field collation.
smf_log_search_topics dont have any field collation.
smf_log_topics dont have any field collation.
smf_membergroups table. The fields of this table are in greek_general_ci
So the latest one is sure between smf_log_online and smf_membergroups tables...
Those tables don't have any collations because they don't have any text fields. int and mediumint are integer (numeric) types. Character sets and collations apply only to text and text-like fields.
Copy the backup database over to the test database again and empty (don't drop) these tables from phpMyAdmin:
smf_log_search_messages
smf_log_search_results
smf_log_search_subjects
smf_log_search_topics
and try the conversion again.
Another member reported the same issue:
http://www.simplemachines.org/community/index.php?topic=209038
Note that SMF conversion to UTF-8 causes the display character encoding to be switched to UTF-8 as well. This means that you will need to install the greek-utf8 language pack as default (there's a way to reset it for all members), and your forum members might have to switch encoding manually to UTF-8 in their browsers.
I should mention that SMF specifies the encoding explicitly in the forum pages, so all modern browsers should switch automatically, unless the user has "frozen" (locked) the encoding to Greek, but I don't think a lot of people do that.
Anyway, if you want to keep the Greek character set (like you have it now), there's another way. Let me know what character set you'd like: Greek or UTF-8. Either of them should work, although UTF-8 is more universal and is better supported by database servers.
Quote from: Sarge on February 05, 2008, 02:12:45 PM
Those tables don't have any collations because they don't have any text fields. int and mediumint are integer (numeric) types. Character sets and collations apply only to text and text-like fields.
Copy the backup database over to the test database again and empty (don't drop) these tables from phpMyAdmin:
smf_log_search_messages
smf_log_search_results
smf_log_search_subjects
smf_log_search_topics
and try the conversion again.
Another member reported the same issue:
http://www.simplemachines.org/community/index.php?topic=209038
OK i did it and i am trying the conversion again now...
New error...
QuoteColumn 'body' cannot be part of FULLTEXT index
Αρχείο: /home/alliance/public_html/commtest/Sources/Admin.php
Γραμμή: 1329
:(
Quote from: Sarge on February 05, 2008, 02:12:45 PM
Note that SMF conversion to UTF-8 causes the display character encoding to be switched to UTF-8 as well. This means that you will need to install the greek-utf8 language pack as default (there's a way to reset it for all members), and your forum members might have to switch encoding manually to UTF-8 in their browsers.
I should mention that SMF specifies the encoding explicitly in the forum pages, so all modern browsers should switch automatically, unless the user has "frozen" (locked) the encoding to Greek, but I don't think a lot of people do that.
Anyway, if you want to keep the Greek character set (like you have it now), there's another way. Let me know what character set you'd like: Greek or UTF-8. Either of them should work, although UTF-8 is more universal and is better supported by database servers.
You dont have to write so many details. I know all the above things you said :)
Quote from: Yngwiedis on February 06, 2008, 06:52:37 AM
New error...
QuoteColumn 'body' cannot be part of FULLTEXT index
Αρχείο: /home/alliance/public_html/commtest/Sources/Admin.php
Γραμμή: 1329
It looks like you have fulltext search indexing enabled.
Copy the backup database over to the test database again. Just to be sure, also empty the search tables (as mentioned in my previous post).
In the test forum, go to Admin > Search > Search Method tab. Click on the link [remove fulltext index] and switch "Search index" to "No index".
Then try the conversion again. You should be able to recreate the search index afterwards.
New error again :
QuoteYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc desc blob NOT NULL,
CHANGE COLUMN module module tinyblob NOT NULL,
' at line 3
Αρχείο: /home/alliance/public_html/commtest/Sources/Admin.php
Γραμμή: 1329
Quote from: Yngwiedis on February 06, 2008, 09:21:29 AM
New error again :
QuoteYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc desc blob NOT NULL,
CHANGE COLUMN module module tinyblob NOT NULL,
' at line 3
Αρχείο: /home/alliance/public_html/commtest/Sources/Admin.php
Γραμμή: 1329
I've never seen that error message before... It looks like the SMF Shop tables.
List of mods installed on the main forum?
1. TinyPortal 0.983
2. SMF Shop Lottery 1.0.1
3. Google Member Map 0.1
4. Enhanced Quick Reply Box 2.0.2
5. Additional Membergroups on Profile 1.0
6. Googlebot & Spiders Mod 2.0.3
9. YouTube BBCode 1.9
10. Thank-O-Matic 1.1.9
11. Thank-O-Matic Greek Translation 1.0.1
12. Spoiler Tag 0.3.2
13. SMFShop 3.0
Can you post the database structure for your main forum? Just the structure, not the data.
To do this, open phpMyAdmin, select your main forum database (do not select any tables) and click on the Export menu on the upper right side:
(http://i29.tinypic.com/mubeky.png)
Uncheck the "Data" checkbox, check the "Save as file" option, select "zipped" for the compression and click the Go button to download the file onto your computer. Verify that it doesn't contain any sensitive data and attach it to your reply here.
Quote from: Sarge on February 06, 2008, 11:56:46 AM
Can you post the database structure for your main forum? Just the structure, not the data.
To do this, open phpMyAdmin, select your main forum database (do not select any tables) and click on the Export menu on the upper right side:
(http://i29.tinypic.com/mubeky.png)
Uncheck the "Data" checkbox, check the "Save as file" option, select "zipped" for the compression and click the Go button to download the file onto your computer. Verify that it doesn't contain any sensitive data and attach it to your reply here.
There is no need to write so many details.
I know how to do it. I am not so n00b :P
The structure of the database is attached.
I have already figured out that you aren't such a n00b ;) but I want to be on the safe side and type it all so that, when someone else has the same problem, I can simply point them to these posts instead of retype everything. So please bear with me. ;)
The 'desc' related error is a known SMFShop issue. Rename it to 'description' and rename it back to 'desc' after the conversion.
The conversion finished succesfully. After that i change the SMF Shop description field back to desc.
Then i upload the greek-UTF8 language files and i change the default language it in the Server Settings to greek-utf8.
It seems that is working correct.
[EDIT] Not exactly correct... The subjects of the topics are not readable :( [/EDIT]
I have 2 questions now...
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 ?
Quote from: Yngwiedis on February 08, 2008, 04:17:06 AM
[EDIT] Not exactly correct... The subjects of the topics are not readable :( [/EDIT]
In SMF, every topic gets its subject from the first message (post) for that topic.
Search for smf_messages in the dump file you uploaded and look at the `subject` field definition:
`subject` tinytext character set utf8 NOT NULL,
The problem is that its not clear whether rows (data) are all in greek, all in utf8, all in latin1, or mixed (some rows in utf8 and/or some in latin1 and/or some in greek). However, most probably it's already in greek.
Try setting that field as greek_general_ci (which is the default collation for the greek character set) and try the conversion again. Do this
after copying the backup database over to the test database, emptying the smf_log_search_* tables, renaming `desc` to `description` etc as before.
Thank you very very much Sarge. All is ok now :)
Can you answer my 2 questions now ?
Because i have 2 more forums with mixed collations and character sets to convert...
For a strange reason that i dont know, the quote function is not working in Greek-UTF8 language.
Only in English... :-\
[EDIT]
Is not only the quote function. All the Ajax functions is not working
Also some old topics with quotes in the message body are semi "vanished".
I check the database and the half messages body are not there !!!
[/EDIT]
Did you check smf_messages in the database? Does it contain the full message bodies? If not, can you compare them to the original contents and see where is the content cut off?
Yes i check the smf_messages table and the message bodys are not complete there.
The problem is that after the conversion i delete the backup database because i thought that all went ok :(
Is not a big problem anyway, because this is happened to a small amount of messages.
My big problem is the Ajax functions...
If you have any recent backups (which you should), you can import those incomplete messages back into the original database. Since the problem happens with just a few messages, I suspect it's because the sizes of those messages are at least half of the message body size limit. Try increasing the limit in the database and then import one of those incomplete messages. More specifically, increase the type of the `body` column in smf_messages from text (about 65K) to mediumtext (16M), which should be more than enough.
As mentioned in the other topic I linked to above, UTF-8 uses more than 1 byte for some characters. If a message consists mostly of non-English characters, the effective size can easily get doubled (2 bytes are needed for Greek letters in Unicode).
As for the AJAX functions, how do they work with the default theme and English?
I can also take a closer look if you want. You can PM me your forum URL and a test account (regular member), but I would need to be able to select the default theme and English. I think there's a mod by Rudolf to install a theme change permission, and you can apply that to specific membergroups. I don't need admin access. You might want to clear this with the other admins as well. :)
Quote from: Sarge on February 11, 2008, 10:55:08 AM
If you have any recent backups (which you should), you can import those incomplete messages back into the original database. Since the problem happens with just a few messages, I suspect it's because the sizes of those messages are at least half of the message body size limit. Try increasing the limit in the database and then import one of those incomplete messages. More specifically, increase the type of the `body` column in smf_messages from text (about 65K) to mediumtext (16M), which should be more than enough.
As mentioned in the other topic I linked to above, UTF-8 uses more than 1 byte for some characters. If a message consists mostly of non-English characters, the effective size can easily get doubled (2 bytes are needed for Greek letters in Unicode).
Like i said before, i dont have any backup anymore. Yes i am stupid :(
Anyway i change the body field in smf_messages from TEXT to MEDIUMTEXT for future need.
Quote from: Sarge on February 11, 2008, 10:55:08 AM
As for the AJAX functions, how do they work with the default theme and English?
I can also take a closer look if you want. You can PM me your forum URL and a test account (regular member), but I would need to be able to select the default theme and English. I think there's a mod by Rudolf to install a theme change permission, and you can apply that to specific membergroups. I don't need admin access. You might want to clear this with the other admins as well. :)
Ajax functions are working with the theme i use, but only in english language.
I will send you a PM with the login details.
You can change the language from profile.
There shouldn't be any need to change the field type after the conversion, of course unless you need bigger messages. However, a larger type might cause problems with the search function.
I checked your forum and couldn't find anything at the moment. What exactly is the language pack you're using in your forum? Was the same pack working before?
The language pack i use now is Greek-UTF8.
Before the conversion i use Greek.
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?
Quote from: Sarge on February 11, 2008, 12:23:33 PM
Have you removed "SET NAMES greek" from index.php?
Yes. Right after the conversion.
Quote from: Sarge on February 11, 2008, 12:23:33 PM
What happens if you upload the Greek (not utf8) pack and switch to that in your profile?
Let me upload it first...
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...
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 ?
Quote from: Yngwiedis on February 11, 2008, 12:54:01 PM
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 :(
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.
It might well be caused by BOM in this file. BOM, Byte Order Mark (http://msdn2.microsoft.com/en-us/library/ms776429.aspx), 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.
Quoteο»Ώ<?php
Remove the first 3 "strange" chars and try.
Sorry agridoc but my editor dont show me any characters before
<?php
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.
Heyyy.... Is working :)
One more time that i learn new things ;)
Thank you very very much agridoc :D
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 ?
Quote from: Yngwiedis on February 11, 2008, 05:55:32 PM
Heyyy.... Is working :)
One more time that i learn new things ;)
Thank you very very much agridoc :D
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 ;D
Quote from: Yngwiedis on February 11, 2008, 05:55:32 PM
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. :)
Quote from: Yngwiedis on February 11, 2008, 05:55:32 PM
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. ;)
Thank you very very much Sarge for all your help :)
I am going to convert the other 2 forums now.
Wish me luck :)
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. ;)
Is there any fast way to convert all the tables collation at once ?
Quote from: Yngwiedis on February 12, 2008, 03:14:56 AM
Is there any fast way to convert all the tables collation at once ?
I will check about it tomorrow and will let you know. :)
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
QuoteΣφάλμα βάσης δεδομένων: 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 ?
QuoteΣφάλμα βάσης δεδομένων: 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.
Quote from: Sarge on February 12, 2008, 04:15:48 AM
QuoteΣφάλμα βάσης δεδομένων: 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
Quote from: Sarge on February 12, 2008, 04:15:48 AM
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.
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):
(http://i32.tinypic.com/j7tfl5.png)
I dont need it right now so i will delete it ;)
All went smooth to the second forum conversion.
I just need a good UTF8 editor :)
I can recommend Notepad++ (http://notepad-plus.sourceforge.net/uk/site.htm). There's an option from the Format menu to encode in UTF-8 without BOM.
I am struggling with Notepad++ for over an hour now...
I change the encoding from the Format option to UTF-8 Without BOM.
Then i go to Save As and i save the files, but all the files i save, are again in ANSI format.
What i am doing wrong ?
I convert the files with BabelPad. I am ok now.
Second forum conversion is finished :)
Quote from: Yngwiedis on February 12, 2008, 03:14:56 AM
Is there any fast way to convert all the tables collation at once ?
Do you still need info about this? Were table collations converted automatically in the second forum?
I could write a script that converts the collation of all SMF tables.
Quote from: Sarge on February 13, 2008, 02:57:58 AMI could write a script that converts the collation of all SMF tables.
That would be quite useful for future use Sarge.
Yngwiedis had first reported the mixed field collation problem in the Greek support board. After explanations I answered that a script would be needed to do a proper conversion and I suggested referring in English support board, as I couldn' t write it. Fortunately with your help the problem is solved. ;)
Quote from: Sarge on February 13, 2008, 02:57:58 AM
Quote from: Yngwiedis on February 12, 2008, 03:14:56 AM
Is there any fast way to convert all the tables collation at once ?
Do you still need info about this? Were table collations converted automatically in the second forum?
I could write a script that converts the collation of all SMF tables.
Yes i am still need that info.
The table collations of the second forum are not converted automatically again.
One script like this will be awesome :)
New problem after the conversion...
Verification images on registration are not showing in greek-utf8 language.
Only in english...
Check all greek-utf8 language files for BOM.
Quote from: agridoc on February 13, 2008, 03:12:12 PM
Check all greek-utf8 language files for BOM.
Yes. Also check that nothing (mod strings etc.) is present in the language files before the opening <?php tag and after the last ?> tag.
The problem solved one more time by checking all the greek-utf8 files.
I remove the BOM from some files that have it and all is ok now :)
I am waiting for the awesome script Sarge ;D
Quote from: Yngwiedis on February 14, 2008, 04:13:14 PM
I am waiting for the awesome script Sarge ;D
First of all, backup your database! I can't stress this enough.
Find the script attached. Upload it to your forum directory (where SSI.php is located) and run it from your browser.
The script is very crude: all you will see on the screen is a confirmation message when it's done. Also, the only thing that this script does is switch character sets of the SMF tables in the database to utf8 and collations to ut8_general_ci. No data conversions are done and nothing else is modified.
Important!1) Since it uses SMF code, this script is released under the Simple Machines License (http://www.simplemachines.org/about/license.php).
2) The script is slow. There's built-in protection against timeouts, but if it stops for some reason, simply run it again until the confirmation message is displayed. You should check in phpMyAdmin to be sure that all collations are set as utf8_general_ci.
I run the script on 2 of my forums. Is working nice and smoothly.
Good job Sarge. Thank you very very very very much :D
You're welcome! Let us know if the third forum gets converted well too. ;)
My third forum is also converted to UTF8 without any problem :)
Tell me something Sarge...
Because i found more "half messages" on my main forum, what is the best work i have to do ?
I found an older backup of the database. I will export the smf_messages table from that database with what export type ?
UPDATE, REPLACE or INSERT ? And then i will import it in the live database ?
Quote from: Yngwiedis on February 16, 2008, 07:47:26 AM
Because i found more "half messages" on my main forum, what is the best work i have to do ?
Can you explain a bit more? Compare a "half message" with the original in the backup. Where are the messages cut off? At a Greek character or some other symbol?
How big are those messages?
OK i will show you some messages...
"Half Message"
[size=12pt][b]ΕΙΣΑΓΩΓΗ[/b][/size]<br /><br />Ανοιγετε το SpeedSim και το γυριζετε στα αγγλικα...αν δεν γνωριζετε πως να το κανετε αυτο διαβαστε την επομενη παραγραφο...<br /><br />[b]-ΠΩΣ ΝΑ
"Original Message" before the conversion:
[size=12pt][b]ΕΙΣΑΓΩΓΗ[/b][/size]<br /><br />Ανοιγετε το SpeedSim και το γυριζετε στα αγγλικα...αν δεν γνωριζετε πως να το κανετε αυτο διαβαστε την επομενη παραγραφο...<br /><br />[b]-ΠΩΣ ΝΑ ΒΑΛΕΤΕ ΑΓΓΛΙΚΑ ΣΤΟ SPEEDSIM[/b]<br />αρχικα νομιζω ειναι στα γερμανικα.... <br />1os τροπος ...επιλεγετε απο το μενου language to english... θα γυρισει την γλωσσα στα αγγλικα...κλειστε το και ξανανοιξτε το..<br />2os τροπος ...πιεζετε το κουμπι choose language...επιλεγετε το αρχειο eng_lang_v0.9.3.1b.ini<br />αλλαζετε την γλωσα στα αγγλικα για να καταλαβαινουμε τι μας γινεται....<br />κλεινετε και ξανανοιγετε το Speedsim...<br /><br />[b]-ΑΡΧΙΚΕΣ ΡΥΘΜΙΣΕΙΣ[/b]<br /><br />πρωτα πατε στην επιλογη file -> extended options -> misc και επιλεγετε τον τυπο των πλοιων με τα οποια θα πατε για να μεταφερετε τα resources του οχτρου... στην αρχη βαζετε SC, επειτα το αλλαζετε σε LC, και οταν περασετε τους 10000 ποντους μπορειτε να το βαλετε σε BS... εχουν αρκετο χωρο, αληθεια!!!<br /><br />τσεκαρετε την επιλογη Supervise Clipboard... με αυτο τον τροπο δεν θα χρειαζετε να περνατε τα στοιχεια με το χερι...οπως εκανα εγω στην αρχη :blush:<br /><br />περαστε τις τεχνολογιες που διαθετετε πανω δεξια στην περιοχη technologies... επιλεξτε file->save technologies...<br /><br />στην θεση που λεει own position βαζετε την θεση απο την οποια επιτιθεστε συνηθως... στη συνεχεια μπορειτε να βαλετε κι αλλες θεσεις αν θελετε.... σημαντικο για τον υπολογισμο καυσιμων και χρονου...<br /><br />[size=12pt][b]ΜΕΡΟΣ 1ο[/b][/size]<br /><br />ξεκιναω με το espionage report ενος προσφιλους μου προσωπου... :tongue3: στη θεση του βαλτε καποιον αγαπημενο σας αντιπαλο :laugh:<br /><br />[quote]Resources on YPC [3:284:5] at 10-06 23:37:06<br />Metal: 90010 Crystal: 202076<br />Deuterium: 161318 Energy: 3006<br />Fleets<br />Small Cargo Ship 7 Large Cargo Ship 10<br />Heavy Fighter 13 Recycler 17<br />Espionage Probe 11<br />Defence<br />Small Laser 152 Heavy Laser 6<br />Gauss Cannon 5 Ion Cannon 2<br />Buildings<br />Metal Mine 19 Crystal Mine 15<br />Deuterium Synthesizer 15 Solar Plant 19<br />Fusion Plant 7 Robotic Factory 6<br />Shipyard 7 Metal Storage 2<br />Crystal Storage 3 Deuterium Tank 2<br />Research Lab 5 Rocket Silo 3<br />Research<br />Weapon Technology 10 Shielding Technology 7<br />Armour Technology 7[/quote]<br /><br />το μαρκαρετε και το κανετε copy me ctrl+c <br /><br />παρατηρηστε οτι οι αμυνες και ο στολος του αντιπαλου σας εχουν ηδη περασει στη λιστα Defender...<br /><br />στην θεση technologies-defender θα περασουν και οι τεχνολογιες του, αν εχετε πληρες σκαναρισμα του...<br />αν δεν εχετε, θα επρεπε να ειχατε, αλλα τεσπα... μαρκαρετε το κουτακι που λεει set techs of attacker to 0....<br />θα εξαφανισει προσωρινα τις δικες σας τεχνολογιες, για να μην αλλοιωσει τα αποτελεσματα της μαχης....<br /><br />[size=12pt][b]ΜΕΡΟΣ 2ο (εδω το καλο!!)[/b][/size]<br /><br />περνατε τα πλοια με τα οποια σχεδιαζετε να επιτεθειτε, στην πρωτη καθετη στηλη αριστερα με τιτλο attacker... προσοχη βαλτε πλοια που εχετε και οχι αυτα που θα θελατε να εχετε ;) αυτο ειναι για παρακατω...<br /><br />πιεζετε απαλα το κομβιον που λεει [color=Green]begin simulation[/color]... <br /><br />1h φορα...βλεπετε τι βγαζει στο πεδιο Losses attacker...<br />2h φορα...βλεπετε τι βγαζει στο πεδιο Losses attacker...<br />3h φορα...βλεπετε τι βγαζει στο πεδιο Losses attacker...<br />4h φορα...βλεπετε τι βγαζει στο πεδιο Losses attacker...<br /><br />οι επαναληψεις εχουν νοημα γιατι αλλαζει ελαφρα ο υπολογισμος :stunned:<br /><br />τωρα εχετε μια πρωτη εκτιμηση για το τι θα γινει αν πατε μεχρι εκει.... αν σας ικανοποιει ριξτε του στα αυτια!!!!<br /><br />αν το αποτελεσμα δεν σας ικανοποιει, μην βιαστειτε να σπασετε το PC... σκεφτειτε τι θα χρειαζοσασταν για να του την πεσετε χωρις απωλειες... σε καθε επιπεδο που βρισκεστε μπορειτε να προσπαθησετε να φτιαξετε τα πλοια που σας χρειαζονται ωστε να τον νικησετε... ετσι θα βελτιωσετε σημαντικα τον στολο σας κανοντας συνεχεις προσομειωσεις μαχων...<br /><br />προσθεστε μερικα LF αν ειστε νεος :baby:, εγω τουλαχιστον αυτο εκανα... επειτα προσθεστε μερικα HF, οταν θα μπορειτε να τα φτιαξετε... αργοτερα προσθεστε μερικα BS.... και τελος βαλτε και 2-3 DeathStar απο την αποθηκη....δεν ειναι κακο να ονειρευεται κανεις!!! αν φτασετε στο επιπεδο να προσθεσετε BS που δεν τα εχετε, τοτε μαλλον δεν χρειαζεται πλεον να διαβαζετε αυτο το how-to!! ;)<br /><br />[b]-ΑΝΑΛΥΣΗ ΑΠΟΤΕΛΕΣΜΑΤΩΝ ΜΑΧΗΣ[/b]<br /><br />στο πεδιο result σας δινει το αποτελεσμα της μαχης... ειναι ποσοστο γιατι κανει 100 επαναληψεις για να το βγαλει... δεν εχω παει ποτε σε κανενα, αν δεν εχω παρει Attacker wins 100% ;)<br /><br />στο πεδιο Debris field σας βγαζει μια εκτιμηση για το ποσα recyclers θα χρειαστειτε για να μαζεψετε τα σκουπιδια που δημιουργησατε... μην λερωνουμε κιολας...προσεξτε το ειναι σημαντικο για το fleetcrashing, μην ξεχασετε να προσθεσετε κι αυτα στο κερδος και θα εχετε μια αλλη εικονα ;)<br /><br />στο πεδιο Moonchance σας δινει τις πιθανοτητες να δημιουργηθει φεγγαρι στο πεδιο της μαχης... αχ..ελπιζω καποτε να εχω κι εγω ενα δικο μου φεγγαρι :laugh: :laugh:<br /><br />τα πεδια Losses Attacker, losses Defender ειναι οι απωλειες της μαχης απο καθε πλευρα... μην ξεχνατε μπορει να χρειαστει καποια στιγμη να υπολογισετε και ως αμυνομενος....<br /><br />στο πεδιο theoretic plunder σας δειχνει τι θα παιρνατε κατα μεγιστο... αναλογα με τον αριθμο των LC που στελνετε...<br /><br />στο πεδιο real plunder σας δειχνει τι θα παρετε στην πραγματικοτητα.... οχι μη φοβαστε δεν θα ειναι το μακρυτερο :laugh: :laugh: στο χερι σας ειναι τα παντα... <br /><br />στο πεδιο needed fuel βγαζει τα καυσιμα που θα χρειαστειτε για την βολτα σας...<br /><br />στο πεδιο flight times σας βγαζει τον χρονο που θελετε για να πατε εκει... υπολογιστε και τον χρονο επιστροφης, και θα εχετε το χρημα στην πορτα σας :thumbsup:<br /><br />[b]-ΛΟΙΠΕΣ ΛΕΙΤΟΥΡΓΙΕΣ[/b]<br /><br />Απανω αριστερα στο μενου εχει την επιλογη fleet -> save fleet kai load fleet... με αυτα μπορειτε να σωσετε τις πληροφοριες για τον δικο σας στολο και για ορισμενους αλλους που ισως εχετε βαλει στο ματι.... την επομενη φορα που θα ανοιξετε το προγραμμα θα φορτωσετε τα στοιχεια του στολου σας για να μην κουραζεστε...<br /><br />τελειωνω εδω με το κουμπι [color=Green]Show B/W case[/color]... σε αντιθεση με οτι πιστευα στην αρχη δεν σου προτεινει να στα δειξει ολα ασπρομαυρα :wideeyed: αλλα κανει μια πολυ καλη αναλυση για το αποτελεσμα μιας αμφιλεγομενης μαχης... ετσι λοιπον αν παιρνετε καθε φορα μια διαφορετικη εκτιμηση πατωντας το κουμπι [color=Green]Begin Simulation[/color], πατηστε το B/W, αλλα προσοχη... ανοιγει σε ενα ηδη ανοιχτο παραθυρο του firefox, και μπορει να σας καλυψει το tab του παιχνιδιου. :thumbsdown: δωστε του το δικο του χωρο και ειναι ΟΚ...<br /><br />κατω απο τα στοιχεια των στολων βρισκεται το κομβιον που λεει [color=Green]prepare next wave[/color]... οταν θελετε να στραγγιξετε εναν αντιπαλο, και αφου υπολογισετε την πρωτη σας επιθεση οπως ειπαμε παραπανω... το πιεζετε... <br />αυτο σας αφαιρει τις αμυνες του αντιπαλου που θα καταστραφουν πραγματικα και τα πλοια του, υπολογιζει και αφαιρει τι πηρατε απο την πρωτη επθεση, και πλεον εισαστε ετοιμοι να βαλετε τα πλοια του δευτερου κυματος που θα στειλετε.... <br />αξιζει ισως τον κοπο αν ο αντιπαλος εχει 100k+ resources , να κανετε και μια δευτερη επισκεψη.... εσεις θα κρινετε, θυμηθειτε μονο οτι δεν επιτρεπονται απο το παιχνιδι περισσοτερες απο 3 επιθεσεις... ;) σε ενα 24ωρο.<br />
Another "Half Message":
Επειδη εβλεπα την γινοταν ζηλεψα και ειπα να δοκιμασω νεες τακτικες, :annoyed: τι τα εφτιαχνα τα σιλο?? ???<br /><br />[size=11][CENTER]<br />[font=verdana]Επιτιθέμενος [B][color=#CCFFCC] Vassilis [/color][/B](X:XXX:XX)<br />Όπλα: [B]90[/B]% Ασπίδες: [B]90[/B]% Θωράκιση: [B]90[/B]%<br />[/font][font=courier new][U][color=#1F273C][color=#00FFFF]Καταδιωκτικό[/color][/color] [color=#1F273C][color=#FFCC00]Καταδρομικό[/color][/color] [color=#1F273C][color=#00FF99]
"Original Message" before the conversion:
Επειδη εβλεπα την γινοταν ζηλεψα και ειπα να δοκιμασω νεες τακτικες, :annoyed: τι τα εφτιαχνα τα σιλο?? ???<br /><br />[size=11][CENTER]<br />[font=verdana]Επιτιθέμενος [B][color=#CCFFCC] Vassilis [/color][/B](X:XXX:XX)<br />Όπλα: [B]90[/B]% Ασπίδες: [B]90[/B]% Θωράκιση: [B]90[/B]%<br />[/font][font=courier new][U][color=#1F273C][color=#00FFFF]Καταδιωκτικό[/color][/color] [color=#1F273C][color=#FFCC00]Καταδρομικό[/color][/color] [color=#1F273C][color=#00FF99]Βομβαρδιστικό[/color][/color] [/U]<br />[color=#1F273C]__________[color=#00FFFF]16[/color][/color] [color=#1F273C]_________[color=#FFCC00]24[/color][/color] [color=#1F273C]____________[color=#00FF99]3[/color][/color] <br />[color=#1F273C]__________[color=#FF0000]-0[/color][/color] [color=#1F273C]_________[color=#FF0000]-0[/color][/color] [color=#1F273C]___________[color=#FF0000]-0[/color][/color] <br />[U][color=#1F273C]__________[color=#00FFFF]16[/color][/color] [color=#1F273C]_________[color=#FFCC00]24[/color][/color] [color=#1F273C]____________[color=#00FF99]3[/color][/color] [/U][/font]<br /><br />[font=verdana]Επιτιθέμενος [B][color=#CCFFCC] Vassilis [/color][/B](X:XXX:XX)<br />Όπλα: [B]90[/B]% Ασπίδες: [B]90[/B]% Θωράκιση: [B]90[/B]%<br />[/font][font=courier new][U][color=#1F273C][color=#00FFFF]Καταδιωκτικό[/color][/color] [color=#1F273C][color=#FFCC00]Καταδρομικό[/color][/color] [color=#1F273C]______[/color][/U]<br />[color=#1F273C]__________[color=#00FFFF]18[/color][/color] [color=#1F273C]_________[color=#FFCC00]16[/color][/color] [color=#1F273C]______[/color]<br />[color=#1F273C]__________[color=#FF0000]-2[/color][/color] [color=#1F273C]_________[color=#FF0000]-0[/color][/color] [color=#1F273C]______[/color]<br />[U][color=#1F273C]__________[color=#00FFFF]16[/color][/color] [color=#1F273C]_________[color=#FFCC00]16[/color][/color] [color=#1F273C]______[/color][/U][/font]<br /><br />[font=verdana]Έπιασε [B][color=#FF9900]32.845[/color][/B] μέταλλο, [B][color=#FF9900]32.845[/color][/B] κρύσταλλο και [B][color=#FF9900]21.409[/color][/B] δευτέριο.<br />[/font][font=verdana]Ο επιτιθέμενος έχασε συνολικά [B][color=#FF9900]54.000[/color][/B] μονάδες.<br />[/font]<br /><br />[font=verdana]Αμυνόμενος [B][color=#EEC273] neon [/color][/B](X:XXX:XX)<br />Όπλα: [B]90[/B]% Ασπίδες: [B]90[/B]% Θωράκιση: [B]80[/B]%<br />[/font][font=courier new][U][color=#1F273C][color=#33FF99]Ε.Μαχητικό[/color][/color] [color=#1F273C][color=#FF00FF]Β.Μαχητικό[/color][/color] [color=#1F273C][color=#00FFFF]Καταδιωκτικό[/color][/color] [/U]<br />[color=#1F273C]_______[color=#33FF99]261[/color][/color] [color=#1F273C]_________[color=#FF00FF]9[/color][/color] [color=#1F273C]___________[color=#00FFFF]5[/color][/color] <br />[U][color=#1F273C]___________ [color=#FF0000]καταστράφηκε[/color] __________[/color] [/U][/font]<br /><br />[font=verdana]Ο αμυνόμενος έχασε συνολικά [B][color=#FF9900]1.269.000[/color][/B] μονάδες.<br />[/font]<br /><br />Σε αυτές τις συντεταγμένες τώρα αιωρούνται [B][color=#FF9900]293.100[/color][/B] μέταλλο και [B][color=#FF9900]103.800[/color][/B] κρύσταλλο.<br />Η πιθανότητα δημιουργίας φεγγαριού είναι [B][color=#FF9900]3[/color][/B] %.<br /><br /><br />[color=#66FFCC][size=8pt][url=http://www.takanacity.com] -- Created by Takana's CR Converter v1.955 beta - Dragon Takana --[/url][/size][/color]<br />[/CENTER][/size]<br /><br />Παμε 2η διαδρομη επιθεσης<br /><br />5 μεγαλα μεταγωγικα<br />Ο επιτιθέμενος κέρδισε τη μάχη!<br />Έπιασε<br />57.521 μέταλλο, 48.898 κρύσταλλο, και 10.707 δευτέριο<br /><br /><br />Παμε 3η διαδρομη επιθεσης<br /><br />3 μεγαλα μεταγωγικα<br />Ο επιτιθέμενος κέρδισε τη μάχη!<br />Έπιασε<br />30.878 μέταλλο, 24.459 κρύσταλλο, και 5.354 δευτέριο<br /><br />[color=yellow]Πριν ειχε[/color]<br />Αμυνα <br />Εκτοξευτής Πυραύλων 8 Ελαφρύ Λέιζερ 91 <br />Βαρύ Λέιζερ 40 Κανόνι Gauss 5 <br />Κανόνι Ιόντων 34 Μεγάλος Αμυντικός Θόλος 1 <br />Τωρα τιποτα...<br /><br />Σημαντικο πρωτα ριχνουμε στους θολους και μετα στα πιο δυνατα οπλα...<br />Το Raksim μου ελεγε 9 πυραυλους και τελικα χρειαστηκαν 8...<br /><br />Και 4η διαδρομη επιθεσης<br />Ο επιτιθέμενος κέρδισε τη μάχη!<br />Έπιασε<br />13.091 μέταλλο, 6.450 κρύσταλλο, και 1.024 δευτέριο<br /><br /><br /><br /><br />Πηρα και 13 ανακυκλωτες που ειχα...εστειλα και αλλους 6...<br /><br />Πιστευω να εφτιαξα σωστα την αναφορα, ο converter μου εσπασε τα νευρα.<br />
So now i want to take the smf_messages table from the backup database and insert it in the live database but without delete the newer posts.
What option i need to use in the export opoeration ? UPDATE, REPLACE or INSERT ?
Quote from: Yngwiedis on February 19, 2008, 03:57:18 AM
So now i want to take the smf_messages table from the backup database and insert it in the live database but without delete the newer posts.
What option i need to use in the export opoeration ? UPDATE, REPLACE or INSERT ?
Use UPDATE. If it doesn't work for whatever reason, try REPLACE.
Thank you one more time Sarge.
Import of the messages finished successfully :)