Dealing with MySQL NativeError Code 1366 and SQLState HY000 in coldfusion

Question

Please find the initial few lines of stack trace of the error below: (The column sl is what I have used in the stored procedure code)

Incorrect string value: '\xC2\x80\xC2\x99t ...' for column 'sl' at row 1 at 
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946):946 at 
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985):2985 at 
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631):1631 at 
com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723):1723 at 
com.mysql.jdbc.Connection.execSQL(Connection.java:3283):3283 at 

and so on..

The stored procedure code, CODE II which is getting called from CODE I mentioned below:

CODE II

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`mystuff`@`%` PROCEDURE `usp_sg_ins_fv3`(IN `na` BIGINT, IN `sb` VARCHAR(200), IN `sc` INT, IN `se` INT, IN `sf` VARCHAR(200), IN `sg` VARCHAR(200), IN `sh` VARCHAR(500), IN `si` VARCHAR(200), IN `dj` DATETIME, IN `sk` VARCHAR(200), IN `sl` VARCHAR(500), IN `sm` VARCHAR(200)

, IN `sn` VARCHAR(50))
BEGIN  
    INSERT INTO sgfDatav3
                (
                 a_bi, 
                 b_vc,
                 c_int,
                 e_int,
                 f_vc, 
                 g_vc, 
                 h_vc,
                 i_vc,
                 j_dt,
                 k_vc,
                 l_vc,
                 m_vc,
                 n,
                 myTimestamp_dt
               )
            VALUES
                (
                na,
                sb,
                sc,
                se,
                sf,
                sg,
                sh,
                si,
                dj,
                sk,
                sl,
                sm,
                sn,
                CURRENT_TIMESTAMP()
                );
END

CODE I: Code which I am using to get the data posted by Sendgrid

<cftry>              
 <cfset incomingData = toString(getHttpRequestData().content) />
 <cfset djs = DeserializeJSON(incomingData)/>

<cfset a = "0">
<cfset b = "">
<cfset c = "0">
<cfset d = "0">
<cfset e = "">
<cfset f = "">
<cfset g = "">
<cfset h = "">
<cfset i = "">
<cfset k = "#NOW()#">
<cfset l = "">
<cfset m = "">
<cfset n = "">

<cfoutput>
<cfloop from="1" to="#arraylen(djs)#" index="i">

    <cfset a = "0">
    <cfset b = "">
    <cfset c = "0">
    <cfset d = "0">
    <cfset e = "">
    <cfset f = "">
    <cfset g = "">
    <cfset h = "">
    <cfset i = "">
    <cfset k = "#NOW()#">
    <cfset l = "">
    <cfset m = "">
    <cfset n = "">

    <cfif StructKeyExists(djs[i],'p')>
        <cfset a       = djs[i].p />
    </cfif>

    <cfif StructKeyExists(djs[i],'q')>
        <cfset b           = djs[i].q />
    </cfif>
    <cfif StructKeyExists(djs[i],'r')>
        <cfset c       = djs[i].r />
    </cfif>
    <cfif StructKeyExists(djs[i],'s')>
        <cfset d       = djs[i].s />
    </cfif>     
    <cfif StructKeyExists(djs[i],'t')>
        <cfset e       = djs[i].t />
    </cfif>
   <cfif StructKeyExists(djs[i],'u')>
        <cfset f           = djs[i].u />
    </cfif>
    <cfif StructKeyExists(djs[i],'v')>
        <cfset g       = djs[i].v />
    </cfif>

    <cfif StructKeyExists(djs[i],'w')>
        {                                                                         
        <cfset i  = djs[i].w />
        <cfset k  = dateAdd("s", i, createDateTime(1970, 1, 1, 0, 0, 0))/>
        }
    </cfif>
    <cfif StructKeyExists(djs[i],'x')>
        <cfset l         = djs[i].x />
    </cfif>
    <cfif StructKeyExists(djs[i],'y')>
        <cfset m       = djs[i].y />
    </cfif>
    <cfif StructKeyExists(djs[i],'z')>
        <cfset n       = djs[i].z /> 
    </cfif>

    <cfstoredproc procedure="sp1" datasource="db1">
        <cfprocparam cfsqltype="cf_sql_bigint" value="#a#">
        <cfprocparam cfsqltype="cf_sql_varchar" value="#left(b,199)#">
        <cfprocparam cfsqltype="cf_sql_integer" value="#c#">
        <cfprocparam cfsqltype="cf_sql_integer" value="#d#">
        <cfprocparam cfsqltype="cf_sql_varchar" value="#left(e,199)#">
        <cfprocparam cfsqltype="cf_sql_varchar" value="#left(f,199)#">
        <cfprocparam cfsqltype="cf_sql_varchar" value="#left(g,499)#">
        <cfprocparam cfsqltype="cf_sql_varchar" value="#left(h,199)#">
        <cfprocparam cfsqltype="cf_sql_timestamp" value="#k#">
        <cfprocparam cfsqltype="cf_sql_varchar" value="#left(l,199)#">
        <cfprocparam cfsqltype="cf_sql_varchar" value="#LEFT(m,499)#">
        <cfprocparam cfsqltype="cf_sql_varchar" value="#left(n,99)#">
        <cfprocparam cfsqltype="cf_sql_varchar" value="XX.XX.X.XX">
    </cfstoredproc>
</cfloop>

</cfoutput>

</cftry>

Upon researching I found people talking about some UTF8 format. But if that is the actual problem, then why do I get the above error for only few cases and not every time? Please advise.

Answer 1

UPDATE: As of MySQL 5.5.3, there is also UTF8mb4 which is often recommended over UTF8.

---

It does sound like it is related to unicode. What is the charset and collation for that column? See the INFORMATION_SCHEMA.COLUMNS view.

As far as the error being sporadic, I imagine it would depend on exactly what characters you are inserting (which probably change). Going strictly off the error message description, ie er_truncated_wrong_value_for_field, it sounds like either the input contains invalid characters or the interpretation of the input string is being truncated. Again, it sounds like some sort of charset issue.

Update:

Assuming you are receiving a valid UTF8 string, it does seem to be a charset problem. Though my test database defaults to charset UTF8, I was able to reproduce that error by creating a small table that used two different charsets: LATIN1 and UTF8. Then inserting a small UTF8 string into the both columns. The insert into the UTF8 column worked fine, but the LATIN1 column failed with the error:

Incorrect string value: '/xD0/x9D/xD0/xB0 /xD0...' for column 'ColDefaultCharset' at row 1 ...

Try changing the charset to UTF8 and I think the INSERT will work correctly:

  ALTER TABLE YourTable MODIFY YourColumnName VARCHAR(500) CHARACTER SET utf8;

Table:

CREATE TABLE  TestTable (
  ID INTEGER NOT NULL AUTO_INCREMENT
  , ColDefaultCharset VARCHAR(100) CHARSET LATIN1 NULL
  , ColUTF8Charset VARCHAR(100) CHARSET UTF8 NULL
  , PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=LATIN1;

Sample text:

На берегу пустынных волн
Стоял он, дум великих полн,

Procedures:

CREATE PROCEDURE `testWithUTF8`
(
  IN `sl` VARCHAR(500)
)
BEGIN
    INSERT INTO testTable (ColUTF8Charset)
    VALUES ( sl );

END


CREATE PROCEDURE `testWithLatin1`
(
  IN `sl` VARCHAR(500)
)
BEGIN
    INSERT INTO testTable (ColDefaultCharset)
    VALUES ( sl );

END

Code:

<cfprocessingdirective pageEncoding="UTF8">
<cfsavecontent variable="text">
На берегу пустынных волн
Стоял он, дум великих полн,
</cfsavecontent>

<!--- Note: For CF10, use cf_sql_nvarchar --->
<cfstoredproc procedure="testWithUTF8" datasource="MySQL" result="procResult">
    <cfprocparam cfsqltype="cf_sql_varchar" value="#text#">
</cfstoredproc>
<cfdump var="#procResult#">

<cfstoredproc procedure="testWithLatin1" datasource="MySQL" result="procResult">
    <cfprocparam cfsqltype="cf_sql_varchar" value="#text#">
</cfstoredproc>
<cfdump var="#procResult#">

DSN Settings:

• Driver: MySQL 5
• Advanced Settings > Connection String: characterEncoding=UTF8