Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted May 29, 2012

Understanding the MySQL Information Schema Database

By Rob Gravelle

The INFORMATION_SCHEMA database is where each MySQL instance stores information about all the other databases that the MySQL server maintains.  Also sometimes referred to as the data dictionary and system catalog, it's the ideal place to lookup information such as the name of a database or table, the data type of a column, or access privileges. Today's article will provide an overview of the INFORMATION_SCHEMA database as well as some practical examples of its many uses.

The METADATA Databases

As of MySQL 5, the information_schema is now listed in the database list when you execute the show databases command:

mysql> show databases; 

+--------------------+ 
 | Database           | 
 +--------------------+ 
 | business_contacts  |
 | company            |
 | customers          |
 | information_schema |
 | mysql              |
 | performance_schema |
 | sonar              |
 +--------------------+ 

You'll also notice the mysql and performance_schema databases, which also store database metadata.

Likewise, issuing a show tables command on the information_schema database will give you a list of all its tables:

mysql> use information_schema;
 mysql> show tables;
 
 +---------------------------------------+
 | Tables_in_information_schema          |
 +---------------------------------------+
 | CHARACTER_SETS                        |
 | COLLATIONS                            |
 | COLLATION_CHARACTER_SET_APPLICABILITY |
 | COLUMNS                               |
 | COLUMN_PRIVILEGES                     |
 | KEY_COLUMN_USAGE                      |
 | PROFILING                             |
 | ROUTINES                              |
 | SCHEMATA                              |
 | SCHEMA_PRIVILEGES                     |
 | STATISTICS                            |
 | TABLES                                |
 | TABLE_CONSTRAINTS                     |
 | TABLE_PRIVILEGES                      |
 | TRIGGERS                              |
 | USER_PRIVILEGES                       |
 | VIEWS                                 |
 +---------------------------------------+

In actuality, these tables are read-only views.  Hence, you cannot change its structure or modify its data. Their sole purpose is to provide information about the database system. In fact, all tables in the INFORMATION_SCHEMA database are stored directly in memory as MEMORY storage engine tables. Thus, when mysqld is shutdown, all information_schema tables are dropped. Then, when MySQL (mysqld) is restarted, all information_schema tables are recreated as TEMPORARY tables and repopulated with metadata for every table. 

Removing the Metadata Databases from Query Results

Most of the time, when querying the INFORMATION_SCHEMA database, you'll only be interested in your own databases, not the metadata ones.  To remove them, include this filter in the where clause:

AND information_schema.TABLES.table_schema 

    NOT IN('information_schema','mysql','performance_schema');

It's present in most of the following examples…

Example #1: List All Tables without Primary Key

Here's a query that uses the TABLE_CONSTRAINTS view to check for table names whose constraint_name field is null:

 
SELECT CONCAT(t.table_schema,'.',t.table_name) as table_name
FROM information_schema.TABLES t
LEFT JOIN information_schema.TABLE_CONSTRAINTS tc
ON t.table_schema = tc.table_schema
AND t.table_name = tc.table_name
AND tc.constraint_type = 'PRIMARY KEY'
WHERE tc.constraint_name IS NULL
AND t.table_type = 'BASE TABLE'
AND t.table_schema not in('information_schema','mysql','performance_schema');
 
 
+----------------------------------+
 | table_name                       | 
 +----------------------------------+
| customers.orders                 |
| business_contacts.company_info   |
 | sonar.schema_migrations          |
| sonar.resource_index             |
| sonar.groups_users               |
| sonar.duplications_index         |
| sonar.characteristic_edges       |
| sonar.action_plans_reviews       |
 +----------------------------------+
 

Example #2: Display Tables with More Than a Given Number of Rows

Sometimes it's useful to see which tables' size exceeds a given number of rows.  That may signal the time to archive some of the older data for performance reasons.  The following SQL statement returns all the tables in your user databases that contain more than one thousand rows:

SELECT CONCAT(table_schema,'.',table_name) as table_name,
       table_rows
FROM   information_schema.tables 
 WHERE table_rows > 1000
 AND table_schema not in('information_schema','mysql','performance_schema');
ORDER BY table_rows desc;
 
+----------------------------------+------------+ 
 | table_name                       | table_rows | 
 +----------------------------------+------------+ 
 | customers.orders                 | 2007       | 
 | customers.contact_info           | 1245       | 
 | customers.rewards_points         | 2147       | 
 | business_contacts.company_info   | 1340       | 
 | business_contacts.phone_numbers  | 1712       | 
 | sonar.project_measures           | 178618     |
| sonar.resource_index             | 110328     |
| sonar.rule_failures              | 40793      |
 +----------------------------------+------------+ 

For greater flexibility, this query can be placed in a stored procedure.  That way, you can provide the number of rows via an input parameter:

CREATE PROCEDURE `pr_display_tables_with_more_than_n_rows`
                 (IN `numberOfRows` BIGINT)
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT 'Displays tables with more than given number of rows.'
BEGIN
  SELECT CONCAT(table_schema,'.',table_name) as table_name,
         table_rows
  FROM   information_schema.tables 
  WHERE table_rows > 1000
  AND table_schema not in('information_schema','mysql','performance_schema');
  ORDER BY table_rows desc;
END

Example #3: Display Access Privileges

You can use the INFORMATION_SCHEMA to review users' privileges in order to help you decide which rights to grant or revoke. Moreover, the INFORMATION_SCHEMA database can provide views at varying levels of granularity.  While the SHOW PRIVILEGES command shows the system privileges the server supports, the INFORMATION_SCHEMA.SCHEMA_PRIVILEGES view lists which database users have been assigned those system privileges:

SELECT grantee, 
       privilege_type, 
       is_grantable
FROM   information_schema.schema_privileges
WHERE  table_schema = 'sonar';

+---------------------+-------------------------+--------------+
| grantee             | privilege_type          | is_grantable |
+---------------------+-------------------------+--------------+
| 'sonar'@'localhost' | SELECT                  | NO           |
| 'sonar'@'localhost' | INSERT                  | NO           |
| 'sonar'@'localhost' | UPDATE                  | NO           |
| 'sonar'@'localhost' | DELETE                  | NO           |
| 'sonar'@'localhost' | CREATE                  | NO           |
| 'sonar'@'localhost' | DROP                    | NO           |
| 'sonar'@'localhost' | REFERENCES              | NO           |
| 'sonar'@'localhost' | INDEX                   | NO           |
| 'sonar'@'localhost' | ALTER                   | NO           |
| 'sonar'@'localhost' | CREATE TEMPORARY TABLES | NO           |
| 'sonar'@'localhost' | LOCK TABLES             | NO           |
| 'sonar'@'localhost' | EXECUTE                 | NO           |
| 'sonar'@'localhost' | CREATE VIEW             | NO           |
| 'sonar'@'localhost' | SHOW VIEW               | NO           |
| 'sonar'@'localhost' | CREATE ROUTINE          | NO           |
| 'sonar'@'localhost' | ALTER ROUTINE           | NO           |
| 'sonar'@'localhost' | EVENT                   | NO           |
| 'sonar'@'localhost' | TRIGGER                 | NO           |
+---------------------+-------------------------+--------------+

Example #4: Find Long Running Queries

In version 5.1, MySQL added the pocesslist to the INFORMATION_SCHEMA database. It shows information on all the currently running processes:

Show processlist;

+----+----------+----------------+------+---------+------+-------+-------------------+
| Id | User     |Host            | db   | Command | Time | State | Info              |
+----+----------+----------------+------+---------+------+-------+-------------------+
| 1  | root     | localhost:1715 |      | Sleep   | 73   |       |                   |
| 2  | root     | localhost:1716 | test | Query   | 0    |       | show processlist  |
| 5  | sonar    | localhost:1835 | test | Sleep   | 2359 |       |                   |
+----+----------+----------------+------+---------+------+-------+-------------------+

We can query the processlist to find long running queries.  The Time field is in seconds, so we can fetch all the queries that are running for over10 minutes by comparing it to the expression of sixty seconds times ten:

SELECT * FROM information_schema.processlist 
WHERE COMMAND = 'Query'  
AND   time    > 60 * 10;

Conclusion

There are still many more excellent uses for INFORMATION_SCHEMA that will have to wait until another day.  Until then, you can run a SELECT * on any of the INFORMATION_SCHEMA views to see what information is contained therein.

See all articles by Rob Gravelle



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM