Topic: It's all Greek to me .... :)

[amordad_yar]

Thanks in advance for any help passed my way.  I did try to search a bit before asking but wasn't really sure where to search. Several years ago I built a forum for a Greek friend and she has been maintaining it ever since so I hate to admit that I've let it slip to the back corners of my mind and don't recall all the struggling to get it work originally. It is currently at 1.1.14 though the language files ( specifically the greek-utf8 ) are at 1.1 rc3

Even when first installed, the Greek letters changed to garbage when I would change the site to English so that I could help her configure it. We thought it was no big deal until 2 years later she mentioned to me that searches in the site don't work.  I deduced that it was related to the 'garbage' I saw when changing it back to English but wasn't certain and couldn't figure out what I needed to do to fix that problem.

After all these years and lots of data, she wants to upgrade the site but most importantly, she ( nor I ) want to lose all the data that is there so I truly need to find the correct path now. The database collation is set to utf8_general_ci BUT within the maintenance sectinon of SMF, I am being offered the opportunity to convert the data to utf-8 so I'm guessing that though the language files are utf8 and the collation is set to utf8, I'm still missing one thing.  I did copy the database and entire site and then ran the 'convert to utf8' option but this did not fix the problem.   Where do I go next?

[agridoc]

A link would be useful. PM it to me if you don't want it public. Also, please set the user permission to change languages to on.

Please check collation of database SMF tables and text fields inside them.

[agridoc]

Even when first installed, the Greek letters changed to garbage when I would change the site to English so that I could help her configure it. We thought it was no big deal until 2 years later she mentioned to me that searches in the site don't work.

The installation is probably an ISO-8859-1 English first install. "Garbage" chars are easy cured by changing codepage to Greek in /Themes/default/Languages/index.english.php.

Find

Code: [Select]

$txt['lang_character_set'] = 'ISO-8859-1';
Replace with

Code: [Select]

$txt['lang_character_set'] = 'windows-1253';
English won't be affected, special chars are stored as entities.

Search works with this setup, even for entities, sorting is not correct, a weak point for ISO installations here. If the forum has many messages some indexing may be necessary.

[amordad_yar]

Yes... index.english.php was iso9959-1 so I changed that as you instructed - the chars did change to somewhat 'less' garbage though they still not the same as when I am viewing the site in Greek. If that will help the search function though, I'm pleased.

As for the tables and text fields inside the database, you are correct again. They are not UTF but instead latin1_swedish_ci.  I'm going to copy the database and entire site once again before making any more changes but you are more than welcome to look at the site to see what I see - ( www.veganforum.gr [nofollow] ) changing language support has been enabled. All accounts must be approved but I'll see to it that you are asap.   

[agridoc]

You had reported in the first message that Greek was UTF-8. I examined the site a bit it's an ISO SMF 1.1x installation with latin1_swedish_ci database tables and text fields.

Unfortunately you have added the Greek UTF-8 language pack and not the Greek (windows-1253) one. So the change in index.english.php has not the desired effect. Changing the codepege to UTF-8 will make Greek show correctly but this is not the right solution. So bring back the codepage in index.english.php to ISO-8859-1.

The forum must be converted to UTF-8. Have a bit of patience to clear a few more things as this is not a "normal" case. You must be able to take database and forum backup and be able to restore a database backup.

[amordad_yar]

I'm nodding in total agreement, thinking way back when I set this up the first time - my first attempt to setup a SM Forum and then having it complicated by needing it in Greek and not really knowing the best way to do it. Now I have a mess on my hands   ( I sent you a PM also )  Index.english.php is back to where it was (ISO) and I have backups - just needing to know what is the correct way to proceed next.

[agridoc]

It's better to write in public, I mentioned PM only for URL.

Let's see things.

- We have a SMF ISO installation, originally in English ISO-8859-1.
- Database tables and text fields are in latin1_swedish_ci.
- Later Greek UTF-8 instead of Greek (window-1253) was added and forum was used extensively in this language.
- When Greek language was used data was stored as UTF-8 in  latin1_swedish_ci tables.

To correct things we must convert to UTF-8. It's a procedure that might not finish properly. Some mods may conflict so it's better to do it without them.

Steps (if other software is bridged with SMF additional steps might be needed).

1. Download SMF 1.1.14 (this is your version) UPGRADE (Not Update or Install) and Greek UTF-8 1.1.14 to a dir in your PCfrom  http://download.simplemachines.org/?archive;version=62 , Greek UTF-8 in page 3.

2. Put the forum in English (ISO-8859-1.) default language and maintenance mod.

3. Backup SMF dir and database, so we can go back if something goes wrong.

4. Upload the content of SMF 1.1.14 Upgrade and Greek UTF-8 with FTP in SMF dir in the server or upload and unpack with CP File Manager, if available. Unpack usually overwrites old files but this is not always the case.

5. Run upgrade.php with your browser. Use English language. Remember to delete file upgrade.php after the procedure finishes. The Upgrade will delete file changes made by mods. Check the upgrade. If everything seems OK go to next step.

6. Using English go to Admin -> Forum Maintenance -> Forum Maintenance - General Maintenance: Convert the database and data to UTF-8 <- Click here
You will see after text

Data character set	: UTF-8 <- Input UTF-8
Database character set	: ISO-8859-1
Convert data and database to	: UTF-8

Greek are stored as UTF-8, English ISO-8859-1 has no significant difference with UTF-8.
Proceed to conversion. You may have to wait for quite some time. Don't rush things.
ADD ON: ATTENTION. This is a special case, so we select UTF-8. In usual cases the selection for converting a Greek SMF forum to UTF-8 is windows-1253.

If the procedure finishes correctly check the forum. If an error occurs report it.

There are a few more steps but number 6 is the critical procedure. If everything worked as expected you should have converted to UTF-8. Greek messages should show correctly with English language selected.

7. Go to PhpMyAdmin and check SMF database tables. Field text must have been set to utf8_general_ci collation. Table collation may not have been set, can be done manually with PhpMyAdmin.

8. To complete the conversion we go to and run "Convert HTML-entities to UTF-8 characters".

9. If everything seems to be OK either install again the mods or upgrade to SMF 2.0. The latter might require another theme and some of the mods might not be available.

[amordad_yar]

Thank you...I will arrange for some down time on the forum (likely this Sunday evening) and let you know what happens

[agridoc]

Good luck.   

[amordad_yar]

Unfortunately, all didn't go well. Everything went as planned until trying to convert the database which generated this error: ( path obscured for public display


Duplicate entry 'Σαλάτα-3704' for key 'PRIMARY'
File: /xxxx/xxxx/public_html/veganforum.gr/Sources/Admin.php
Line: 1330

[agridoc]

Did you have a partial database restore in the past?

Check your error log, what is the name of the SMF database table that caused the error? [smf_]log_search_results (smf_ prefix could be different) can easily cause this error, it can be emptied (not dropped).

[amordad_yar]

hmm...that is all I get from the error log while accessing the forum error log under forum maintenance and no errors are logged in the server provided php error log for several days now...and I don't seem to have access to any mysql log if needed.

However, if there is no harm at this point in emptying smf_log_search_results, I can do that and try again. ( I've since moved this site to an alternate location and restored the live site so they can get back to their fun and I'll attack the live site again when I'm confident I can finish

.....5 minutes later....

Perhaps I jumped ahead too soon   I emptied that table then tried again and got a different error so am restoring the db to where it was before I emptied the table. ( the result after emptying smf_log_search_results.

Database Error: Column 'body' cannot be part of FULLTEXT index
File: /home4/amordadt/public_html/veganforum.gr.ip/Sources/Admin.php
Line: 1312

[agridoc]

It's an excellent idea to try first the conversion in a test copy, make sure that the database is different too.

It seems that the forum used a full text index. It has to be deleted. Go to Admin -> Search -> Search method and remove the fulltext index, then select "No index" and save. A new one can be built after the conversion finishes. In the particular case, the existing index is useless and maybe the cause of search not working.

[amordad_yar]

We've made more progress.  You were correct that it was set to full text index. That was deleted as you said and it was set to 'no index' and saved.  Then I tried to do the conversion to utf8 again and I got the same error I received earlier about line 1330 of Admin.php.  So, I again emptied the table smf_log_search_results then tried the conversion again and THIS time it completed with no errors *jumping up and down about now because the greek characters are actually displaying correctly now*

Unfortunately, the next step (7) revealed that all the tables are still set to latin1_swedish_ci so I halted there and did not proceed to step 8.

If need be, I can change each one manually but is it possible that something else didn't happen that should have also? ( there are no new errors in the log )

[agridoc]

I am glad we are near to a happy end.

As this is an unusual case, before giving instructions, I did a test reproducing the error (SMF ISO 8859-1 1.1.14 install then addition of Greek UTF-8 language, then adding some messages using both character sets, then converting to UTF-8). I noticed that, after conversion the tables were still in latin1_swedish_ci BUT text fields (the important thing) inside them were converted to utf8_general_ci collation. Maybe this happened because we had a UTF-8 to UTF-8 conversion. I had noticed that.

7. Go to PhpMyAdmin and check SMF database tables. Field text must have been set to utf8_general_ci collation. Table collation may not have been set, can be done manually with PhpMyAdmin.

I don't believe that functionality is affected as is, collation is not important for numeric and logical fields. Don't worry the entities will be converted as is. However the tables should be changed to utf8_general_ci. Alternatively you can use a php script like that to change the collation of all database tables. If you are in a test site you can play with it, otherwise you can do it with PhpMyAdmin.

Do extensive tests in the test site then do the conversion in the working one. Please leave a message when everything is fixed.

[amordad_yar]

I used the script you pointed me to and converted each of the tables to utf8_general_ci and thought for sure we were home safe. Next used phpadmin to change the database collation to utf8_general_ci also and then after verifying that things still seem to be working well moved on to the next ( and last ) step.

..8. To complete the conversion we go to and run "Convert HTML-entities to UTF-8 characters".

and unfortunately got another error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM smf_log_search_subjects
WHERE word BETWEEN 0 AND 0 + 499
AND ( LIK' at line 2
File: /xxxxx/xxxxxxxx/xxxxxxx/xxxxxxxxxxx/Sources/Admin.php
Line: 1468

Note: It appears that your database may require an upgrade. Your forum's files are currently at version SMF 1.1.14, while your database is at version 1.1 RC3. The above error might possibly go away if you execute the latest version of upgrade.php.

I had seen the complaint about the database being at 1.1 rc3 before...even after running upgrade.php but it went away so I gave it little thought until now.

..and we're so close..

[agridoc]

We are very close.

Empty (NOT drop) all smf_log_search_* tables and try again.

[amordad_yar]

Success!  I'm seeing no problems and have asked the 'real' site admin to look around before I repeat this on the live site.  One more question ( assuming no problems are found ), For the best search results in this instance, what do you recommend?

[agridoc]

Keep the backup for quite a while and take also a backup of smf_members table only. Some problems might occur here. Some members may have been registered with ISO-8859-1 codepage (English), some with UTF-8 (Greek). There are no significant differences betwen English ISO-8859-1 and UTF-8, however some special chars may not have been properly converted. A polite notice should be present for awhile in the working forum, informing that, after upgrading, some members, especially if they used  special chars in their username, might have problem logging in and give clear instructions to use the "Forgot your passord" inputting the email if the username fails. If problems occur with logout, clearing the cookies for the site usually is the fix. Also the email of the administrator(s) should be given. I don't expect big problems, however some minor ones might exist.

The search will work now, for search method selection SMF help has enough information.

A search index can greatly improve the performance of searches on your forum. Especially when the number of messages on a forum grows bigger, searching without an index can take a long time and increase the pressure on your database. If your forum is bigger than 50.000 messages, you might want to consider creating a search index to assure peak performance of your forum.

Note that a search index can take up quite some space. A fulltext index is a built-in index of MySQL. It's relatively compact (approximately the same size as the message table), but a lot of words aren't indexed and it can, in some search queries, turn out to be very slow. The custom index is often bigger (depending on your configuration it can be up to 3 times the size of the messages table) but it's performance is better than fulltext and relatively stable.

SMF can be modified to use an external search script (as in this site). Such a script, special for Greek language, could be beneficial but, as far as I know, there is no information for such a working solution in the Greek support board.

It seems that we are near the end. You know what to do before converting, so I will wait for the final results. 

[amordad_yar]

Perfect!  The live site has been converted and I'll wait a few days to ensure nothing arises and will then likely upgrade to 2.0 ( but will try it first on the alternate site )