[horde] switching to UTF8 database

[Vilius Šumskas]

Sveiki,

Tuesday, February 7, 2012, 10:54:05 PM, you wrote:

> On Tue, 7 Feb 2012, Simon Brereton wrote:

>> 2012/2/7 Vilius Šumskas <vilius at lnk.lt>:
>>> Sveiki,
>>>
>>> Tuesday, February 7, 2012, 8:17:28 PM, you wrote:
>>>
>>>> On Tue, 7 Feb 2012, Vilius Šumskas wrote:
>>>
>>>>> Sveiki,
>>>>>
>>>>> Tuesday, February 7, 2012, 12:34:57 AM, you wrote:
>>>>>
>>>>>> I'm running Horde v3.3.12 (I know, upgrade to Horde 4 is on my todo list)
>>>>>> with MySQL v5.1.  My Horde database is an old install that has been
>>>>>> upgraded many times.
>>>>>
>>>>>> The character set in horde/config/conf.php is:
>>>>>
>>>>>> $conf['sql']['charset'] = 'iso-8859-1';
>>>>>
>>>>>> and my collation on the database and tables in MySQL are set to
>>>>>> "latin1_swedish_ci".
>>>>>
>>>>>> I'd like to switch to UTF8.  Are these steps sufficient:
>>>>>
>>>>>> 1. Alter MySQL database and table collations to "utf8_unicode_ci"
>>>>>
>>>>>> 2. Set $conf['sql']['charset'] = 'utf-8';
>>>>>
>>>>> It  depends  what data is stored. See my older messages regarding that
>>>>> on this mailing list. Or was it on dev at lists.horde.org?
>>>
>>>> I searched via Google but nothing obvious turned up.  If you have a
>>>> timeframe and a listname, I'd be happy to go search the archives.
>>>
>>> Mainly these treads:
>>>
>>> http://lists.horde.org/archives/dev/Week-of-Mon-20110718/026367.html
>>> http://lists.horde.org/archives/dev/Week-of-Mon-20101213/025567.html
>>>
>>> You  can  also  search  the  net  for  general tips and tricks how to
>>> convert   data/charset   in   MySQL.   This  is really related more to
>>> MySQL itself than to Horde.
>>
>> I don't know if this helps - but when I was migrating to Horde4 I
>> coupled it with a change in data/charset encoding.  Since I wanted to
>> use the H3 db, what I did was:
>>
>> Convert to UTF8:
>> mysql -u root -p -B -N  -u root -p -v --execute="select CONCAT('alter
>> table ',TABLE_SCHEMA,'.',TABLE_NAME,' CONVERT to CHARACTER SET uft8
>> COLLATE utf8_general_ci;') from information_schema.TABLES WHERE
>> TABLE_SCHEMA != 'information_schema';" > alter.sql

> Any reason you are using utf8_general_ci instead of utf8_unicode_ci?  I
> read that utf8_unicode_ci is a more accurate collation, but can be slower.

>> vim alter.sql
>> remove any mysql/mysqlbkup table lines
>>
>> Apply the new Character Set
>> mysql -u root -p -v < alter.sql
>> /etc/init.d/mysql stop
>> /etc/init.d/mysql start

> Why did you stop/start MySQL?

>> Then I installed Horde4 and pointed it to the same DB (actually a
>> copy, just in case things went fubar) and then after configuring each
>> Horde application (IMP, Turba, etc,) I just ran the update-schema
>> action from the Admin panel.

> Vilius mentioned some special concerns when converting data between 
> character sets.  Do those apply if I am converting from iso-8859-1 
> (latin1) to UTF8?  I think iso-8859-1 maps directly into UTF8, right?

> We have been running latin1 the whole time, so we shouldn't have any 
> unusual data stored in the tables right now.

MySQL's  latin1  is  in  fact  Windows-1252.  It  has  some additional
characters.   I   would  still  suggest  dumping  the  database, using
something like iconv and importing back. It's really easy.

> Can I switch to UTF8 while I'm still on Horde3?  Our Horde4 deployment
> will take a while for testing and documentation updates, but I'd like to
> get this conversion done first if that is technically possible.

It  is  possible but it's not supported and you have to modify PEAR::DB library which is used
by  Horde and add needed SET NAMES call. Also if you are running Whups
or  other "not so popular" applications  which  uses  PEAR::MDB2 you have to modify it
either.

As  always,  backup, backup and backup once more before you do anything
with original database.

-- 
Best regards,
 Vilius