I'm trying to run a rather large query that is supposed to run nightly to populate a table. I'm getting an error saying Incorrect key file for table '/var/tmp/#sql_201e_0.MYI'; try to repair it
but the storage engine I'm using (whatever the default is, I guess?) doesn't support repairing tables.
how do I fix this so I can run the query?
This question is related to
mysql
mysql-error-126
You'll need to run this command from the MySQL prompt:
REPAIR TABLE tbl_name USE_FRM;
From MySQL's documentation on the Repair command:
The USE_FRM option is available for use if the .MYI index file is missing or if its header is corrupted. This option tells MySQL not to trust the information in the .MYI file header and to re-create it using information from the .frm file. This kind of repair cannot be done with myisamchk.
Your query is generating a result set so large that it needs to build a temporary table either to hold some of the results or some intermediate product used in generating the result.
The temporary table is being generated in /var/tmp. This temporary table would appear to have been corrupted. Perhaps the device the temporary table was being built on ran out of space. However, usually this would normally result in an "out of space" error. Perhaps something else running on your machine has clobbered the temporary table.
Try reworking your query to use less space, or try reconfiguring your database so that a larger or safer partition is used for temporary tables.
this issue is because of low storage space availability of a particular drive(c:\ or d:\ etc.,), release some memory then it will work.
Thanks Saikumar.P
Apply proper charset and collation to database, table and columns/fields.
I creates database and table structure using sql queries from one server to another. it creates database structure as follows:
I change collation of table and column to utf8_general_ci
, and it resolves the error.
This happenes might be because you ran out of disk storage and the mysql files and starting files got corrupted
The solution to be tried as below
First we will move the tmp file to somewhere with larger space
Step 1: Copy your existing /etc/my.cnf file to make a backup
cp /etc/my.cnf{,.back-`date +%Y%m%d`}
Step 2: Create your new directory, and set the correct permissions
mkdir /home/mysqltmpdir
chmod 1777 /home/mysqltmpdir
Step 3: Open your /etc/my.cnf file
nano /etc/my.cnf
Step 4: Add below line under the [mysqld] section and save the file
tmpdir=/home/mysqltmpdir
Secondly you need to remove or error files and logs from the /var/lib/mysql/ib_* that means to remove anything that starts by "ib"
rm /var/lib/mysql/ibdata1
and rm /var/lib/mysql/ibda.... and so on
Thirdly you will need to make sure that there is a pid file available to have the database to write in
Step 1 you need to edit /etc/my.cnf
pid-file= /var/run/mysqld/mysqld.pid
Step 2 create the directory with the file to point to
mkdir /var/run/mysqld
touch /var/run/mysqld/mysqld.pid
chown -R mysql:mysql /var/run/mysqld
Last step restart mysql server
/etc/init.d/mysql restart
Simple "REPAIR the table" from PHPMYADMIN solved this problem for me.
In my case, there was a disc space issue. I deleted some unwanted war files from my server and it worked after that.
REPAIR TABLE tbl_name USE_FRM;
Command only run when MySQL 'Storage Engine' type should be 'MyISAM'
Hope this helps
Change to MyISAM engine and run this command
REPAIR TABLE tbl_name USE_FRM;
You must change the location of MySQL's temporary folder which is '/tmp' in most cases to a location with a bigger disk space. Change it in MySQL's config file.
Basically your server is running out of disk space where /tmp is located.
Source: Stackoverflow.com