Solved

Character set problem with MySQL 5.0 and Connector/J

Posted on 2006-10-29
10
314 Views
Last Modified: 2008-03-10
I have ported a Java web application from an old server with MySQL 4.0 to a new server with MySQL 5.0 and Connector/J 5 JDBC driver.
After doing so, I notice that a symbol which I think is an acute accent, which here in Italy many people use in spite of the apostrophe, is rendered by the application as a question mark. Other latin1 symbols, like the accented letters, are rendered properly. I have checked that the tables have been imported properly through the mysql command line client, and they have, the symbols seem correct in the db.
I wonder if this is a problem in the Connector/J or MySQL.
Can anybody give me some suggestions? I hope I have given all the relevant information. If not, please ask me.

Thanks,
Fabio
0
Comment
Question by:fcanepa
  • 6
  • 4
10 Comments
 
LVL 35

Expert Comment

by:Raynard7
ID: 17828173
Hi,

are you using the same character set that you were using on the other server?
how did you migrate - did you run an sql dump and then copy the files accross - alternativley do you have the ability to do so?

the problem looks to me like you are using incompatible character sets - if this is not the problem I would suspect but doubt it would be the JDBC driver.

Generally I would advise using a dump file to migrate data rather than emulate it through JDBC or ODBC because you are more likley to have identical setups if you are recreating the tables using the exact same create statements.

If you have access to the server using jdbc I'm guessing that you could also mysql dump to transfer the tables - if not i'll advise another approach
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17828186
Also - is the problem with the data in the tables themselves or just in the java application that you are trying to view it through?
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17828187
If you try to insert an accented character into your table does it convert it to the ? or does it save correctly?
0
 

Author Comment

by:fcanepa
ID: 17828212
Hi,
to import the db I have used mysqldump in the old server, and then I have source'd the data in the new db. If I select the data using the 'mysql' client from the imported db, I see the correct symbols.
So it's a very weird problem: I would say that the problem is in the java application, but the same worked fine in the old server... For this I had doubts about the Connector/J.
Another thinkg that has changed is the Apache version, from 1.3 to 2. I had to add the line AddDefaultCharset ISO-8859-1 in the virtual host, since if I'm not wrong apache 2 defaulted to UTF-8.
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17828272
If you can see the correct character through the client then the database is not the problem.

So it is either jdbc or it is the web server - what happens when you get apache 2 to just display your character with this in a page - ie

<html>
<head>
    <title>Test</title>
</head>
<body>
    Your Character Here
</body>
</html>

If this displays correctly it is liable to be jdbc or your applicatoin.  I'm convinced that if you can see the data in the database it is something else not interpreting it correctly.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:fcanepa
ID: 17843046
When I put the symbol in a static html file it is displayed correctly. I have verified that the character which gives me problems has the hex code 92.
I have thought from start that the problem might be related not with MySQL itselt, but in the use of the JDBC driver. Perhaps some options... It could be my application, but with the old server it worked, and it doesn't do very strange things with the strings fetched from the db...
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17845622
Hi,

There are no documented errors with displaying this character using JDBC.  I'm not sure if you have access to the code but it may be worth writing an application that tries to display this character (only a couple of lines) and see if that is the problem in retreiving the data.  I now do not think the problem is with apache or mysql so the options are jdbc or your application. You would have to look then at your current application and see what it is doing when trying to display.

Short of that I'm out of ideas - but I'd try testing jdbc first because if that works and displays then it is almost definitley going to be something that you do with your application.
0
 

Author Comment

by:fcanepa
ID: 17856871
I have now logged the data with the symbol that gives me problems immediately after the jdbc driver fetches it... Where there should be the symbol, I read this sequnce of three characters '’'. It seems to me like UTF-8 encoding...
That's very strange: this didn't happen in the old server. I have always treated the data as ISO-8859-1. If I set the charset to UTF-8 the problem worsen: also other characters become unreadable. It seems that only the 0x92 symbol is for some reason encoded in this strange way.
0
 
LVL 35

Accepted Solution

by:
Raynard7 earned 500 total points
ID: 17856938
I agree that this looks like UTF-8 - what character set were you using previously?

If this was me I would just replace that '’' combination when outputting data with your character - and log this as an error on the mysql.bugs page.

Unfortunatley I think that this is a JDBC issue and not a database one.  Changing the encoding on the dabase probably will not make a difference (except for worse) as it seems to be the way that the driver is interpreting the data at the other end, and as such changing the encoding may just make the driver interpret other characters badly.

Have you tried installing an older version of JDBC and see if this resolves your problem?
0
 

Author Comment

by:fcanepa
ID: 17857023
I assumed everything was latin1. I had thought of the text replacement hack myself, but I'm not very happy of this...
I had the same problem with an older driver. The problems are with the new server with mysql 5.0. Same driver, same java application
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
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…

744 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

13 Experts available now in Live!

Get 1:1 Help Now