mysql number类型引号问题

背景

 这几天在做数据自动化同步测试过程，发现一个诡异的现象。一批100条语句的更新过程中，同步到目标库去执行，总会有几条记录出现更新失败。

原因

1. 查看了同步过程中的执行日志，也米有啥特别明显的问题，单就是update affect = 0 。

2. 问题的查找方式也是比较简单，针对底层执行的update语句，挨个字段确认，到底是哪一个字段影响了记录的定位。 最后发现是一个Decimal(19,8)的字段类型。

3. debug跟踪了下对应的mysql driver代码，发现针对setBigDecimal类型数据处理时，多了个单引号，字段内容就变成了 '1234.12312'.  测试过程中手工去除引号，就可以正常的update. 

 

测试

 

1.创建表
mysql> create table ljhtable2 ( ID int(11) auto_increment , NUMBER_VALUES decimal(19,8) , CONSTRAINT ljhtable2_pk PRIMARY KEY(id) );

2. 插入记录
mysql> insert into ljhtable2 values(1, 4005747665.56665202);

 

 

查询

 

mysql> select * from ljhtable2 ;
+----+---------------------+
| ID | NUMBER_VALUES       |
+----+---------------------+
|  1 | 4005747665.56665202 |
+----+---------------------+
1 row in set (0.00 sec)

mysql> select * from ljhtable2 where number_values = '4005747665.56665202'; #加了引号就无法查询到
Empty set (0.00 sec)

mysql> select * from ljhtable2 where number_values = 4005747665.56665202;
+----+---------------------+
| ID | NUMBER_VALUES       |
+----+---------------------+
|  1 | 4005747665.56665202 |
+----+---------------------+
1 row in set (0.00 sec)

 

深度分析

1. 资讯了下公司的资深的mysql DBA，针对加引号的处理，目前mysql的版本存在一些bug，我们使用的mysql 版本(5.1.41)，所以只能通过mysql driver层面进行修复。 

2. mysql driver我们使用了server-side preparestatment + batch处理，问题就出在这里 (参数介绍： mysql几个参数(编码,预编译,批处理) )

 

这是我们使用的mysql driver参数

 

dbcpDs.addConnectionProperty("useServerPrepStmts", "true");
dbcpDs.addConnectionProperty("rewriteBatchedStatements", "true");

对应server-side的prepare实现类：ServerPreparedStatement .  

通过debug分析，在ServerPreparedStatement.toString()过程中针对Decimal类型，添加了引号，在跟踪了execute发送给mysql的数据包都是使用了writeLenString进行处理,不过看了下针对int,long,float类型也没有明确的类型标识，估计是在mysql server端进行类型转换处理，刚好类型转换又有些bug，就触发了上面的问题。

 

发送数据包的格式代码：ServerPreparedStatement.storeBinding()

解决

去掉上面的两项配置定义，使用mysql的client-side模式进行处理，由客户端拼装好sql，直接到mysql上进行执行。针对client-side的batch的一些优化处理，可以参见PreparedStatement.executePreparedBatchAsMultiStatement方法

考虑

目前mysql driver默认的都是基于client-side模式进行处理，可参见mysql driver changelog: http://dev.mysql.com/doc/refman/5.1/en/cj-news-5-0-5.html 

5.0.5的版本里提到: 

 

Due to a number of issues with the use of server-side prepared statements, Connector/J 5.0.5 has disabled their use by default. The disabling of server-side prepared statements does not affect the operation of the connector in any way

 

 

client-side和server-side的性能差别：

1. server-side需要和服务端的2次网络交互，一次进行prepare statement的构建过程，一次发送具体的bind数据。服务端可以缓存对应的prepare statement内容

2. client-side只需要一次和服务端的网络交互，但每次的sql都需要在服务端进行parse

 

所以针对server-side模式，如果不在jdbc客户端层面开启prepare statement cache，性能还不如client-side模式。而dbcp开启prepare statement cache，可能又会引发另外的问题： statement异常关闭。 在之前的dbcp测试过程中遇到过，后来就关闭了dbcp的prepare statement cache.