Character set problem with MySQL 5.0 and Connector/J

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.

Who is Participating?

Improve company productivity with a Business Account.Sign Up

Raynard7Connect With a Mentor Commented:
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?

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
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?
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

If you try to insert an accented character into your table does it convert it to the ? or does it save correctly?
fcanepaAuthor Commented:
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.
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

    Your Character Here

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.
fcanepaAuthor Commented:
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...

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.
fcanepaAuthor Commented:
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.
fcanepaAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.