In MariaDB it is not always necessary to run an explicit CREATE TABLE statement for a table to appear. Sometimes a table may already exist in the storage engine, but the server does not know about it, because there is no .frm file for this table. This can happen for various reasons, for example, for a cluster engine the table might have been created in the cluster by another MariaDB server node. Or for the engine that supports table shipping a table file might have been simply copied into the MariaDB data directory. But no matter what the reason is, there is a mechanism for an engine to tell the server that the table exists. This mechanism is called table discovery and if an engine wants the server to discover its tables, the engine should support the table discovery API.

There are two different kinds of table discovery — a fully automatic discovery and a user-assisted one. In the former, the engine can automatically discover the table whenever an SQL statement needs it. In MariaDB, Archive and Sequence engines support this kind of discovery. For example, one can copy a t1.ARZ file into the database directory and immediately start using it — the corresponding .frm file will be created automatically. Or one can select from the, say, seq_1_to_10 table without any explicit CREATE TABLE statement.

In the latter, user-assisted, discovery the engine does not have enough information to discover the table all on itself. But it can discover the table structure, if the user will provide certain additional information. In this case, an explicit CREATE TABLE statement is still necessary, but it should contain no table structure — only the table name and the table attributes. In MariaDB, the FederatedX storage engine supports it. When creating a table, one only needs to specify the CONNECTION attribute and the table structure — fields and indexes — will be provided automatically by the engine.

Automatic Discovery

As far as automatic table discovery is concerned, the tables, from the server point of view, may appear, disappear, or change the structure anytime. Thus the server needs to be able to ask whether a given table exists and what its structure is. It needs to be notified when a table structure changes outside of the server. And it needs to be able to get a list of all (unknown to the server) tables, for statements like SHOW TABLES. The server does all that by invoking specific methods of the handlerton:

const char **tablefile_extensions;
int (*discover_table_names)(handlerton *hton, LEX_STRING *db, MY_DIR *dir,
                            discovered_list *result);
int (*discover_table_existence)(handlerton *hton, const char *db,
                                const char *table_name);
int (*discover_table)(handlerton *hton, THD* thd, TABLE_SHARE *share);


Engines that store tables in separate files (one table might occupy many files with different extensions, but having the same base file name) should store the list of possible extensions in the tablefile_extensions member of the handlerton (earlier this list was returned by the handler::bas_ext() method). This will significantly simplify the discovery implementation for these engines, as you will see below.


When a user asks for a list of tables in a specific database — for example, by using SHOW TABLES or by selecting from INFORMATION_SCHEMA.TABLES — the server invokes discover_table_names() method of the handlerton. For convenience this method, besides the database name in question, gets the list of all files in this database directory, so that the engine can look for table files without doing any filesystem i/o. All discovered tables should be added to the result collector object. It is defined as

class discovered_list
  bool add_table(const char *tname, size_t tlen);
  bool add_file(const char *fname);

and the engine should call result->add_table() or result->add_file() for every discovered table (use add_file() if the name to add is in the MariaDB file name encoding, and add_table() if it's a true table name, as shown in SHOW TABLES).

If the engine is file-based, that is, it has non-empty list in the tablefile_extensions, this method is optional. For any file-based engine that does not implement discover_table_names(), MariaDB will automatically discover the list of all tables of this engine, by looking for files with the extention tablefile_extensions[0].


In some rare cases MariaDB needs know whether a given table exists, but does not particularly care about this table structure (for example, when executing a DROP TABLE statement). In these cases, the server uses discover_table_existence() method to find out whether a table with the given name exists in the engine.

This method is optional. For the engine that does not implement it, MariaDB will look for files with the tablefile_extensions[0], if possible. But if the engine is not file-based, MariaDB will use the discover_table() method to perform a full table discovery. While this will allow to determine correctly whether a table exists, a full discovery is usually slower than the simple existence check. In other words, engines that are not file-based might want to support discover_table_existence() method as a useful optimization.


This is the main method of the table discovery, the heart of it. The server invokes it when it wants to use the table. The discover_table() method gets the TABLE_SHARE structure, which is not completely initialized — only the table and the database name (and a path to the table file) are filled in. It should initialize this TABLE_SHARE with the desired table structure.

MariaDB provides convenient and easy to use helpers that allow the engine to initialize the TABLE_SHARE with minimal efforts. They are TABLE_SHARE methods init_from_binary_frm_image() and init_from_sql_statement_string().


This method is used by engines that use "frm shipping" — such as Archive or NDB Cluster in MySQL. An frm shipping engine reads the frm file for a given table, exactly as it was generated by the server, and stores it internally. Later it can discover the table structure by using this very frm image. In this sense, a separate frm file in the database directory becomes redunant, because a copy of it is stored in the engine.


This method allows to initialize the TABLE_SHARE using a conventional SQL CREATE TABLE syntax.


Engines that use frm shipping need to get the frm image, corresponding to a particular table (typically in the handler::create() method). They do it via the read_frm_image() method. It returns an allocated buffer with the binary frm image, that the engine can use the way it needs.


The frm image that was returned by read_frm_image() must be freed with the free_frm_image().


One of the consequences of automatic discovery is that the table definition might change when the server doesn't expect it to. Between two SELECT queries, for example. If this happens, if the engine detects that the server is using an outdated version of the table definition, it should return HA_ERR_TABLE_DEF_CHANGED handler error. Depending on when in the query processing this error has happened, MariaDB will either re-discover the table and execute the query with the correct table structure, or abort the query and return an error message to the user.


The previous paragraph doesn't cover one important question — how can the engine know that the server uses an outdated table definition? The answer is — by checking the tabledef_version, the table definition version. Every table gets a unique tabledef_version value. Normally it is generated automatically when a table is created. When a table is discovered the engine can force it to have a specific tabledef_version value (simply by setting it in the TABLE_SHARE before calling init_from_binary_frm_image() or init_from_sql_statement_string() methods).

Now the engine can compare the table definition version that the server is using (from any handler method in can be accessed as this->table->s->tabledef_version) with the version of the actual table definition. If they differ — it is HA_ERR_TABLE_DEF_CHANGED.

Assisted discovery

Assisted discovery is a lot simpler from the server point of view, a lot more controlled. The table cannot appear or disappear at will, one still needs explicit DDL statements to manipulate it. There is only one new handlerton method that the server is using to discover the table structure when a user has issued an explicit CREATE TABLE statement without declaring any columns or indexes.

int (*discover_table_structure)(handlerton *hton, THD* thd,
                               TABLE_SHARE *share, HA_CREATE_INFO *info);

The assisted discovery API is pretty much independent from the automatic discovery API. An engine can implement either of them or both (or none), there is no requirement to support automatic discovery, if only assisted discovery is needed.


Much like discover_table() method, the discover_table_structure() handlerton method gets a partially initialized TABLE_SHARE with the table name, database name, and a path to table files filled in, but without a table structure. Unlike discover_table(), here the TABLE_SHARE has all the engine-defined table attributes in the the TABLE_SHARE::option_struct structure. Based on the values of these attributes the discover_table_structure() method should initialize the TABLE_SHARE with the desired set of fields and keys. It can use TABLE_SHARE helper methods init_from_binary_frm_image() and init_from_sql_statement_string() for that.


Comments loading...