MySQL Bug 65745分析

7月 2nd, 2012

MySQL Bug 65745分析

BUG描述

MySQL 5.5.25,5.5.26版本,一个更新单行的操作,有可能存在死循环,一直持续更新,直至耗尽磁盘空间。详细的BUG描述及重新脚本,见下面的网址:http://bugs.mysql.com/bug.php?id=65745

接下来,本文将分步骤,详细分析此BUG的执行流程,以及产生此BUG的内在原因。

处理流程

— MySQL 5.5.25

  1. 判断出id1索引与primary key索引均以id1列开始,因此是一个 Rowid Ordered Retrieval (ROR)

2. ROR查询流程,首先根据查询条件(a is null and id1 = 2),构造一个查询的range minkey[2,null], maxkey[2,null]

函数处理流程:

    opt_range.cc::get_quick_keys();

        range = new QUICK_RANGE();

        insert_dynamic(&quick->ranges, (uchar*)&range);

3. 从id1索引进行索引扫描,对于取到的索引项,判断是否位于查询range之中,若满足查询range,则读取记录的主键,根据主键从聚簇索引中读取记录,然后更新;若不满足查询range,退出.

函数处理流程:

sql_update.cc::mysql_update();

        while(info.read_record());

            opt_range.cc::QUICK_ROR_INTERSECT_SELECT::get_next();

                opt_range.cc::QUICK_RANGE_SELECT::get_next();

                    …

                        // 从id1索引中读取下一条记录

                        row0sel.c::row_search_for_mysql();

                // 判断记录是否位于查询range之中

                opt_range.cc::QUICK_RANGE_SELECT::row_in_ranges();

                // 若满足range,进行一次postion scan,根据primary key读取完整记录

                ha_innodb.cc::ha_innobase::rnd_pos();

        // 调用InnoDB的update_row方法,更新记录

        ha_innobase::update_row();

— MySQL 5.1.49

1. 选择主键索引做扫描(未进行ROR优化,认为二级索引回表代价过大,因此选择主键索引)

2. 由于走主键索引,并且update同时更新主键索引,因此判断出可能存在Halloween问题,将满足条件的项首先保存在临时文件中,待扫描完成之后,读取临时文件,逐个更新项。

函数处理流程:

    sql_update.cc::mysql_update();

        if (used_key_is_modified || order || …) {

            /*

                We can’t update table directly; We must first search after all

                matching rows before updating the table!

            */

            …

        }

BUG分析

为什么会出现死循环?

这是因为update语句(SET id2 = id2 + 1),将id2列递增,而id2列本身是主键列,因此id2列也位于id1索引中(二级索引包含主键列),而且每次更新,索引id1中会出现两项,原有项标识为删除,新项插入到原有项的后面。

在完成更新之后,通过id1索引读取下一项,恰好读取到上一次update的列,由于上一次的update并未更新查询列(id1, a),因此,新读取的列,仍旧位于查询range之中,继续满足查询条件,再次被更新。

由此产生一个死循环,当前次的更新列,恰好作为下一次查询的返回列。一个典型的Halloween问题。

 

ROR分析

RowId Ordered Retrieval的代价估算:

    opt_range.cc::test_quick_select();

        opt_range.cc::get_best_ror_intersect();

            // ROR的代价,是所有range相加的代价总和

            opt_range.cc::ror_intersect_add();

                // 一个range的ROR代价 =

// range的索引扫描代价 + range中索引记录产生的表扫描代价

                info->index_scan_costs += ror_scan->index_read_cost;

                opt_range.cc::get_sweep_read_cost();

                    // range中有records个记录,对应于records个聚簇索引的ranges

                    // 下面的公式计算出ROR下回表的代价

                    ha_innobase::read_time(records, records);

                // ROR扫描的代价 = 二级索引range查询代价 + ROR下回聚簇索引代价

                info->total_cost =     info->index_scan_costs + get_sweep_read_cost();

            // 若当前的ROR扫描代价要小于目前最优的范围扫描代价,则进行ROR扫描

            min_cost = intersect->total_cost;

            if (min_cost < read_time)

                trp= new (param->mem_root) TRP_ROR_INTERSECT;

计算出来的min_cost与read_time的结果:

MySQL 5.5.25

read_time:    2.0153418803418801

min_cost:    2.0000000000000000

结果:选择ROR,出现BUG

MySQL 5.1.49

read_time:    1.6132051282051283

min_cost:    2.0000000000000000

结果:不选ROR,无BUG

猜测一

MySQL 5.1.49选择主键索引,而非ROR的原因,在于主键索引range查询的代价较小。此时,我们可以通过增加测试数据的方法,使得其走上ROR,如本文下面的MySQL 5.1.49测试脚本所示。

使用MySQL 5.1.49测试脚本(见下方),其成功走上了ROR Scan,但是仍旧没有出错,跟踪对比MySQL 5.1.49与MySQL 5.5.25的代码流程,再次发现问题出现在下面:

— MySQL 5.5.25

    sql_update.cc::mysql_update();

        used_index = get_index_for_order();

            if (!order)

                if (select && select->quick)

                    // 此时,quick->index = MAX_KEY,也就是未初始化

                    return select->quick->index;

        // 由于返回的是MAX_KEY,因此此处判断不成功,used_key_is_modified = false

        // 意味着没有Halloween现象,不需要先取到临时表,然后进行更新

        // 参照MySQL 5.1.49,这个处理是错误的,不能根据used_index来判断,而需要

        // 调用quick的is_keys_used方法判断

        if (used_index != MAX_KEY)

            used_key_is_modified = is_key_used();

— MySQL 5.1.49

    sql_update.cc::mysql_update();

        if (select && select->quick)

            // 此处,quick->index = 0,primary key,说明走primary key索引

            // 同时又需要更新primary key的id2列,因此存在Halloween问题

            used_index = select->quick->index;

            used_key_is_modified = (!select->quick->unique_key_range() &&

                select->quick->is_keys_used(table->write_set));

                // 无论used_index是否为MAX_KEY,均会调用is_keys_used函数

                // 检查涉及到的索引是否被update更新,存在Halloween问题

                // 因此MySQL 5.1.49的处理方法是正确的,不会出现此BUG

                QUICK_ROR_INTERSECT_SELECT::is_keys_used();

                    List_iterator_fast<QUICK_RANGE_SELECT> it(quick_selects);

                    is_key_used(head, quick->index, fields);

                         /* If table handler has primary key as part of the

                         index, check that primary key is not updated */

在MySQL Optimizer选择ROR(id1索引range + 聚簇索引position)时,无论是MySQL 5.5.25,亦或是MySQL 5.1.49,其判断    used_index 均是错误的。

实际上 used_index 应该是    1(id1索引),而不是0(MySQL 5.1.49)或者MAX_KEY(MySQL 5.5.25)。

BUG重现脚本(MySQL 5.5.25)

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (

id1 int NOT NULL,

id2 int NOT NULL,

a int,

b int,

PRIMARY KEY (id1,id2),

KEY (id1, a)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t1` VALUES (1,1,NULL,1);

INSERT INTO `t1` VALUES (2,2,1,NULL);

INSERT INTO `t1` VALUES (2,3,2,NULL);

INSERT INTO `t1` VALUES (2,4,3,NULL);

INSERT INTO `t1` VALUES (2,5,4,NULL);

INSERT INTO `t1` VALUES (2,6,NULL,2);

UPDATE t1 SET id2 = id2 + 1, b = null WHERE a is null and id1 = 2;

反向更新id2脚本

根据此BUG产生的原理,可以分析出,反向更新id2列并不会产生相同的BUG。

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (

id1 int NOT NULL,

id2 int NOT NULL,

a int,

b int,

PRIMARY KEY (id1,id2),

KEY (id1, a)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t1` VALUES (1,1,NULL,1);

INSERT INTO `t1` VALUES (2,2,1,NULL);

INSERT INTO `t1` VALUES (2,3,2,NULL);

INSERT INTO `t1` VALUES (2,4,3,NULL);

INSERT INTO `t1` VALUES (2,5,4,NULL);

INSERT INTO `t1` VALUES (2,600000,NULL,2);

UPDATE t1 SET id2 = id2 – 1, b = null WHERE a is null and id1 = 2;

ROR测试脚本(MySQL 5.1.49)

以下测试脚本,能够在MySQL 5.1.49版本中模拟出ROR Scan。但是MySQL 5.1.49版本并不会报错。

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (

id1 int NOT NULL,

id2 int NOT NULL,

a int,

b int,

PRIMARY KEY (id1,id2),

KEY (id1, a),

KEY (a)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t1` VALUES (1,1,NULL,1);

INSERT INTO `t1` VALUES (2,2,1,NULL);

INSERT INTO `t1` VALUES (2,3,2,NULL);

INSERT INTO `t1` VALUES (2,4,3,NULL);

INSERT INTO `t1` VALUES (2,5,4,NULL);

INSERT INTO `t1` VALUES (2,6,5,NULL);

INSERT INTO `t1` VALUES (2,7,6,NULL);

INSERT INTO `t1` VALUES (2,8,7,NULL);

INSERT INTO `t1` VALUES (2,9,8,NULL);

INSERT INTO `t1` VALUES (2,10,9,NULL);

INSERT INTO `t1` VALUES (2,11,NULL,2);

UPDATE t1 SET id2 = id2 + 1, b = null WHERE a is null and id1 = 2;

  1. Vicki
    10月 29th, 201222:23

    牛人!