Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

character issue with mysql 5

Posted on 2006-11-28
8
Medium Priority
?
235 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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 

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 1500 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

715 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