On the remote server, periodically when an application is running, an error like this occurs:

SQLException: java.sql.SQLException: Incorrect key file for table 'C: \ WINDOWS \ TEMP # sql_a38_0.MYI'; try to repair it

or

SQLException: java.sql.SQLException: Incorrect key file for table 'C: \ WINDOWS \ TEMP # sql_a38_25.MYI'; try to repair it

Here is an example of one of the queries that led to this error:

SELECT count(*) FROM (select * from table1 union all select * from table2) a WHERE time > 1468443612608 AND time <= 1468530021572 AND status=1 AND 1 GROUP BY a.cur ORDER BY null

But this error occurs when performing various queries. When you run and run the application on the local server, this error is not observed. What could be the reason for this error? Various sources indicate that this error may occur when there is a shortage of space to create temporary tables, but where exactly should it be increased and how?

  • > you can use where you have enough space. But in principle, you need to remove garbage from temp, put more hard in it or change the request so that it does not take too much data. make "EXPLAIN your_query" - strangeqargo
  • @strangeqargo, when using EXPLAIN, in the Extra field, "Using temporary" is displayed, but this query cannot be changed. - Ksenia
  • what does "no" mean? You need to change either the query or the indexes in the database, or both. or add space to hdd. you have no other options. If you add space to hdd, but do not optimize the query / indexes, the query will be executed, but slower and slower, until its performance becomes unacceptable. show an example. - strangeqargo
  • @strangeqargo, added sample request - Ksenia
  • This is a very bad request. - strangeqargo

1 answer 1

  SELECT count(*) #первый запрос, который бесполезно #выбирает данные из своих подзапросов FROM ( #второй запрос, выбирает ВСЕ даннные без лимитов и ограничений select * from table1 union all #третий запрос, выбирает ВСЕ даннные без лимитов и ограничений select * from table2 ) a #почему вы не ограничили time в подзапросах? WHERE time > 1468443612608 AND time <= 1468530021572 AND status=1 #почему вы не ограничили status=1 в подзапросах? # вот это кто-то где-то вычитал для какой-то оптимизации # не понимая, что делает AND 1 GROUP BY a.cur ORDER BY null

you make a query that selects all data for all time from both tables, and then you filter the output.

Naturally, the base is choking. besides, in subqueries you collect * ALL data, although you only need to read the records, and not output / process the data

at least you can rewrite it like this:

 SELECT count(*) FROM ( select cur /* вместо "*" здесь должно быть нужное поле */ from table1 WHERE status = 1 AND (time > 1468443612608 AND time <= 1468530021572) union all select cur /* вместо "*" здесь должно быть нужное поле */ from table2 WHERE status = 1 AND (time > 1468443612608 AND time <= 1468530021572) ) a GROUP BY a.cur;

I do not know your task and architecture of your base, perhaps - and most likely - you should use JOIN on status=1 with WHERE, instead of UNION ALL

on a local server, you naturally don’t see such errors, simply because you do n’t have enough data on a local server, and there are many data on a production server, this is a typical error of a novice web developer - to assume that if the request is executed quickly on a test database will be great to fly in combat

do not want (or can not) change the query: welcome to the nightmare world of bad database administrators:

you can use where you have enough space. It is necessary to remove garbage from temp, put more hard or change the request so that it does not take too much data.

You can still dump the memory, another six months, hold out without terrible brakes.

PS I'm afraid to imagine that I will see if you show the result of executing SHOW CREATE TABLE

I recommend you a great book: MySQL. Performance Optimization . Buy, borrow, download, get a gift, but read it (not necessarily all, at least relevant to your case chapter), if you want to understand what you are doing.

  • Thank you for such a detailed answer! Indeed, I tried to execute a query with WHERE in subqueries, the difference in execution time is huge) - Ksenia
  • get a book anyway, though it’s about mysql but in general it’s about different rdmbs inserts brains - strangeqargo