question about show databases;

[John Salerno]

Hi everyone. I just installed MySQL on my home PC and I tested out the
show databases query, and it lists two:

information_schema
bbdatabank

The second one is one I created myself, but I don't understand what the
first one is. It is not listed in my 'data' folder, but mysql and test
*are*, yet they aren't listed when I type the show command. Why is that?
How can I get them listed so that I can use them, and what is the
information_schema?

Thanks!
John

[Gordon Burditt]

>Hi everyone. I just installed MySQL on my home PC and I tested out the

show databases query, and it lists two:

information_schema
bbdatabank

The second one is one I created myself, but I don't understand what the
first one is. It is not listed in my 'data' folder, but mysql and test
*are*, yet they aren't listed when I type the show command. Why is that?
Unless you have the "show databases" privilege, you can only see
databases you have some privileges on.
How can I get them listed so that I can use them, and what is the
information_schema?

Use a username and a password with privileges to use them (e.g. the
root account set up when you installed MySQL).

information_schema is a database giving information on the databases
and stuff in them. Another way of getting a list of databases is
"select schema_name from information_schema.schemata". It is
automatically generated from the databases, so if you create or
delete tables or columns in them, it's automatically up to date.
On the other hand, it tends to be a bit slow if you list a whole
table.

Some of the information in information_schema is sensitive to privileges.
You may not see the rows for things you don't have access to.

Gordon L. Burditt

[John Salerno]

Gordon Burditt wrote:

Use a username and a password with privileges to use them (e.g. the
root account set up when you installed MySQL).

information_schema is a database giving information on the databases
and stuff in them. Another way of getting a list of databases is
"select schema_name from information_schema.schemata". It is
automatically generated from the databases, so if you create or
delete tables or columns in them, it's automatically up to date.
On the other hand, it tends to be a bit slow if you list a whole
table.

Thank you! I realized that I wasn't seeing mysql or test because I
wasn't signed in as the root account. As for the other question, thanks
for explaining. I'll just leave it alone! :)

[Gordon Burditt]

>> information_schema is a database giving information on the databases

and stuff in them. Another way of getting a list of databases is
"select schema_name from information_schema.schemata". It is
automatically generated from the databases, so if you create or
delete tables or columns in them, it's automatically up to date.
On the other hand, it tends to be a bit slow if you list a whole
table.

Thank you! I realized that I wasn't seeing mysql or test because I
wasn't signed in as the root account. As for the other question, thanks
for explaining. I'll just leave it alone! :)

information_schema does have its uses. For example, it's easy to
list all the tables with a 'stamp' column in them. And information_schema
is a bit less MySQL-specific than various "SHOW" commands, and you
can put a WHERE clause on the queries.

It *is* a really good idea to leave information_schema alone when
using mysqldump or restoring the output of mysqldump.

Gordon L. Burditt