MySQL 8.0.13离奇的代码2013 bug

1.环境

• Mac Os：
  macOS 10.14.2 (18C54)
• mysql：
  mysql Ver 8.0.13 for osx10.14 on x86_64 (Homebrew)

2.现象

执行一个带有创建临时表的存储过程，0.367s左右就返回错误：

Error Code: 2013. Lost connection to MySQL server during query

3.过程

• 客户端设置：Workbench连接时间正常，而且直接用mysql命令行也出故障
• 参数查正，并无异常

>show variables like '%timeout%';
>show variables like 'max_allowed_packet'

• my.cnf 中添加skip-name-resolve，没用
• 把create table取消，直接返回结果，正常
• 换环境，docker拉一个mysql 8.0.13，同一结果。奇怪的是执行这个存错过程后容器关闭了！
• 查日志，发现痕迹，mysql崩溃了！

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fc3e43fe428): create table tmp_trd_info as (
  with tmp_new_pd_detail as (
    SELECT
      -- if(order_time_in < @dt, DATE_ADD(@dt, interval 1 second), order_time_in) t1,
     ......
Connection ID (thread ID): 8
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
2019-01-20T02:39:15.6NZ mysqld_safe mysqld restarted

看来create table 命令居然把MySQL搞垮了！

• 换建表引擎

create table tmp_trd_info  ENGINE = MEMORY  as ......

结果

ERROR 1163 (42000): The used table type doesn't support BLOB/TEXT columns

再换

create table tmp_trd_info  ENGINE = myisam  as ......

居然TNND好了!

4.结论

MySQL 8.0.13在存储过程中建立innodb的表会服务崩溃？？深层次原因未知。

5.2022-03-05补充

https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html
https://dev.mysql.com/doc/refman/8.0/en/create-temporary-table.html

You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only within the current session, and is dropped automatically when the session is closed. This means that two different sessions can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.)

验证：
1.打开两个mysql命令行窗口
2.分别执行

use test ;
drop TEMPORARY table if exists _tmp_table;
CREATE TEMPORARY TABLE _tmp_table
(
    name  VARCHAR(10) NOT NULL,
    value INTEGER     NOT NULL
) engine = memory;
insert into _tmp_table(name, value)
values ('name1', 999);
select *
from _tmp_table;

use test ;
drop TEMPORARY table if exists _tmp_table;
CREATE TEMPORARY TABLE _tmp_table
(
    name  VARCHAR(10) NOT NULL,
    value INTEGER     NOT NULL
) engine = memory;
insert into _tmp_table(name, value)
values ('name2', 999);
select *
from _tmp_table;

删除临时表最好用 drop temporary table if exists…

会发现两个session互不影响，若在一个session调用存储过程建立的临时表，存错过程执行完成后在命令行还能访问这个临时表，除非手动drop.

delimiter $
drop procedure if exists create_tmp$
create procedure create_tmp()
begin
    drop TEMPORARY table if exists _tmp_table;
    CREATE TEMPORARY TABLE _tmp_table
    (
        name  VARCHAR(10) NOT NULL,
        value INTEGER     NOT NULL
    ) engine = memory;
    insert into _tmp_table(name, value) values ('upname1', 999);
    select * from _tmp_table;
    drop TEMPORARY table _tmp_table;
end $
delimiter ;

6.其他参考

上面引擎只能用myisam问题：
从MySQL 8.0.16开始，MySQL始终使用InnoDB存储引擎来管理磁盘上的内部临时表。8.0.15之前的版本使用internal_tmp_disk_storage_engine参数来定义内部临时表引擎，在MySQL8.0.16之后不再支持用户修改
MySQL 8.0.15之前，对于公用表表达式(CTE)，用于磁盘内部临时表的存储引擎不能是MyISAM。 如果internal_tmp_disk_storage_engine = MYISAM，则使用磁盘临时表实现CTE的任何尝试都会发生错误。在MySQL 8.0.15和更早版本中：当使用internal_tmp_disk_storage_engine = INNODB时，生成超过InnoDB行或列限制的磁盘内部临时表的查询将返回Row size too large或者Too many columns错误。 解决方法是将internal_tmp_disk_storage_engine设置为MYISAM。–来自参考来源