Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

4 Answers
4

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:

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

MySQL keeps making baby steps (which I like to call inchstones [instead of milestones]) from minor release to minor release and sending out a list of bugs fixed from the previous release. I doubt if Oracle will ever pick up the slack.
–
RolandoMySQLDBANov 18 '11 at 20:31

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. They were sent to this database by the server. Yet, it was 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 else you will loose all your users and permissions. I guess that's what this built-in database is for in my case.

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.

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.