Discovering FALCON Metadata in MySQL® v. 6.0.5-alpha

Fri, 11 Jul 2008 10:48:57 -0700

Introduction
MySQL® 6.0.5-alpha, the latest version of the 6.x branch of the Database Server, is available for download from the SUN|MySQL Web Site.

Metadata (data about the data) are very important, especially for software developers. In this article we will see what’s new in FALCON metadata handling doing some comparison with the old 6.0.4-alpha version.

New tables in the `information_schema` database
As you know, the source for metadata is the database `information_schema`. To start, let’s see which tables related with FALCON metadata are included in that database:

mysql> SELECT VERSION();
+—————————+
| VERSION() |
+—————————+
| 6.0.5-alpha-community-log |
+—————————+
1 row in set (0.01 sec)

mysql> USE `information_schema`;
Database changed

At a glance, we see that we have thirteen tables while in the old 6.0.4-alpha version the tables were only eleven:

mysql> SELECT VERSION();
+—————————+
| VERSION() |
+—————————+
| 6.0.4-alpha-community-log |
+—————————+
1 row in set (0.00 sec)

mysql> USE `information_schema`;
Database changed

The new and very useful tables are `FALCON_TABLESPACES` and `FALCON_TABLESPACE_FILES`.

If you played with version 6.0.4-alpha you probably felt the need of similar tables and, if no, you can see why they were important for me looking at bug #37030 .

Also note that the table `FALCON_DATABASE_IO` has been renamed in `FALCON_TABLESPACE_IO` so, for example, the query for calculating the Falcon Cache Hit Ratio by Tablespace becomes:

`FALCON_TABLESPACES` and `FALCON_TABLESPACE_FILES` in details
Let’s see the structure of the new tables:

As you can see a column named `COMMENT` is included in `information_schema`.`FALCON_TABLESPACES`, so, unlike version 6.0.4-alpha and previous, in version 6.0.5-alpha you can now specify a comment for your FALCON tablespaces.

Let’s create two FALCON tablespaces, `test1` and `test2`, and see how those two metadata tables are populated:

mysql> CREATE TABLESPACE `test1`
    -> ADD DATAFILE ‘test1.fts’
    -> EXTENT_SIZE = 1M
    -> INITIAL_SIZE = 10M
    -> COMMENT=’test1-comment’
    -> ENGINE = FALCON;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLESPACE `test2`
    -> ADD DATAFILE ‘test2.fts’
    -> EXTENT_SIZE = 1M
    -> INITIAL_SIZE = 10M
    -> COMMENT=’test2-comment’
    -> ENGINE = FALCON;
Query OK, 0 rows affected (0.00 sec)

I’d make you aware of the fact that when you create a FALCON tablespace sometimes you can get the following error: “ERROR 1044 (42000): Access denied for user ‘root’@'localhost’ to database ‘information_schema’”. Frankly, at the moment I don’t know why this error occurs. I am trying to create a reproducible bug report. If you experienced that error and you know why it sometimes occurs, please let me know. By the way, if that error will occur you can try closing your client and reopening it. That fix the error for me.

If we select over the table `FALCON_TABLESPACES` we get the following output:

As you can see, there are two rows for what we now call ’system-tablespaces’ - i.e. the tablespaces ‘FALCON_USER’ and ‘FALCON_TEMPORARY’ - and one row for each tablespace created with the CREATE TABLESPACE syntax (in our example ‘test1′ and ‘test2′).

By looking at that table you can also see that there are three types of FALCON tablespaces. One is the type ‘USER_DEFINED’ - that is the type of the tablespaces created by you using the CREATE TABLESPACE syntax - and two are system-related: ‘FALCON_USER’ and ‘FALCON_TEMPORARY’. We will talk about those ’system’ types later.

If we select over the table `FALCON_TABLESPACE_FILES` we get the following output:

As you see, for each FALCON tablespace the name of the file as well as an ID is shown.

`FALCON_TABLES` in details
The table `FALCON_TABLES`, already included in MySQL® 6.0.4-alpha and previous, lists the FALCON tables created on your server:

mysql> SELECT * FROM `information_schema`.`FALCON_TABLES`;
Empty set (0.00 sec)

In my system I get an empty set because there are no FALCON tables at the moment.

Let’s now try answering to the following question: “why do we have two system-related FALCON tablespaces (’FALCON_USER’ and ‘FALCON_TEMPORARY’)?”

The answer is quite simply. When you create a FALCON table you can specify a FALCON tablespace as a table option (CREATE TABLE … TABLESPACE) but if you don’t set a tablespace the FALCON table being created will use the default system tablespaces.

Let’s see this in action.

mysql> CREATE DATABASE `fdatabase`;
Query OK, 1 row affected (0.05 sec)

mysql> USE `fdatabase`;
Database changed

mysql> CREATE TABLE `fdatabase`.`ftable_generic`
-> ENGINE = FALCON
-> IGNORE AS SELECT * FROM `mysql`.`user`;
Query OK, 3 rows affected (0.64 sec)
Records: 3 Duplicates: 0 Warnings: 0

As you can see, we now have our FALCON table listed in `information_schema`.`FALCON_TABLES` and we can see that the tablespace used by the table just created is the system-related tablespace ‘FALCON_USER’.

Let’s create a temporary FALCON table (as before, without setting a tablespace) and see that the ‘FALCON_TEMPORARY’ tablespace is used:

mysql> CREATE TEMPORARY TABLE `fdatabase`.`ftable_generic_temporary`
-> ENGINE = FALCON
-> IGNORE AS SELECT * FROM `mysql`.`user`;
Query OK, 3 rows affected (0.19 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM `information_schema`.`FALCON_TABLES`;
+————-+—————-+———–+——————+—————-+

+————-+—————-+———–+——————+—————-+

+————-+—————-+———–+——————+—————-+

2 rows in set (0.00 sec)

If we drop the temporary table:

mysql> DROP TABLE `fdatabase`.`ftable_generic_temporary`;
Query OK, 0 rows affected (0.13 sec)

we have:

You can now guess what’s happen if you specify a FALCON tablespace in the CREATE TABLE syntax:

mysql> CREATE TABLE `fdatabase`.`ftable1_test1`
    -> TABLESPACE `test1`
    -> ENGINE = FALCON
    -> IGNORE AS SELECT * FROM `mysql`.`user`;
Query OK, 3 rows affected (0.16 sec)
Records: 3 Duplicates: 0 Warnings: 0

We can see above that the FALCON table `ftable1_test1` uses the tablespace ‘test1′.

Let’s create a second table using the tablespace ‘test1′:

mysql> CREATE TABLE `fdatabase`.`ftable2_test1`
    -> TABLESPACE `test1`
    -> ENGINE = FALCON
    -> IGNORE AS SELECT * FROM `mysql`.`user`;
Query OK, 3 rows affected (0.19 sec)
Records: 3 Duplicates: 0 Warnings: 0

Filtering FALCON Tables by TableSpaces
Now a common question is: “Which are the FALCON tables that are using a particular tablespace?”. Here’s the possible answer:

mysql> SELECT CONCAT(’`', `SCHEMA_NAME`, ‘`.`’, `TABLE_NAME`, ‘`’) FROM `information_schema`.`FALCON_TABLES` WHERE `TABLESPACE` = ‘test1′;
+——————————————————+
| CONCAT(’`', `SCHEMA_NAME`, ‘`.`’, `TABLE_NAME`, ‘`’) |
+——————————————————+
| `FDATABASE`.`FTABLE1_TEST1` |
| `FDATABASE`.`FTABLE2_TEST1` |
+——————————————————+
2 rows in set (0.00 sec)

Unfortunately, you should note that this method fails due to bug #35210.
In fact, let’s create a FALCON table with a strange name and see what happen:

mysql> CREATE TABLE `fdatabase`.`ftable_strange_name_#_!_test1`
    -> TABLESPACE `test1`
    -> ENGINE = FALCON
    -> IGNORE AS SELECT * FROM `mysql`.`user`;
Query OK, 3 rows affected (0.23 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM `information_schema`.`FALCON_TABLES`\G
*************************** 1. row ***************************
SCHEMA_NAME: FDATABASE
   TABLE_NAME: FTABLE_GENERIC
    PARTITION:
   TABLESPACE: FALCON_USER
INTERNAL_NAME: FTABLE_GENERIC
*************************** 2. row ***************************
SCHEMA_NAME: FDATABASE
   TABLE_NAME: FTABLE1_TEST1
    PARTITION:
   TABLESPACE: test1
INTERNAL_NAME: FTABLE1_TEST1
*************************** 3. row ***************************
SCHEMA_NAME: FDATABASE
   TABLE_NAME: FTABLE2_TEST1
    PARTITION:
   TABLESPACE: test1
INTERNAL_NAME: FTABLE2_TEST1
*************************** 4. row ***************************
SCHEMA_NAME: FDATABASE
   TABLE_NAME: FTABLE_STRANGE_NAME_@0023_@0021_TEST1
    PARTITION:
   TABLESPACE: test1
INTERNAL_NAME: FTABLE_STRANGE_NAME_@0023_@0021_TEST1
4 rows in set (0.00 sec)

The table `ftable_strange_name_#_!_test1` is stored in the FALCON metadata catalog with the name `FTABLE_STRANGE_NAME_@0023_@0021_TEST1` so the method above fails:

mysql> SELECT CONCAT(’`', `SCHEMA_NAME`, ‘`.`’, `TABLE_NAME`, ‘`’) FROM `information_schema`.`FALCON_TABLES` WHERE `TABLESPACE` = ‘test1′;
+——————————————————+
| CONCAT(’`', `SCHEMA_NAME`, ‘`.`’, `TABLE_NAME`, ‘`’) |
+——————————————————+
| `FDATABASE`.`FTABLE1_TEST1` |
| `FDATABASE`.`FTABLE2_TEST1` |
| `FDATABASE`.`FTABLE_STRANGE_NAME_@0023_@0021_TEST1` |
+——————————————————+
3 rows in set (0.00 sec)

We do not get the correct list of FALCON tables: the table `FDATABASE`.`FTABLE_STRANGE_NAME_@0023_@0021_TEST1` does not exist:

mysql> DESCRIBE `FDATABASE`.`FTABLE_STRANGE_NAME_@0023_@0021_TEST1`;
ERROR 1146 (42S02): Table ‘fdatabase.ftable_strange_name_@0023_@0021_test1′ does n’t exist

Indeed, you should note that it is not difficult to avoid such a problem at software-level. For example you can create a function to parse, using defined rules, the name `FTABLE_STRANGE_NAME_@0023_@0021_TEST1`. With this approach you have a function that returns the real name `ftable_strange_name_#_!_test1`.

Conclusion
It’s now time to close this article. New features of the FALCON metadata catalog have been (in part) explained.

For other interesting stuff, you can refer to the articles by Robin Schumacher available on the SUN|MySQL Web Site at dev.mysql.com.

alpha on SWiK

Xen 3.3 Proposed Feature Set

Xen 3.3 Proposed Feature Set

Index of /pub/mozilla.org/firefox/nightly/latest-trunk

Portable Network Graphics

ISO 3166-1 alpha-3 - Wikipedia, the free encyclopedia

Discovering FALCON Metadata in MySQL® v. 6.0.5-alpha

Acne Scar: Options For acne scar removal

Stunich " Ubuntu 8.10 has a new look

EmacsWiki: Transparent Emacs

WebKit Nightly Builds

maemo SDK+ - Trouble Shooting

Release Notes for Firebird 2.5 Alpha 1

Free Blender Workshop - Sao Paulo, Brazil, March 19th.

Mozilla Firefox 3.0 Alpha 2

Search results for Alpha Lipoic Acid

Neowin.net - Ubuntu, Kubuntu and Xubuntu 8.04 Alpha 3

Wyzo - The Media Browser

Ubuntu ja Kubuntu 8.04 "Hardy Heron" Alpha 1 julkaistu

HardyHeron/Alpha1 - Ubuntu Wiki

BBXML - An XML Interface for BetaBrite Signs: Users Guide

PEAR :: Package :: XML_MXML

Adobe Labs - Adobe Flex Builder Linux Public Alpha

The Burning Edge - Firefox nightly build changelog

Idealog: Breaking Open Facebook with Open Source Software (Part 2)

Mozilla Gran Paradiso Alpha 7 Release Notes

KDE4 Rocks, Stupid!

Index of /~faaborg/files/granParadisoUI

Joomla! Extensions Directory - JCE

Gran Paradiso releases

Firefox 3 Alpha 8 Released