character encoding in DSRequest/DSResponse

[csillag]

Hi,

My data-bound components create a DSRequest with UTF8 characters in them. They are sent to the server. If I redirect them via DMI, I can see that the characters arrive to the server OK.

After I call the execute() method on the request, in the resulting DSResponse object, some (accented) characters are messed up.

The DSRequest belongs to a DataSource which is using the builtin SQL backend, with MySQL.

All my SQL tables are defined as UTF8.

The SQL command written by SQLDriver seems to contain the right characters (according to the eclipse console), but what is written in the DB is not correct.

It's like the sender and the receiver of the SQL command (ie. SQLDriver and mysql) had a different idea about the encoding of the SQL command string.

The interesting thing is that when I am only viewing the data (either on the client or on the server), all characters are transferred correctly. This problem only occurs if I try to save something.

What could be wrong?

What can be the reason of the asymmetry? (Reading is fine, writing is not.)

(I can observe all this on the server side, so no client/server communication problem here.)

[Isomorphic]

We've never seen a problem of this kind, but the typical problems are:

1. make sure the charset setting for your JVM is UTF8

2. make sure your JDBC driver is likewise set for UTF8

[csillag]

The configuration of the DB in the server.properties:

sql.SzolorgDB.database.type: mysql
sql.SzolorgDB.interface.type: dataSource
sql.SzolorgDB.database.ansiMode: false
sql.SzolorgDB.driver.useUnicode: true
sql.SzolorgDB.pool.enabled: true
sql.SzolorgDB.driver: com.mysql.jdbc.jdbc2.optional.MysqlDataSource
sql.SzolorgDB.driver.context: _container_
sql.SzolorgDB.driver.driverType: thin
sql.SzolorgDB.driver.networkProtocol: tcp

This seems to be correct, right?
Is there a way to actually query the settings of the JDBC driver, on the fly?

[Isomorphic]

Those seem fine, but again we haven't seen this problem.

Did you check your JVM charset setting?

Did you check the MySQL website for character encoding issues?

[csillag]

Having checked the MYSQL reference, it seems that there are a whole bunch of character encoding settings for all connections, namely:

character_set_client, character_set_connection and character_set_results.

See here: http://dev.mysql.com/doc/refman/5.1/...onnection.html

Based on the behavior I see, my guess would be that either character_set_client or character_set_connection is wrong in my case.

To check this, I would like to execute

show variables like '%_set_%';

on my connection - but how do I do that?

Is there an API to pass SQL strings directly to the SQL server? (The same question also holds for the JDBC: how can I access the JDBC connection used internally by the SQL backend?)

[Isomorphic]

So again, did you check your JVM charset setting? Bear in mind, special configuration has not been necessary to make this work in the past.

[csillag]

I would like to solve this.
My JVM settings seem to be right.

I have no idea why this happens (in about some of my installations), buy I have a pretty good idea which MySQL parameters are wrong on my connection.

I just need some way to execute some direct SQL code on the SQL connections the SQL backend opens to to the DB.

Can I do that, somehow?

[Isomorphic]

Sure, use <customSQL>.

[csillag]

And how can I ensure that a given piece of code is executed on each connection in the connection pool?

[Isomorphic]

A given piece of SQL code? Well, you can add code in DataSource.execute() of a custom DataSource to always create a new DSRequest() that calls an operationBinding that has <customSQL> in it.

However, this is not particularly efficient, and as noted before, it's not normal that you would need to do this. When you have time, you should look into the root cause and get rid of this hack.

[csillag]

A given piece of SQL code? Well, you can add code in DataSource.execute() of a custom DataSource to always create a new DSRequest() that calls an operationBinding that has <customSQL> in it.

You mean doing this in a subclass of SQLDataSource?

However, this is not particularly efficient,

Indeed. Is there any way to do this when the connection is first created, instead of every time it is used?

and as noted before, it's not normal that you would need to do this. When you have time, you should look into the root cause and get rid of this hack.

I plan to do exactly that, when I have time. Right now, I am about two months over the original schedule (a long story), so I have to postpone the investigation for now.

[Isomorphic]

If it's a temporary workaround for a problem that isn't happening for anyone else, please don't ask to have it optimized - an extra SQL statement per call should be fine.

[csillag]

[...] you should look into the root cause and get rid of this hack.

Here is what I know until so far:

As I suspected, the culprit are the wrong character settings for _for the JDBC connection_ between the JVM and the DB.

I have found two workarounds for this, but none of them is really satisfactory.

Approach 1:

- I set

default-character-set =	utf8

in /etc/mysql/my.conf. This is the central configuration file of the MySQL server, and controls all default charset settings. With this setting, everything works OK.

However, since this setting is global, this approach requires root access to the machine, which I do not always have. Therefore, it would be much better if I could set these parameters on the Database or the Connection level, instead of on the database manager level. Unfortunately, configuring the individual database for full utf8 is not enough.

Approach 2:

Setting the charset as part of the JDBC connection URL also works. However, I could not figure out how to specify the JDBC connection string when using the normal SQL backend, so I had to change my server.properties from this:

sql.OrgTelDB.database.type: mysql
sql.OrgTelDB.interface.type: dataSource
sql.OrgTelDB.database.ansiMode: false
sql.OrgTelDB.driver.useUnicode: true
sql.OrgTelDB.pool.enabled: true
sql.OrgTelDB.driver: com.mysql.jdbc.jdbc2.optional.MysqlDataSource
sql.OrgTelDB.driver.context: _container_
sql.OrgTelDB.driver.driverType: thin
sql.OrgTelDB.driver.networkProtocol: tcp

sql.OrgTelDB.driver.serverName: <protected>
sql.OrgTelDB.driver.portNumber: 3306
sql.OrgTelDB.driver.databaseName: <protected>
sql.OrgTelDB.driver.user: <protected>
sql.OrgTelDB.driver.password: <protected>

to this:

sql.OrgTelDB.database.type: mysql
sql.OrgTelDB.interface.type: driverManager
sql.OrgTelDB.database.ansiMode: false
sql.OrgTelDB.driver.useUnicode: true
sql.OrgTelDB.pool.enabled: true
sql.OrgTelDB.driver: org.gjt.mm.mysql.Driver
sql.OrgTelDB.driver.context: _container_
sql.OrgTelDB.driver.driverType: thin
sql.OrgTelDB.driver.networkProtocol: tcp
sql.OrgTelDB.driver.databaseName: orgtel
sql.OrgTelDB.driver.url: jdbc:mysql://<protected>:3306/<protected>?characterEncoding=utf8
sql.OrgTelDB.interface.credentialsInURL: false
sql.OrgTelDB.driverName: mysql
sql.OrgTelDB.driver.user: <protected>
sql.OrgTelDB.driver.password: <protected>

After inserting "characterEncoding=utf8" to the JDBC url, everything works perfectly.

The problem is that since obtaining the sql connection via "driver manager" instead of the usual "DataSource", the performance is _much_ worse.

* * *

So, the question is, how can I do this (inserting the wanted encoding to the JDBC url) with the normal backend?
Or how can I influence the default charset chosen for the JDBC connection by the normal backend
(besides changing the global default of the mysql server, which I might not have the permission for)?

Thank you for your help!

[Isomorphic]

What aspect of the performance is "much worse"? There's no reason there should be a discernible difference.