Koozali.org: home of the SME Server

Obsolete Releases => SME 7.x Contribs => Topic started by: cactus02 on October 23, 2009, 06:05:17 PM

Title: Mysql character sets configuration
Post by: cactus02 on October 23, 2009, 06:05:17 PM

I want to change mySQL latin1 character set configuration to UTF8 by default !
Wordpress blog used UTF8 but mySQL is set to latin1.
This cause problems when restoring a backup. Accentuated characters gets messed up !
é becomes A°... Etc...
Is there a configuration file I can change ?
Thank you !

Title: Re: Mysql character sets configuration
Post by: mmccarn on October 25, 2009, 04:39:03 PM

From this post: http://alexking.org/blog/2008/03/06/mysql-latin1-utf8-conversion, it looks like you need to:
 - dump your wordpress database
 - delete it
 - re-create it using the correct character set
 - reimport your data

(alternatively, it may be possible to dump your wordpress data, create a new database, then change your wordpress config to use the new database)

SME includes a script to dump all SQL databases: /etc/e-smith/events/actions/mysql-dump-tables, which places dumps of all databases in /home/e-smith/db/mysql

Title: Re: Mysql character sets configuration
Post by: cactus on October 25, 2009, 08:27:08 PM

I suggest you do as mmccarn suggests as changing the default charachter set will undoubtedly influence webmail, which also uses MySQL.

The easiest solution is to do something like this:

Code: [Select]

mysqldump --opt --database databasename > /tmp/wordpress-org.sql
sed -e 's/SET NAMES utf8/SET NAMES latin1/' /tmp/wordpress-org.sql > /tmp/wordpress-new.sql
mysqladmin drop databasename
mysqladmin create databasename
mysql -e 'ALTER DATABASE databasename DEFAULT CHARACTER SET latin1'
mysql databasename < /tmp/wordpress-new.sql
Make sure to replace every occurence of databasename with the correct name of your database.