Railo -> MySQL utf-8 issue.

[Daniel Rosewarne]

Hi,

I'm getting a problem trying to add content to a MySQL database that
contains the UTF-8 character "→". If I add directly to MySQL, all is
fine and so it's not an issue with the database structure, however
when trying via Railo I get the error "Incorrect string value:
'\xE2\x86\x92' for column '_name' at row 1".

For the record, I'm using Railo 3.3.1.000 on Ubuntu with MySQL
5.1.41-3ubuntu12.10.

I can see that a similar issue has been fixed in previous updates to
Railo, so am I missing a trick here somewhere?

Thanks,
Dan

[Sean Daniels]

What driver are you using for your datasource? Is it set up to use UTF8 for the connection?

[Daniel Rosewarne]

Using the built-in MySQL driver with "Use unicode" set to true and
"Charset" set to UTF-8.

[Sean Daniels]

On Feb 22, 2012, at 10:25 AM, Daniel Rosewarne wrote:

> Using the built-in MySQL driver with "Use unicode" set to true and
> "Charset" set to UTF-8.

Is the character coming in via a form field or is it coded into one of your templates? Check that you have the following:

1. Railo Admin -> Settings -> Charset - all should be UTF-8
2. You are saving your code templates in UTF-8 (this would be particularly relevant if the character is hardcoded into your CFML somewhere).
3. You could also try adding <cfprocessingdirective pageEncoding="utf-8"> to the CFML template in question.

These encoding things drive me nuts!

[Michael Offner]

be careful simply setting everything to UTF-8, most code/text editors do not use UTF-8. the default value for the template/resource charset is the OS default charset and this for a good reason. most text/code editors use this charset.

UTF-8 is not the "universal charset" that solve everything.

But Sean is right.

First question is, from where comes your data ...

if from form/url:

make sure the data are send in udf-8, use function "SetEncoding" to make sure of it.

if from cfml template:

make sure if template is stored in UTF-8 and if this is the only template stored in UTF-8, 

add <cfprocessingdirective pageEncoding="utf-8">, only if you are a 100% sure all your templates are stored in UTF-8, you can change the template charset in admin to UTF-8, but i have never seen a environment where this was the case.

if you read from file:

make sure the template is is stored in UTF-8, define charset="UTF-8" when opening the file.

/micha

2012/2/22 Sean Daniels <daniel...@gmail.com>

[Daniel Rosewarne]

Thanks for the suggestions.

FYI, this particular issue is when reading in a text file that has
been uploaded. The <CFFILE> tag has the charset attribute set to UTF-8
and if I dump to the screen the contents then I can see that it has
been read in correctly. If I copy and paste the content to my SQL
client, it's added correctly to the database. The only issue is when
trying to add to the database from Railo, which uses the following
(where the unicode data is in arrItem._name):

<cfquery name="qryStep" datasource="#variables.dsn#">
INSERT INTO step ( test_id
, task_id
, _name
, _value
, active )
VALUES ( <cfqueryparam cfsqltype="cf_sql_integer"
value="#intTestID#">
, <cfqueryparam cfsqltype="cf_sql_integer"
value="#arrItem.task_id#">
, <cfqueryparam cfsqltype="cf_sql_varchar"
value="#arrItem._name#">
, <cfqueryparam cfsqltype="cf_sql_varchar"
value="#arrItem._value#">
, <cfqueryparam cfsqltype="cf_sql_integer" value="1"> );
</cfquery>

It feels like it's a driver issue between Railo & MySQL, but I got the
impression that that had been fixed fairly recently.

Has anybody got any other suggestions?

Thanks,
Dan

On Feb 22, 8:15 pm, Michael Offner <mich...@getrailo.com> wrote:
> be careful simply setting everything to UTF-8, most code/text editors do
> not use UTF-8. the default value for the template/resource charset is the
> OS default charset and this for a good reason. most text/code editors use
> this charset.
> UTF-8 is not the "universal charset" that solve everything.
>
> But Sean is right.
> First question is, from where comes your data ...
>
> if from form/url:
> make sure the data are send in udf-8, use function "SetEncoding" to make
> sure of it.
>
> if from cfml template:
> make sure if template is stored in UTF-8 and if this is the only template
> stored in UTF-8,
> add <cfprocessingdirective pageEncoding="utf-8">, only if you are a 100%
> sure all your templates are stored in UTF-8, you can change the template
> charset in admin to UTF-8, but i have never seen a environment where this
> was the case.
>
> if you read from file:
> make sure the template is is stored in UTF-8, define charset="UTF-8" when
> opening the file.
>
> /micha
>

> 2012/2/22 Sean Daniels <daniels.s...@gmail.com>

[Jean Moniatte]

Hello,

Just tested, the following query runs fine here with both ACF and Railo:

<cfquery name ="testUTF8" datasource="test">

insert into textUTF8

(

testName

)

values

(

'→'

)

</cfquery>

Can you try it on your setup? Like Sean said, make sure that your .cfm file is UTF8 encoded.

Thanks,
Jean

--
Jean Moniatte
UGAL
je...@ugal.com
www.ugal.com
--

[Daniel Rosewarne]

Hi,

Thanks for that. Intriguingly that doesn't work for me.

I tried three separate things, firstly the existing query in
isolation, secondly the existing query without queryparams and lastly
a brand new table with your query, based on the following schema:

CREATE TABLE `textUTF8` (
  `testName` varchar(255) CHARACTER SET latin1 NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Each time I get the same error.

DatabaseName: MySQL
DatabaseVersion: 5.1.41-3ubuntu12.10
DriverName: MySQL-AB JDBC Driver
DriverVersion: mysql-connector-java-5.0.6 ( $Date: 2007-03-09 22:13:57
+0100 (Fri, 09 Mar 2007) $, $Revision: 6341 $ )
SQL: insert into textUTF8 ( testName ) values ( '���' )
Message: Incorrect string value: '\xEF\xBF\xBD\xEF\xBF\xBD...' for
column 'testName' at row 1

FYI, the coldfusion template I've used is in UTF-8, checked by using
the following:

file -i utf8.cfm
utf8.cfm: text/plain; charset=utf-8

I'm wondering if perhaps I have an old driver for MySQL, but presumed
they'd be updated as part of the Railo update process?

Thanks,
Dan

On Feb 23, 11:54 am, Jean Moniatte <j...@ugal.com> wrote:
> Hello,
>
> Just tested, the following query runs fine here with both ACF and Railo:
>
> <cfquery name ="testUTF8" datasource="test">
> insert into textUTF8
> (
> testName
> )
> values
> (
> '→'
> )
> </cfquery>
>
> Can you try it on your setup? Like Sean said, make sure that your .cfm file
> is UTF8 encoded.
>
> Thanks,
> Jean
>
> --
> Jean Moniatte
> UGAL

> j...@ugal.comwww.ugal.com

> --
>
> On Thu, Feb 23, 2012 at 12:42, Daniel Rosewarne

> <danielrosewar...@gmail.com>wrote:

[Jean Moniatte]

Hello,

You have: `testName` varchar(255) CHARACTER SET latin1 NOT NULL

Meaning that the testName column is latin1. Can you try the same after making sure that the column itself is UTF8:

CREATE TABLE `textUTF8` (

  `testName` varchar(255) CHARACTER SET utf8 NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Thanks,
Jean

--
Jean Moniatte
UGAL

je...@ugal.com
www.ugal.com
--

[Daniel Rosewarne]

Ah, thanks I'd totally missed that!

A combination of the column name being the wrong character set and the
template requiring <cfprocessingdirective pageEncoding="utf-8"> was
causing the problem.

Thanks for your help everyone!

Dan

On Feb 23, 3:02 pm, Jean Moniatte <j...@ugal.com> wrote:
> Hello,
>
> You have: `testName` varchar(255) CHARACTER SET latin1 NOT NULL
>
> Meaning that the testName column is latin1. Can you try the same after
> making sure that the column itself is UTF8:
>
> CREATE TABLE `textUTF8` (
>   `testName` varchar(255) CHARACTER SET utf8 NOT NULL
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
>
> Thanks,
> Jean
>
> --
> Jean Moniatte
> UGAL

> j...@ugal.comwww.ugal.com

> --
>
> On Thu, Feb 23, 2012 at 15:31, Daniel Rosewarne

> <danielrosewar...@gmail.com>wrote: