0

we wrote an application in .Net using Entity Framework and a MySQL Connector. But sometimes a query fails to execute in the application and we don't know why. The database is installed on a Debian server.

We se the following message in the log files:

System.Data.EntityCommandExecutionException: Er is een fout opgetreden tijdens het uitvoeren van de opdrachtdefinitie. Zie de interne uitzondering voor details. ---> MySql.Data.MySqlClient.MySqlException: Incorrect key file for table '/tmp/...MYI'; try to repair it
bij MySql.Data.MySqlClient.MySqlStream.ReadPacket()
bij MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int32& insertedId)
bij MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int32& insertedId)
bij MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
bij MySql.Data.MySqlClient.MySqlDataReader.NextResult()
bij MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
bij MySql.Data.Entity.EFMySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
bij System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
bij System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
--- Einde van intern uitzonderingsstackpad ---
bij System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
bij System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
bij System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
bij System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
bij System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
bij System.Data.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__1[TResult](IEnumerable`1 sequence)
bij System.Data.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
bij System.Data.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[S](Expression expression)
bij System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)

Here on stackoverflow, and on goole, I see several posts with the same "error". Most of them are related to free space on the temp partition. But when I look at the database server the /tmp partition is only used for 3% and has enough free space.

I traced the query which results in these error messages, but I can execute the query right now. I don't see any usage of the temp drive/partition. Also the tables which a queried are pretty small, only a few 1000 rows per table (but we do join 4 tables on foreign keys). That brings me the next thing, we use InnoDB tables for most of our tables. Only 4 tables use a MyIsam structure. Those tables do contain large amounts of data (about 10gb per table). But these tables aren't touched by the query (and the Entity framework context) at the moment.

Can it has something to do with a backup or something which runs on a certain moment? And does someone know of InnoDb and MyIsam tables use the temporary storage for certain queries, so that this error can occur by querying InnoDb tables?

Bojo
  • 381
  • 1
  • 5
  • 16
  • Possible duplicate of [MySQL incorrect key file for tmp table when making multiple joins](https://stackoverflow.com/questions/2090073/mysql-incorrect-key-file-for-tmp-table-when-making-multiple-joins) – y2k-shubham Mar 01 '18 at 09:19

1 Answers1

0

Cause:

This happens when there is a disk space issue in the /tmp mount point. A MySQL query creates a temporary MyISAM table to complete the execution and fetch result. Now the temporary MyISAM table generaly gets created on the --tmpdir as mentioned in my.cnf. Generally /tmp if explicitly not configured. We may not see the space scarcity if when the error occurred. We need to check the space of /tmp directory during the execution of the query

Resolution:

  1. Increase the size of the /tmp directory

  2. Tune the query such a way that it does not need to create any '/tmp/#..MYI'. specially avoiding UNION, ORDER BY etc

  3. Alter MySQL configuration such as sort_buffer_size, join_buffer_size, query_cache_size and most importantly tmp_table_size

  4. Change the --tmpdir path to somewhere else apart from the default /tmp directory where there is more free space

Reference:

http://kakadba.blogspot.in/2016/05/mysql-incorrect-key-file-for-table.html

mysqlrockstar
  • 2,536
  • 1
  • 19
  • 36