GreatSQL CTE 查询报告临时表找不到问题解析-优快云博客

GreatSQL CTE 查询报告临时表找不到问题解析

一、问题发现

在客户现场的一次问题报告中发现某个带有CTE语句进行查询的时候，把tmp_table_size设置为最小1024，数据量少的时候却报错临时表找不到。注意：该问题在最新的MySQL版本中仍存在。

看下面例子：

1、准备表

CREATE TABLE t7 (
  ID int NOT NULL AUTO_INCREMENT,
  NAME varchar(128) COLLATE utf8mb4_bin DEFAULT NULL,
  PARENTNODEID int DEFAULT NULL,
  STATE int DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

greatsql> INSERT INTO t7 VALUES (-1,'万里',0,1);

2、tmp_table_size为默认值场合

如下所示用默认tmp_table_size并且进行CTE派生表查询，可以发现结果正常显示一条数据，符合预期。

-- tmp_table_size参数为默认值的情况
greatsql> SHOW variables LIKE '%tmp_table_size%';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 16777216 |
+----------------+----------+

greatsql> WITH RECURSIVE cte AS(   (select id    from t7 where id= -1)   UNION   (select b.id     from t7 b inner join cte on cte.id=b.parentNodeid and b.state =1) ) select * from cte;      
+------+
| id   |
+------+
|   -1 |
+------+

3、tmp_table_size修改小的场合

如下所示设置最小的tmp_table_size并且进行CTE派生表查询，可以发现报错了，问题复现。

greatsql> WITH RECURSIVE cte AS(   (select id    from t7 where id= -1)   UNION   (select b.id     from t7 b inner join cte on cte.id=b.parentNodeid and b.state =1) ) select * from cte;      
ERROR 1813 (HY000): Tablespace '`tmp`.`#sqle7cc2_8_4`' exists.

二、问题调查过程

查询带有CTE派生表的时候内部会创建临时表用于保存临时数据，因此先看一下下面的运行流程图：

上面的流程图可以简化归纳总结为以下：

1、创建表A，创建表B ==>先赋值tmp table引擎
2、创建表A引擎 ==> 创建表A表tmp table引擎，发现内存不够，改为innodb引擎并创建表空间(见图上红字部分)，但是表B还是用tmp table引擎
3、打开表A，打开表B ==>表A打开成功，打开表B失败，因为在kv_store列表找不到这个临时表名
4、插入表A，插入表B
因此可以看出问题出在第二步，表A改变了引擎但是没有对应改变表B的引擎，接着表B打开的时候列表找不到对应的名字报错。

三、问题解决

结合上面分析，可以发现应该在第二步场景下，在表A的引擎改变的时候同步改变表B的引擎，因此对函数create_tmp_table_with_fallback修改如下：

static bool resolve_cte_common_table_expr(TABLE *wtable,
                                          Table_ref *wtable_list) {
  assert(wtable_list);
  Derived_refs_iterator ref_it(wtable_list);

  if (wtable_list) {
    Common_table_expr *cte = wtable_list->common_table_expr();
    if (cte) {
      int i = 0, found = -1;
      TABLE *t;
      while ((t = ref_it.get_next())) {
        if (t == wtable) {
          found = i;
          break;
        }
        ++i;
      }
      assert(found >= 0);
      if (found > 0)
        // 'wtable' is at position 'found', move it to 0 to convert it first
        std::swap(cte->tmp_tables[0], cte->tmp_tables[found]);
      ref_it.rewind();
    }
  }
  TABLE new_table, *table = nullptr;
  bool error = false;
  TABLE_SHARE *share = wtable->s;
  uint count = 0;
  // 遍历所有相关表并改变引擎
  while (true) {
    HA_CREATE_INFO create_info;
    table = ref_it.get_next();
    if (table == nullptr) break;

    table->file = get_new_handler(
        table->s, false, share->alloc_for_tmp_file_handler, innodb_hton);
    // for CTE's cloned table,it doesn't need to do
    // innobase_basic_ddl::create_impl again.
    if (count == 0) {
      error = table->file->create(share->table_name.str, table, &create_info,
                                  nullptr);
      if (error) return true;
    }
    count++;
  }
  return false;
}

static bool create_tmp_table_with_fallback(THD *thd, TABLE *table) {
  int error =
      table->file->create(share->table_name.str, table, &create_info, nullptr);
  if (error == HA_ERR_RECORD_FILE_FULL &&
      table->s->db_type() == temptable_hton) {
    Table_ref *const wtable_list = table->pos_in_table_list;
    // for CTE table,it should set all tables to innodb_hton.
    // 这里增加一个CTE判断，如果是CTE就对所有相关表做操作，改变所有表的引擎。
    if (wtable_list && resolve_cte_common_table_expr(table, wtable_list))
      return true;
    else {
      table->file = get_new_handler(
          table->s, false, share->alloc_for_tmp_file_handler, innodb_hton);
      error = table->file->create(share->table_name.str, table, &create_info,
                                  nullptr);
    }
  }
}

接着执行上面的查询，发现可以查出结果了。

greatsql> SET tmp_table_size=1024;
Query OK, 0 rows affected (0.00 sec)

greatsql> WITH RECURSIVE cte AS(   (select id    from t7 where id= -1)   UNION   (select b.id     from t7 b inner join cte on cte.id=b.parentNodeid and b.state =1) ) select * from cte;      
+------+
| id   |
+------+
|   -1 |
+------+
1 row in set (0.10 sec)

四、问题总结

通过以上分析我们可以发现，带有CTE派生表的查询会在内部创建临时表用于储存中间数据，根据tmp_table_size值设置会影响临时表存放地方，如果tmp_table_size设置小了那么一开始就会从内存表改为创建落盘表，但是CTE内部涉及好几层迭代器，这时候每一层临时表的引擎都需要改，而导致报错的代码只改了其中一层的临时表，没有把别的引擎一起改了，最后导致问题。

这个问题涉及了2个不同的迭代器的强相关表，理论上应该一起操作引擎，但是有时候代码处理一张表却漏了处理另一张，这就会导致问题。

建议：在没修复该Bug前，可以先适当调大tmp_table_size参数值。