ERROR 1034 (HY000): Incorrect key file for table

[Egor Shevtsov]

Hi Guys,

I run Tokudb 5.5.30-tokudb-7.0.3-MariaDB-log installed from official community tarball on  CentOS release 6.4 (Final).

Table:

CREATE TABLE `keyword_rankings` (

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `site_id` int(11) NOT NULL,

  `index_date` int(11) NOT NULL,

  `engine` varchar(50) NOT NULL,

  `search_type` varchar(20) NOT NULL,

  `country` varchar(20) NOT NULL,

  `keyword` varchar(128) NOT NULL,

  `rank` int(11) NOT NULL,

  `page` int(11) NOT NULL,

  `landing_page` varchar(255) NOT NULL,

  `client_rank_check` tinyint(4) NOT NULL,

  PRIMARY KEY (`id`),

  CLUSTERING KEY `clstr_site_id_rank` (`site_id`,`engine`,`search_type`,`country`,`keyword`,`client_rank_check`,`rank`,`index_date`),

  CLUSTERING KEY `clstr_site_id_landing_page` (`site_id`,`engine`,`search_type`,`country`,`keyword`,`client_rank_check`,`landing_page`,`index_date`)

) ENGINE=TokuDB AUTO_INCREMENT=314605914 DEFAULT CHARSET=utf8

When I run:

SELECT DISTINCT MAX(index_date)  

FROM keyword_rankings   

WHERE site_id = 156   

GROUP BY site_id, index_date, engine, search_type, country, keyword, page  

HAVING COUNT(*) > 1  

ORDER BY index_date DESC;

I've got:

ERROR 1034 (HY000): Incorrect key file for table '/data/mysqltmp/#sql_56e2_0'; try to repair it

I see in my temp dir:

-rw-rw---- 1 mysql mysql 7.3M Sep 25 08:53 #sql_56e2_0.MAD

-rw-rw---- 1 mysql mysql 4.3M Sep 25 08:53 #sql_56e2_0.MAI

They look like MyISAM to me. 

 Although: show global variables like '%storage_engine';

+------------------------+--------+

| Variable_name          | Value  |

+------------------------+--------+

| default_storage_engine | TokuDB |

| storage_engine         | TokuDB |

+------------------------+--------+

Any ideas?

I see in the mariadb-5.5.30-tokudb-7.0.3-users-guide:

Version 7.0.3 includes important improvements:

 Bug fixes:

– ”1034 Incorrect key file” issue (#52)

But couldn't find more on that issue.

Any ideas?

Thanks

[Rich Prohaska]

Hello Egor,

If I had a copy of the mysql data directory, then I could debug it.

Rich Prohaska

proh...@tokutek.com

[Egor Shevtsov]

Hi Rich,

I can't send you all the mysql data directory as it has client's data.

Do you have anything in particular in question, so I can supply you with that part of info?

Thanks.

I

[Rich Prohaska]

On Wednesday, September 25, 2013 4:13:18 AM UTC-4, Egor Shevtsov wrote:

[Rich Prohaska]

Hello Egor,

Maybe you can run the query with a tokudb debug variable set and email me the mysql error log.  From mysql client, run 'set global tokudb_debug=28', and then run the query.  Nothing else should be running so that the debug trace is specific to only the one query that we are running.  My email address is: proh...@tokutek.com

On Wednesday, September 25, 2013 4:13:18 AM UTC-4, Egor Shevtsov wrote:

[Egor Shevtsov]

Hi Rich,

This is a prod database with 3K QPS at usual load.

Could we try anything less intrusive?
Thanks,

I


On Wednesday, 25 September 2013 09:13:18 UTC+1, Egor Shevtsov wrote:

[Rich Prohaska]

We need to try to reproduce on a debug machine.

On Wednesday, September 25, 2013 4:13:18 AM UTC-4, Egor Shevtsov wrote:

[Rich Prohaska]

Hello Egor,

We saw the 'incorrect key file for table error occur while running the random query generator (https://launchpad.net/randgen).  The problem that we saw was caused by the file system running out of space.  In your case, the query is using a temporary MyISAM table in the MySQL tmp directory to store temporary rows prior to the group by sort.  Is it possible that the file system that contains /data/mysqltmp ran out of space while running this query?

[Egor Shevtsov]

Hi Rich,

No, it's not possible.

 /data/mysqltmp locates on the same 2Tb partition as mysql datadirectory with plenty space available. 

Filesystem    Type    Size  Used Avail Use% Mounted on
/dev/mapper/vg-lv_mysql xfs    2.4T  280G  2.1T  12% /data

and temp files die at the size of about 7mb.

What's unusual that Tokudb create MyISAM temp files. Original table that we query has 2 clustering indexes.

Is it possible that created temp MyISAM table can't read those clustering indexes to dump the data?

As I posted earlier  default_storage_engine set to TOKUDB, but I think I started MySQL server with MyISAM as default engine

and than changed this dynamical global var to TokuDB.

Could it be that the temp tables are still created as MyISAM because this variable somehow hasn't been picked up?

Why temporary table is created as MyISAM when  default_storage_engine set to TOKUDB? 

Is it normal behaviour for TokuDB?   

Thanks,

I

[Rich Prohaska]

For your query, a temporary table is created during query execution by MariaDB to store intermediate results.

The engine used is either the heap engine or the aria engine.

The choice of engines depends on several factors including the expected number of rows in the result set.

You can read the create_tmp_table function in the MySQL or MariaDB source for details.

The setting of the 'default_storage_engine' variable does NOT matter in MySQL or MariaDB 5.5 for these temporary tables.

The error says that a 'Incorrect key file for table' error occurred on the temporary aria table when executing the query.

This error type encompasses a lot of possible causes.

For the aria storage engine, there are about 16 places where HA_ERR_CRASHED is set.

Note that HA_ERR_CRASHED gets translated to ER_NOT_KEYFILE which is the error that your app sees.

When I run your query on your schema with an empty table, the query execution uses a heap storage engine.

Therefore, I can not reproduce the problem with the information you have given me.

Here is what I would do to track down this problem.

First, get the problem reproduced on a debug machine by copying the MySQL data directory to it.

Then, we can use a debug builds of MariaDB to find which one of 16 places the HA_ERR_CRASHES error is asserted.

Once we know that, we can theorize about possible causes.

This is how we tracked down a similar problem with randgen that was shown to be caused by no space in the file system.

Since you eliminated this case, the problem that you see must be caused by one of the other 15 possibilities.