Wow what a hectic month - just logged on and noticed I haven’t updated the site in a month! I have a part done entry on triggers that I haven’t had time to finish yet, look out for that in a couple of days. Work has been hectic, a lot of work going in to the MySQL Module of NORAD (a system/database monitoring tool that we distribute.. But that’s a story for another day), which I’m responsible for building the collections / window definitions for at the moment.

One of the windows I was building today was for “Open Tables” - sourced from SHOW OPEN TABLES; - yet I wasn’t entirely sure on what all of the output meant. Specifically “Name_locked”. The documentation doesn’t comment on this at all (the documentation for SHOW OPEN TABLES is particularly poor, even noting a “Comment” field that simply doesn’t exist (on 4.1 at least). “In_use” was not entirely clear either at first glance, “Does it mean being …

Wow what a hectic month - just logged on and noticed I haven’t updated the site in a month! I have a part done entry on triggers that I haven’t had time to finish yet, look out for that in a couple of days. Work has been hectic, a lot of work going in to the MySQL Module of NORAD (a system/database monitoring tool that we distribute.. But that’s a story for another day), which I’m responsible for building the collections / window definitions for at the moment.

One of the windows I was building today was for “Open Tables” - sourced from SHOW OPEN TABLES; - yet I wasn’t entirely sure on what all of the output meant. Specifically “Name_locked”. The documentation doesn’t comment on this at all (the documentation for SHOW OPEN TABLES is particularly poor, even noting a “Comment” field that simply doesn’t exist (on 4.1 at least). “In_use” was not entirely clear either at first glance, “Does it mean being …

I mentioned “fuzzy foreign key” matches in my post the other day, and thought that I’d give it a crack tonight. The aim of the fuzzy match is to try and find relationships when a) They are InnoDB and haven’t been created or b) Foreign keys are not avilable to the storage engine that is being used.

Most DBA’s will know their schemas pretty well, this however should be pretty handy for consultants or anyone starting fresh at a new job / on a new database etc.

It should be noted that this will only “fuzzy” match against a schema that follows strict naming conventions - Where the tablename and “id” is used for the primary key column, and the same name is used for the foreign key in the child table. For instance, given the following example tables:

mysql> desc company;
+————–+————–+——+—–+———+—————-+
| Field        | Type         | Null | Key | Default | Extra …

I mentioned “fuzzy foreign key” matches in my post the other day, and thought that I’d give it a crack tonight. The aim of the fuzzy match is to try and find relationships when a) They are InnoDB and haven’t been created or b) Foreign keys are not avilable to the storage engine that is being used.

Most DBA’s will know their schemas pretty well, this however should be pretty handy for consultants or anyone starting fresh at a new job / on a new database etc.

It should be noted that this will only “fuzzy” match against a schema that follows strict naming conventions - Where the tablename and “id” is used for the primary key column, and the same name is used for the foreign key in the child table. For instance, given the following example tables:

<code>&lt;blockquote&gt;<br />
mysql&gt; desc company;<br /> …

Arjen Lentz (”Community Relations Manager” for MySQL AB) posted “The MySQL 5.0 Beta Challenge” on his blog today - so in the spirit of community I decided to give it a crack, by putting together some posts on the new features of MySQL 5.0 up here to my humble blog. Who knows - I may win a mug! Then I can have a list of ALL the people to bug over at MySQL AB!

I’m a DBA at heart, so whilst I’ll take a look at the new features that are available for developers, I’m going to start from the DBA tack, and take a look at INFORMATION_SCHEMA.

So what is the INFORMATION_SCHEMA? Those that have used other RDBMS’ in the past will recognize it as MySQL’s “Data Dictionary”. An RDBMS Data Dictionary is a collection of tables or views (that appear as tables), that utilize the database metadata to give easy access to information about the …

Arjen Lentz (”Community Relations Manager” for MySQL AB) posted “The MySQL 5.0 Beta Challenge” on his blog today - so in the spirit of community I decided to give it a crack, by putting together some posts on the new features of MySQL 5.0 up here to my humble blog. Who knows - I may win a mug! Then I can have a list of ALL the people to bug over at MySQL AB!

I’m a DBA at heart, so whilst I’ll take a look at the new features that are available for developers, I’m going to start from the DBA tack, and take a look at INFORMATION_SCHEMA.

So what is the INFORMATION_SCHEMA? Those that have used other RDBMS’ in the past will recognize it as MySQL’s “Data Dictionary”. An RDBMS Data Dictionary is a collection of tables or views (that appear as tables), that utilize the database metadata to give easy access to information about the …