Character encoding Problem

Report · Aug 09, 2012

I have the following setup:

Existing Application on CF7 with a real old MySQL Database. Everything works fine. Using ISO-8859-1 about everywhere and latin1 on the Database.

Now I want to migrate this to CF10 with a MySQL 5.1.52 Server

Everything works fine except for, when I try to change data, specifically I'm trying to do the following query:

UPDATE db_dj_user set gestaeti='Webprogrammierung oder ähnliches äöü' WHERE id=10235

and it failes with the Error

Incorrect string value: '/xE4hnlic...' for column 'gestaeti' at row 1

Looking at the page, it is encoded with ISO-8859-1

Looking at the HTML-Code it says <META http-equiv=Content-Type content="text/html; charset=ISO-8859-1">

Looking at the Field in the Database it says gestaeti text latin1_swedish_ci

Looking at the Datasource it has a connection String of characterEncoding=latin1

Looking at the ISO-8859-1-Table E4 is the correct Character for the german Umlaut ä

In Application.cfm URL and FORM Encoding are set to ISO-8859-1 cfcontent specifies ISO-8859-1 and cfprocessingdirective is set to ISO8859-1 (that last in both, Application.cfm as well as in the template where the query actually is in)

Reading umlauts out of this table works perfectly fine.

Updating the data with phpmyadmin which runs on the same apache works fine.

Any idea what I am missing, or did I hit a bug ?

Thanks for your ideas

Frank

Report · Aug 09, 2012

Found the following Workaround:

cd /opt/coldfusion10/cfusion/lib

mv mysql-connector-java-commercial-5.1.17-bin.jar mysql-connector-java-commercial-5.1.17-bin.jar.old

Download mysql-connector-java-5.0.8-bin.jar from mysql and save it in that directory.

Make a new custom datasource with URL jdbc:mysql://localhost:3306/prod?characterEncoding=latin1 Driver Class com.mysql.jdbc.Driver and Driver name mysql

Restart CF

Now it works. Error in the commercial connector or some problem with the connection-string-to-jdbc-url-mapping ?

-frank