Mysql – INFORMATION_SCHEMA Selection Error

MySQLUbuntu

I'm trying to select data from information_schema but I'm getting the following error. How can I fix this?

mysql> SELECT * FROM information_schema.tables ;
ERROR 1018 (HY000): Can't read dir of '.' (errno: 13)

Best Answer

I find that error particularly disturbing because MySQL introduced INFORMATION_SCHEMA with the introduction of MySQL 5.0. @gbn's answer shows that this bug

  • goes back to MySQL 5.0.16
  • was reported on ANY operating having this
  • was based on the error happening in the IBM AIX 5.3 ML2 Operating System
  • was closed 2006-01-26

MySQL's definition of a database is simply a subfolder under datadir.

Now, here is why I find the error particularly disturbing: The INFORMATION_SCHEMA database is not supposed to be a manifested folder under datadir.

For example

  • datadir is /var/lib/mysql
  • You have two databases: db1 and db2

Go to the OS and run the following:

cd /var/lib/mysql
ls -l

You will see several folders:

  • mysql
  • db1
  • db2
  • test (since MySQL default installs a test database)
  • . (current dir)
  • .. (parent dir)

In mysql, when you do SHOW DATABASES;, you should not see . and ... The source code would have made sure of that. Now, where is INFORMATION_SCHEMA? Guess what? All tables in the INFORMATION_SCHEMA are temp tables and use the memory storage engine. Also note that you do not a see a folder called INFORMATION_SCHEMA. Now, check out the definition of INFORMATON_SCHEMA.TABLES:

mysql> use information_schema
Database changed
mysql> show create table tables\G
*************************** 1. row ***************************
       Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.05 sec)

I lay full blame for this error on the source code because just as mysql bypasses . and .. as special case folders that are not to be considered databases, INFORMATION_SCHEMA is supposed to be considered a s special case folder as well, a special case folder that does not have a manifested file under the datadir.

I would highly recommend just upgrading MySQL to the latest version because, as @gbn found, there was a bug report but it is marked as closed. This could only happen

  • if you are still running a very old version of mysql
  • this situation was not properly handled in the source code for the Ubuntu OS

@gbn gets a +1 from me for finding the bug report which helped me look a little deeper

Related Question