标签 mysql 下的文章

记一次Mysql更新问题的排查

表结构如下:

mysql> desc daily_change;
+------------+---------------------+------+-----+---------------------+----------------+
| Field      | Type                | Null | Key | Default             | Extra          |
+------------+---------------------+------+-----+---------------------+----------------+
| dc_id      | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment |
| date       | date                | NO   | MUL | NULL                |                |
| today      | decimal(20,8)       | NO   |     | NULL                |                |
| yesterday  | decimal(20,8)       | NO   |     | NULL                |                |
| diff       | decimal(20,8)       | NO   |     | NULL                |                |
| diff_rate  | decimal(2,2)        | NO   |     | NULL                |                |
| dc_created | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| dc_updated | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
+------------+---------------------+------+-----+---------------------+----------------+

现象:
脚本会计算每天的统计情况,计算每天的使用量、昨日的使用、差值和波动比例,然后写到表里。
测试过程中,发现表里记录的diff_rate和更新语句里的diff_rate不一致。
好多记录的diff_rate都是0.99或-0.99,与实际值不一致;有一些记录的值又是对的。

一开始怀疑是不是有其他脚本有更新逻辑,查了下binlog日志,没找到其他更新请求。

在mysql命令行里做了如下测试。

update daily_change set diff_rate = 0 where dc_id = 1;
select * from daily_change where dc_id = 1; // diff_rate变成了0
update dail_change set diff_rate = 3.52 where dc_id = 1;
select * from daily_change where dc_id = 1; // diff_rate变成了0.99

这真是见鬼了,看来不是有其他脚本更新,我的更新sql是没问题的,看来是mysql自己的问题。

看了下diff_rate字段的类型,decimal(2,2)。

decimal(a,b)

参数说明

a指定指定小数点左边和右边可以存储的十进制数字的最大个数,最大精度38。
b指定小数点右边可以存储的十进制数字的最大个数。小数位数必须是从 0 到 a之间的值。默认小数位数是 0。

一下明了了,字段类型导致的锅。
改成decimal(20,2)之后,一切正常。

mysql添加DATETIME类型字段导致Invalid default value错误的问题

执行sql如下:

CREATE TABLE `qcloud_sms_template` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `ctime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

执行结果:

ERROR 1067 (42000): Invalid default value for 'ctime'

这段sql在本机测试没问题,放到开发环境下就报错了。
很奇怪,搜索一番后,发现DATETIME类型只支持mysql 5.6.5+.

解决方法:

    将DATETIME改为TIMESTAMP。

参考资料:
http://stackoverflow.com/questions/168736/how-do-you-set-a-default-value-for-a-mysql-datetime-column/10603198

前台查询的结果字段不更新的问题排查

今天开发更新状态的一个功能,开发完后测试发现数据库里的字段值已经变了,但是前台仍显示的是以前的状态。
一开始以为是没更新成功,在数据库里仔细查了下确定是更改了状态,但是前台就是没有变化。
然后怀疑是前台查询的sql有问题,可能是没加相应的状态判断,于是文件里断点记录了下sql和查询结果,执行后发现sql没有问题,但是查询出来的结果里相应记录的状态没变。

呃,焦虑,这是什么情况啊...

接着怀疑是否是连错了数据库,不过这个直接被排除了,不可能的情况...

然后突然想到以前同事讲过的数据库缓存,于是搜索了下发现还真有这个功能。

MySQL cache功能分析:

1 MySQL的cache功能的key的生成原理是:把select语句按照一定的hash规则生成唯一的key,select的结果生成value,即key=>value。所以对于cache而言,select语句是区分大小写的,也区分空格的。两个select语句必须完完全全一致,才能够获取到同一个cache。

2 生成cache之后,只要该select中涉及到的table有任何的数据变动(insert,update,delete操作等),相关的所有cache都会被删除。因此只有数据很少变动的table,引入MySQL 的cache才较有意义。

使用场景:

MySQL的cache功能只适用于下列场合:数据变动较少,select较多的table。

执行下面的命令

show variables like '%query_cache%';

结果如下图:

可以看出来确实是开启了cache功能,但是这个缓存要求查询的sql必须要写SQL_CACHE,看了下php文件里并没有用到这个SQL_CACHE,应该不是这个缓存的,同样也排除了。

没办法,从最底层看吧。检查Mysql类文件,发现里面增加了memcache的处理,看了下代码,应该是memcache缓存了sql结果,至此困扰了快一下午的问题找到了原因。