Problem of the day, DESC gives error.

I hit this interesting problem last night on 5.0.51a.

mysql> use information_schema;
mysql> desc routines;
ERROR 1 (HY000): Can't create/write to file '/home/tmp/#sql_fea_1.MYD' (Errcode: 24)
mysql> show create table routinesG
*************************** 1. row ***************************
       Table: ROUTINES
Create Table: CREATE TEMPORARY TABLE `ROUTINES` (
  `SPECIFIC_NAME` varchar(64) NOT NULL default '',
  `ROUTINE_CATALOG` varchar(512) default NULL,
  `ROUTINE_SCHEMA` varchar(64) NOT NULL default '',
  `ROUTINE_NAME` varchar(64) NOT NULL default '',
  `ROUTINE_TYPE` varchar(9) NOT NULL default '',
  `DTD_IDENTIFIER` varchar(64) default NULL,
  `ROUTINE_BODY` varchar(8) NOT NULL default '',
  `ROUTINE_DEFINITION` longtext,
  `EXTERNAL_NAME` varchar(64) default NULL,
  `EXTERNAL_LANGUAGE` varchar(64) default NULL,
  `PARAMETER_STYLE` varchar(8) NOT NULL default '',
  `IS_DETERMINISTIC` varchar(3) NOT NULL default '',
  `SQL_DATA_ACCESS` varchar(64) NOT NULL default '',
  `SQL_PATH` varchar(64) default NULL,
  `SECURITY_TYPE` varchar(7) NOT NULL default '',
  `CREATED` datetime NOT NULL default '0000-00-00 00:00:00',
  `LAST_ALTERED` datetime NOT NULL default '0000-00-00 00:00:00',
  `SQL_MODE` longtext NOT NULL,
  `ROUTINE_COMMENT` varchar(64) NOT NULL default '',
  `DEFINER` varchar(77) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I tried in on my MySQL 5.1 server without incident. In a few minutes of thinking about it, I was unable to determine the problem. Anybody care to guess?

Comments

Justin Swanhart says

January 26, 2010 at 4:58 pm

Error code 24 is ‘too many open files’
Baron says

January 26, 2010 at 9:44 pm

[baron@kanka ~]$ perror 24
OS error code 24: Too many open files

I’d strace it and see what’s going on.
Isotopp says

January 27, 2010 at 4:41 am

perror 24 is, as said above, too many open files. After a FLUSH TABLES it is likely to work again.
Isotopp says

January 27, 2010 at 4:44 am

The other question is why a ‘desc’ would want to write to a MYD file.

I guess table conversion: The desc command is about to write to a temp table in order to materialize (here: to generate in the first place) the result set. The resulting table is for some reason not representable as a MEMORY table (i.e. it contains a TEXT or BLOB), forcing a write to disk as MyISAM. This then fails with the above error 24.