Last week,
I described how to use the
MySQL plug-in API to write a minimal
'Hello world!' information schema plug-in. The main purpose of that plug-in is to illustrate the bare essentials of the MySQL information schema plug-in interface.
In this article, I'd like to take that to the next level and demonstrate 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.
In a forthcoming article, I will describe a few information schema plug-ins that are arguably more interesting, such as a plug-in to list the currently existing
TEMPORARY tables,
user-defined variables, and the contents of the
query cache. Although the plug-in described in this article may be of some use, its main purpose is to illustrate the minimal requirements for plug-ins that can access the server's internals.
A Quick Recapitulation
You might recall that:
- The MySQL plug-in API is one of the new features in MySQL 5.1, and forms a generic extension point of the MySQL database server, allowing privileged database users to add functionality to the MySQL Server by loading a shared library from the plug-in directory
- Loading and unloading a plug-in is a completely dynamic process controlled using the
INSTALL PLUGIN and UNINSTALL PLUGIN syntax, and does not involve compiling the server or even restarting it
- There are several types of plug-ins, the most well-known being storage engines and full-text parsers. Less well-known types include information schema and daemon plug-ins.
- An information schema plug-in provides the implementation of a table (or actually, a system view) in the information_schema database
- Plug-ins are usually implemented in C/C++. To implement a plug-in, the implementor must include the header file
plugin.h and provide an initialized instance of the st_mysql_plugin structure. In addition, the implementor must provide code to implement the plug-in type dependent part of the interface
- The plug-in type dependent part of the interface for information schema plug-ins consists of two things: the column definitions of the information schema table and a
fill_table function that is called whenever the server wants to retrieve the rows of data from that table.
How Information Schema plug-ins can access MySQL Server internals
Before we discuss the
MYSQL_SAVEPOINTS information schema plug-in in detail, let's take a look at the way information schema plug-ins can obtain access to the internals of the MySQL server.
Like I just recapitulated from last week's article, the plug-in type dependent inferface for information schema plug-ins consists of two things:
- An array of
ST_FIELD_INFO structures, each of which defines a column of the information schema table - A
fill_table function that is called by the server when it needs to retrieve the data from table
The column definitions may be considered a static part of the interface - they simply define the structure of the table - no more, no less. The
fill_table function is a different matter. Let's take a look at the signature of the signature of that function:
int fill_table(THD *thd, TABLE_LIST *tables, COND *cond);
The
TABLE_LIST *tables argument provides the handle to the information schema table that is being filled, and the
COND *cond argument represents the
WHERE condition of the SQL statement that is currently being handled, allowing the
fill_table function to directly filter rows (instead of relying on the query execution engine to do that). As such, these arguments are occupied with the actual delivery of rows of data to the server.
The first argument to the
fill_table function offers all kinds of interesting opportunities to see what is going on inside the server. We will discuss it in more detail in the next section.
Public accessors to the current THD instance
The first argument to the
fill_table function is
THD *thd. This is the so-callled
thread descriptor - something that is best thought of as a handle to the current session. Note that in a MySQL context, the terms
connection,
thread and
session are often used interchangeably. However, I find the term thread too broad, and the term connection too narrow. As there are many parts in
THD that maintain state regarding the events that occurred since a connection is established, it seems most sensible to think of
THD as the server-side implementation of a
session.
This session handle or thread descriptor has the form of a pointer to an instance of the
THD class. The
plugin.h header file contains a forward declaration to this class, but the actual declaration is contained in
sql/sql_class.h. The
THD class is one of the key data structures in understanding the workings of the MySQL server as it is passed as an argument to many internal server functions. Consequently it provides a wealth of possibilities to create interesting new information schema plug-ins. In fact, the number of possibilities are so great, that a number of common usages has been explicitly set aside in the
plugin.h header file.
The
plugin.h header file contains a number of function declarations and macros that provide access to the members of a
THD instance. I will not discuss all of them here, but highlight just a few just to give you an idea:
thd_test_options() - Find out which options are set. This can be used to find out whether a number of boolean options like big_tables, (general and binary) logging, and autocommit are enabled or disabled.
thd_proc_info() - Should be used by the plug-in implementor before starting a potentially time-consuming operation so the rest of the world can monitor what this session doing. The code set here corresponds to the value reported in the State column by the SHOW PROCESSLIST statement
thd_killed() - Can be used by the plug-in implementor to find out if the thread in which this session lives was killed. If the plug-in is involved in a potentially time-consuming process, the plugin-in implementor should periodically check this and gracefully abort the plugin-ins work when it detects that the thread was killed.
thd_alloc() - Allocates some memory from this session's memory pool. If the plug-in requires some small amount of memory, plug-in implementors should use this rather than the standard malloc() function. Calling thd_alloc(); is likely to be faster because it takes memory out of a pre-allocated pool, reducing contention. In addition, it is more convenient because the memory need not be explicitly freed: it is automatically reclaimed by the pool after handling the current statement.
If you are interested in seeing all these declarations, just open
plugin.h and look for comments like this:
/*************************************************************************
Miscellaneous functions for plugin implementors
*/
plugin.h describes a public interface
In
plugin.h, the declarations as described in the previous section together form a
public interface to the current session. They are there for the convenience of plug-in implementors and represent a 'safe' way to work with the
THD pointer passed to the
fill_table function.
To say that these form a public interface is to stay that these are officially supported by MySQL AB. That is: they will be supported officially once the MySQL 5.1 Server is a generally available release. From that point on you can rely on these functions when writing plug-ins in the sense that you do not have to be afraid that they will change. At least, the public interface will remain the same for all forthcoming builds of the 5.1 server. Any interface changes in future releases will involve a proper process, giving everybody the chance to update their code well in time.
Unfortunately, not every function declaration in
plugin.h has source code comments. This means that for now, you sometimes need to do some digging in the server's source code to find out what you can do with them. I admit that this situation is not exactly perfect. However, the matter has been
reported as a bug, and hopefully, it will be adressed soon.
Beyond the public interface
I just described the public interface plug-in implementors can rely on. A distinct advantage of the public interface is that it takes away a lot of the complexity of the underlying internals of the MySQL Server. However, there will always be cases where the public interface does not offer the features you really need. In those cases, you simply need to be able to work directly on the server internals.
The advantage of directly referencing the server's internals is that you can access all the interesting nuts and bolts and bits and pieces. The downside is that there is absolutely no guarantee that your code will work in another version of the server. The internals are by definition the parts that are not meant to be exposed. As such, it is possible that your code does not work or behaves unexpectedly in another version of the server.
Let's not dwell too long on the disadvantages. Instead, let's focus on the merits of pluggable information schema tables. Granted, it is inconvenient that we may need to make our code resilient to each different build of the server. However, for many applications, it is not very likely that we have to constantly do that.
Even if we do have to change our code, the burden will be on the developer of the plug-in. For each specific build of the server, your code may need to be different. Even if the code itself does not change, you will probably at least have to recompile your plug-in for each specific build of the server. However, your users are still not required to recompile the server itself. They can still install the plug-in without stopping or restarting the server, which in many cases seems more important than bearing the burden of changing the code.
You need to break some eggs to bake an omelet - so if you're hungry, you better get over it and start breaking some eggs ;-)
The MYSQL_SERVER define
In order to access the server's internals beyond the public interface, we need to use some C/C++ preprocessor magic and define
MYSQL_SERVER. This define needs to be present before we include any MySQL header (or source) files:
#ifndef MYSQL_SERVER
#define MYSQL_SERVER
#endif
Throughout the MySQL codebase, there are many sections that are conditionally included or excluded depending on whether
MYSQL_SERVER is defined. It is hard to pinpoint the exact effect of adding this definition, because there many spots that use this definition to control conditional compilation.
Normally the
MYSQL_SERVER definition need be present only when compiling the server proper, but in this case we need it to let the plug-in code work with internal structures such as
THD instances directly, that is, without using the accessors provided by the public interface.
To be absolutely clear: using the
MySQL_SERVER define in your code does
not mean you must compile your plug-in as part of the server. On the contrary - you can compile your plug-ins separately from the server, and still (un)install them at runtime. The only thing the
MySQL_SERVER define does, is pull in the declarations that are normally considered to be 'internal'. They will for example allow us to work directly with the members of the
THD class instead of being required to use the public accessors defined in
plugin.h.
Implementing the MYSQL_SAVEPOINTS Information Schema plug-in
Now that we sketched the backgrounds, we can quickly proceed and discuss the implementation of the
MYSQL_SAVEPOINTS information schema plug-in. (Note that you can download the source code file
mysql_is_savepoints.cc here.)
Most of the things are rather similar to what was described in
the article describing the MYSQL_HELLO plug-in, for which you can still download the
mysql_is_hello.cc source code.
We will do like we did last week and assume the following things are in place on your system:
Creating the source file
First, we need to create a C++ source file. We will assume that the working directory is ~/mysql_is_savepoints/, and that the source file is called mysql_is_savepoints.cc.
The MYSQL_SERVER define
Like we explained in the previous sections, we need to define
MYSQL_SERVER so we can directly reference the members of the
THD class passed to our
fill_table function.
#ifndef MYSQL_SERVER
#define MYSQL_SERVER
#endif
Because this affects how the included files are processed, we do this at the very top of our source file.
Include files
We can use the same list of includes we used for the
MYSQL_HELLO plug-in - the
MYSQL_SERVER define is responsible for including all the additional things we require to write the
MYSQL_SAVEPOINTS plug-ins.
#include <mysql_priv.h>
#include <stdlib.h>
#include <ctype.h>
#include <mysql_version.h>
#include <mysql/plugin.h>
#include <my_global.h>
#include <my_dir.h>
Defining the columns
For the
MYSQL_SAVEPOINTS plug-in, we will define two columns:
SAVEPOINT_ID and
SAVEPOINT_NAME. At the SQL level, it will look something like this:
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| SAVEPOINT_ID | bigint(0) | NO | | 0 | |
| SAVEPOINT_NAME | varchar(64) | NO | | | |
+----------------+-------------+------+-----+---------+-------+
...and this is what it looks like in the C/C++ source file:
#define COLUMN_SAVEPOINT_ID 0
#define COLUMN_SAVEPOINT_NAME 1
static ST_FIELD_INFO mysql_is_savepoints_field_info[]=
{
{"SAVEPOINT_ID", 0, MYSQL_TYPE_LONGLONG, 0, 0, "Savepoint Id"},
{"SAVEPOINT_NAME", 64, MYSQL_TYPE_STRING, 0, 0, "Savepoint Name"},
{NULL, 0, MYSQL_TYPE_NULL, 0, 0, NULL, 0}
};
This time, in addition to creating the
ST_FIELD_INFO array of column definitions, we also create
#defines for the array entry indexes. The defines allow us to refer to the column definitions using the names rather than the raw, literal integer array indexes. This has the advantage that we do not have to change code should we want to change the positions of the columns. Another advantage is that our
fill_table code will be easier to read: by consistently referring to
COLUMN_SAVEPOINT_ID and
COLUMN_SAVEPOINT_NAME rather than
0 and
1 it will be much easier to see what is going on.
Filling the table
Now we come to the heart of the matter: generating a row for each SQL
SAVEPOINT available in the current session.
The savepoints for the current session are available in the
transaction member of the
THD class. The
transaction member is an instance of the
st_transactions struct, which is declared locally inside the
THD class:
class THD :public Statement,
public Open_tables_state
{
...many, many lines here...
public:
struct st_transactions {
SAVEPOINT *savepoints;
...a few more lines here...
} transaction;
...many, many more lines here ...
};
Now you might recall that the
THD class is declared in
sql/sql_class.h. However, you might have some trouble locating the
transaction member, because the declaration of the
THD class is extremely large and long-winded: in the MySQL 5.1.22-rc source distribution, it ranges from lines 960 to 1886(!!) - and those 900 something lines make up only the declaration!
(Although the official explanation for the name of the
THD class is that it is an acronym for
THread
Descriptor, some developers* explained that it is one of the few class names that is spelled in capitals because it is so incredibly heavy. According to this anecdote, its name should be pronounced as "
...THUD!!...THUD!!..." because of the sound it makes each time it is dumped into the argument list of a function that makes up the servers source code.
* = Thanks to Eric Herman for painting this creative and tangible likeness ;-)
Anyway, you will find it easier when you look for
st_transactions, or go directly to line 1149, but note that the line number is likely to be different in other versions of the server code.
Now, we can see that the
st_transaction struct contains a pointer to a
SAVEPOINT pointer called
savepoints. As we shall see later, this is actually the list of savepoints we need. But what kind of type is this
SAVEPOINT exactly?
Well, to get past this point, you really need some patience and a set of tools that allow you to search the source code. In the case of
SAVEPOINT, it turns out that this is actually a
typedef for the
st_savepoint structure. Now, the odd thing is that this
typedef appears in
sql/handler.h:
typedef struct st_savepoint SAVEPOINT;
But the structure
st_savepoint itself is declared in
sql/sql_class.h - that is, the same file that declares
THD, which seems to prefer
SAVEPOINT rather than
st_savepoint!
Well - it is beyond me why it was done like this. For our purpose it doesn't really matter though, let's examine the declaration of
st_savepoint instead:
struct st_savepoint {
struct st_savepoint *prev;
char *name;
uint length, nht;
};Here we can see that each
st_savepoint has a
char * member called
name, which is presumably whatever name the user provided in the savepoint syntax:
mysql> SAVEPOINT my_savepoint;
So in this case, the
name member of the
st_savepoint instance corresponding to this SQL
SAVEPOINT will point to the character string
"my_savepoint".
Apart from the
name we can also see that each
st_savepoint has itself a pointer to another
st_savepoint called
prev. This suggests a single linked list of savepoints.
This is about all the information we need to implement the
fill_table function. So, here it is:
int mysql_is_savepoints_fill_table(THD *thd, TABLE_LIST *tables, COND *cond)
{
int status = 0; /* return value for this func, 0=success, 1=error*/
CHARSET_INFO *scs= system_charset_info; /* need this to store field into table */
TABLE *table= (TABLE *)tables->table; /* handle to the I_S table. class declared in table.h */
uint savepoint_id = 0;
SAVEPOINT *sv= thd->transaction.savepoints;
while(sv && !status)
{
/* store the savepoint sequence into the table column */
table->field[COLUMN_SAVEPOINT_ID]->store(++savepoint_id, 0);
/* store the savepoint name into the table column */
table->field[COLUMN_SAVEPOINT_NAME]->store(sv->name, strlen(sv->name), scs);
status= schema_table_store_record(thd, table);
sv= sv->prev;
}
return status;
}
The biggest difference with the
fill_table function we used in the
MYSQL_HELLO example is that instead of just storing one single row, we have a loop, storing one row for each iteration. The loop is initialized by assigning the
SAVEPOINT pointer from the
transaction member from the
THD instance that is passed as the first argument to the
fill_table function to a local
sv variable:
SAVEPOINT *sv= thd->transaction.savepoints;
Of course, it is possible that there are no savepoints in the current session, in which case
thd->transaction.savepoints will be the
NULL pointer. However, if there are savepoints, a pointer to the
last savepoint that was created in the current session will now be stored in
sv. We can now set up the actual loop:
while(sv && !status)
{
...lines here...
status= schema_table_store_record(thd, table);
sv= sv->prev;
}
Note that the loop will be entered only if
sv points to a savepoint. If it does, data from the savepoint is written to the columns of our information schema table.
In the bottom of the loop, we store the current record using the
schema_table_store_record, which we discussed already for the
MYSQL_HELLO example:
status= schema_table_store_record(thd, table);
Interestingly, we were required to make a forward declaration to it in the
MYSQL_HELLO example. Now, we don't have to do this, presumably because we defined
MYSQL_SERVER.
You might recall that
schema_table_store_record function returns
0 in case of success and
1 instead of failure. Note that if a failure occurs at this point, the loop will not iterate again, as the
while condition requires
status to be not true (that is, zero).
After storing the row, the last step of the loop is to move back and examine the previous savepoint:
sv= sv->prev;
If the end of the list is reached,
sv will be
NULL, preventing the loop to iterate again. However, if there is in fact a previous savepoint, the loop will run once again and create a new row for that savepoint too, on and on until we reach the end of the list of savepoints.
In the top of the loop, we store data into the columns of our information schema table:
/* store the savepoint sequence into the table column */
table->field[COLUMN_SAVEPOINT_ID]->store(++savepoint_id, 0);
/* store the savepoint name into the table column */
table->field[COLUMN_SAVEPOINT_NAME]->store(sv->name, strlen(sv->name), scs);
This time, we use our defines
COLUMN_SAVEPOINT_ID and
COLUMN_SAVEPOINT_NAME instead of the literal numerical field indexes. We already demonstrated in the MYSQL_HELLO example how to store a string, so we won't discuss the line that stores the savepoint's name. Instead, let's find out how we can store an integer value by looking at the line that stores the savepoint id.
As you can see, we stipulate the value for the
SAVEPOINT_ID column ourselves by simply adding one for each row:
table->field[COLUMN_SAVEPOINT_ID]->store(++savepoint_id, 0);
Savepoints by no means have a numerical ID of their own, but it makes sense to make one up in order to unambigously indicate the order in which the savepoints were created during this session. Note the second argument to the
store method, which is always
0 here:
table->field[COLUMN_SAVEPOINT_ID]->store(++savepoint_id, 0);
This second argument is there to tell the
store method whether the value represents a signed or an unsigned value. In this case, we are storing an
unsigned value - it should be
1 for an unsigned value.
The rest of the implementation
The remainder of the implementation is quite similar to what was discussed for the
MySQL_HELLO example. The most important difference is actually the plug-in name, but otherwise the implementation is identical. Therefore, it is not discussed here further.
Building and Installing
The build and install process is pretty much similar to that for the
MYSQL_HELLO plug-in.
Compiling
We can compile the plug-in like this:
g++ -DMYSQL_DYNAMIC_PLUGIN -Wall -shared
-I/home/user/mysql-5.1.22-rc/include
-I/home/user/mysql-5.1.22-rc/regex
-I/home/user/mysql-5.1.22-rc/sql
-o mysql_is_savepoints.so mysql_is_savepoints.cc
This will create the shared library
mysql_is_savepoints.so.
Installing the plug-in
You might recall that the shared library needs to be moved to the plug-in directory. After that, we can install the plug-in using the
INSTALL PLUGIN syntax:
mysql> INSTALL PLUGIN MYSQL_SAVEPOINTS soname 'mysql_is_savepoints.so';
Query OK, 0 rows affected (0.00 sec)
Using the plug-in
Now we can finally see our plug-in in action. At first, there will be no savepoints present:
mysql> SELECT * FROM information_schema.MYSQL_SAVEPOINTS;
Empty set (0.02 sec)
Even if we set one, we won't see it immediately:
mysql> SAVEPOINT A;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM information_schema.MYSQL_SAVEPOINTS;
Empty set (0.00 sec)
This is beause by default, the session has autocommit enabled. As each statement is wrapped in its own transaction, we will never be able to see any savepoints. So we disable autocommit:
mysql> SET autocommit = OFF;
Query OK, 0 rows affected (0.00 sec)
And now we can really witness the behaviour of our plug-in:
mysql> SAVEPOINT A;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM information_schema.MYSQL_SAVEPOINTS;
+--------------+----------------+
| SAVEPOINT_ID | SAVEPOINT_NAME |
+--------------+----------------+
| 1 | A |
+--------------+----------------+
1 row in set (0.00 sec)
mysql> SAVEPOINT B;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM information_schema.MYSQL_SAVEPOINTS;
+--------------+----------------+
| SAVEPOINT_ID | SAVEPOINT_NAME |
+--------------+----------------+
| 1 | B |
| 2 | A |
+--------------+----------------+
2 rows in set (0.00 sec)
mysql> ROLLBACK TO SAVEPOINT A;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM information_schema.MYSQL_SAVEPOINTS;
+--------------+----------------+
| SAVEPOINT_ID | SAVEPOINT_NAME |
+--------------+----------------+
| 1 | A |
+--------------+----------------+
1 row in set (0.00 sec)
Learn More
This has been quite a ride! In this article it was demonstrated how we can use information schema plug-ins to report some of the things that are going on inside the current session. As such, the
MYSQL_SAVEPOINTS plug-in is a big step forward compared to the
MYSQL_HELLO plug-in.
However, this is just the tip of the iceberg - the current session harbours much more interesting information about the current session, and in a forthcoming article I will demonstrate a number of other usages. In particular, I will present a plug-in to report the user variables in the current session, and the temporary tables defined in the current session.
In another article, we will also see that it is sometimes possible to look beyond the current session and report on the status of server-wide structures, such as the query cache.
Meet us at the user's conference
When I discussed the
MYSQL_HELLO plug-in, I already hinted that there will be a lot there for those people that want to learn more about extending the server with (information schema) plug-ins. You can find all those links in the bottom of
that article.
In addition, you can learn a lot about the MySQL Server internals. And...you can learn it from one of the founding fathers:
Monty himself will be doing
A tour into MySQL's internals. So, I guess that's going to be one of those occasions where you get the opportunity to clear up some of those details in the source code you never quite managed to wrap your head around.
If you
register before the 26th of februari, you'll get a $200 discount. There are more discounts available depending on whether you attended before, or if you register together with a number of colleagues; there's special student and non-profit discounts too -
check it out here.
See you at the conference! (Bonus points for the first one to ask Monty in the Q&A why
SAVEPOINT is typedef-ed in
sql/handler.h instead of
sql/sql_class.h; double bonus points for the first one to ask Monty if
THD is really called like that because it is so heavy ;-)
