Go Back   vb.org Archive > vBulletin Article Depository > Read An Article > vBulletin 3 Articles
Reload this Page How to convert vbulletin to UTF8 charset
FAQ Community Calendar Today's Posts Search

Reply
 
Thread Tools
How to convert vbulletin to UTF8 charset
timhj
Join Date: Jan 2008
Posts: 4

 

Show Printable Version Email this Page Subscription
timhj timhj is offline 06-22-2009, 10:00 PM
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.
Reply With Quote
  #2  
Old 07-19-2009, 06:02 PM
N-0p3rz's Avatar
N-0p3rz N-0p3rz is offline
 
Join Date: Aug 2003
Location: San Antonio, TX
Posts: 81
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
Would there be any possible way to do this without shell access?

I suppose I could get my host to do it..
Reply With Quote
  #3  
Old 12-10-2009, 03:11 PM
bydosangel's Avatar
bydosangel bydosangel is offline
 
Join Date: Dec 2008
Location: Damas
Posts: 40
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
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
Reply With Quote
  #4  
Old 03-13-2010, 12:33 PM
glcvr6 glcvr6 is offline
 
Join Date: Feb 2010
Posts: 6
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
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
Reply With Quote
  #5  
Old 05-31-2010, 05:22 AM
funmasti's Avatar
funmasti funmasti is offline
 
Join Date: Aug 2009
Location: World Wide Web
Posts: 108
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
can i use this modification to convert my forum into Hindi (Indian) Language?
Reply With Quote
  #6  
Old 07-18-2010, 05:12 PM
Khaleal Khaleal is offline
 
Join Date: Mar 2009
Location: Palestine
Posts: 10
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
how can I create a database with utf-8 collation via SSH ?
Reply With Quote
  #7  
Old 05-20-2016, 04:20 PM
charalampos's Avatar
charalampos charalampos is offline
 
Join Date: Nov 2008
Location: Athens
Posts: 5
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
Didnt work to me my friend !

And I need a solution, but didnt work!

Sad.
Reply With Quote
  #8  
Old 04-28-2017, 01:33 PM
seb5594's Avatar
seb5594 seb5594 is offline
 
Join Date: Jun 2011
Location: Germany / Frankfurt
Posts: 30
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
This has worked so far, but after that no plugin does work.

Code:
define('DISABLE_HOOKS', true);
... is not in my config.

Any idea?
Reply With Quote
Reply

« Previous Thread | Next Thread »

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT. The time now is 02:46 AM.

Contact Us - Archive - Top

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2024, vBulletin Solutions Inc.
X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.04973 seconds
  • Memory Usage 2,281KB
  • Queries Executed 23 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)ad_showthread_beforeqr
  • (9)bbcode_code
  • (1)bbcode_php
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)modsystem_article
  • (1)navbar
  • (4)navbar_link
  • (120)option
  • (8)post_thanks_box
  • (5)post_thanks_box_bit
  • (8)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (1)post_thanks_postbit
  • (8)post_thanks_postbit_info
  • (7)postbit
  • (8)postbit_onlinestatus
  • (8)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 
Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_postinfo_query
  • fetch_postinfo
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • fetch_musername
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • post_thanks_function_fetch_thanks_bit_start
  • post_thanks_function_show_thanks_date_start
  • post_thanks_function_show_thanks_date_end
  • post_thanks_function_fetch_thanks_bit_end
  • post_thanks_function_fetch_post_thanks_template_start
  • post_thanks_function_fetch_post_thanks_template_end
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • post_thanks_function_can_thank_this_post_start
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete