INFORMATION_SCHEMA Tables

INFORMATION_SCHEMA provides access to database metadata. Metadata is data about the data, such as the name of a database or table, the data type of a column, or access privileges. Other terms that sometimes are used for this information are data dictionary and system catalog. INFORMATION_SCHEMA is the information database, the place that stores information about all the other databases that the MySQL server maintains. Inside INFORMATION_SCHEMA there are several read-only tables. They are actually views, not base tables, so there are no files associated with them. In effect, we have a database named INFORMATION_SCHEMA, although the server does not create a database directory with that name. It is possible to select INFORMATION_SCHEMA as the default database with a USE statement, but it is possible only to read the contents of tables. You cannot insert into them, update them, or delete from them. Example of a statement that retrieves information from INFORMATION_SCHEMA: mysql> SELECT table_name, table_type, engine FROM information_schema.tables WHERE table_schema = 'db5' ORDER BY table_name DESC;   The statement requests a list of all the tables in database db5, in reverse alphabetic order, showing just three pieces of information: the name of the table, its type, and its storage engine.

 

SCHEMATA Table:

This table provides information about databases.

 

TABLES Table:

This table provides information about tables in databases.

 

STATISTICS Table:

This table provides information about table indexes.

 

USER_PRIVILEGES Table:

This table provides information about global privileges. This information comes from the mysql.user grant table.

 

SCHEMA_PRIVILEGES Table:

This table provides information about schema (database) privileges. This information comes from the mysql.db grant table.

 

TABLE_PRIVILEGES Table:

This table provides information about table privileges. This information comes from the mysql.tables_priv grant table.

 

COLUMN_PRIVILEGES Table:

This table provides information about column privileges. This information comes from the mysql.columns_priv grant table.

 

TABLE_CONSTRAINTS Table:

This table describes which tables have constraints.

 

REFERENTIAL_CONSTRAINTS Table:

This table provides information about foreign keys. This table was added in MySQL 5.1.10.

 

ROUTINES Table:

This table provides information about stored routines (both procedures and functions). his information comes from the mysql.proc table.

 

VIEWS Table:

This table provides information about views in databases. You must have the SHOW VIEW privilege to access this table.

 

TRIGGERS Table:

This table provides information about triggers. You must have the SUPER privilege to access this table.

 

PROFILING Table:

This table provides statement profiling information. Its contents correspond to the information produced by the SHOW PROFILES and SHOW PROFILE statements.  The table is empty unless the profiling session variable is set to 1.

 

ENGINES Table:

This table provides information about storage engines.

 

PARTITIONS Table:

This table provides information about table partitions.

 

GLOBAL_STATUS and SESSION_STATUS Tables:

This tables provide information about server status variables. Their contents correspond to the information produced by the SHOW GLOBAL STATUS and SHOW SESSION STATUS statements. This tables were added in MySQL 5.1.12.

 

GLOBAL_VARIABLES and SESSION_VARIABLES Tables:

This tables provide information about server status variables. Their contents correspond to the information produced by the SHOW GLOBAL VARIABLES and SHOW SESSION VARIABLES statements. This tables were added in MySQL 5.1.12.

 

PROCESSLIST Table:

The PROCESSLIST table provides information about which threads are running.

 

EVENTS Table:

The EVENTS table provides information about scheduled events. MySQL Profiler: The SQL Profiler is built into the database server and can be dynamically enabled/disabled via the MySQL client utility. To begin profiling one or more SQL queries, simply issue the following command: mysql> set profiling=1; Two things happen once you issue this command. First, any query you issue from this point on will be traced by the server with various performance diagnostics being created and attached to each distinct query. Second, a memory table named profiling is created in the INFORMATION_SCHEMA database for your particular session (not viewable by any other MySQL session) that stores all the SQL diagnostic results. This table remains persistent until you disconnect from MySQL at which point it is destroyed. Now, simply execute a SQL query: mysql> SELECT COUNT(*) FROM t1 WHERE broker_id = 2; +----------+ | count(*) | +----------+ |      200  | +----------+   Once the query completes, you can issue the following command to view the SQL profiles that have currently been stored for you: mysql> show profiles; +----------+-----------------+-----------------------------------------------------------------------+ | Query_ID | Duration   | Query                                                                            | +----------+-----------------+------------------------------------------------------------------------+ |           0 | 0.00007300 | set profiling=1                                                                | |           1 | 0.00044700 | SELECT COUNT(*) FROM t1 WHERE broker_id = 2   | +----------+-----------------+------------------------------------------------------------------------+   You get a quick summary of all your captured SQL plus the total duration that the query took to complete. To get the same diagnostic info, you can also query the memory table that holds your statistical information: mysql> SELECT SUM(duration) FROM information_schema.profiling WHERE query_id = 1; +-------------------+ | sum(duration) | +-------------------+ |      0.000447   | +-------------------+   You can view more detailed diagnostic info about one or more queries that you've profiled. The most basic command is one that lists the steps a profiled query went through to satisfy your SQL request, along with each step's time: mysql> show profile for query 1; +------------------------+----------------+ | Status                    | Duration     | +------------------------+----------------+ | (initialization)        | 0.00006300 | | Opening tables     | 0.00001400 | | System lock          | 0.00000600 | | Table lock              | 0.00001000 | | init                         | 0.00002200 | | optimizing              | 0.00001100 | | statistics                | 0.00009300 | | preparing               | 0.00001700 | | executing               | 0.00000700 | | Sending data         | 0.00016800 | | end                        | 0.00000700 | | query end              | 0.00000500 | | freeing items          | 0.00001200 | | closing tables         | 0.00000800 | | logging slow query | 0.00000400 | +-------------------------+-----------------+

 

Event Scheduler:

The MySQL Event Scheduler manages the scheduling and execution of events: Tasks that run according to schedule. Event support was added in MySQL 5.1.6. MySQL Events are tasks that run according to a schedule. Therefore, we sometimes refer to them as scheduled events. When you create an event, you are creating a named database object containing one or more SQL statements to be executed at one or more regular intervals, beginning and ending at a specific date and time. Conceptually, this is similar to the idea of the Unix crontab (also known as a “cron job”) or the Windows Task Scheduler. MySQL Events have the following major features and properties:

• In MySQL 5.1.12 and later, an event is uniquely identified by its name and the schema to which it is assigned.
• An event performs a specific action according to a schedule. This action consists of an SQL statement, which can be a compound statement in a BEGIN ... END block if desired
• An event's timing can be either one-time or recurrent. A one-time event executes one time only. A recurrent event repeats its action at a regular interval.

Event Scheduler Configuration:

Events are executed by a special event scheduler thread; when we refer to the Event Scheduler, we actually refer to this thread. When running, the event scheduler thread and its current state can be seen by users having the PROCESS privilege in the output of SHOW PROCESSLIST. The global event_scheduler system variable determines whether the Event Scheduler is enabled and running on the server. Beginning with MySQL 5.1.12, it has one of these 3 values, which affect event scheduling as described here:

• OFF: The Event Scheduler is stopped. The event scheduler thread does not run, is not shown in the output of SHOW PROCESSLIST, and no scheduled events are executed. OFF is the default value for event_scheduler.
• ON: The Event Scheduler is started; the event scheduler thread runs and executes all scheduled events. When the Event Scheduler is ON, the event scheduler thread is listed in the output of SHOW PROCESSLIST as a daemon process.
• DISABLED: This value renders the Event Scheduler nonoperational. When the Event Scheduler is DISABLED, the event scheduler thread does not run (and so does not appear in the output of SHOW PROCESSLIST). In addition, the Event Scheduler state cannot be changed at runtime.

It is possible to set the Event Scheduler to DISABLED only at server startup. If event_scheduler is ON or OFF, you cannot set it to DISABLED at runtime. Also, if the Event Scheduler is set to DISABLED at startup, you cannot change the value of event_scheduler at runtime. mysql > SET GLOBAL event_scheduler = ON; To disable the event scheduler, use one of the following two methods:

• As a command-line option when starting the server:

--event-scheduler=DISABLED

• In the server configuration file (my.cnf, or my.ini on Windows systems), include the line where it will be read by the server

event_scheduler=DISABLED   Note: You can issue event-manipulation statements when event_scheduler is set to DISABLED. No warnings or errors are generated in such cases (provided that the statements are themselves valid). However, scheduled events cannot execute until this variable is set to ON (or 1). Once this has been done, the event scheduler thread executes all events whose scheduling conditions are satisfied. Beginning with MySQL 5.1.17, starting the MySQL server with the --skip-grant-tables option causes event_scheduler to be set to DISABLED, overriding any other value set either on the command line or in the my.cnf or my.ini file (Bug#26807). MySQL 5.1.6 and later provides an EVENTS table in the INFORMATION_SCHEMA database. This table can be queried to obtain information about scheduled events which have been defined on the server. Creating Events: CREATE EVENT <event_name> ON SCHEDULE [AT] <schedule> [EVERY] <interval> DO <event_body>;   Example: CREATE EVENT myevent ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO UPDATE myschema.mytable SET mycol = mycol + 1;   The previous statement creates an event named myevent. This event executes once—one hour following its creation—by running an SQL statement that increments the value of the myschema.mytable table's mycol column by 1. The ON SCHEDULE clause determines when, how often, and for how long the event_body defined for the event repeats. This clause takes one of two forms:

• AT timestamp is used for a one-time event. It specifies that the event executes one time only at the date and time given by timestamp, which must include both the date and time, or must be an expression that resolves to a datetime value.
• To repeat actions at a regular interval, use an EVERY clause. The EVERY keyword is followed by an interval as described in the previous discussion of the AT keyword. (+ INTERVAL is not used with EVERY.) For example, EVERY 6 WEEK means “every six weeks”.

CREATE EVENT e_hourly ON SCHEDULE EVERY 1 HOUR COMMENT 'Clears out sessions table each hour.' DO DELETE FROM site_activity.sessions;   More complex compound statements, such as those used in stored routines, are possible in an event. delimiter |   CREATE EVENT e ON SCHEDULE EVERY 5 SECOND DO BEGIN DECLARE v INTEGER; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;   SET v = 0;   WHILE v < 5 DO INSERT INTO t1 VALUES (0); UPDATE t2 SET s1 = s1 + 1; SET v = v + 1; END WHILE; END |   delimiter ;   There is no way to pass parameters directly to or from events; however, it is possible to invoke a stored routine with parameters within an event: CREATE EVENT e_call_myproc ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY DO CALL myproc(5, 27);   Alter Event: Examples: ALTER EVENT myevent ON SCHEDULE EVERY 12 HOUR;   ALTER TABLE myevent AT CURRENT_TIMESTAMP + INTERVAL 1 DAY DO TRUNCATE TABLE myschema.mytable;   To disable myevent, use this ALTER EVENT statement:             ALTER EVENT myevent DISABLE;             ALTER EVENT myevent RENAME TO yourevent;   You can also move an event to a different database using ALTER EVENT ... RENAME TO ... and db_name.event_name notation, as shown here:             ALTER EVENT olddb.myevent RENAME TO newdb.myevent;

Drop Event:

DROP EVENT [IF EXISTS] event_name;   Check Out Our Related Courses Sql Tutorials Oracle Pl SQL Tutorials Top 50 Sql Server Interview Questions SQL Server T-SQL Training In Houston