Resolving the “ERROR 126 Incorrect key file” error

The Incorrect key file error:

ERROR 126 (HY000) at line 3: Incorrect key file for table '/var/tmp/#sql3f5_1b6c4e_1.MYI'; try to repair it

This error probably means that you ran out of disk space on /var/tmp while MySQL was trying to create a temporary table to resolve a complex query. The generation of temporary tables can be caused by derived tables (like a subselect) or filesort kicking in when you use ORDER BY.

To fix this error, you can use any of the following solutions:

• Put temporary tables on a bigger filesystem., and add the new path to my.cnf:

  [mysqld]
  tmpdir=/path/to/large/filesystem/mysql-tmp-dir
  

• Avoid using a subselect. Rephrasing it as a join, using SELECT STRAIGHT_JOIN will override the query optimizer to get your performance back.

• Avoid sorting, or sort the smallest dataset possible. For example:

  SELECT id, my_huge_text_field
  FROM table
  ORDER BY another_field;
  

Will run faster if you restructure the query like so:

SELECT id, my_huge_text_field
FROM table
JOIN (SELECT id
      FROM table
      ORDER BY another_field) AS derived_table USING (id);

In the first example, filesort will operate over all the selected data (including the huge text field). In the second example, the temporary table will only contain the id column, and the outer query will fetch the text chunks.

This article has been tested on MySQL 5.1.

Tags:

Rename a Postgresql database

#database #postgresql

Here's the procedure to rename a PostgreSQL database: 1. Disconnect from the database that you want to rename and connect to a different database. 2. Check and terminate all active connections to the database that you want to rename. 3. Use the `ALTER DATABASE` statement to rename the database to the new one. Let’s take a look at an example of... Read more

20 May 2019 - 1 minute read

How to duplicate a Postgresql database

#database #postgresql

If you need to duplicate an existing Postgresql database, and possibly transfer ownership of the database objects to a new user, here's how to do that in a quick way: ```sql -- First, I recommand getting the size of the database to copy, as this might be important for the rest of the process. SELECT pg_database.datname,pg_size_pretty(pg_databas... Read more

20 Apr 2019 - less than 1 minute read

Forcing ASMM component to shrink

#oracle #database

Here's the way to force the shared pool to shrink dynamically. Documentation states that ASMM can only increase shared pool, and can't shrink. When the automatic shared memory management feature is enabled, the internal tuning algorithm tries to determine an optimal size for the shared pool based on the workload. It usually converges on this va... Read more

20 Mar 2019 - 2 minute read

Last rowcount in MySQL

#mysql

Suppose you want to count the number of lines returned by the last SQL statement issued. For **select** statements you can use the *FOUND_ROWS* construct: ```sql SELECT SQL_CALC_FOUND_ROWS something FROM your_table WHERE whatever; SELECT FOUND_ROWS(); ``` This will return the number of rows in the last **select** query (or if the first query ... Read more

20 Sep 2016 - less than 1 minute read

MySQL punch user creation DDL

#mysql

You can get the DDL necessary to duplicate an existing user with the following system command: ``` MYSQL_CONN="-uroot -ppassword" mysql ${MYSQL_CONN} --skip-column-names -A -e "SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user'' " | mysql ${MYSQL_CONN} --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants... Read more

20 Jul 2016 - less than 1 minute read

InnoDB table rebuild

#mysql

### PROBLEM On a **MySQL 5.6** database server, the *ibdata1* file includes 5 InnoDB tables in the mysql schema. ```sql mysql> select table_name from information_schema.tables -> where table_schema='mysql' and engine='InnoDB'; +----------------------+ | table_name | +----------------------+ | innodb_index_stats | | innodb_table... Read more

20 Jun 2016 - 1 minute read