tidb4.0执行大型sql报没有tmp目录错处理（ERROR 1105 (HY000): open /tmp/1000_tidb/MC4wLjAuMDo0MDAwLzAuMC4wLjA6MTAwODA）

环境：tidb4.0

错误详情：

?

1 2	mysql> select count(*) total,zrqdm from wpt_jbdjxx_hcy where xt_zxbz='0' and ywlsh in (select ywlsh from wpt_jbdjxx where xt_confirm = '1') group by zrqdm; ERROR 1105 (HY000): open /tmp/1000_tidb/MC4wLjAuMDo0MDAwLzAuMC4wLjA6MTAwODA=/tmp-storage/chunk.ListInDisk028952359: no such file or directory

原因：sql查询需要比较大的空间，自动使用临时目录存储数据，默认配置的路径没权限。

处理方法：修改临时目录

1、查看tidb的临时目录

?

1 2 3 4 5 6 7 8

mysql>  SHOW CONFIG WHERE type = 'tidb' AND name like '%tmp-storage-path%'; +------+--------------------+------------------+-----------------------------------------------------------------+ | Type | Instance           | Name             | Value                                                           | +------+--------------------+------------------+-----------------------------------------------------------------+ | tidb | 172.21.210.33:4000 | tmp-storage-path | /tmp/1000_tidb/MC4wLjAuMDo0MDAwLzAuMC4wLjA6MTAwODA=/tmp-storage | | tidb | 172.21.210.32:4000 | tmp-storage-path | /tmp/1000_tidb/MC4wLjAuMDo0MDAwLzAuMC4wLjA6MTAwODA=/tmp-storage | | tidb | 172.21.210.39:4000 | tmp-storage-path | /tmp/1000_tidb/MC4wLjAuMDo0MDAwLzAuMC4wLjA6MTAwODA=/tmp-storage | +------+--------------------+------------------+-----------------------------------------------------------------+

2、修改临时目录

?

1 2 3 4 5 6 7 8 9 10 11 12

tiup cluster edit-config tidb server_configs:   tidb:     binlog.enable: false     binlog.ignore-error: false     log.slow-threshold: 2000     mem-quota-query: 573741824     tmp-storage-path: /data1/tidb-deploy/tmp   新建临时目录（该目录必须tidb有权限） su - tidb mkdir -p /data1/tidb-deploy/tmp

3、依次重启tidb服务节点　　

?

1 2	tiup cluster stop tidb -N 172.21.210.32:4000   #停单个节点 （33、39） tiup cluster start tidb -N 172.21.210.32:4000   #启动单个节点

4、进行验证

?

1 2 3 4 5 6 7 8 9 10

--再次查看tmp目录并执行语句，可以了 mysql>  SHOW CONFIG WHERE type = 'tidb' AND name like '%tmp-storage-path%'; +------+--------------------+------------------+------------------------+ | Type | Instance           | Name             | Value                  | +------+--------------------+------------------+------------------------+ | tidb | 172.21.210.32:4000 | tmp-storage-path | /data1/tidb-deploy/tmp | | tidb | 172.21.210.33:4000 | tmp-storage-path | /data1/tidb-deploy/tmp | | tidb | 172.21.210.39:4000 | tmp-storage-path | /data1/tidb-deploy/tmp | +------+--------------------+------------------+------------------------+ 3 rows in set (0.02 sec)