Solved

character issue with mysql 5

Posted on 2006-11-28
8
232 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

761 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