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".




 

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.




 

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.




 

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.




 

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).


Posted on May 13th, 2015 · Updated on January 13th, 2017

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

Return to the Article Index
Return to the Category "Guides"
Submit a Article for Review