It appears that you're running an Ad-Blocker. This site is monetized by Advertising and by User Donations; we ask that if you find this site helpful that you whitelist us in your Ad-Blocker, or make a Donation to help aid in operating costs.

Converting to UTF8 · Article

By Isaac DeCoursey Edited by James Corthell

What Is Covered

Summary
Converting Your Forums
     • Duplicating your Database in MySQL via PHPMyAdmin
     • Converting your MySQL Database via PHPMyAdmin
     • Converting your MySQL Tables via PHPMyAdmin
     • Repairing your MySQL Tables via PHPMyAdmin
     • Changing your Forum Character Set
     • Rebuild your UBB.threads Content
     • Testing the Conversion
Troubleshooting


 

Summary


In 2006, I converted the MySQL database on one of my larger forum by changing the collation to "utf8_general_ci" (UTF-8 Unicode) and never ran in to any problems, not even problems with passwords. Then again, I never allowed the use of extended character sets to be accepted in user names or passwords, and those forums are 100% English, even though they cater mostly to users in the US, Canada, the UK, Australia, and Japan.

In 2010, as part making that same forum much more SEO friendly, I updated in the language files and the header meta tags from "iso-8859-1" to "utf-8" (Control Panel > Languages > Language Editor > generic.php > CHARSET).


 

Converting Your Forums


Be sure to create a database backup, then be sure that backup is complete. The steps outlined here are all done manually, and backups are not created automatically. There are backup instructions included on the "Migration Guide".

Note
As noted in the Migration Guide, we do not advise backups to be made via PHPMyAdmin as server settings could result in the datastream of the backup collection to be ended prematurely; resulting in a file which you believe to be complete, which could in fact only contain a portion of your content.



 

Duplicating your Database in MySQL via PHPMyAdmin


To duplicate your database in PHPMyAdmin you'll want to be sure you have the correct database selected and select the "Operations" tab at the top of the page. In the "Copy Database To:" field you'll enter the name that you want your backup to have (such as "ubbt_BAK"); be sure that "Structure and Data", "Create Database Before Copying, and "Add AUTO_INCREASEMENT Value" are all selected (any additional fields should not be selected). When everything is configured as specified you can click the "Go" button to begin.

Note
If anything goes wrong with the conversion, just delete the converted database and rename your backup to reflect the active database name.



 

Converting your MySQL Database via PHPMyAdmin


After you've backed up your database you'll want to first close your forums; you don't want new content being created when you're attempting to convert the existing data. After you've created your backups, and confirmed that they are complete, go back to the "Operations" tab for your active database. In the "Collation" group, choose the "utf8_general_ci" collation; do not change any other options, select "Go" when you're ready.


 

Converting your MySQL Tables via PHPMyAdmin


From the table selection menu, displayed on the left of the page when your database is selected, go to the first table of your database (ex: ubbt_ADDRESS_BOOK). Now select the "Operations" tab at the top of the page. In the "Table Options" group, choose the "utf8_general_ci" collation; do not change any other options, select "Go" when you are ready. Repeat this step for all of the tables in your UBB.threads database.

Note
It took me about 10 minutes to complete the conversion of all of the tables in my UBB.threads database.



 

Repairing your MySQL Tables via PHPMyAdmin


You should now REPAIR all of the MySQL Tables that you converted; this step will optimize all of the tables when they are rebuilt. First you'll select your UBB.threads database in the left navigational menu so that it displays all of the tables in the database. At the bottom of this page you'll see a "Check All" button on the bottom left of the page. Once all of the tables are selected, choose the "Repair Table" option from the dropdown listing.


 

Changing your Forum Character Set


You'll now want to edit your forums Character Set, changing the CHARSET key from "iso-8859-1" to "utf-8" in the Control Panel (Control Panel > Languages > Language Editor > generic.php). Changing the CHARSET key has no affect on the data in your database, it simply tells your web browser what character set that content is expected to be displayed in, rather than leave that decision up to the browser to determine on its own.


 

Rebuild your UBB.threads Content


Now you'll want to rebuild your UBB.threads content; this is done in the Control Panel (Control Panel > Content Rebuilder). You'll want to rebuild all Posts, Topics, Forums, Signatures, and Private Messages.

Note
Rebuilding all forum content is probably overkill, but I did it without loosing any sleep worrying about not doing it; if you are for certain that you have not introduced any extended character sets to your database, you most likely will not have any problems (I have roughly 4k users that browse my forum each day, since about 2003; when there is a problem, I hear about it).



 

Testing the Conversion


Now, you should login to your forums and browse your existing content, making sure that all forums and posts are displaying properly (see "How to Fix Black Diamonds for examples of display issues). If all goes according to plan, no one will notice anything, and everything should look as intended. If anything goes wrong, you should have backups on hand to restore your forums to how they where before you started, so that you can try again.


 

Troubleshooting


One issue that you (or your members) might come across is that some single byte characters (such as Swedish å, ä, ö, the temperature degree symbol, 1st/2nd/3rd (...) symbols, and most notoriously, Microsoft Word's backwards-single-quote "'", to name a few) will look like a black diamond; this has no effect on the functionality of your forums, it's simply a display issue that you may run across once in a blue moon. If this bothers you, you can opt to edit the post and change the offending character with its equivalent character (such as changing the backwards single quote with a standard single quote).

Note
If you read this article and you don't understand what has been conveyed, please do NOT attempt to preform the update to your site, you should instead have a professional complete this task for you.

Posted on May 13th, 2015 · Updated on January 13th, 2017
▼ Sponsored Links ▼
▲ Sponsored Links ▲

Comments and Attributions

This article was formed with the data available on the UBB.Central thread Can we finally switch to UTF-8?.

Comments

( Posted)