Go Back   vb.org Archive > vBulletin Article Depository > Read An Article > Programming Articles
Reload this Page Another easy way to convert database from Latin1 to UTF8
FAQ Community Calendar Today's Posts Search

Reply
 
Thread Tools
Another easy way to convert database from Latin1 to UTF8
huuquynh
Join Date: Sep 2004
Posts: 23

 

Show Printable Version Email this Page Subscription
huuquynh huuquynh is offline 01-07-2010, 10:00 PM
I tried another way by searching some articles on vbulletin but it takes me long time, many steps, and not success... I found another way to do. I hope that my experiment can help someone is running a vbulletin for a long time with latin1, and now he want to convert to utf8.

After trying by myself offline some ways to convert database from latin1 to utf8, I see that there is a simple way: less steps and really easy, and no need special tool: iconv-chunk (if the database file is too big), uconv (database tools)...

First of all. Turn off your vbulletin board. And turn off httpd/apache service to dedicate all resources for database converting.


Next step: change mysql database server configuration.

Because the default of mysql database server is running at latin1 charset. So we need to change it to UTF8 by modify my.cnf and input more settings:

Code:
default-character-set = utf8

[client]

default-character-set = utf8
After modified, restart mysql server for the changing affect...

Next step: dump the old database to file.

Code:
mysqldump -u dbuser -p --opt --skip-set-charset --default-character-set=latin1 database > yourboard.sql
(this command will ask you type the db's password)

Next step: change charset from latin1 to utf8 in your dump file.

Code:
perl -i -pe 's/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8' yourboard.sql
Next step: keep the old database for rescure if you do something wrong... create new database with charset utf8.

Code:
mysql --user=dbuser -p --execute="CREATE DATABASE new_database_name CHARACTER SET utf8 COLLATE utf8_general_ci;"
(this command will ask you type the db's password too)

Next step: import to new database.

Code:
mysql -u dbuser -p new_database_name < yourboard.sql
Last step: Modify config.php.

Modify the new database to connect to in config.php.

And if you are using mysqli, go to the bottom of your config, you will see one line to un-comment.

That's all. I did by myself for my board with more than 100.000 members and 1.000.000 posts.

Goodluck! And remember that you should keep the original database for rescure if you do something wrong.

Quynh H Nguyen
Reply With Quote
  #2  
Old 01-15-2010, 02:10 PM
SeV3n SeV3n is offline
 
Join Date: Jan 2004
Posts: 16
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
I'll try it for my forum too. Thanks.
Reply With Quote
  #3  
Old 06-25-2010, 05:21 PM
Trajche Trajche is offline
 
Join Date: Aug 2009
Posts: 3
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
What about the language encoding setting in vBulletin ? Did you change that too?

P.S.

I think you forgot the third slash here:
Code:
perl -i -pe 's/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8' yourboard.sql
As it gives the error..
Code:
Substitution replacement not terminated at -e line 1.
Therefore the right line would be:
Code:
 perl -i -pe 's/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/' yourboard.sql
Am I on the right track here? Or completely off?
Reply With Quote
  #4  
Old 03-09-2014, 08:13 PM
dimitrisgr dimitrisgr is offline
 
Join Date: Aug 2012
Posts: 22
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
Ηello...

I have the same problem but I have my own server and I can not make this change. There are other ways;;
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 03:45 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.03877 seconds
  • Memory Usage 2,225KB
  • Queries Executed 19 (?)
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
  • (8)bbcode_code
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)modsystem_article
  • (1)navbar
  • (4)navbar_link
  • (120)option
  • (4)post_thanks_box
  • (4)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (4)post_thanks_postbit_info
  • (3)postbit
  • (4)postbit_onlinestatus
  • (4)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
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • fetch_musername
  • 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