One of the most popular plugin types both in MariaDB and MySQL is INFORMATION_SCHEMA plugin type. INFORMATION_SCHEMA plugins add new tables to the INFORMATION_SCHEMA. There are lots of INFORMATION_SCHEMA plugins, because they can be used to show just anything to the user and are very easy to write.
MariaDB 10.1.1 comes with nine INFORMATION_SCHEMA plugin:
- Feedback — shows the anonymised server usage information and can optionally send it to the configured url.
- Locales — lists compiled-in server locales, implemented by Roberto Spadim
- METADATA_LOCK_INFO — Lists metadata locks in the server. Implemented by Kentoku Shiba
- QUERY_CACHE_INFO — Lists queries in the query cache. Originally by Roland Bouman
- QUERY_RESPONSE_TIME — Shows distribution of query response times. Originally implemented in Percona
- CLIENT_STATISTICS — part of Percona “userstat” patch
- USER_STATISTICS — part of Percona “userstat” patch
- INDEX_STATISTICS — part of Percona “userstat” patch
- TABLE_STATISTICS — part of Percona “userstat” patch
Also there are many INFORMATION_SCHEMA plugins that come together with storage engines and show various information about them. InnoDB comes with 28 of such plugins. XtraDB — with 32, TokuDB — with 12. And if you google you can find many more INFORMATION_SCHEMA plugins — for example, a plugin that shows various system information or a plugin that lists all created user variables. INFORMATION_SCHEMA plugins are indeed numerous and very popular.
If you look at the these plugins, you can see that there are plugins that display some kind of a current state of something, and there are plugins that accumulate and display some statistics. For this second group of plugins it is sometimes desirable to reset this statistics — but there was no natural way for a user to do that. For status variables (as in SHOW STATUS statement) one can use FLUSH STATUS, but for plugins there was nothing similar. But now MariaDB 10.1.1 extends INFORMATION_SCHEMA plugin API by introducing reset_table callback. For example, look at the QUERY_RESPONSE_TIME plugin initialization function:
static int query_response_time_info_init(void *p)
{
ST_SCHEMA_TABLE *i_s_query_response_time= (ST_SCHEMA_TABLE *) p;
i_s_query_response_time->fields_info= query_response_time_fields_info;
i_s_query_response_time->fill_table= query_response_time_fill;
i_s_query_response_time->reset_table= query_response_time_flush;
query_response_time_init();
return 0;
}
See the highlighted line — it sets the reset_table callback to the query_response_time_flush function. When this plugin is loaded, MariaDB will automatically start supporting new statement
FLUSH QUERY_RESPONSE_TIME;
and it will invoke this callback that will, in turn, reset query response time statistics. Similarly “userstat” tables CLIENT_STATISTICS, USER_STATISTICS, INDEX_STATISTICS, and TABLE_STATISTICS support the FLUSH statement in a similar way.
MariaDB 10.1.1 also implements another enhancement for the INFORMATION_SCHEMA plugins — the SHOW statement. Indeed, until 10.1.1 only native server INFORMATION_SCHEMA tables could have a SHOW counterpart. To query a plugin INFORMATION_SCHEMA table one would need to type a lengthy SELECT and than see how long lines wrap around in the terminal window, making it impossible to see what column each value belongs to. You all know it very well:
MariaDB [test]> select * from information_schema.locales; +-----+-------+-------------------------------------+-----------------------+- --------------------+---------------+--------------+------------------------+ | ID | NAME | DESCRIPTION | MAX_MONTH_NAME_LENGTH | MAX_DAY_NAME_LENGTH | DECIMAL_POINT | THOUSAND_SEP | ERROR_MESSAGE_LANGUAGE | +-----+-------+-------------------------------------+-----------------------+- --------------------+---------------+--------------+------------------------+ | 0 | en_US | English - United States | 9 | 9 | . | , | english | | 1 | en_GB | English - United Kingdom | 9 | 9 | . | , | english | | 2 | ja_JP | Japanese - Japan | 3 | 3 | . | , | japanese | | 3 | sv_SE | Swedish - Sweden | 9 | 7 | , | | swedish | | 4 | de_DE | German - Germany | 9 | 10 | , | . | german | ...
So we’ve introduced a SHOW statement for INFORMATION_SCHEMA plugins that is quick to type and is supposed to provide just enough columns to fit nicely on the screen:
MariaDB [test]> show locales; +-----+-------+-------------------------------------+------------------------+ | Id | Name | Description | Error_Message_Language | +-----+-------+-------------------------------------+------------------------+ | 0 | en_US | English - United States | english | | 1 | en_GB | English - United Kingdom | english | | 2 | ja_JP | Japanese - Japan | japanese | | 3 | sv_SE | Swedish - Sweden | swedish | | 4 | de_DE | German - Germany | german | ...
The API is very simple, a plugin does not need to implement anything special to support this. It only needs to decide what subset of columns will be visible in the SHOW statement and specify names for this columns when declaring INFORMATION_SCHEMA table fields. For example, in the LOCALES plugin:
static ST_FIELD_INFO locale_info_locale_fields_info[]=
{
{"ID", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Id", 0},
{"NAME", 255, MYSQL_TYPE_STRING, 0, 0, "Name", 0},
{"DESCRIPTION", 255, MYSQL_TYPE_STRING, 0, 0, "Description", 0},
{"MAX_MONTH_NAME_LENGTH", 4, MYSQL_TYPE_LONGLONG, 0, 0, 0, 0},
{"MAX_DAY_NAME_LENGTH", 4, MYSQL_TYPE_LONGLONG, 0, 0, 0, 0},
{"DECIMAL_POINT", 2, MYSQL_TYPE_STRING, 0, 0, 0, 0},
{"THOUSAND_SEP", 2, MYSQL_TYPE_STRING, 0, 0, 0, 0},
{"ERROR_MESSAGE_LANGUAGE", 64, MYSQL_TYPE_STRING, 0, 0, "Error_Message_Language", 0},
{0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0}
};
See, the highlighted lines specify column names for the SHOW statement and only these columns are visible in SHOW. It is, of course, a sole responsibility of the plugin writer to pick up a reasonable subset of columns so that the resulting table is not too wide but still contains enough information to be useful. If a plugin does not specify any column names for SHOW, the SHOW statement will not work for this plugin.
An inquisitive reader may note that “userstat” tables CLIENT_STATISTICS, USER_STATISTICS, INDEX_STATISTICS, and TABLE_STATISTICS supported FLUSH and SHOW also in 10.0 and in earlier versions of MariaDB. That’s right, but before 10.1.1 these tables were not plugins, and the syntax support was hard-coded into the server. Now they are plugins which, indeed, required INFORMATION_SCHEMA plugins to support FLUSH and SHOW. That’s why these extensions were implemented.