Re: migrating 4.0 to 4.1 character set problems
Hi Sean, I used to have similar problems with different character sets and my solution is: 1) dump all data using mysql dump utility 2) check if data are OK - view your data with viewer which suports correct character set 3) if data are not OK try to dump data in different character set 4) you must be sure that data are dumped OK and you know their character set 5) you can convert your dumped data manualy using some conversion program or you can let MySQL server do it 6) if you did conversion of dumped file you must also change SET NAMES 'new character set' command 7) setup correct default character set for your MySQL server or change or add character sets to your definitions of tables and databases in dumped file 8) load data HTH Dusan - Original Message - From: Sean O'Hara [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, March 14, 2006 5:09 PM Subject: migrating 4.0 to 4.1 character set problems 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
migrating 4.0 to 4.1 character set problems
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/[EMAIL PROTECTED]
Re: migrating 4.0 to 4.1 character set problems
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]
