在ColdFusion中处理MySQL NativeError代码1366和SQLState HY000 [英] Dealing with MySQL NativeError Code 1366 and SQLState HY000 in coldfusion
问题描述
请找到以下错误的初始几行堆栈跟踪：
（ sl 是我在存储过程代码中使用的） / p>
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..
正在从 CODE I 调用的存储过程代码 CODE II ：
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：
我使用来获取Sendgrid发布的数据
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>
在研究过程中，我发现有人在谈论一些UTF8格式。但如果这是实际的问题，那么为什么我得到上面的
错误只有少数情况下，而不是每次？请指导。
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.
推荐答案
UPDATE：从MySQL 5.5.3开始，还有 UTF8mb4，通常建议采用UTF8 。
UPDATE: As of MySQL 5.5.3, there is also UTF8mb4 which is often recommended over UTF8.
听起来像是和unicode有关。该列的字符集和归类是什么？请参见 INFORMATION_SCHEMA.COLUMNS 视图。
It does sound like it is related to unicode. What is the charset and collation for that column? See the INFORMATION_SCHEMA.COLUMNS view.
至于错误是零星的，我想这将取决于你插入什么字符（这可能改变）。严格遵守错误消息说明 ，即er_truncated_wrong_value_for_field，它听起来像是输入包含无效字符或输入字符串的解释正在被截断。再次，它听起来像某种字符集问题。
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.
更新：
假设您收到有效的UTF8字符串，似乎是一个charset问题。虽然我的测试数据库默认为字符集UTF8，我能够通过创建一个小表使用两个不同的字符集：LATIN1和UTF8重现该错误。然后在这两列中插入一个小的 UTF8字符串。插入到UTF8列很好，但LATIN1列失败，并显示错误：
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:
字符串值不正确：'/ xD0 / x9D / xD0 / xB0 / xD0 ...'for column
'ColDefaultCharset'in row 1 ...
Incorrect string value: '/xD0/x9D/xD0/xB0 /xD0...' for column 'ColDefaultCharset' at row 1 ...
尝试更改charset to UTF8 and我认为INSERT将正常工作：
Try changing the charset to UTF8 and I think the INSERT will work correctly:
ALTER TABLE YourTable MODIFY YourColumnName VARCHAR(500) CHARACTER SET utf8;
表格：
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;
示例文字：
На берегу пустынных волн
Стоял он, дум великих полн,
程序：
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设置：
- 驱动程序：MySQL 5
- 高级设置>连接字符串：
characterEncoding = UTF8li>
- Driver: MySQL 5
- Advanced Settings > Connection String:
characterEncoding=UTF8
这篇关于在ColdFusion中处理MySQL NativeError代码1366和SQLState HY000的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
