Solved

character issue with mysql 5

Posted on 2006-11-28
8
229 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
  • 4
  • 4
8 Comments
 
LVL 19

Expert Comment

by:VoteyDisciple
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:ExfoWeb
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 19

Expert Comment

by:VoteyDisciple
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

728 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now