Adapting Joomla database to UTF8

Posted by Saša Bodiroža | Filed under Programming, SQL, internet

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.

Tags: , , , , , , ,

Permalink | July 23rd, 2008

3 Responses to “Adapting Joomla database to UTF8”

  1. Karlo Says:
    September 15th, 2008 at 3:15 pm

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

    Great !

  2. Igor Đorđević Says:
    September 26th, 2008 at 4:26 pm

    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! =)

  3. zenway Says:
    November 9th, 2008 at 1:13 am

    This unicode problem trouble us for 2 days. Your codes solved all the problem. Thanks so much!!!

Leave a Reply