Table Discovery

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, the 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 say, the 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 its own. But it can discover the table structure if the user provides 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 this. 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 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);

handlerton::tablefile_extensions

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.

handlerton::discover_table_names()

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
{
  public:
  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 extension tablefile_extensions[0].

handlerton::discover_table_existence()

In some rare cases MariaDB needs to 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 the 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 determining 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.

handlerton::discover_table()

This is the main method of 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 the TABLE_SHARE methods init_from_binary_frm_image() and init_from_sql_statement_string().

TABLE_SHARE::init_from_binary_frm_image()

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 redundant, because a copy of it is stored in the engine.

TABLE_SHARE::init_from_sql_statement_string()

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

TABLE_SHARE::read_frm_image()

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.

TABLE_SHARE::free_frm_image()

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

HA_ERR_TABLE_DEF_CHANGED

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 a 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.

TABLE_SHARE::tabledef_version

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 the 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 it 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 uses 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.

handlerton::discover_table_structure()

Much like the 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.

The role of .frm files

Before table discovery was introduced, MariaDB used .frm files to store the table definition. But now the engine can store the table definition (if the engine supports automatic discovery, of course), and .frm files become redundant. Still, the server can use .frm files for such an engine — but they are no longer the only source of the table definition. Now .frm files are merely a cache of the table definition, while the original authoritative table definition is stored in the engine. Like any cache, its purpose is to reduce discovery attempts for a table. The engine decides whether it makes sense to cache table definition in the .frm file or not (see the second argument for the TABLE_SHARE::init_from_binary_frm_image()). For example, the Archive engine uses .frm cache, while the Sequence engine does not. In other words, MariaDB creates .frm files for Archive tables, but not for Sequence tables.

The cache is completely transparent for a user; MariaDB makes sure that it always stores the actual table definition and invalidates the .frm file automatically when it becomes out of date. This can happen, for example, if a user copies a new Archive table into the datadir and forgets to delete the .frm file of the old table with the same name.