79555

Incorrect key file for table MYSQL

When i want to GROUP BY a query i get this error message:

Incorrect key file for table '/tmp/#sql_623d_0.MYI'; try to repair it

It is an InnoDB table with 3.000.000 records and 4,3 GiB large.

When I run

repair table mytablename

I get this message:

The storage engine for the table doesn't support repair

Optimize won't work. What to do?

Answer1:

The problem was not that the table was corrupt. When you do a group by, mysql creates a temporary table. Because the table is so large, the temporary table takes more than 10gb of diskspace. The error message appears when the disk got 100% full.

I used the commando to check the diskspace:

df -h /dev/root

So in the MySQL config (my.ini) I changed the location (tmpdir) where the temporary tables are stored to a partition with enough free diskspace.

now the query runs succesfull

Recommend

  • Decimal.Parse doesn't remove trailing zeros
  • Tracking visitors coming from Facebook
  • Why won't writeRaster save a 4390x4390 raster?
  • cockroachdb/kubernetes logging lots of errors on stackdriver
  • How to delete log file after a week?
  • AWS Api Gateway as a HTTP Proxy is currupting binary uploaded image files
  • All my layout xml files became autogenerated files
  • Why is it best to not catch exceptions in general-purpose classes?
  • Add image using WordML
  • PHP Water mark script with using GD library function imagejpeg Error
  • Magento - Sales order information appears in left sidebar in stead of big right part
  • Strange gradle error android studio
  • PHP shell_exec update output as script is running
  • How to create an undeletable file in Delphi
  • Fatal error: Failed to write core dump
  • error with magento checkout
  • turn image binary data into img tag
  • mysql row number implementation that is always incremental (sorted from 1 to n)
  • PyAudio: when is it safe to modify the callback buffer?
  • How to get or calculate size of Azure File/Share or Service
  • Where to save the local DB created for iphone app?
  • Is a .txt file created in VB different than one I'd randomly create?
  • Binary Tree Traversal Sum Of Each Depth
  • get passwords from chrome
  • Small video playback
  • Selecting a subset of data in ServiceStack.OrmLite
  • Does the Azure table storage API cache results?
  • SQL: Getting the physical size of a subset of a table
  • How do I remove all but some records based on a threshold?
  • How do I get the list of bad records that didn't load in Bigquery?
  • Sequential (transactional) API calls in angular 4 with state management
  • What is Eclipse's Declaration View used for?
  • Join two tables and save into third-sql
  • How to model a transition system with SPIN
  • ORA-29908: missing primary invocation for ancillary operator
  • R: gsub and capture
  • jqPlot EnhancedLegendRenderer plugin does not toggle series for Pie charts
  • Comma separated Values
  • Converting MP3 duration time
  • How to load view controller without button in storyboard?