mysql bug 之 “on duplicate key update”

首先发现一个异常，徘徊好久不能解决！error log如下：

 

Caused by: java.sql.SQLException: Parameter index out of range (35 > number of parameters, which is 34).
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
	at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3729)
	at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3713)
	at com.mysql.jdbc.PreparedStatement.setBytesNoEscapeNoQuotes(PreparedStatement.java:3463)
	at com.mysql.jdbc.PreparedStatement.setOneBatchedParameterSet(PreparedStatement.java:4264)
	at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1766)
	at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1440)
	at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
	at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
	at com.ibatis.sqlmap.engine.execution.SqlExecutor$Batch.executeBatch(SqlExecutor.java:611)
	at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeBatch(SqlExecutor.java:118)
	at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.executeBatch(SqlMapExecutorDelegate.java:759)
	at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.executeBatch(SqlMapSessionImpl.java:184)

 

mysql jdbc jar 版本：5.1.18

 

 

初步定位：sql语句问题！

 

<insert id="insert" parameterClass="***">
	insert into ****** ( 
	gmt_created,
	nick
	)
	values (
	#gmtCreated#,
	#nick#
	) on duplicate
	 key update 
		gmt_modified=#gmtModified#,
		nick=#nick#
</insert>

 

 

最终定位：

 

 on duplicate
	 key update 

应该为：

on duplicate key update 

 

问题原因：

    “ on duplicate key update ” 中如果有换行，sql解析就会失败！

 

注意：

mysql jdbc jar 版本5.1.8以下都存在一定程度的sql解析问题。最好能升级到最新版本！

如果sql解析异常，大多异常信息为：

Caused by: java.sql.SQLException: Parameter index out of range