Revision: 8 - Last Update: September 03 2008

This is the first part of a series of short articles with a how-to approach about MySQL® Performance Monitoring, Tuning & Auditing. We will see the question from a GUI prospective. In particular we will describe which monitoring-oriented features HoneyMonitor, a GUI for MySQL® currently in alpha development, implements.

I will explain how HoneyMonitor let you

1. install an audit database on your server, without the need of using 3th Party Agents nor using remote repository databases
2. enable the auditing and start monitoring your server
3. tuning your server changing a few suggested list of variables to get better performance.

We will use only the 5.1.x series of the Server as we use some Scheduled Events and the Event Scheduler has been added only in the 5.1 branch. In particular we will use MySQL® 5.1.24-rc. We will also do some tests on the 5.1.23-alpha-maria preview and MySQL® 6.0.4-alpha.

As regard HoneyMonitor, we will use the latest alpha version, i.e. 1.0.13-alpha.

Requirements:

1. a MySQL® Server v. 5.1. - no matter of the platform it is running on - with a user with basic privileges (like create database or create and execute routines)
2. a Windows® XP® or Vista® client - where we can install HoneyMonitor.

In this article I will not explain the meaning of the performance variables and metrics. There are several Guides available on the web to get more information about the Derived Performance Metrics. Of course, the first step is to browse the MySQL® Documentation. By the way, you can read all the Ratio’s formulas, as well the description of the MySQL® system and status variables, directly from HoneyMonitor.

You can read more on HoneyMonitor, at the project’s page - www.honeysoftware.com/products/honeymonitor - and for any question you can write me directly at sales@honeysoftware.com.

Contents:

• Downloading & Installing HoneyMonitor
• First start of HoneyMonitor: Edition Choosing, Software Registration, CEIP, Connection Creation
• Introduction to the Performance Audit System
• Installing the Audit Database & Enabling the Auditing
• Audit Options & Audit Statistics
• Conclusion
• What we will see in the next article
• Other Resources
• About the Author

1. Downloading & Installing HoneyMonitor

You can download HoneyMonitor at http://www.honeysoftware.com/download. After the download just unzip the file in a temporary folder and run the Installation Wizard.

Honeymonitor uses direct connection to the Server but for some features it also needs the MyODBC driver. If you haven’t that driver installed on your system, you can install it using the HoneyMonitor’s Installation Wizard.

2. First start of HoneyMonitor: Edition Choosing, Software Registration, CEIP, Connection Creation

After installation the icon ‘HoneyMonitor - Welcome’ is created on your Desktop. As the Installation Wizard installs all the available HoneyMonitor’s Editions (Lite Free, Standard, Enterprise, Network, Pro, Audit Pro), the HoneyMonitor - Welcome Window (Fig. 1) allows you to choose the Edition you want to start.

Let’s click on the ‘HoneyMonitor - Welcome’ icon and choose the Audit Pro Edition. For more information about the HoneyMonitor Editions’ Logic, please refer to the page Editions & Features Matrix of the HoneySoftware Web Site.

HoneyMonitor requires a free web registration. The first time you run the software, a Registration Window will appear (Fig. 2).

To register your copy you can follow the instructions reported in that Window and when you get your Web Key, just past it in the Registration Window and click on the ‘Ok’ button.

After Registration, the Trial Period Information Window should appear (Fig. 3). This is just to inform that you are using a 30-days Trial software. You can click on the ‘Next’ button.

If this is the first time you are running HoneyMonitor, the Customer Experience Improvement Program Window should appear (Fig. 4). You can decide to join the Program and anonymously partecipate to help us improve our software. Wheter decision you take, click on the ‘Ok’ button to go on.

You are now ready to create a connection to the MySQL® Server (Fig. 5). HoneyMonitor can save your connection’s parameters so that the next time you will use the software you will be able to open that connection just double-clicking on its name from the Server Object List or selecting it using the ‘Stored Connection’ combo-list of the Connection Window.

 Fig. 5: New MySQL® Connection

To create a new connection, insert the Server Hostname (or IP Address), the Port the server is listening on, your Username and your Password. Then do a check clicking on the ‘Test Connection’ button. If the connection was succesfully established, just click on the ‘Connect’ button. The Connection Window will be closed and you are ready to use the connection just created.

Of course, you can create as many connections as you need. For more information about connections creation in HoneyMonitor - included instructions on how to create SSH Tunneling Connections or SSL Connections - please refer to the HoneyMonitor’s Getting Starting Guide.

3. Introduction to the Performance Audit System

The connection to the Server has now been established and we are ready to install the performance audit system and start collecting data.

As we will see more in details in other articles of this series, HoneyMonitor includes a Performance Tuning Monitor. When used in the Pro Edition, the Performance Tuning Monitor is just a static monitor: when you open it (or you use its refresh button) it calculates the most common Performance Hit Ratios and Derived Performance Metrics. When used in HoneyMonitor Audit Pro or HoneyMonitor Network Edition, it not also calculates the Ratios but also gives you the possibility to create time-trend charts of those Ratios so that you can see how the performance of your Server are changing over the time. This could be very useful to identify why, for example, your server’s performance is going worse.

The Performance Tuning Monitor, as well the Audit Report Generation Window reads audit data from an audit database - created on your MySQL® Server by the Audit Installation Wizard. Audit data are hence stored on your server rather than on an external repository database server and this gives you full access to the audit data: you can see them or delete them simply when you want.

Audit data are stored in you server by a Scheduled Event. A Scheduled Event simply is a temporal trigger: you define a time interval (when installing the Audit System, or later using the Audit Options Window) and the Event takes care of calculating all the Performance Hit Ratios and Derived Performance Metrics and stores them in the audit database, so that the data can be used for charts’ creation.

At any time you can change the time interval for the data acquisition or pausing the audit Event so that no other data will be stored until you will enabled the audit Event once again.

As a Populating Event takes care of storing audit data, a Pruning Event takes care of deleting old audit data. You can set the interval for data pruning or simply choose not to prune audit data.

HoneyMonitor doesn’t require third party agents running on your Server, nor cron jobs. It simply use SQL. You don’t need to install - and maintain - software on your servers: all the administration tasks, performance monitoring, tuning and report generation is made at client-side!

Once you create a Performance Report, you can save it in the directory you prefer, print it and export it to pdf or other formats.

HoneyMonitor comes with 90+ performance charts, included in 10 Reports (Temporary Tables, Query Cache, InnoDB, …) but you can also create the Reports you prefer using the included Report Designer.

3.1. Replication Issues

If your server is a Master Server, you must skip the replication of the audit database otherwise the performance audit data of the master will be replicated on the slaves.

Option -replicate-ignore-table=db_name.tbl_name should avoid this problem. Please refer to the MySQL® Reference Manual for further information on replication.

You can monitor your master / slaves environment simply installing the audit database on the master and on each slave.

4. Installing the Audit Database & Enabling the Auditing

To start Audit Installation just click on the menu Auditing / Install Audit and follow the few steps of Audit Installation Wizard. A database named hs_audit_schema will be created. Please make sure that you don’t have a database with the same name as, at the moment, there is no possibilities to change the name of the audit database installed by the software. During installation, the Wizard will perform some tests to ensure that all is installed propertly and also allows you to set the events’ intervals and enable data collection (Fig. from 6 to 9).

By the way at any time you can open the Check Audit Installation Window to check the status of your Audit System.

Fig. 10: Check Audit Installation Window

5. Audit Options & Audit Statistics

By default, the Audit Installation Wizard, after installation, enables the auditing. If you have paused it, to enable it once again, just open the Audit Options Window (Fig. 11) and check the Enable Populating Event check-box.

You may be interested knowing how many records the Audit Table contains or how many disk space is being used. If you click on the Auditing / Audit Statistics menu, the Audit Statistics Window (Fig. 12) will be opened and you’ll can see some statistics on your Audit Database.

6. Conclusion

In this first article of the series, we have focused on the first startup of HoneyMonitor and on introducing the Audit System showing you how to get it working.

7. What we will see in the next articles

In the next article of this series, we’ll take a look at the Performance Tuning Monitor and we will see how to create Performance Reports.

The following is a not-complete list of subsections we plan to discuss over the series of articles:

• Quick Tour of the Performance Tuning Monitor
• Quick Tour of the Performance Reports
• The Audit Database in Details
• Limitations: how the system can be improved
• Future Plans: which features we plan to add in the Audit Pro Edition
• A Brief Introduction to other features of HoneyMonitor
  • Quick Tour of the Metadata Catalog
  • Quick Tour of the Replication Monitor
  • Quick Tour of the Cluster Monitor

Contents are subject to change. Let us know what you’d like to see.

8. Other Resources

If you want to learn more on HoneyMonitor please visit the Project Home Page, at http://www.honeysoftware.com/honeymonitor, or check out one of the following resources:

• Getting Started Guide
• Video Tutorials
• User’s Guide

9. About the Author

Santo Leto is a two years experience MySQL® DBA and Developer.
Leader and main programmer of the HoneyMonitor Project, he graduated in physics and he lives in Italy, where he works from home.

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

mysql> SHOW TABLES LIKE ‘FALCON%’;
+—————————————-+
| Tables_in_information_schema (FALCON%) |
+—————————————-+
| FALCON_TABLES                                               |
| FALCON_SERIAL_LOG_INFO                        |
| FALCON_SYSTEM_MEMORY_DETAIL        |
| FALCON_SYSTEM_MEMORY_SUMMARY  |
| FALCON_VERSION                                             |
| FALCON_TRANSACTION_SUMMARY           |
| FALCON_TABLESPACES                                   |
| FALCON_SYNCOBJECTS                                    |
| FALCON_TRANSACTIONS                                |
| FALCON_RECORD_CACHE_DETAIL             |
| FALCON_TABLESPACE_IO                              |
| FALCON_RECORD_CACHE_SUMMARY       |
| FALCON_TABLESPACE_FILES                       |
+—————————————-+
13 rows in set (0.00 sec)

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

mysql> SHOW TABLES LIKE ‘FALCON%’;
+—————————————-+
| Tables_in_information_schema (FALCON%)      |
+—————————————-+
| FALCON_TABLES                                                    |
| FALCON_RECORD_CACHE_SUMMARY            |
| FALCON_SYSTEM_MEMORY_DETAIL            |
| FALCON_SERIAL_LOG_INFO                             |
| FALCON_VERSION                                                 |
| FALCON_TRANSACTION_SUMMARY             |
| FALCON_DATABASE_IO                                      |
| FALCON_SYNCOBJECTS                                      |
| FALCON_TRANSACTIONS                                   |
| FALCON_RECORD_CACHE_DETAIL                 |
| FALCON_SYSTEM_MEMORY_SUMMARY      |
+—————————————-+
11 rows in set (0.00 sec)

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:

mysql> SELECT
    ->  `tablespace` AS `Tablespace`,
    ->  100 * 1-(SUM(`physical_reads`) / SUM(IF(`logical_reads` > 1, `logical_reads`,1))) `Cache Hit Ratio`
    -> FROM `information_schema`.`FALCON_TABLESPACE_IO`
    -> GROUP BY `tablespace`;
+——————+—————–+
| Tablespace       | Cache Hit Ratio |
+——————+—————–+
| FALCON_MASTER          |         99.9660 |
| FALCON_TEMPORARY |         99.0000 |
| FALCON_USER                |         99.0000 |
+——————+—————–+
3 rows in set (0.00 sec)

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

mysql> DESCRIBE `information_schema`.`FALCON_TABLESPACES`;
+—————–+————–+——+—–+———+——-+
| Field           | Type         | Null | Key | Default | Extra |
+—————–+————–+——+—–+———+——-+
| TABLESPACE_NAME | varchar(127) | NO   |     |         |       |
| TYPE            | varchar(127) | NO   |     |         |       |
| COMMENT         | varchar(127) | NO   |     |         |       |
+—————–+————–+——+—–+———+——-+
3 rows in set (0.33 sec)

mysql> DESCRIBE `information_schema`.`FALCON_TABLESPACE_FILES`;
+—————–+————–+——+—–+———+——-+
| Field           | Type         | Null | Key | Default | Extra |
+—————–+————–+——+—–+———+——-+
| TABLESPACE_NAME | varchar(127) | NO   |     |         |       |
| TYPE            | varchar(127) | NO   |     |         |       |
| FILE_ID         | int(127)     | NO   |     | 0       |       |
| FILE_NAME       | varchar(127) | NO   |     |         |       |
+—————–+————–+——+—–+———+——-+
4 rows in set (0.00 sec)

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:

mysql> SELECT * FROM `information_schema`.`FALCON_TABLESPACES`;
+——————+——————+—————+
| TABLESPACE_NAME  | TYPE             | COMMENT       |
+——————+——————+—————+
| FALCON_USER      | FALCON_USER      |               |
| FALCON_TEMPORARY | FALCON_TEMPORARY |               |
| test1            | USER_DEFINED     | test1-comment |
| test2            | USER_DEFINED     | test2-comment |
+——————+——————+—————+
4 rows in set (0.00 sec)

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:

mysql> SELECT * FROM `information_schema`.`FALCON_TABLESPACE_FILES`;
+——————+——————+———+———————-+
| TABLESPACE_NAME  | TYPE             | FILE_ID | FILE_NAME            |
+——————+——————+———+———————-+
| FALCON_USER      | FALCON_USER      |       1 | falcon_user.fts      |
| FALCON_TEMPORARY | FALCON_TEMPORARY |       1 | falcon_temporary.fts |
| test1            | USER_DEFINED     |       1 | test1.fts            |
| test2            | USER_DEFINED     |       1 | test2.fts            |
+——————+——————+———+———————-+
4 rows in set (0.02 sec)

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> DESCRIBE `information_schema`.`FALCON_TABLES`;
+—————+————–+——+—–+———+——-+
| Field         | Type         | Null | Key | Default | Extra |
+—————+————–+——+—–+———+——-+
| SCHEMA_NAME   | varchar(127) | NO   |     |         |       |
| TABLE_NAME    | varchar(127) | NO   |     |         |       |
| PARTITION     | varchar(127) | NO   |     |         |       |
| TABLESPACE    | varchar(127) | NO   |     |         |       |
| INTERNAL_NAME | varchar(127) | NO   |     |         |       |
+—————+————–+——+—–+———+——-+
5 rows in set (0.00 sec)

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

mysql> SELECT * FROM `information_schema`.`FALCON_TABLES`;
+————-+—————-+———–+————-+—————-+
| SCHEMA_NAME | TABLE_NAME     | PARTITION | TABLESPACE  | INTERNAL_NAME  |
+————-+—————-+———–+————-+—————-+
| FDATABASE   | FTABLE_GENERIC |           | FALCON_USER | FTABLE_GENERIC |
+————-+—————-+———–+————-+—————-+
1 row in set (0.00 sec)

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`;
+————-+—————-+———–+——————+—————-+

| SCHEMA_NAME | TABLE_NAME     | PARTITION | TABLESPACE       | INTERNAL_NAME  |

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

| FDATABASE   | FTABLE_GENERIC |           | FALCON_USER      | FTABLE_GENERIC |

| TEMP        |                |           | FALCON_TEMPORARY | #SQL1FF4_13_0  |

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

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:

mysql> SELECT * FROM `information_schema`.`FALCON_TABLES`;
+————-+—————-+———–+————-+—————-+
| SCHEMA_NAME | TABLE_NAME     | PARTITION | TABLESPACE  | INTERNAL_NAME  |
+————-+—————-+———–+————-+—————-+
| FDATABASE   | FTABLE_GENERIC |           | FALCON_USER | FTABLE_GENERIC |
+————-+—————-+———–+————-+—————-+
1 row in set (0.00 sec)

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

mysql> SELECT * FROM `information_schema`.`FALCON_TABLES`;
+————-+—————-+———–+————-+—————-+
| SCHEMA_NAME | TABLE_NAME     | PARTITION | TABLESPACE  | INTERNAL_NAME  |
+————-+—————-+———–+————-+—————-+
| FDATABASE   | FTABLE_GENERIC |           | FALCON_USER | FTABLE_GENERIC |
| FDATABASE   | FTABLE1_TEST1  |           | test1       | FTABLE1_TEST1  |
+————-+—————-+———–+————-+—————-+
2 rows in set (0.00 sec)

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

mysql> SELECT * FROM `information_schema`.`FALCON_TABLES`;
+————-+—————-+———–+————-+—————-+
| SCHEMA_NAME | TABLE_NAME     | PARTITION | TABLESPACE  | INTERNAL_NAME  |
+————-+—————-+———–+————-+—————-+
| FDATABASE   | FTABLE_GENERIC |           | FALCON_USER | FTABLE_GENERIC |
| FDATABASE   | FTABLE1_TEST1  |           | test1       | FTABLE1_TEST1  |
| FDATABASE   | FTABLE2_TEST1  |           | test1       | FTABLE2_TEST1  |
+————-+—————-+———–+————-+—————-+

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.