0

I have this sql query in my code (php)

SELECT * FROM (
                SELECT
                    A.ID,
                    A.ID as QuoteID,
                    A.Price as QuotePrice,
                    A.Price_Per,
                    A.Currency,
                    P.Packaging,
                    A.PackagingID,
                    A.Quantity,
                    A.MOQ,
                    A.QuoteDate,
                    A.IncoTerm,
                    A.RefNO,
                    B.E_name,
                    B.C_name,
                    B.Type,
                    B.Form,
                    B.Dosage,   
                    B.ProductID,
                    C.E_Name as SupplierName,
                    C.SupplierID,
                    A.Remark
                FROM quote A
                left join prodpackaging P on A.PackagingID = P.PackagingID
                join product B on A.ProductID = B.ProductID 
                join supplier C on A.SupplierID = C.SupplierID
                left join rfq r on r.RefNO = A.RefNO and r.RefNO like 'PL%'
                WHERE IFNULL(r.status,'Valid') = 'Valid'
            ) oph;

This is not getting results in my local application. I checked it on phpMyAdmin and run it there, so this error is displayed

Error Code: 126. Incorrect key file for table '/tmp/#sql_4a9_0.MYI'; try to repair it

I saw this is a very common problem with the size of the query result and the /tmp folder size. I have tried these things:

1.- Changing system variables (join_buffer_size, sort_buffer_size, tmp_table_size, max_heap_table_size).

2.- Releasing space in disk.

Unfortunately, i couldn't solved this problem yet. Probably i need to improve the sql query or add more space to the /tmp folder, maybe any of these ways work. I'll really appreciate your help.

Thanks for your time.

Note, i attached the df -h command and the ncdu of "/":

enter image description here

undefined_variable
  • 6,180
  • 2
  • 22
  • 37
Kurono K.
  • 3
  • 3
  • its about the space available in the server for the user running the query. Try the clear the allocated space for the user and run the query. In case of buffer size that will take longer time with less memory if that is the case you need to buffer size too. – krishna aryal Nov 27 '17 at 09:15
  • Thanks for editing it. – Kurono K. Nov 27 '17 at 09:15
  • I'm deleting some files in the server, so i hope can get enough space so the query can run finally. – Kurono K. Nov 27 '17 at 09:17
  • @krishnaaryal, I have removed all the possible spacing in the hard disk (around 100Gb) but still cannot run that query, generates the same problem Error Code: 126. Incorrect key file for table '/tmp/#sql_4a9_0.MYI'; try to repair it, is there anything else i should do? another way to clean? – Kurono K. Nov 27 '17 at 18:27
  • This could be a duplicate problem to added link. Please follow the solution given and let me know if that worked? https://stackoverflow.com/questions/2090073/mysql-incorrect-key-file-for-tmp-table-when-making-multiple-joins – krishna aryal Nov 30 '17 at 10:01

0 Answers0