| Bug #118001 | delete sql returns error: Temporary file write failure. | ||
|---|---|---|---|
| Submitted: | 17 Apr 4:15 | Modified: | 21 Apr 8:07 |
| Reporter: | Chunling Qin | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S2 (Serious) |
| Version: | 8.4.3, 8.4.5 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[17 Apr 4:15]
Chunling Qin
[17 Apr 4:33]
Chunling Qin
It can be reproduced.
[17 Apr 11:03]
MySQL Verification Team
Hello Chunling Qin, Thank you for the bug report. Could you please provide test case(create table and sample data) to reproduce this issue at our end? Regards, Ashwini Patil
[17 Apr 11:08]
Chunling Qin
Please run 'source mysql.sql' to create table and insert data.
[17 Apr 12:16]
MySQL Verification Team
Hello Chunling Qin, Thank you for the details. I tried to reproduce your issue on windows 11 with MySQL Server 8.4 using table and data provided but I am not seeing any issues at my end. Regards, Ashwini Patil
[18 Apr 1:39]
Chunling Qin
Hi Developer, I have added a new table ddl file. Please try again. I can reproduced this issue with the following env. MySQL [test1]> delete from mysql_1 t1 where exists ( SELECT a1.* FROM ( ( SELECT a2.col_int_auto_increment, a1.col_pk_char, a1.col_pk_varchar, a1.col_pk_date, a2.col_int, a1.col_int__1, a2.col_int_8, a2.col_int_8__1, a2.col_bigint, a2.col_smallint, a1.col_tinyint, a2.col_float, a1.col_double, a2.col_numeric, a2.col_bit, a1.col_bit__1, a1.col_enum, a1.col_set, a2.col_char_3, a1.col_char_255, a1.col_varchar_1, a1.col_varchar_64, a1.col_varchar_2048, a1.col_binary_8, a1.col_varbinary_8, a2.col_date, a1.col_time, a2.col_datetime, a1.col_timestamp, a2.col_year, a1.col_text, a1.col_bool, a1.col_boolean, a1.col_longtext, a2.col_mediumtext, a2.col_tinyblob, a2.col_mediumblob, a1.col_longblob, a2.col_blob FROM mysql_2 a1 LEFT OUTER JOIN mysql_2 a2 ON ( a1.col_timestamp IN ( a1.col_datetime, a2.col_timestamp ) OR t1.col_timestamp NOT IN ( '1997-08-19 19:35:50.003064','1996-12-24 21:42:42.020996' ) ) WHERE ( t1.col_pk_date <> a2.col_date ) OR ( a2.col_bit__1 IS NULL ) AND t1.col_int_8 IS NULL GROUP BY a2.col_int_auto_increment, a1.col_pk_char, a1.col_pk_varchar, a1.col_pk_date, a2.col_int, a1.col_int__1, a2.col_int_8, a2.col_int_8__1, a2.col_bigint, a2.col_smallint, a1.col_tinyint, a2.col_float, a1.col_double, a2.col_numeric, a2.col_bit, a1.col_bit__1, a1.col_enum, a1.col_set, a2.col_char_3, a1.col_char_255, a1.col_varchar_1, a1.col_varchar_64, a1.col_varchar_2048, a1.col_binary_8, a1.col_varbinary_8, a2.col_date, a1.col_time, a2.col_datetime, a1.col_timestamp, a2.col_year, a1.col_text, a1.col_bool, a1.col_boolean, a1.col_longtext, a2.col_mediumtext, a2.col_tinyblob, a2.col_mediumblob, a1.col_longblob, a2.col_blob HAVING NOT ( a2.col_int NOT BETWEEN 5 AND 5 + 10 ) INTERSECT SELECT a1.* FROM ( mysql_2 a1 JOIN mysql_2 a2 ON (a1.col_bigint = a2.col_bigint +1 OR a1.col_numeric IS NOT NULL ) ) RIGHT OUTER JOIN mysql_2 a3 ON ( a2.col_bigint = a3.col_bigint AND a1.col_timestamp IS NULL ) WHERE a1.col_varchar_2048 = 'jlicx' ) a1 LEFT OUTER JOIN mysql_2 a2 ON (a1.col_bigint = a2.col_bigint +1 ) ) ) /* QNO 2043 CON_ID 20312 */; ERROR 1878 (HY000): Temporary file write failure. MySQL [test1]> select version(); +-----------+ | version() | +-----------+ | 8.4.3 | +-----------+ 1 row in set (0.00 sec)
[18 Apr 1:40]
Chunling Qin
My environment is as followed: [root@TENCENT64 ~]# uname -a Linux TENCENT64.site 4.14.105-1-tlinux3-0023.1 #1 SMP Tue Mar 1 15:50:11 CST 2022 x86_64 x86_64 x86_64 GNU/Linux [root@TENCENT64 ~]# cat /etc/redhat-release CentOS Linux release 7.9 (Final)
[21 Apr 8:07]
MySQL Verification Team
Hello Chunling Qin, Thank you for the test case. Able to reproduce with MySQL Server 8.4.5. Regards, Ashwini Patil
