There are those that are very adamant about letting people know that using INFORMATION_SCHEMA can crash your database. For example, in making changes to many tables at once Baron writes: “querying the INFORMATION_SCHEMA database on MySQL can completely lock a busy server for a long time. It can even crash it. It is very dangerous.” Though Baron is telling the truth here, he left out one extremely important piece of information: you can actually figure out how dangerous your INFORMATION_SCHEMA query will be, ahead of time, using EXPLAIN.

Part 2 of "Understanding How MySQL Works by Understanding Metadata", presented by Sheeri K. Cabral (The Pythian Group) and Patrick Galbraith (Lycos Inc.). This was a 3-hour tutorial. Part 2 is at http://www.youtube.com/watch?v=C_Uv_4I9gus The PDF of the slides can be found at http://technocation.org/files/doc/2009_04_Understanding.pdf. From the official abstract at http://www.mysqlconf.com/mysql2009/public/schedule/detail/5682: We have spent countless hours researching over 1,000 pieces of metadata. In the process, we have learned a lot about how MySQL works, and realized that it was a pretty good learning method. Examples: Understanding the query_cache% system variables and Qcache% status variables helps us learn about the query cache—what it is, when it is used, how to examine query cache efficiency, how to tune the query cache. This relates to the GLOBAL_VARIABLES and GLOBAL_STATUS system  []

Part 1 of "Understanding How MySQL Works by Understanding Metadata", presented by Sheeri K. Cabral (The Pythian Group) and Patrick Galbraith (Lycos Inc.). This was a 3-hour tutorial. Part 2 is at http://www.youtube.com/watch?v=V3IBVsYGdtA The PDF of the slides can be found at http://technocation.org/files/doc/2009_04_Understanding.pdf. From the official abstract at http://www.mysqlconf.com/mysql2009/public/schedule/detail/5682: We have spent countless hours researching over 1,000 pieces of metadata. In the process, we have learned a lot about how MySQL works, and realized that it was a pretty good learning method. Examples: Understanding the query_cache% system variables and Qcache% status variables helps us learn about the query cache—what it is, when it is used, how to examine query cache efficiency, how to tune the query cache. This relates to the GLOBAL_VARIABLES and GLOBAL_STATUS system  []

Presented at the 2009 MySQL Camp: Grand Tour of the Information Schema and its Applications by Roland Bouman Description: The following topics will be covered: - Introduction of the information schema and its components - How to generate WIKI documentation from the information schema - Generating code (triggers, SPs) to maintain a rich history database - Creating a stored procedure to check violated foreign key constraints - Creating a stored procedure to create federated tables

What's new, in a nutshell: http://dev.mysql.com/doc/refman/5.1/en/mysql-nutshell.html Release notes: http://dev.mysql.com/doc/refman/5.1/en/news-5-1-x.html (In the video, it's the page entitled "Changes in release 5.1.x"). And yes, very early on (at about 2 minutes in) I talk about my take on Monty's controversial post at http://monty-says.blogspot.com/2008/11/oops-we-did-it-again-mysql-51-released.html The slides can be downloaded as a PDF at http://technocation.org/files/doc/2008_12_New51.pdf or in Open Office presentation format at http://technocation.org/files/doc/2008_12_New51.odp

As I putter around the MySQL "INFORMATION_SCHEMA", I am finding lots of undocumented behavior for fields that should be straightforward. For example, the "VIEWS" table holds information about views, and the "VIEW_DEFINITION" field contains the view definition, right?Well, when I was looking at the "VIEW_DEFINITION" today, I noticed an odd thing. Even though I had permissions to see the view definition (as proven by the "SHOW CREATE VIEW

A friend asked for this, so I thought it'd be helpful:All tables with no primary key:use INFORMATION_SCHEMA;select CONCAT(t.table_name,".",t.table_schema) as tbl,from TABLES AS t LEFT JOIN KEY_COLUMN_USAGE AS c ON (t.TABLE_NAME=c.TABLE_NAME AND c.CONSTRAINT_SCHEMA=t.TABLE_SCHEMA AND constraint_name='PRIMARY') WHERE t.table_schema!="information_schema" AND constraint_name IS NULL;All tables and their primary keys, if exist:use INFORMATION_SCHEMA

When using Oracle, the data dictionary provides us with tons of tables and views, allowing us to fetch information about pretty much anything within the database. We do have information like that in MySQL 5.0 (and up) in the "information_schema" database, but it's scattered through several different tables.Sometimes a client asks us to change the datatype of a column, but forgets to mention the schema name, and sometimes even the table name. As you can imagine, having this kind of information is vital to locate the object and perform the requested action. This kind of behaviour must be related to Murphy's Law.In any case, I'd like to share with you a simple stored procedure that has helped us a lot in the past.

In this article, you'll learn how to write an information schema plug-in that can access some of the internals of the MySQL server. For this particular purpose, we will focus on a plug-in that reports all the SAVEPOINTs available in the current session. This MYSQL_SAVEPOINTS plug-in may be of some value when debugging scripts and stored routines that rely on complex scenarios using transactions and savepoints.

MySQL 5.1 offers an extremely useful feature called information schema plug-ins. This feature allows dynamic runtime loading of a shared library into the MySQL server to implement a table in the information_schema database. The SQL standard (ISO/IEC 9075-11:2003) allows database implementations to extend the information_schema. MySQL 5.1 transfers the possibility to do this directly to privileged database users so they can extend the information_schema themselves, in any way they see fit. In this article, we will demonstrate how to create a minimal "Hello, World!" MySQL information schema plugin. In a forthcoming article, we'll demonstrate how information schema plugins may be used to report some of the server's internals such as the contents of the query cache, session level objects such as the currently defined TEMPORARY tables, user-defined variables and SAVEPOINTs.