File full error in simple select statement
Posted by: Derek McKinnon
Date: April 20, 2021 04:00AM

I am a relative newbie to MySQL, although not to SQL generally, and am not sure if this post should be in here or BI.

When running a SQL Query in both SQLYog and MySQL Workbench it gives me an error.

Error Code: 1114
The table 'C:\Windows\SERVIC~1\NETWOR~1\AppData\Local\Temp\#sql1e10_8_37' is full

This is a new computer and this error did not occur when I ran the code on my old computer about a year ago.

The Hard drive has 800 GB free.

SHOW VARIABLES LIKE 'innodb_data_file_path';
gives
ibdata1:12M:autoextend

If I remove one of the sums in the query is removed then the query runs. It is the one marked with ****.

If I add the where clause (to restrict the return values) it runs fine.

I am running version 8.0.23.

I feel like there is some problem with temporary file storage size, but the size doesn't look that big to me.

Code
________________

SELECT
W.WeekEnd,
d2.Code AS Family,
SUM(IFNULL(d2.Amount, 0)) AS Owing -- ****Removing this line and the code runs fine.
FROM weekendings W -- Contains 800 rows
INNER JOIN
dr02 d2 -- Contains 350,000 rows.
ON ((W.WeekEnd >= d2.Date AND d2.t1 <> "G") OR (W.WeekEnd >= d2.WeekEnd AND d2.t1 = "G"))
-- WHERE d2.Code = "SMI1"
GROUP BY W.WeekEnd, d2.Code;

Options: ReplyQuote


Subject
Written By
Posted
File full error in simple select statement
April 20, 2021 04:00AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.