Database charset conversion (downgrade sql to non utf-8)

Wednesday 24 January 2007 7:37:28 am

Hey ai could really use some help.

We need to switch hosts for our ezpublish sites but the new one has MYsql 4.0.23 while the old one had Mysql 4.1.x

We had originaly set up for unicode support so the database was set up using UTF-8 and our pages were output as UTF-8. The primary use was to be able to output trademark and copyright characters easily.

I need to transition to MYSQL 4.0.23 while preserving these characters. Copyright has an ISO-8859 equivalent while trademark does not. These fields are stored as text lines in the Class, not XHTML. However we cna substirtute them with HTML entitites if need be.

What I need to know is the following:

How to export a mysqldump from a UTF-8 database into latin-1..
What settings should I use in siteaccess.ini and i18.ini for in the following

site.ini:

[DatabaseSettings]
Charset=utf-8

i18n.ini:
[CharacterSettings]
Charset=utf-8

template.ini:
[CharsetSettings]
DefaultTemplateCharset=utf-8

Do i use "latin"1 or "iso-8859-1" ... the documentation is sparse on this.

Thursday 25 January 2007 12:29:30 am

"DEFAULT CHARSET=" is not supported in mysql 4.0. You need to convert the database.

Dump the database and remember to use --skip-set-charset, or use the sed command after you have created the dump

mysqldump -u username -p --skip-set-charset database > dump.sql

sed -e 's/ DEFAULT CHARSET=UTF-8//' dump.sql > dump_fix.sql

Convert to ISO-8859-1

iconv --from-code=UTF-8 --to-code=ISO-8859-1 dump.sql > new_dump.sql

Dump database back to new server

mysql -u username -p database < new_dump.sql

This should be all.

Try out Free eZ Publish 4.0 Trial Hosting for 14 days:
http://webdealhosting.com/ez-publish-trial-hosting

High quality eZ Publish Hosting since 2001!

Thursday 25 January 2007 5:38:35 am

Hi,

I have utf8 on mysql 4.0 servers. I know they are limitations/problems but it has been running smoothly on my sites so far.

Skip the iconv step and see how it works for you.

Another option (the one I used) was
1) create the tables (empty) on the 4.0 server
(kernel/sql/mysql/kernel_schema.sql )
2) dump only the datas from the 4.1 database

3) Import on the existing empty tables

Might make it easier...

Let us know.

X+

http://www.sydesy.com