Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

character issue with mysql 5

Hello Expert !

I have another issue since I upgraded the mysql 4.1 to 5.0.17.  All of my field type text have strange character instead of é à etc ...

I try to import data, backup, restore, change my table type innodb -->  myisam and vice-versa ...  

nothings work ...

any idea ?
christine
0
ExfoWeb
Asked:
ExfoWeb
  • 4
  • 4
1 Solution
 
VoteyDiscipleCommented:
In what character encoding did you download the data when running mysqldump from the 4.1 database?

The storage engine has no impact on the character encoding used, so changing away from InnoDB doesn't help anything.  What matters is most likely the character encoding of the CONNECTION you used when uploading the data again.

Make sure you've run...

SET NAMES utf8;

... if you downloaded the data in utf8 (or SET NAMES latin1 or whatever encoding you're using) before uploading the data again.


It's possible, though unlikely, that there's a more complex configuration of what encoding is really used in the file, what encoding the database is supposed to use, and what encoding the connection uses.  Most likely, though, you just have to be sure to SET NAMES correctly before uploading the dumped data.
0
 
ExfoWebAuthor Commented:
I use mysqladmin to backup and restore data between my server, I never used a command line mysqldump ...

but why the data looks great in sql file (backup from one server) , looks good in query browser but not in my application ?  
0
 
VoteyDiscipleCommented:
Well, let's fall back on the basic questions:

1.  What character encoding does MySQL use for the tables (i.e. what shows up from a SHOW CREATE TABLE?)
2.  What character encoding did you use to download the data?
3.  (Presumably the same answer as to (2)): In what character encoding are the data stored in the file?  Probably the only way to really answer that is to look in a hex editor.
4.  What character encoding did you use to upload the data?
5.  What character encoding is actually used in the table (again, selecting HEX(column_name) or HEX(SUBSTRING(column_name, ..., 1)) for a particular character would be the only way to find that out).  This should be the same as the answer to (1).
6.  What character encoding is the query browser set to use?
7.  What character encoding does your application use (i.e. its SET NAMES command)?


If everything all the way through is consistent then there'd be no problem, so somewhere something's set wrong.  It's just finding which thing or combination of things it is that's the trick.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
ExfoWebAuthor Commented:
1.  CHARSET=latin1
2.  I can't set up this by using mysqladmin
3.  CHARSET=latin1
4.  I try latin 1 and utf8 neither works...
5.  ?
6.  I can't set up this by using mysqladmin
7.  I don't specify this command in my asp application (I did't have to do with mysql 4.1 )...

maybe I Should  re-install my developpment server and restore data from prod but only in command line ? there is an easy way to do this with command line ? maybe (by miracle) there is a script to execute ...  ;-)

very newbie ...
0
 
VoteyDiscipleCommented:
If you're not using SET NAMES in your application, then the server will assume a default character set.  Adding a SET NAMES latin1 just after you've connected is the easiest and safest fix for that.  That by itself wouldn't be inherently problematic, though, unless the client is getting UTF-8 data, say, and interpreting it as latin1.


Let's try this, since I think this is the most telling piece of information.  Find a é somewhere in the database.  Let's say it's the 18th character of field1.  Run...

SELECT HEX(SUBSTRING(field1, 17, 5))
FROM the_table
WHERE ...
;

This will show the actual encoding used.  It should, for latin1, be E9.
0
 
ExfoWebAuthor Commented:
I have a C3 for é  .... what does it mean ?

Can I put the set names = latin1; juste before my select query in my code?
0
 
VoteyDiscipleCommented:
You'd put "SET NAMES latin1" immediately after connecting to the database.  It's a query you'd run only once per session.


If you're seeing C3A9 for é, that's the utf8 encoding, not latin1.  So you've got utf8 data in a table that's declared to be latin1, and it's no wonder things are getting confused.  (-:

Somewhere in this backup, which I'm guessing is a SQL script much like mysqldump produces, there should be a SET NAMES latin1 line; try changing it to SET NAMES utf8;  and running the script again to reimport the data.
0
 
ExfoWebAuthor Commented:
Hello !

I tryied to change the set names but any good results.

 I have no more time to spend on this issue and look to transfert my database on MSSQL. I'm not a gouru on mysql and find that it's not enough easy to work with.

So I will close this question but I would like to thank you for your quick answers and time you spent to help me!
christine
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now