How to convert vbulletin to UTF8 charset
 

We've just successfully migrated a 6m post VB forum cross server and charset and as I spent a fair amount of time working this out I thought I'd share. There are apparently third party tools to do this but I've read mixed reviews about them.

I found a good step by step on the charset changeover here (http://megaz.arbuz.com/2009/04/09/co...letin-to-utf-8) though there are some errors in what he's done.

To manually make the transition on a linux box, you'll need shell access and here's what needs to be done...

1. Shut-off forums in admin panel.
2. Shell into your box and dump all the mysql data minus binary image data
   
   Code:

   ---

   #        mysqldump -u DBUSERNAME -p --opt --skip-set-charset --ignore-table=VBULLETINDBNAME.customavatar --ignore-table=VBULLETINDBNAME.customprofilepic --ignore-table=VBULLETINDBNAME.picture --ignore-table=VBULLETINDBNAME.socialgroupicon --ignore-table=VBULLETINDBNAME.socialgrouppicture VBULLETINDBNAME > VBULLETINDBNAME.sql

   ---

3. Dump the image data
   
   Code:

   ---

   #mysqldump -u DBUSERNAME -p --opt --default-character-set=latin1 VBULLETINDBNAME customavatar customprofilepic picture sigpic socialgroupicon socialgrouppicture > VBULLETINDBNAME_images.sql

   ---

4. Convert all the characters in the data dump to UTF8 via iconv
   
   Code:

   ---

   #iconv -c -f windows-1251 -t utf-8 VBULLETINDBNAME.sql > VBULLETINDBNAME_utf8.sql

   ---

5. Change the import script to make sure it imports as UTF8
   
   Code:

   ---

   #sed ?i 's/latin1/utf8/g' VBULLETINDBNAME_utf8.sql
#sed ?i 's/latin1/utf8/g' vogue_forums_untouchable_images.sql

   ---

6. Create your new database on the second server *Obviously make sure your new database is setup with UTF8 as the default collation*
7. Import your converted dump into the db you just created.
   
   Code:

   ---

   #mysql -u NEWDBUSER -p ?default-character-set=utf8 NEWVBDATABASENAME < VBULLETINDBNAME_utf8.sql
#mysql -u NEWDBUSER -p ?default-character-set=utf8 NEWVBDATABASENAME < VBULLETINDBNAME_images.sql

   ---

8. Now if you've changed domains you'll need to update the settings tables... you can get away with the following - there's two db values you need to change first one is pretty simple:
   
   Code:

   ---

   mysql> UPDATE setting set value = 'http://YOURNEWDOMAIN' where varname = 'bburl'

   ---

9. The second update is stored as a serilaized value in the datastore table so you need to do a bit of work to get the right return value:
   First grab the data you need to change from the old DB
   
   Code:

   ---

   mysql>select data from VBULLETINDBNAME.datastore where title = 'options'

   ---

   Grab the returning value and throw it in the following php (once you've changed the your new domain bits) script.. then grab the output:
   
   PHP Code:

   ---

   # MYSQL: select data from VBULLETINDBNAME.datastore where title = 'options'
$the_object = 'PUT RESULT OF ABOVE MYSQL QUERY';
$the_object = unserialize($the_object);
$the_object['homeurl'] = 'http://YOUR NEW DOMAIN';
$the_object['bburl'] = 'http://YOURNEWDOMAIN';
$the_object['cookie_domain'] = 'YOURNEWDOMAIN';
echo serialize($the_object); 


   ---

10. Update the datastore table with the output from the above script
    
    Code:

    ---

    mylsql> UPDATE datastore set data = 'OUTPUT FROM ABOVE - ESCAPE THE SINGLE QUOTES ETC' where title = 'options';

    ---

11. Load up a clean vbulletin latest release in a new vhost on second server
12. change the /includes/config.php on the new install to reflect the new environment
13. run through the vbulletin upgrade process at newserver.xxx/install/upgrade.php (disregard warning about wrong version)
14. Update thread counters (admin -> maintenance -> update counters -> thread then post)
15. Go to admin panel -> address all issues. (if any hang, log out and in again and they will be shown again for you to run)
16. If you can't login: Upload the DO_NOT_UPLOAD folder and reset the cookie_domain using the newserver.xxx/DO_NOT_UPLOAD/tools.php script
17. If forum not displaying use the tools (and you've updated thread counts etc in admin/maintenance) reset the caches using the tools.php page
18. update the default character set (Admin CP -> Languages & Phrases -> Language Manager -> [Edit Settings] -> HTML Character Set) change ISO-8991 to UTF8
19. delete the DO_NOT_UPLOAD folder from your server
20. delete the install directory from server

Now you're be right to go with a UTF8 forum! YAY. don't you feel good.

Would there be any possible way to do this without shell access?

I suppose I could get my host to do it..

Hmmmm
Woul'd it Work on Arabic Forums?
I have a huge Arabic Site with too much threads... usernames... Data

Can I do that and Convert all of them into UTF 8 to all the Data?

Thanks for sharing though :)

You don't need to do all that, just go to admincp > language manager > edit > and there find caracter set and change it to UTF-8 or UTF8

can i use this modification to convert my forum into Hindi (Indian) Language?

how can I create a database with utf-8 collation via SSH ?

Didnt work to me my friend !

And I need a solution, but didnt work!

Sad.

This has worked so far, but after that no plugin does work.

... is not in my config.

Any idea?