NOT NULL列用IS NULL也能查到数据?

导读

datetime列设置了NOT NULL约束,但查询条件IS NULL却能返回结果,奇怪吗?

测试表DDL

CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `dt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB

插入测试数据:

yejr@imysql.com> insert into t1(id) select 1;  --- 不指定dt列的值
yejr@imysql.com> insert into t1 select 2, now();  --- 指定dt列的值为now()
yejr@imysql.com> insert into t1(id) select 3;  --- 不指定dt列的值

查询数据:

yejr@imysql.com> select * from t1 where dt is null;
+------+---------------------+
| id   | dt                  |
+------+---------------------+
|    1 | 0000-00-00 00:00:00 |
|    3 | 0000-00-00 00:00:00 |
+------+---------------------+
2 rows in set (0.00 sec)

有没有觉得很奇怪,为什么查到了2条 dt 列值为 ‘0000-00-00 00:00:00’ 的记录?

先查看执行计划:

yejr@imysql.com> desc select * from t1 where dt is null\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 20.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

yejr@imysql.com> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `yejr`.`t1`.`id` AS `id`,`yejr`.`t2`.`dt` AS `dt` from `yejr`.`t1` where (`yejr`.`t1`.`dt` = '0000-00-00 00:00:00')

发现 IS NULL 条件被转换了,所以才能查到结果,这是为什么呢?
我尝试了调整SQL_MODE,发现并没什么卵用,最后还是在官方文档找到了答案:

For DATE and DATETIME columns that are declared as NOT NULL, you can find the special date ‘0000-00-00’ by using a statement like this:

SELECT * FROM tbl_name WHERE date_column IS NULL

This is needed to get some ODBC applications to work because ODBC does not support a ‘0000-00-00’ date value.

See Obtaining Auto-Increment Values, and the description for the FLAG_AUTO_IS_NULL option at Connector/ODBC Connection Parameters.

文档出自:12.3.2 Comparison Functions and Operators

最后的结论告诉我们,遇到问题时,查询官档是有多么重要。

Enjoy MySQL :)

全文完。


扫码加入叶老师的「MySQL核心优化课」,开启MySQL的修行之旅吧。

FAQ系列 | table id问题导致主从复制失败

0、导读

主从复制环境中,IO、SQL线程都很正常,也没设置过滤规则,但数据就是无法复制到slave上,什么原因?

1、问题描述

事实上,这个案例发生已经有一阵子了,一直拖到现在我才整理。

发现一个主从环境中,slave上的io_thread、sql_thread状态均正常,relay log也正常接收来自master的event,但slave上却无法正常应用这些event,个别表数据没有复制过来。而且slave上的binlog也没有记录这些表上的操作。

2、原因分析

接到现场后,第一反应是是先检查是否设置了ignore/do规则,发现并不是这个原因引起的。

我自己手动测试创建了个新的测试表,写了几条数据,发现在slave上这个表能被创建,但写入的测试数据仍旧无法复制过来。这说明,slave上的复制并不是完全失效的,只是有特殊情形下才会失效。

结合上面的问题,想到了可能是因为binlog format以及事务隔离级别等原因导致失效的,于是做了下面的尝试。

//首先修改事务隔离级别为RR(此前是RC),尽可能保证主从数据一致性

root@imysql [mydb]> set session transaction isolation level repeatable read;

//测试写入2条数据

root@imysql [mydb]> insert into z select 5,5;

root@imysql [mydb]> insert into z select 6,6;

经过观察,这2条数据不可以复制到slave上。

//修改binlog format为statement(此前是row),再写入2条数据

root@imysql [mydb]> set session binlog_format=’statement’;

root@imysql [mydb]> insert into z select 7,7;

root@imysql [mydb]> insert into z select 8,8;

经过观察,这2条数据则可以复制到slave上。

现在至少表面上看起来,是由于binlog format+事务隔离级别综合因素引起的,所以我们来对比下不同binlog format下的binlog有什么区别吧。

tableid1
这些日志中,前两条是row模式下的日志,后两条则是statement模式下的。我们注意到红框中内容是:table_id: 24874588093,正是由于这个原因导致了slave无法正常复制数据。

正常情况下,row模式下的binlog event应该是这样的:

tableid2
在上面的日志中,我们看到的是:table_id: 108,这种情况下就可以正常复制了。

现在问题很明确了,就是由于binlog中table id异常导致无法复制。那么,到底什么原因导致table id出现异常呢。

3、案例建议

搜索了一些资料,发现也有别人遇到同样的问题。我就不多啰嗦了,大家可以看下方参考文章详细了解下。简言之,发生这中问题的原因,主要是因为table cache不够了,导致要频繁打开、关闭table,导致table id急剧增长,因而导致主从数据复制失败。

解决办法有几个:

  1. 加大 table_cache_size,或者 table_open_cache 值,以及 table_definition_cache 选项。一般设置不低于总table数量的1.5倍,更严谨的话,要看 Open_tables 和 Opened_tables 这两个status值。Open_tables 表示当前正被打开的table数量,而 Opened_tables 表示历史上反复打开table的总次数。如果 Opened_tables 值特别高,表明 table cache 很可能不够用所致。
  2. 择机重启主库实例,让table id的值再次从0开始计数。
  3. 临时解决方案:把binlog format改成statement,并且把事务隔离级别改成RR,尽量避免数据不一致的风险。

本文参考:

1. 杨奇龙《【MySQL】再说MySQL中的 table_id 》,http://blog.itpub.net/22664653/viewspace-1158547/

2. yuyue2014《MySQL table_id原理及风险分析》,http://www.cnblogs.com/yuyue2014/p/3721172.html

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yejinrong.com 直达

FAQ系列 | 写新数据时某列值总是被自动修改

0、导读

往表里写入新数据时,却一直报告主键冲突,某列值一直被重置为一个固定值,疑似被黑,啥情况?

1、问题描述

某朋友的线上数据库,怀疑被侵入了。具体表象是:INSERT的时候,某列值总被自动改成一个固定值。

他们先自查了 TRIGGER 和 EVENT,都是空的,确定不是因为这两种原因引起,实在想不出是哪里被动了手脚。

问题的现象:

MariaDB [information_schema]> use bbs9;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [bbs9]> INSERT INTO cdb_mythreads_latest (uid,username,tid,fid,subject,special,dateline) VALUES (‘1239009‘,’yayv’,’13482713‘,’815′,’bbs5 …………….’,’0′,’1459569279′);

ERROR 1062 (23000): Duplicate entry ‘1239009-8388607’ for key ‘PRIMARY’

可以看到,tid列的值被从 13482713(原始值) 自动替换成了 8388607(篡改值)。

更让人奇怪的是,这条SQL在mysql client端手动执行手,也会报告同样的错误。究竟是什么黑客这么牛逼呢,百思不得其解~~~

2、原因分析

单从现象来看,好像还真是被黑了的意思。

but,但是,可是,你如果足够细心,就会发现端倪。

为什么这么说呢,因为 8388607 这个数值是不是看起来挺眼熟的?嗯,没错,你才对了,这个值是 MEDIUMINT 类型的最大值,而 MEDIUMINT UNSIGNED 的最大值是 16777215

当然了,你再认真看一眼表的名字是什么:cdb_mythreads_latest,我又要呵呵了,你懂得的。

3、其他建议

既然原因已经清楚了,那么解决起来也就简单了,只需要把tid列类型改成INT UNSIGNED,甚至BIGINT UNSIGNED都行。

MEDIUMINT和INT两种类型,也只是差了1个字节,何必呢。与其在这个地方节约1个字节,还不如在别的CHAR/VARCHAR/TEXT列调整下,其优化效果要好的多得多。

4、相关案例

FAQ系列 | 添加自增列失败

 

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yejinrong.com 获得专属优惠

FAQ系列 | 防范SQL注入风险

0、导读

在MySQL里,如何识别并且避免发生SQL注入风险

1、关于SQL注入

互联网很危险,信息及数据安全很重要,SQL注入是最常见的入侵手段之一,其技术门槛低、成本低、收益大,颇受各层次的黑客们所青睐。

一般来说,SQL注入的手法是利用各种机会将恶意SQL代码添加到程序参数中,并最终被服务器端执行,造成不良后果。

例如,我们访问接口 http://imysql.com/user.php?userid=123 来根据userid获取用户信息,假设程序中是这么处理的:

$sql = “SELECT * FROM user WHERE userid = $_GET[userid]“;

上面这段代码看起来既low有很xx对吧,尤其是在双引号里面还可以直接引用数据类型变量,所以说php是世界上最好的语言一点不为过,哈哈(其实我早期也写过几年php的)。

这时候,如果我们传递进去的参数改成这样:http://imysql.com/user.php?userid=123 or 1=1,这就会导致SQL条件永远成立,所有的数据都会被读取出来。又或者可以传递这样的参数:http://imysql.com/user.php?userid=123 or if(now()=sysdate(),sleep(5),1),这时候不但所有的数据都会被读取到,也会让这个SQL执行完毕后再等待5秒才能返回,黑客可据此来判断这个SQL注入探测是否成功。

在上面这个例子中,其实我们只需要对用户输入的参数进行简单的类型判断和控制,即可快速避免被注入的风险,例如改成下面这样就可以了:

$userid = intval(strim($_GET[‘userid’]));

$sql = “SELECT * FROM user WHERE userid = “ . mysql_real_escape_string($userid);

可见,至少基础的SQL注入并不难防范,只要在各个层面都做足工作就可以。而简单的SQL盲注(就是乱拳打死老师傅的玩法)已经可以采用sqlmap之类的辅助工具来做了,完全不需要人工执行。

2、如何防范

上面提到过sqlmap,它既可以作为SQL盲注的工具,也可以在新项目上线前内部扫一次,提前发现潜在漏洞,及时修补,反过来为我们所用。其他可以检测sql注入漏洞的知名扫描工具有:SQLIer、SQLID、SQL Power Injector、SQLNinja

我们也可以自己通过频繁扫描当前执行的SQL列表,根据一些关键字来判断是否发生了SQL注入或潜在风险,常见的关键字有:

  • SLEEP() — 一般的SQL盲注都会伴随SLEEP()函数出现,而且一般至少SLEEP 5秒以上
  • MID()
  • CHAR()
  • ORD()
  • SYSDATE()
  • SUBSTRING()
  • DATABASES()
  • SCHEMA()
  • USER()
  • VERSION()
  • CURRENT_USER()
  • LOAD_FILE()
  • OUTFILE/DUMPFILE
  • INFORMATION_SCHEMA
  • TABLE_NAME
  • fwrite()/fopen()/file_get_contents() — 这几个是PHP文件操作函数

我们可以以较高频率检查当前的活跃SQL命令,一旦发现上述关键字,可以立即记录下来并触发告警,通知管理员及时人工确认处理,甚至也可以先直接自动杀掉这些SQL查询(可以用 pt-kill 工具来做到这点,也可以自行开发),以防万一,少给黑客留机会。

还有,我们建议把选项 safe-update/sql_safe_updates 设置为 1,防止没有任何 WHERE 条件的误操作更新,将全表数据都写错

3、其他建议

防范SQL注入只是数据安全保护工作很小的一部分,只要做好基本功就可以防住至少80%以上的SQL注入探测。

在app server层,以PHP开发语言为例,除了上面提到的规范用户输入类型外,还可以改成用 sprintf() 函数来格式化构造 SQL 语句,也可以一定程度防范SQL注入。还可以修改 php cgi 程序的运行属主为普通用户,最起码不能使用 root 用户,避免因为代码层不严谨导致被黑客上传可执行 php 程序代码文件。还可以把php中的远程文件调用权限关闭,把选项 allow_url_fopen、allow_url_include 均设置为 off,并限定php可以打开的文件目录,不允许跨区域访问敏感文件。

除了在代码层面做好数据类型判断、用户输入判断外,还可以在web server层加上过滤策略,比如在nginx上启用WAF插件。或者,也可以购买IDC运营商、云主机提供商提供的商业解决方案。对于重视数据安全的企业来说,花点钱保平安更为重要。

4、附录

下面是一些常见SQL注入参考案例:

案例1:SELECT * FROM t WHERE a LIKE ‘%xxx%’ OR (IF(NOW=SYSDATE(), SLEEP(5), 1)) OR b LIKE ‘1=1‘;

案例2:SELECT * FROM t WHERE a > 0 AND b IN(497 AND (SELECT * FROM (SELECT(SLEEP(20)))a));

案例3:SELECT * FROM t WHERE a=1 and b in (1234,(SELECT (CASE WHEN (5=5) THEN SLEEP(5) ELSE 5*(SELECT 5 FROM INFORMATION_SCHEMA.CHARACTER_SETS) END)));

 

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yejinrong.com 获得专属优惠

FAQ系列 | 如何避免ibdata1文件大小暴涨

0、导读

遇到InnoDB的共享表空间文件ibdata1文件大小暴增时,应该如何处理?

1、问题背景

用MySQL/InnoDB的童鞋可能也会有过烦恼,不知道为什么原因,ibdata1文件莫名其妙的增大,不知道该如何让它缩回去,就跟30岁之后男人的肚腩一样,汗啊,可喜可贺的是我的肚腩还没长出来,hoho~

正式开始之前,我们要先知道ibdata1文件是干什么用的。

ibdata1文件是InnoDB存储引擎的共享表空间文件,该文件中主要存储着下面这些数据:

  • data dictionary
  • double write buffer
  • insert buffer/change buffer
  • rollback segments
  • undo space
  • Foreign key constraint system tables

另外,当选项 innodb_file_per_table = 0 时,在ibdata1文件中还需要存储 InnoDB 表数据&索引。ibdata1文件从5.6.7版本开始,默认大小是12MB,而在这之前默认大小是10MB,其相关选项是 innodb_data_file_path,比如我一般是这么设置的:

innodb_data_file_path = ibdata1:1G:autoextend

当然了,无论是否启用了 innodb_file_per_table = 1,ibdata1文件都必须存在,因为它必须存储上述 InnoDB 引擎所依赖&必须的数据,尤其是上面加粗标识的 rollback segmentsundo space,它俩是引起 ibdata1 文件大小增加的最大原因,我们下面会详细说。

2、原因分析

我们知道,InnoDB是支持MVCC的,它和ORACLE类似,采用 undo log、redo log来实现MVCC特性的。在事务中对一行数据进行修改时,InnoDB 会把这行数据的旧版本数据存储一份在undo log中,如果这时候有另一个事务又要修改这行数据,就又会把该事物最新可见的数据版本存储一份在undo log中,以此类推,如果该数据当前有N个事务要对其进行修改,就需要存储N份历史版本(和ORACLE略有不同的是,InnoDB的undo log不完全是物理block,主要是逻辑日志,这个可以查看 InnoDB 源码或其他相关资料)。这些 undo log 需要等待该事务结束后,并再次根据事务隔离级别所决定的对其他事务而言的可见性进行判断,确认是否可以将这些 undo log 删除掉,这个工作称为 purge(purge 工作不仅仅是删除过期不用的 undo log,还有其他,以后有机会再说)。

那么问题来了,如果当前有个事务中需要读取到大量数据的历史版本,而该事务因为某些原因无法今早提交或回滚,而该事务发起之后又有大量事务需要对这些数据进行修改,这些新事务产生的 undo log 就一直无法被删除掉,形成了堆积,这就是导致 ibdata1 文件大小增大最主要的原因之一。这种情况最经典的场景就是大量数据备份,因此我们建议把备份工作放在专用的 slave server 上,不要放在 master server 上。

另一种情况是,InnoDB的 purge 工作因为本次 file i/o 性能是在太差或其他的原因,一直无法及时把可以删除的 undo log 进行purge 从而形成堆积,这是导致 ibdata1 文件大小增大另一个最主要的原因。这种场景发生在服务器硬件配置比较弱,没有及时跟上业务发展而升级的情况。

比较少见的一种是在早期运行在32位系统的MySQL版本中存在bug,当发现待 purge 的 undo log 总量超过某个值时,purge 线程直接放弃抵抗,再也不进行 purge 了,这个问题在我们早期使用32位MySQL 5.0版本时遇到的比较多,我们曾经遇到这个文件涨到100多G的情况。后来我们费了很大功夫把这些实例都迁移到64位系统下,终于解决了这个问题。

最后一个是,选项 innodb_data_file_path 值一开始就没调整或者设置很小,这就必不可免导致 ibdata1 文件增大了。Percona官方提供的 my.cnf 参考文件中也一直没把这个值加大,让我百思不得其解,难道是为了像那个经常被我吐槽的xx那样,故意留个暗门,好方便后续帮客户进行优化吗?(我心理太阴暗了,不好不好~~)

稍微总结下,导致ibdata1文件大小暴涨的原因有下面几个:

  • 有大量并发事务,产生大量的undo log;
  • 有旧事务长时间未提交,产生大量旧undo log;
  • file i/o性能差,purge进度慢;
  • 初始化设置太小不够用;
  • 32-bit系统下有bug。

稍微题外话补充下,另一个热门数据库 PostgreSQL 的做法是把各个历史版本的数据 和 原数据表空间 存储在一起,所以不存在本案例的问题,也因此 PostgreSQL 的事务回滚会非常快,并且还需要定期做 vaccum 工作(具体可参见PostgreSQL的MVCC实现机制,我可能说的不是完全正确哈)

3、解决方法建议

看到上面的这些问题原因描述,有些同学可能觉得这个好办啊,对 ibdata1 文件大小进行收缩,回收表空间不就结了吗。悲剧的是,截止目前,InnoDB 还没有办法对 ibdata1 文件表空间进行回收/收缩,一旦 ibdata1 文件的肚子被搞大了,只能把数据先备份后恢复再次重新初始化实例才能恢复原先的大小,或者把依次把各个独立表空间文件备份恢复到一个新实例中,除此外,没什么更好的办法了

当然了,这个问题也并不是不能防范,根据上面提到的原因,相应的建议对策是:

  • 升级到5.6及以上(64-bit),采用独立undo表空间,5.6版本开始就支持独立的undo表空间了,再也不用担心会把 ibdata1 文件搞大;
  • 初始化设置时,把 ibdata1 文件至少设置为1GB以上;
  • 增加purge线程数,比如设置 innodb_purge_threads = 8;
  • 提高file i/o能力,该上SSD的赶紧上;
  • 事务及时提交,不要积压;
  • 默认打开autocommit = 1,避免忘了某个事务长时间未提交;
  • 检查开发框架,确认是否设置了 autocommit=0,记得在事务结束后都有显式提交或回滚。

 

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yejinrong.com 获得专属优惠