[horde] switching to UTF8 database
morgan at orst.edu
Tue Feb 7 20:54:05 UTC 2012
On Tue, 7 Feb 2012, Simon Brereton wrote:
> 2012/2/7 Vilius Šumskas <vilius at lnk.lt>:
>> Tuesday, February 7, 2012, 8:17:28 PM, you wrote:
>>> On Tue, 7 Feb 2012, Vilius Šumskas wrote:
>>>> 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
>>>>> 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:
>> 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.
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.
More information about the horde