• Welcome to Simple Machines Community Forum. Please login or sign up.
December 09, 2021, 03:09:24 AM

News:

Check out the SMF Function DB!


Converting to utf-8

Started by dxyy, November 26, 2008, 04:39:34 AM

Previous topic - Next topic

dxyy

Norv thanks a lot for your suggestions. I have a quick question about exporting the database. Does structure only mean that I will not be compromising the data of my users? Haha, it might seem like a silly question to experienced heads like yourself, but bear with me please.

Thanks!

Norv

Quote from: hugodiaz on December 26, 2009, 02:10:49 PM
Norv thanks a lot for your suggestions. I have a quick question about exporting the database. Does structure only mean that I will not be compromising the data of my users?

Yes, structure-only means that absolutely nothing of the data of users, no users, no posts, not even the default post that's installed with SMF, will not be in the file. What will be, are SQL instructions about how to create a similar - but empty - database.
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

dxyy

Ok, thanks for the reply.

Here is the structure of both my regular forum (no changes have yet been implemented, but Chinese characters are usually displayed properly if I change my browsers encoding to utf-8) and my test forum (database was already converted to utf-8, but old Chinese characters cannot be displayed no matter what I do)


Norv

Can you please verify and let us know the answers to the other two questions above?

Quote from: Norv on December 11, 2009, 06:45:24 PM
2) connect with FTP to your account, and download to your computer the file Settings.php from your forum directory. Open it in a code editor and see if it contains a line like:

$db_character_set = 'utf8';

Please tell if you have that line. (don't modify the file yet)

3) In phpMyAdmin, select the "SQL" tab, and paste there the following SQL code:

SELECT value FROM smf_settings WHERE variable = 'global_character_set';

and post the result here.
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

dxyy

Norv, just in case you're not aware of this I want to reiterate that I have my regular forum and the test forum. I haven't modified anything on the regular forum as yet, but I already changed the database to utf-8 through phpMyAdmin on the test forum.

Now to answer your questions. All of these are with respect to the test forum.

Settings.php contains the line
$db_character_set = 'utf8';

As for the stuff in phpMyAdmin, maybe the result is ... nothing? Here is a screenshot for your reference.

Norv

Yes, thank you, I know actually, and I've re-read all the topic and checked out the two forums again, in fact, to refresh my memory.
And yes, please consider these questions are about the test forum, if the situation remained the same as in previous posts.

Quote from: hugodiaz on December 27, 2009, 07:02:34 AM
I haven't modified anything on the regular forum as yet, but I already changed the database to utf-8 through phpMyAdmin on the test forum.
How did you do that? I thought you ran "convert to utf8" from the forum administration tools, not with phpMyAdmin. Please correct my understanding if wrong.

What language is selected for the test forum, currently, as default language? Please specify if English, or English-utf8, or is English-utf8 installed at all?
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

dxyy

I believe I later tried installing English utf-8, but the default language is still set to English.

As for changing the database, if I remember correctly I went to phpMyAdmin, then to operations tab and changed the collation to utf8_general_ci.  I also think I then went to the admin panel in my smf forum and .... oh no, actually I'm confusing myself now, because I seem to also remember using the smf admin panel to convert the database.

It's really unfortunate that I didn't deal with this back then when everything was still fresh in my mind.

Anyway, I still have my original forum, so any suggestions as to what I should do with that database? Of course I will set up yet another test forum to see how everything goes before messing around with my actual forum.

Norv

Yes, that's what I would propose.
Please consider copying the "real" forum all on another test forum, both files and restoring the database into a new one. You might want to create the new database as UTF-8, to avoid potential annoyances later.
Run repair_settings.php on the new test forum, then, to correct the paths and database.

Download and install the English-UTF8 language pack.
Switch the default language to English UTF-8. Disable allowing people to set their own language (if enabled).

Then, please let us know how it goes, and what is working wrong with the new test forum, at this point.

Side note: Are you using mods? If yes, they may need edits in the English-UTF8 files, most likely you would need to make those manually.
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

dxyy

How do I create the database as utf-8?

Yes, I'm using quite a few mods... haha, so I'm not really looking forward to having to make all those manual edits.

Norv

If you can create a database in phpMyAdmin, then on the page there must be a "collation" option, where you can choose utf8-general-ci.
If you create it using your host's panel interface, then just create it and then, go in phpMyAdmin and see what collation is has, if possible. You might be able to also change it directly, then.

Well, only those that modified language files will need edits.
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

dxyy

Ok, I'm supposedly on vacation right now so I should have enough time to finally tackle this issue.

I'm confused and concerned about one thing. My database is currently not utf-8 and due to the fact that both English and Chinese have been used by members to make posts wouldn't that affect the way these posts are stored in the database tables? So if I create a new database as utf-8 and upload my current database there, wouldn't I still have a problem with those characters that were not originally stored as utf-8?

What I'm really asking is do I need to somehow convert my databse to utf-8 (like on my computer) and then upload it to the newly created database (which is also setup as utf-8)? Hope my question makes sense?

I just want to start from scratch. Take my real forum's database and figure out how to make it work the way it should i.e. individual members do not have to mess with browser encoding settings and everyone can see the characters being displayed properly! Haha, but there still needs to be the option for users to choose between English (I guess utf-8) and simplified Chinese for the forum's display language. ;)

JimM

If you are doing this on a test forum then I would try it both ways.  Import your database and then convert it from the adminCP and see what the results are.  If everything is good, then you can move on to trying the other methods you mentioned.  The key is to keep a backup untouched so you can keep trying till you get it the way you want.
Jim "JimM" Moore
Former Support Specialist

dxyy

Question; if after going to Admin - Forum Maintenance - Convert the database and data to UTF-8 and proceeding, I still see the same option "Convert the database and data to UTF-8" does that mean it was not successful?

babjusi

The easiest solution would be to write a php script connecting to the db changing the coalltion charatcters to utf-8. Or you could do it table by table at the db by changing it at the Operation tab or running a few queries.

dxyy

Thanks for your reply, but your easy solution is actually quite complex to me since I know next to nothing about writing scripts.

Care to help? ;)

http://www.simplemachines.org/community/index.php?topic=362048.0

JimM

Once you complete this be sure and mark this topic as solved. :)
Jim "JimM" Moore
Former Support Specialist

dxyy

Quote from: JimM on January 26, 2010, 04:55:58 PM
Once you complete this be sure and mark this topic as solved. :)
I'll be sure to do that, but I'm still struggling with this. I'm going to hire someone to write a script for me, and hopefully that will put this issue to bed. ;)

Advertisement: