问题描述
请找到以下错误的初始几行堆栈跟踪：
（ 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 aboveerror for only few cases and not every time? Please advise.
推荐答案
UPDATE：从MySQL 5.5.3开始，还有。
UPDATE: As of MySQL 5.5.3, there is also UTF8mb4 which is often recommended over UTF8.
听起来像是和unicode有关。该列的字符集和归类是什么？请参见视图。
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列很好，但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:
尝试更改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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！