Hi,
I have figured out a way to get this to work. I am just posting what
I did in case anyone else stumbles across my original message. I am
sure there are other ways to get this to work, but this is the way
that worked for me:
###################################################
# My HOWTO: Covernt mysql 4.0.x DBs to 4.1.x #
# This will also update the character sets from #
# Latin1 to utf8 #
# #
###################################################
1. Backup the old database. It must be backed up as separate tab
separate files like the following:
mysqldump --tab=/tmp/database database -u user --password=password
2. Tar and transfer the directory if necessary, then import it using
the following commands:
mysql -e "CREATE DATABASE database default character set latin1"
cat /tmp/database/*sql | mysql database -u user --password=password
mysqlimport --default-character-set=latin1 alien8_orig /tmp/tables/
*txt -u user --password=password
3. Convert the database tables to utf-8
ALTER TABLE database.table type=MyISAM, CONVERT TO CHARACTER SET
utf8 COLLATE utf8_general_ci;
4. For use with php5, after every database connection send the
following query:
mysql_query("SET NAMES 'utf8'");
5. Also, for PHP5, you will want to make sure that server is using
utf-8 as the encoding. I believe this is the default. And pages
should be valid xhtml. I use the content-type meta tag just to be safe:
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
Thanks,
Sean
On 14-Mar-06, at 11:09 AM, Sean O'Hara wrote:
Hi,
I am having character set problems while trying to migrate my data
from a server running 4.0.25-standard to a server running 4.1.16. I
believe that the orginal database was using the latin1 character
set (not sure, is there any way to tell? show full column doesn't
seem to be supported in 4.0.x) and the default character set for
the 4.1 server is latin1 as well. However when I try to import my
data from a mysqldump from the older server all accents are turned
into question marks.
I've searched the forums and mailing lists extensively for clues as
to how to resolve this issue. The most helpful post I found was
here: http://lists.mysql.com/mysql/186657
However, even following the instructions on this post I ran into
problems. Rather than turniing accents into question marks, any
data with accent was truncated at the first instance of such a
character (e.g. López becomes L). So that is not working either.
Incidentally, I would be happy to convert the data to utf8 while
upgrading to 4.1 but mostly I just want to get rid of the question
mark characters. Any ideas?
Thanks,
Sean
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
______________
ALIEN8 RECORDINGS
P.O. BOX 666, STATION R
MONTREAL, QC
CANADA, H2S 3L1
http://www.alien8recordings.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]