Ronald Bradford
MySQL Expert

MySQL Expert Ronald Bradford shares valuable input in MySQL Performance Tuning, MySQL Scalability and general MySQL Help from his two decades of working with MySQL, Oracle, Ingres and development technologies.

« Speaking at MySQL UC 2010
Be sure to know your my.cnf [sections] »

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 routines\G
*************************** 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?

This entry was posted on Tuesday, January 26th, 2010 at 4:03 pm and is filed under Databases, MySQL, Professional, Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

4 Responses to “Problem of the day, DESC gives error.”

  1. Justin Swanhart says:
    January 26, 2010 at 4:58 pm

    Error code 24 is ‘too many open files’

  2. 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.

  3. 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.

  4. 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.

Leave a Reply