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參數值。