Adapting Joomla database to UTF8

July 23, 2008 · 6 Comments

Few days ago I was searching for an easy way to adapt MySQL database used by Joomla from latin1 to UTF8 character set. I haven’t found anything, so I wrote this little script.

It’s easy to make a database which uses UTF8 character set when you’re installing Joomla by hand. Though, sometimes your hosting provider doesn’t give you the right to create new databases, so the only way is to install it using some software (cPanel, for example). It will usually set DB to use latin1 encoding. So, after it’s installed we have to modify database and its tables using SQL. This script should do the job, at least for Joomla 1.5, since it’s modifying tables used in Joomla 1.5.

Update: I’m not sure, but altering table jos_users seems to break the passwords. I think you can safely skip it…

ALTER DATABASE CHARACTER SET `utf8` COLLATE `utf8_general_ci`; ALTER TABLE jos_banner CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_bannerclient CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_bannertrack CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_categories CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_components CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_contact_details CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_content CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_content_frontpage CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_content_rating CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_core_acl_aro CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_core_acl_aro_groups CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_core_acl_aro_map CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_core_acl_aro_sections CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_core_acl_groups_aro_map CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_core_log_items CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_core_log_searches CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_groups CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_menu CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_menu_types CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_messages CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_messages_cfg CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_migration_backlinks CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_modules CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_modules_menu CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_newsfeeds CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_plugins CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_poll_data CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_poll_date CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_poll_menu CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_polls CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_sections CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_session CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_stats_agents CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_templates_menu CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_users CONVERT TO CHARACTER SET `utf8`; ALTER TABLE jos_weblinks CONVERT TO CHARACTER SET `utf8`;

This script assumes that the prefix of your tables is “jos_”. In case it’s not, just change it in the script. This script can also be applied to any other database. Just open it, and use the names of your tables, instead of these.

In case there is an easier way, I would be thankful if it gets posted in the comments.

Little disclaimer: I’m in no way responsible if something goes wrong. Be careful when you start doing this.

Categories: Programming · SQL · internet
Tagged: internet, joomla, latin1, mysql, Programming, SQL, tables, utf8

6 responses so far ↓

Karlo // September 15, 2008 at 3:15 pm | Reply

Hvala prijatelju na ovome ! Izgleda jedini pametni i učinkoviti fix za ovaj problem !

Great !
Igor Đorđević // September 26, 2008 at 4:26 pm | Reply

Bravo! Radi!

A za sve one koji ne znaju sta se radi sa ovom skriptom (kao sto sam se ja neko vreme pitao), jedno od resenja je da skinete ekstenziju za Joomla-u sa ove adrese – http://extensions.joomla.org/component/option,com_mtree/task,viewlink/link_id,2867/Itemid,35/ pa da nakon toga to lepo instalirte (pojavice se u meniju Components). U poveliko prazno polje iskopirajte kompletnu skriptu i samo kliknite na “Exec SQL”, nakon cega cete dobiti izvestaj sta je uradjeno…

I to je to!
Hvala jos jednom! =)
zenway // November 9, 2008 at 1:13 am | Reply

This unicode problem trouble us for 2 days. Your codes solved all the problem. Thanks so much!!!
Sokobanja // December 26, 2008 at 1:36 pm | Reply

Thank you so much
stanley // January 3, 2009 at 11:33 pm | Reply

thank you so much for the code
it really helped me out

I installed both joomla and virtue mart, both were in latin code
I just added some lines to your code and now the problem is solve, thanks you so much

by the way, here’s the code I use

ALTER DATABASE CHARACTER SET `utf8` COLLATE `utf8_general_ci`;
ALTER TABLE jos_banner CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_bannerclient CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_bannertrack CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_categories CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_components CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_contact_details CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_content CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_content_frontpage CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_content_rating CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_core_acl_aro CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_core_acl_aro_groups CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_core_acl_aro_map CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_core_acl_aro_sections CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_core_acl_groups_aro_map CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_core_log_items CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_core_log_searches CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_groups CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_menu CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_menu_types CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_messages CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_messages_cfg CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_migration_backlinks CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_modules CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_modules_menu CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_newsfeeds CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_plugins CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_poll_data CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_poll_date CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_poll_menu CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_polls CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_sections CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_session CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_stats_agents CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_templates_menu CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_users CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_affiliate CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_affiliate_sale CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_auth_group CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_auth_user_group CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_auth_user_vendor CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_cart CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_category CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_category_xref CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_country CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_coupons CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_creditcard CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_csv CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_currency CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_export CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_function CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_manufacturer CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_manufacturer_category CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_module CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_orders CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_order_history CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_order_item CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_order_payment CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_order_status CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_order_user_info CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_payment_method CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_product CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_product_attribute CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_product_attribute_sku CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_product_category_xref CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_product_discount CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_product_download CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_product_files CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_product_mf_xref CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_product_price CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_product_product_type_xref CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_product_relations CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_product_reviews CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_product_type CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_product_type_parameter CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_product_votes CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_shipping_carrier CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_shipping_label CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_shipping_rate CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_shopper_group CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_shopper_vendor_xref CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_state CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_tax_rate CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_userfield CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_userfield_values CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_user_info CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_vendor CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_vendor_category CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_visit CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_waiting_list CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_vm_zone_shipping CONVERT TO CHARACTER SET `utf8`;
ALTER TABLE jos_weblinks CONVERT TO CHARACTER SET `utf8`;
PinGUIWin // March 1, 2009 at 5:17 pm | Reply

Thanks a lot from Russia for help! It works!