Solved

character issue with mysql 5

Posted on 2006-11-28
8
234 Views
Last Modified: 2011-10-03
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
Comment
Question by:ExfoWeb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 19

Expert Comment

by:VoteyDisciple
ID: 18030558
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
 

Author Comment

by:ExfoWeb
ID: 18030681
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
 
LVL 19

Expert Comment

by:VoteyDisciple
ID: 18031077
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:ExfoWeb
ID: 18031289
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
 
LVL 19

Expert Comment

by:VoteyDisciple
ID: 18031413
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
 

Author Comment

by:ExfoWeb
ID: 18031504
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
 
LVL 19

Accepted Solution

by:
VoteyDisciple earned 500 total points
ID: 18033168
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
 

Author Comment

by:ExfoWeb
ID: 18037171
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question