Solved

Character set problem with MySQL 5.0 and Connector/J

Posted on 2006-10-29
10
323 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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
 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

776 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