INFORMATION_SCHEMA Selection Error

Question

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)

Answer 1

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

Answer 2

Permissions error, reported as an (old) MySQL bug

Check out the permissions bit in the MySQL install docs

Answer 3

On MySQL Version 5.5 the NFORMATION_SCHEMA directory/folder exists; I am assuming it does on other versions. It's not controlled in the setting of my.ini or my.cnf as datadir=whatever; it appears to be predefined in the install path of MySQL and is read when MySQL first loads on startup.

There is no my.ini or my.cnf setting for placing the location of this schema. This appears to apply to both the Unix/Linux & Windows versions of MySQL 5.5 and may go back to when this schema was first introduced. I went from 4.1 to 5.5 on both Windows and Unix boxes, so I don't know about the versions in between.

I forgot to mention the folder in my case is not the name of the schema but just another folder called simply MySQL with the following catalog/XML file named information_schema.mbp in it containing the below data as you may see in your MySQL Admin client. I guess this is the copy it loads into memory on startup. I see my version appears to be longer than yours and mine also has a user config section.

If you didn't back this up with the regular datadir or sqldump on my systems you would loose your user config setup and permissions. There are other similar files containing the actual data for the tables. I guess the .mbp extension is used to indicate a memory engine backup or copy. I knew it had to be stored somewhere and there had to be a way to access it.

I did import my old user config and permissions when I upgraded to 5.5 and they were sent to this datbase by the server and not saved as they were in the past. So if and when you are using my version you will need to backup this file or you will loose all your users and permissions. I guess that's what this built-in database is for in my case.

<?xml version="1.0"?>
<backup_profile>
  <profile_name>information_schema</profile_name>
  <last_used></last_used>
  <options>288</options>
  <backup_type>0</backup_type>
  <tables>
    <table>
      <name>CHARACTER_SETS</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>COLLATIONS</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>COLLATION_CHARACTER_SET_APPLICABILITY</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>COLUMNS</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>COLUMN_PRIVILEGES</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>ENGINES</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>EVENTS</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>FILES</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>GLOBAL_STATUS</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>GLOBAL_VARIABLES</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>KEY_COLUMN_USAGE</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>PARAMETERS</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>PARTITIONS</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>PLUGINS</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>PROCESSLIST</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>PROFILING</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>REFERENTIAL_CONSTRAINTS</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>ROUTINES</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>SCHEMATA</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>SCHEMA_PRIVILEGES</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>SESSION_STATUS</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>SESSION_VARIABLES</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>STATISTICS</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>TABLES</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>TABLESPACES</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>TABLE_CONSTRAINTS</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>TABLE_PRIVILEGES</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>TRIGGERS</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>USER_PRIVILEGES</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>VIEWS</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>INNODB_CMP_RESET</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>INNODB_TRX</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>INNODB_CMPMEM_RESET</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>INNODB_LOCK_WAITS</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>INNODB_CMPMEM</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>INNODB_CMP</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
    <table>
      <name>INNODB_LOCKS</name>
      <schema>information_schema</schema>
      <catalog>def</catalog>
    </table>
  </tables>
</backup_profile>

Sorry this is so big! I wanted to show you what it looked like and how to find it by name. hmm I have not tried it, however I wonder if you created another data file like this one in its present location and same file extension if it would load it on startup? That is if you really wanted to create a database this way.

Answer 4

I ran into the problem while creating temp tables for a stored procedure. I wanted to use the information_schema.tables view to search for my temp table to determine if I would attempt a create temp table statement or delete from the temp table itself. However, the information_schema.tables view never showed that it existed so my code written like below would not work correctly:

declare v_table_exists int;
select count(*) from information_schema.tables where table_name = 'MY_TEMP_TABLE';
if v_table_exists = 0 then
   create temporary table MY_TEMP_TABLE(...);
else
   delete from MY_TEMP_TABLE; // sets up for the next iteration within the same session
end if;

I discovered a work around that is satisfactory. I rewrote to above code like the following:

create temporary table if not exists MY_TEMP_TABLE(...);
delete from MY_TEMP_TABLE;

I found that this solution provided me the temporary storage within a session while working appropriately.