Digging into MariaDB's Source 2: The Big Picture — the Server Layer, Storage Engines, and the Life of One Query

What this post covers

This is the second post in "Digging into MariaDB's Source". In Part 1 we fetched the source, made a Debug build, and sketched the folder layout and the main road a single query travels as one long line. This post turns that road into a map divided into numbered zones. Parts 3 to 12 each zoom into one zone of this map, and at the top of each post they say where they are using this post's numbers, like "zone ④ Preparation on the Part 2 map".

If you have used MariaDB, you have probably written CREATE TABLE … ENGINE=InnoDB. Put ENGINE=Aria or ENGINE=MEMORY in the same spot and your SELECT statements do not change by a single character. What does that "change one word" property look like in the source? Using that one word as a bridge, this post answers the following questions.

• Why is MariaDB split into a server layer (sql/) and storage engines (storage/), and what is the boundary between them in the source?
• How do handler and handlerton differ, and how does the single word ENGINE=InnoDB become an ha_innobase object?
• Which zones does one SELECT pass through, from the connection down to the engine and back to the client — and where on that path do Parts 3–12 sit?
• Who frees the memory of all the objects created along the way, and when?

All line numbers in this post refer to this commit. Part 1 shows how to get the same commit and build the Docker image used for the examples. Paths start at the repository root.

Since this post draws a map, it follows only the main lines. Each section points ahead to the post that reads that zone in detail ("we'll see this in Part N").

Terms to know first

Server layer and storage engines. Inside the MariaDB server (mariadbd) there are two layers. The upper one, the server layer, does everything to do with SQL — accepting connections, checking privileges, parsing, choosing an execution plan, sending results, the binlog used for replication — and nearly all of its source is in sql/. The lower one, the storage engines, actually keeps rows on disk or in memory and fetches them back. InnoDB, Aria, MyISAM and MEMORY are each one engine, and each has its own folder: storage/innobase/, storage/maria/, storage/myisam/, storage/heap/.

handler is the C++ class that sits on the boundary between the two layers (class handler in sql/handler.h). More precisely, it is an object that represents "one opened table". The server layer always says "give me the next row", "find by this key", "write this row" through the methods of this class, and each engine inherits from it and fills those methods in. InnoDB's is ha_innobase, Aria's is ha_maria, MyISAM's is ha_myisam, and MEMORY's is ha_heap. The ha_ prefix is short for handler.

handlerton is a struct of which there is exactly one per engine (struct handlerton). The source describes it as a "singleton structure — one instance per storage engine" (section 3). It holds, as function pointers, the engine-wide jobs that have nothing to do with a particular table — creating handler objects, committing and rolling back transactions, deleting files on DROP DATABASE.

THD (the TH comes from "thread") is the class that holds all the state of one connection (class THD in sql/sql_class.h). The socket, the statement currently running, the logged-in user, the tables it has open, the transaction in progress, the session variables — all of it is here. That is why most server functions take THD *thd as their first argument.

LEX · SELECT_LEX · Item. After the parser reads an SQL statement, the result is stored in a single struct called LEX. Along with the statement type (SQLCOM_SELECT, SQLCOM_INSERT …), each SELECT becomes a SELECT_LEX object (its real name is st_select_lex), and the column names, constants and expressions inside it (a + 1, note LIKE 'row%') become a tree of objects of a class called Item. We'll see this in detail in Part 6.

TABLE and TABLE_SHARE. A table has one definition (columns, keys, engine — read from the .frm file), and every connection that opens the table gets its own opened instance. The first is TABLE_SHARE, the second is TABLE. One handler object is attached to each TABLE. We'll see this in detail in Part 7.

MEM_ROOT is MariaDB's memory pool. It carves small allocations out of large blocks and, instead of free-ing them one by one, throws them away all at once later. Most objects created while processing a statement come from the connection's MEM_ROOT, which is emptied wholesale when the statement ends (section 7).

1. One map — the server layer and the storage engines

The series map

Let's spread out the map first. I numbered the zones a SELECT passes through from the moment it enters the server until it leaves as a result. This series refers to zones by these numbers.

Blue zones are the server layer, the yellow zone is the storage engine. Each zone lists the part that reads it in detail.

Zones ①–⑦ and ⑨ exist as a single copy of code no matter which engine is used; only ⑧ exists once per engine. The rest of this post revisits the map from three directions. Sections 2–4 look at the boundary between ⑦ and ⑧ (handler, handlerton, plugins), section 5 walks the road from ① to ⑨, and sections 6–7 cover THD and MEM_ROOT, which every zone shares.

Seen as folders

Here is the same map translated into repository folders. The size and contents of each folder are in the folder map of Part 1.

The blue box is the server layer, the yellow box the engines, and the grey boxes the common libraries both of them use.

mysys/ and strings/ are C libraries shared by the server layer and the engines. Opening files (my_open), memory (my_malloc, and the MEM_ROOT of section 7), hashes and character set conversion live here. InnoDB uses them too: at the reference commit, 14 files under storage/innobase/ include my_sys.h or mysys_err.h, and 77 files use DBUG_ macros.

Why split them — one copy of SQL, many ways to store

The comment right above class handler defines the class in one sentence.

The handler class is the interface for dynamically loadable storage engines.

(sql/handler.h lines 3405–3406) In other words, the boundary was drawn so that SQL syntax, the optimizer, privileges and replication are written once, and only "where and how to keep rows" can differ per engine. From the engine's side, filling in a few handler methods gets you SQL for free (ha_example in sections 2 and 4). From the server's side, there is a single executor no matter how many engines there are (the backtrace in section 5).

Example 1: four tables that differ only in engine

To see whether it really is "just one word", I created four tables of the same shape, changing only the engine.

All examples in this post were run in a Docker container (Ubuntu 24.04) with MariaDB 13.2.0 (13.2.0-MariaDB-debug), a Debug build of the reference commit (c894d97781) as is. The script is ex/u02-architecture/run.sh, and the --- lines in the output are headings printed by the script (the SELECT VERSION() result at the very start and the table holding the first heading are left out).

-- Example written for this post (not MariaDB source code)
-- Create four tables of the same shape, changing only the engine, and insert the same 5 rows.
-- The rows come from seq_1_to_5 of the SEQUENCE engine (that is an engine too).
CREATE DATABASE lab;
USE lab;
CREATE TABLE t_innodb (id INT PRIMARY KEY, note VARCHAR(20)) ENGINE=InnoDB;
CREATE TABLE t_aria   (id INT PRIMARY KEY, note VARCHAR(20)) ENGINE=Aria;
CREATE TABLE t_myisam (id INT PRIMARY KEY, note VARCHAR(20)) ENGINE=MyISAM;
CREATE TABLE t_memory (id INT PRIMARY KEY, note VARCHAR(20)) ENGINE=MEMORY;
INSERT INTO t_innodb SELECT seq, CONCAT('row ', seq) FROM seq_1_to_5;
INSERT INTO t_aria   SELECT * FROM t_innodb;
INSERT INTO t_myisam SELECT * FROM t_innodb;
INSERT INTO t_memory SELECT * FROM t_innodb;

SELECT '--- 1. information_schema.TABLES' AS step;
SELECT TABLE_NAME, ENGINE, TABLE_ROWS, ROW_FORMAT
  FROM information_schema.TABLES
 WHERE TABLE_SCHEMA = 'lab' ORDER BY TABLE_NAME;

Then run.sh lists the data directory with ls -1 /var/lib/mariadb-lab/lab | sort.

+------------+--------+------------+------------+
 TABLE_NAME | ENGINE | TABLE_ROWS | ROW_FORMAT |
+------------+--------+------------+------------+
 t_aria     | Aria   |          5 | Page       |
 t_innodb   | InnoDB |          5 | Dynamic    |
 t_memory   | MEMORY |          5 | Fixed      |
 t_myisam   | MyISAM |          5 | Dynamic    |
+------------+--------+------------+------------+
--- 2. files in the data directory (datadir/lab)
db.opt
t_aria.MAD
t_aria.MAI
t_aria.frm
t_innodb.frm
t_innodb.ibd
t_memory.frm
t_myisam.MYD
t_myisam.MYI
t_myisam.frm

• INSERT … SELECT worked the same way on all four tables. Four engines, one SQL.
• ROW_FORMAT differs per engine (Page, Dynamic, Fixed). The engine decides what a row looks like on disk.
• In the data directory, every table has a .frm, and the other files differ per engine. InnoDB has one .ibd, Aria has .MAI (index) and .MAD (data), MyISAM has .MYI and .MYD, and MEMORY keeps its data only in memory, so it has just the .frm.

The .frm file belongs to the server layer. At startup the server sets the extension of definition files to .frm (sql/init.cc line 43) and reads table definitions (TABLE_SHARE) from these files. The engine's own file extensions, on the other hand, are written by the engine into its handlerton. MyISAM lists ".MYI" and ".MYD" in its ha_myisam_exts[] array (ha_myisam.cc lines 747–751), InnoDB lists .ibd and .isl, and Aria lists .MAI and .MAD. The default delete_table() and rename_table() of handler use this list to know which files to delete or rename (see the comment at handler.h lines 1535–1539). We'll come back to the fact that the field holding this list, tablefile_extensions, belongs to the handlerton in section 3.

Easy to confuse: not everything an "engine does" lives in storage/

Transactions are the engine's job, but the code that coordinates a commit across several engines (ha_commit_trans()) lives in the server layer, in sql/handler.cc. The binlog used for replication also belongs to the server layer (sql/log.cc) (the reference commit also contains a recent feature, binlog_storage_engine, that keeps the binlog inside InnoDB — see Part 12). Each engine is responsible for its own tables' rows and its own logs (redo and undo, for InnoDB), and the coordination above that is done by the server layer. We'll meet this boundary again in Part 11 and Part 12.

2. The boundary: handler — one opened table

What a handler holds

Here is the start of class handler. It points to the table definition, the opened table, and its own engine's handlerton.

class handler :public Sql_alloc
{
// ...
protected:
  TABLE_SHARE *table_share;   /* The table definition */
  TABLE *table;               /* The current open table */
// ...
public:
  handlerton *ht;               /* storage engine of this handler */
  // ...
  uchar *ref;			/* Pointer to current row */

sql/handler.h lines 3452–3487

Note also that handler inherits from Sql_alloc. new for this class gets its memory from a MEM_ROOT, not from malloc (section 7).

What the engine fills in: the low-level virtual functions

class handler contains many virtual functions meant to be overridden by engines. One group of them carries this comment.

private:
  /*
    Low-level primitives for storage engines.  These should be
    overridden by the storage engine class. To call these methods, use
    the corresponding 'ha_*' method above.
  */

  virtual int open(const char *name, int mode, uint test_if_locked)=0;
  // ...
  virtual int rnd_init(bool scan)= 0;
  // ...
  virtual int write_row(const uchar *buf __attribute__((unused)))
  {
    return HA_ERR_WRONG_COMMAND;
  }

sql/handler.h lines 5428–5451

"Low-level primitives for storage engines. They should be overridden by the engine class, and to call them, use the corresponding ha_* method". open(), rnd_init() and write_row() are private, so the rest of the server layer cannot even call them directly. It always goes through ha_open(), ha_rnd_init() and ha_write_row().

Counting the functions that end in = 0 — pure virtual functions every engine must implement — in class handler (lines 3452–5782) at the reference commit gives 11.

As the excerpt shows, write_row() has a default implementation that returns HA_ERR_WRONG_COMMAND. So an engine that implements only these 11 becomes a "read-only engine". Index reads (index_read_map() …) have default implementations in the same way.

The top two exist once per engine; the one below exists once per opened table. The italic rnd_next() is a virtual function the engine fills in.

All five engine classes inherit from handler in a one-line declaration: class ha_innobase final : public handler (ha_innodb.h line 63), class __attribute__((visibility("default"))) ha_maria :public handler (ha_maria.h line 43), class ha_myisam final : public handler (ha_myisam.h line 39), class ha_heap final : public handler (ha_heap.h line 23), and class ha_example: public handler (ha_example.h line 59).

What the server calls: the ha_ wrappers

So what does ha_rnd_next(), the method the server layer calls, actually do? It wraps the call to the engine's rnd_next() with server-layer work before and after.

int handler::ha_rnd_next(uchar *buf)
{
  int result;
  // ...
  do
  {
    TABLE_IO_WAIT(tracker, PSI_TABLE_FETCH_ROW, MAX_KEY, result,
      { result= rnd_next(buf); })
    if (result != HA_ERR_RECORD_DELETED)
      break;
    status_var_increment(table->in_use->status_var.ha_read_rnd_deleted_count);
  } while (!table->in_use->check_killed(1));

  if (result == HA_ERR_RECORD_DELETED)
    result= HA_ERR_ABORTED_BY_USER;
  else
  {
    if (!result)
    {
      update_rows_read();
      if (table->vfield && buf == table->record[0])
        table->update_virtual_fields(this, VCOL_UPDATE_FOR_READ);
    }
    increment_statistics(&SSV::ha_read_rnd_next_count);

sql/handler.cc lines 4110–4146

1. It calls the engine's rnd_next(buf) inside the TABLE_IO_WAIT macro. This macro wraps the engine call with the timing used by the ANALYZE statement and with performance_schema's table I/O instrumentation (handler.h lines 6031–6041).
2. If the engine returns "deleted row" (HA_ERR_RECORD_DELETED), it asks for the next row again, checking along the way whether the connection has been KILLed.
3. The last line, increment_statistics(&SSV::ha_read_rnd_next_count), bumps the Handler_read_rnd_next we see in SHOW STATUS by one.

In other words, the Handler_read_* status variables are numbers counted by the server layer's wrappers, not by the engine. If so, doing the same work with a different engine should give the same numbers.

Example 2: Handler_read_rnd_next is the same for every engine

I did one full scan of each of the four tables from Example 1 and printed Handler_read_rnd_next. note has no index, so WHERE note LIKE 'row%' becomes a full scan. Session status variables start from 0 on every new connection.

for t in t_innodb t_aria t_myisam t_memory; do
  out=$(mariadb -N -e "SELECT COUNT(*) FROM lab.$t WHERE note LIKE 'row%';
                       SHOW SESSION STATUS LIKE 'Handler_read_rnd_next';")
  rows=$(echo "$out" | head -1)
  calls=$(echo "$out" | tail -1 | awk '{print $2}')
  printf '%-9s rows=%s  Handler_read_rnd_next=%s\n' "$t" "$rows" "$calls"
done

--- 3. one full scan per engine: rows found / Handler_read_rnd_next
t_innodb  rows=5  Handler_read_rnd_next=6
t_aria    rows=5  Handler_read_rnd_next=6
t_myisam  rows=5  Handler_read_rnd_next=6
t_memory  rows=5  Handler_read_rnd_next=6

All four engines give 6. Why 6 when there are 5 rows? The answer is in the wrapper above too. Five calls returned a row, and on the sixth call the engine returned HA_ERR_END_OF_FILE (the end). As long as the result is not HA_ERR_RECORD_DELETED, the wrapper goes into the else branch and counts the call that reported the end as well.

Easy to confuse: the server and the engine exchange rows as "MySQL format" bytes

The buf in rnd_next(uchar *buf) is usually a byte buffer called table->record[0]. It holds one row in a fixed layout defined by the server layer (null bits, then each column at a fixed offset) — the "TableRecordFormat" described in the comment above class handler (handler.h lines 3417–3426). InnoDB's comments call it "MySQL format" (the description of the buf argument of ha_innobase::rnd_next(), ha_innodb.cc lines 9664–9665). The engine has to convert a row from its own on-disk format into this format. We pass by the place where InnoDB does that conversion in Part 10 and Part 11.

3. One per engine: handlerton

What the struct says about itself

If a handler is "per table", who takes care of engine-wide work? The comment right above struct handlerton contrasts the two.

/*
  handlerton is a singleton structure - one instance per storage engine -
  to provide access to storage engine functionality that works on the
  "global" level (unlike handler class that works on a per-table basis)
*/
struct handlerton : public transaction_participant
{
  // ...
  enum legacy_db_type db_type;
  // ...
  const char **tablefile_extensions; // by default - empty list
  // ...
  handler *(*create)(handlerton *hton, TABLE_SHARE *table, MEM_ROOT *mem_root);

sql/handler.h lines 1514–1555

"handlerton is a singleton structure — one instance per storage engine — to provide access to storage engine functionality that works on the 'global' level (unlike the handler class that works on a per-table basis)". Nearly all of its fields are function pointers. create in the excerpt is the most important one: it creates and returns a handler object for this engine. Next to it are many more, such as drop_database, show_status (SHOW ENGINE … STATUS), flush_logs, and the binlog_* pointers for the recent feature that keeps the binlog inside an engine.

Notice that handler is built with C++ virtual functions, while handlerton is a C-style struct of function pointers. We looked closely at how to hand-build an "interface" out of a struct of function pointers in httpd Part 2. An engine announces its capabilities to the server by filling in the slots of this struct.

Its parent: transaction_participant

handlerton inherits from a struct called transaction_participant. It holds the function pointers that anything taking part in a transaction must fill in.

struct transaction_participant
{
  // ...
  uint slot;
  // ...
  int (*commit)(THD *thd, bool all);
  int  (*rollback)(THD *thd, bool all);
  int  (*prepare)(THD *thd, bool all);

sql/handler.h lines 1290–1350

slot is the index of the slot in each connection (THD) where the engine can hang its own data (ha_data in section 6). commit, rollback and prepare are the engine-side functions that are ultimately called when the user types COMMIT. How the server layer's ha_commit_trans() calls commit on every engine taking part in the transaction is covered in Part 11.

Easy to confuse: transaction participants are not only engines

The source shows why transaction_participant was split out. Things that are not engines but still have to take part in commit and rollback use this struct without a handlerton. The binlog does (transaction_participant binlog_tp;, sql/log.cc line 2031), and so does online ALTER TABLE (static transaction_participant online_alter_tp=, sql/online_alter.cc line 35). That is also why the commit-coordinating functions in sql/handler.cc take a transaction_participant * rather than a handlerton * (for example trans_register_ha(), handler.cc line 1473).

Where the engine fills in its handlerton

When the server starts (or at INSTALL SONAME time for a dynamic plugin), an engine fills in the slots of its handlerton in its own init function. For the smallest engine, EXAMPLE, this is all there is.

static int example_init_func(void *p)
{
  // ...
  example_hton= (handlerton *)p;
  example_hton->create=  example_create_handler;
  example_hton->flags=   HTON_CAN_RECREATE;
  // ...
  example_hton->tablefile_extensions= ha_example_exts;
// ...
static handler* example_create_handler(handlerton *hton,
                                       TABLE_SHARE *table, 
                                       MEM_ROOT *mem_root)
{
  return new (mem_root) ha_example(hton, table);
}

storage/example/ha_example.cc lines 245–296

1. The p passed in by the server is this engine's handlerton. The engine does not create it — the server creates it and hands it over (below).
2. It puts example_create_handler into the create slot. That function is one line, new (mem_root) ha_example(hton, table): it creates one handler object on a MEM_ROOT and returns it.
3. The list of file extensions (tablefile_extensions), table options and so on are filled in here too.

InnoDB has the same shape, just with many more slots.

static int innodb_init(void* p)
{
	DBUG_ENTER("innodb_init");
	handlerton* innobase_hton= static_cast<handlerton*>(p);
	// ...
	innobase_hton->commit = innobase_commit;
	innobase_hton->rollback = innobase_rollback;
	// ...
	innobase_hton->create = innobase_create_handler;

storage/innobase/handler/ha_innodb.cc lines 4295–4326

innobase_commit and innobase_rollback go into the commit and rollback slots, and innobase_create_handler into create. The body of innobase_create_handler() is also a single line, return(new (mem_root) ha_innobase(hton, table)); (ha_innodb.cc lines 1717–1726).

The one that creates the handlerton and passes it to the engine's init function is the server's ha_initialize_handlerton().

int ha_initialize_handlerton(void *plugin_)
{
  // ...
  hton= (handlerton *)my_malloc(key_memory_handlerton, sizeof(handlerton),
                                MYF(MY_WME | MY_ZEROFILL));
  // ...
  hton->tablefile_extensions= hton_no_exts;
  // ...
  plugin->data= hton; // shortcut for the future
  if (plugin->plugin->init && (ret= plugin->plugin->init(hton)))
    goto err;

sql/handler.cc lines 717–743

It allocates one zero-filled handlerton with my_malloc, puts default values into a few slots, and passes it to the plugin's init function (example_init_func, innodb_init). This function is called once per engine, so there is one handlerton per engine. When during server startup it is called is covered in Part 3.

How ENGINE=InnoDB becomes an ha_innobase object

Now let's see where a handler is created when a table is opened. This is inside open_table_from_share(), which builds an opened instance (TABLE) from a table definition (TABLE_SHARE).

    if (!(outparam->file= get_new_handler(share, &outparam->mem_root,
                                          share->db_type())))

sql/table.cc lines 4550–4551

share->db_type() returns the handlerton of the engine plugin (db_plugin) recorded in the definition (table.h lines 848–849). For a table created with CREATE TABLE … ENGINE=InnoDB, that is InnoDB's handlerton. get_new_handler() receives it and calls the handlerton's create slot.

handler *get_new_handler(TABLE_SHARE *share, MEM_ROOT *alloc,
                         handlerton *db_type)
{
  // ...
  if (ha_storage_engine_is_enabled(db_type))
  {
    if ((file= db_type->create(db_type, share, alloc)))
      file->init();
    DBUG_RETURN(file);
  }
  // ...
  file= get_new_handler(share, alloc, ha_default_handlerton(current_thd));
  DBUG_RETURN(file);
}

sql/handler.cc lines 382–402

If the engine is enabled, it calls db_type->create(…) — for InnoDB that is innobase_create_handler() — and an ha_innobase object comes out. If the engine is disabled, it tries again with the default engine. The third argument, &outparam->mem_root, is that TABLE's MEM_ROOT, so the handler object lives exactly as long as the opened table (section 7).

One handlerton per engine, one TABLE_SHARE per table, one handler per opened TABLE. If two connections open the same table, there are two handlers.

In Example 4 (section 5), while gdb was stopped in ha_innobase::rnd_next(), I also printed some fields of the handler object. This is that part of the same run's output.

$1 = DB_TYPE_INNODB
$2 = 0x772c8404c074 "t_innodb"
$3 = true

this->ht->db_type is DB_TYPE_INNODB — the handlerton that created this handler is InnoDB's — this->table_share->table_name.str is t_innodb, and this->table->s == this->table_share is true. The opened TABLE's definition pointer (s) and the definition pointer held by the handler point to the same TABLE_SHARE. The address (0x772c…) differs on every run.

4. Engines are plugins too

One plugin declaration

At the end of ha_example.cc is a declaration of what this file offers to the server.

maria_declare_plugin(example)
{
  MYSQL_STORAGE_ENGINE_PLUGIN,
  &example_storage_engine,
  "EXAMPLE",
  "Brian Aker, MySQL AB",
  "Example storage engine",
  PLUGIN_LICENSE_GPL,
  example_init_func,                            /* Plugin Init */

storage/example/ha_example.cc lines 1085–1093

The first slot, MYSQL_STORAGE_ENGINE_PLUGIN, says "this plugin is a storage engine", and example_init_func in the seventh slot is the init function we saw in section 3. InnoDB makes the same kind of declaration with maria_declare_plugin(innobase) (ha_innodb.cc line 20762), and Aria with maria_declare_plugin(aria) (ha_maria.cc line 4436). A storage engine is one kind of plugin.

The plugin types, and the function that initializes each type, sit side by side in two tables in sql/sql_plugin.cc.

const LEX_CSTRING plugin_type_names[MYSQL_MAX_PLUGIN_TYPE_NUM]=
{
  { STRING_WITH_LEN("UDF") },
  { STRING_WITH_LEN("STORAGE ENGINE") },
// ...
plugin_type_init plugin_type_initialize[MYSQL_MAX_PLUGIN_TYPE_NUM]=
{
  0, ha_initialize_handlerton, 0, 0,initialize_schema_table,
  initialize_audit_plugin, 0, 0, 0, initialize_encryption_plugin,
  initialize_data_type_plugin, 0
};

sql/sql_plugin.cc lines 91–128

The second entry of the first table is "STORAGE ENGINE", and the second entry of the second table is ha_initialize_handlerton. A plugin of type number 1 (MYSQL_STORAGE_ENGINE_PLUGIN, include/mysql/plugin.h line 93) is therefore initialized by the ha_initialize_handlerton() we saw in section 3. At the reference commit there are 12 plugin types in all, including authentication, audit, information schema, encryption and data types.

One type number in the plugin declaration picks the same slot in both tables, and the function in that slot calls the engine's init function. Values come from the two excerpts above and the one in Section 3.

Example 3: loading the smallest engine at runtime

In the example image, engines such as InnoDB, Aria, MyISAM and MEMORY are built statically into the server binary, while EXAMPLE is a separately built ha_example.so. I first listed the engines, then loaded EXAMPLE with INSTALL SONAME and created a table with it.

mariadb -t -e "SELECT PLUGIN_NAME, PLUGIN_STATUS, IFNULL(PLUGIN_LIBRARY,'(built in)') AS LIBRARY
                 FROM information_schema.PLUGINS
                WHERE PLUGIN_TYPE = 'STORAGE ENGINE' ORDER BY PLUGIN_NAME"
mariadb -t -e "INSTALL SONAME 'ha_example';
               SELECT PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_LIBRARY
                 FROM information_schema.PLUGINS WHERE PLUGIN_NAME = 'EXAMPLE';
               CREATE TABLE lab.t_example (id INT, note VARCHAR(20)) ENGINE=EXAMPLE;
               INSERT INTO lab.t_example SELECT * FROM lab.t_innodb;
               SELECT ROW_COUNT() AS rows_inserted;"
# Open a new connection so the session status variables start from 0.
mariadb -t -e "SELECT COUNT(*) AS rows_in_example FROM lab.t_example WHERE note LIKE 'row%';
               SHOW SESSION STATUS LIKE 'Handler_read_rnd_next';"
ls -1 /var/lib/mariadb-lab/lab | grep example

--- 4. storage engines are plugins
+--------------------+---------------+------------+
 PLUGIN_NAME        | PLUGIN_STATUS | LIBRARY    |
+--------------------+---------------+------------+
 Aria               | ACTIVE        | (built in) |
 CSV                | ACTIVE        | (built in) |
 InnoDB             | ACTIVE        | (built in) |
 MEMORY             | ACTIVE        | (built in) |
 MRG_MyISAM         | ACTIVE        | (built in) |
 MyISAM             | ACTIVE        | (built in) |
 partition          | ACTIVE        | (built in) |
 PERFORMANCE_SCHEMA | ACTIVE        | (built in) |
 SEQUENCE           | ACTIVE        | (built in) |
 SQL_SEQUENCE       | ACTIVE        | (built in) |
+--------------------+---------------+------------+
+-------------+---------------+----------------+
 PLUGIN_NAME | PLUGIN_STATUS | PLUGIN_LIBRARY |
+-------------+---------------+----------------+
 EXAMPLE     | ACTIVE        | ha_example.so  |
+-------------+---------------+----------------+
+---------------+
 rows_inserted |
+---------------+
             5 |
+---------------+
+-----------------+
 rows_in_example |
+-----------------+
               0 |
+-----------------+
+-----------------------+-------+
 Variable_name         | Value |
+-----------------------+-------+
 Handler_read_rnd_next | 1     |
+-----------------------+-------+
t_example.frm

• Statically built engines have an empty PLUGIN_LIBRARY ((built in)), and the EXAMPLE engine we loaded shows ha_example.so. PERFORMANCE_SCHEMA is in the engine list too — the tables of the performance_schema database are also read through class ha_perfschema final : public handler (ha_perfschema.h line 44).
• INSERT reported "5 rows" inserted, yet COUNT(*) is 0. That is exactly what two functions of ha_example do.

int ha_example::write_row(const uchar *buf)
{
  DBUG_ENTER("ha_example::write_row");
  // ...
  DBUG_RETURN(0);
}
// ...
int ha_example::rnd_next(uchar *buf)
{
  int rc;
  DBUG_ENTER("ha_example::rnd_next");
  rc= HA_ERR_END_OF_FILE;
  DBUG_RETURN(rc);
}

storage/example/ha_example.cc lines 389–588

write_row() throws the row away and returns success (0), and rnd_next() returns HA_ERR_END_OF_FILE from the very first call. That is why Handler_read_rnd_next counts only the one call that reported the end, giving 1. Only a .frm was created, with no data file, because this engine stores nothing at all. About half of the 1,116-line ha_example.cc is comments, and this much is enough for CREATE TABLE, INSERT and SELECT to work — because parsing, planning and sending results are all done by the server layer.

Blue boxes are the server layer, yellow boxes are the engine's functions. The numbers are Example 3's actual output.

Easy to confuse: not every plugin is an engine

The plugin/ folder collects plugins that are not engines: authentication (auth_ed25519, auth_socket …), audit (server_audit), data types (type_inet, type_uuid), information tables (metadata_lock_info, query_response_time) and so on. Engines, on the other hand, live in storage/. Both are loaded through the same plugin machinery (sql/sql_plugin.cc), but only engines have a handlerton and create handlers. The plugin machinery as a whole is covered in Part 3.

5. The life of one query

In one picture

Now let's walk through zones ① to ⑨ in one go. This is what happens when a client sends a SELECT.

Solid arrows are calls; dashed arrows are returns or things being sent. With many rows, everything from ha_rnd_next() to send_data() repeats once per row.

Let's touch just one spot in the source for each zone.

① Connection. The thread in charge of a connection runs this loop in do_handle_one_connection() until the connection closes. One call to do_command() is one command.

    while (thd_is_connection_alive(thd))
    {
      if (mysql_audit_release_required(thd))
        mysql_audit_release(thd);
      if (do_command(thd))
	break;
    }
    end_connection(thd);

sql/sql_connect.cc lines 1510–1517

This one-thread-per-connection mode, and the thread pool where a few threads share many connections, are covered in Part 4.

② Command. do_command() reads one packet, takes off its first byte (the command type) and hands it to dispatch_command() (sql_parse.cc lines 1437–1438). dispatch_command() branches with switch (command) (line 1713), and COM_QUERY (line 1862), which carries text SQL, ends up calling mysql_parse() (line 1903). The packet format and the other commands are covered in Part 5.

③ Parsing. mysql_parse() looks in the query cache first; if the query isn't there, it runs the parser and then hands over to execution.

void mysql_parse(THD *thd, char *rawbuf, uint length,
                 Parser_state *parser_state)
  // ...
  lex_start(thd);
  thd->reset_for_next_command();

  if (query_cache_send_result_to_client(thd, rawbuf, length) <= 0)
    // ...
    bool err= parse_sql(thd, parser_state, NULL, true);
          // ...
          error= mysql_execute_command(thd);

sql/sql_parse.cc lines 7927–8005

When parse_sql() returns, thd->lex holds the whole statement. LEX starts from the outermost SELECT_LEX_UNIT unit; (sql_lex.h line 3407) and contains the SELECT_LEX objects. Here is a quick look at SELECT_LEX.

class st_select_lex: public st_select_lex_node
{
  // ...
  Item *where, *having;                         /* WHERE & HAVING clauses */
  // ...
  SQL_I_List<TABLE_LIST>  table_list;
  // ...
  List<Item>          item_list;  /* list of fields & expressions */

sql/sql_lex.h lines 1146–1282

For SELECT a, b FROM t WHERE c > 1, item_list gets two Items standing for a and b, table_list gets t, and where gets the root of the Item tree standing for c > 1. The statement type is in sql_command of Query_tables_list, which LEX inherits from (sql_lex.h line 1864). How the lexer and the Bison grammar build all this is covered in Part 6.

④ Preparation. mysql_execute_command() branches per statement type with switch (lex->sql_command) (line 3946). A SELECT checks privileges and then goes to execute_sqlcom_select().

    if (all_tables)
      res= check_table_access(thd, SELECT_ACL, all_tables, 0, UINT_MAX, 0);
    else
      res= check_access(thd, SELECT_ACL, any_db.str, NULL,NULL, 0, 0);

    if (!res)
      res= execute_sqlcom_select(thd, all_tables);

sql/sql_parse.cc lines 4032–4038

execute_sqlcom_select() opens every table in the statement with open_and_lock_tables() (line 6186). This is where open_table_from_share() and get_new_handler() from section 3 get called and handlers are created. Then it calls handle_select() (line 6266). Privilege checks, opening tables, metadata locks (MDL) and name resolution are covered in Part 7.

④⑤⑥ Preparation · optimization · execution. mysql_select(), called by handle_select(), has all three steps on one screen.

    if (!(join= new (thd->mem_root) JOIN(thd, fields, select_options, result)))
	DBUG_RETURN(TRUE);
    // ...
    if (!join->prepared &&
        (err= join->prepare(tables, conds, og_num, order, false, group, having,
                            proc_param, select_lex, unit)))
    {
      goto err;
    }
  }
  // ...
  if ((err= join->optimize()))
  {
    goto err;					// 1
  }
  // ...
  exec_error= join->exec();

sql/sql_select.cc lines 5427–5461

JOIN is the object that executes one SELECT. prepare() ties column names to the actual columns of the tables (the second half of ④), optimize() picks the join order and access methods (⑤, Part 8), and exec() runs that plan (⑥, Part 9). Note that the JOIN object, too, is created with new (thd->mem_root); we come back to this in section 7.

⑦⑧ handler and engine. exec() asks the engine for "the next row" of each table. For a full scan this is section 2's handler::ha_rnd_next() → the engine's rnd_next(). InnoDB's rnd_next() calls index_first() (the first record of the clustered index) on the first call and general_fetch(buf, ROW_SEL_NEXT, 0) after that (ha_innodb.cc lines 9661–9683). What happens inside belongs to Part 10 and Part 11.

⑨ Sending results. A row that passes the conditions goes out to Protocol through a select_send object.

int select_send::send_data(List<Item> &items)
{
  Protocol *protocol= thd->protocol;
  // ...
  protocol->prepare_for_resend();
  if (protocol->send_result_set_row(&items))
  // ...
  thd->inc_sent_row_count(1);
  // ...
  if (likely(thd->vio_ok()))
    DBUG_RETURN(protocol->write());

sql/sql_class.cc lines 3477–3493

thd->protocol is either the text protocol (Protocol_text) or the binary protocol for prepared statements (Protocol_binary). send_result_set_row() turns the row into a packet and write() sends it. inc_sent_row_count(1) is what the slow query log's Rows_sent counts (sql/log.cc lines 4170–4175). The packet format is covered in Part 5.

Example 4: checking our place on the map with gdb

To check that this road is real, I attached gdb to the running mariadbd, set a breakpoint on ha_innobase::rnd_next, and sent SELECT COUNT(*) FROM lab.t_innodb WHERE note LIKE 'row%' from another connection. This is the gdb command file (gdb-innodb.cmd) minus a few set lines and the comments.

break ha_innobase::rnd_next
continue
bt
print this->ht->db_type
print this->table_share->table_name.str
print this->table->s == this->table_share
frame function mysql_execute_command
print thd->thread_id
print thd->lex->sql_command
print thd->query_string.string.str
detach
quit

One of the omitted lines, set print frame-arguments none, shortens argument values to .... Here is the backtrace (the 0x… addresses differ on every run).

#0  ha_innobase::rnd_next (this=..., buf=...) at /usr/src/mariadb/storage/innobase/handler/ha_innodb.cc:9668
#1  0x0000557d5689f504 in handler::ha_rnd_next (this=..., buf=...) at /usr/src/mariadb/sql/handler.cc:4129
#2  0x0000557d56278f03 in rr_sequential (info=...) at /usr/src/mariadb/sql/records.cc:517
#3  0x0000557d5625f09b in READ_RECORD::read_record (this=...) at /usr/src/mariadb/sql/records.h:77
#4  0x0000557d564b97ba in join_init_read_record (tab=...) at /usr/src/mariadb/sql/sql_select.cc:26694
#5  0x0000557d564b6b39 in sub_select (join=..., join_tab=..., end_of_records=...) at /usr/src/mariadb/sql/sql_select.cc:25572
#6  0x0000557d564b5c49 in do_select (join=..., procedure=...) at /usr/src/mariadb/sql/sql_select.cc:25086
#7  0x0000557d5647ba92 in JOIN::exec_inner (this=...) at /usr/src/mariadb/sql/sql_select.cc:5147
#8  0x0000557d5647ab0a in JOIN::exec (this=...) at /usr/src/mariadb/sql/sql_select.cc:4935
#9  0x0000557d5647c4f7 in mysql_select (thd=..., tables=..., fields=..., conds=..., og_num=..., order=..., group=..., having=..., proc_param=..., select_options=..., result=..., unit=..., select_lex=...) at /usr/src/mariadb/sql/sql_select.cc:5461
#10 0x0000557d56469eb2 in handle_select (thd=..., lex=..., result=..., setup_tables_done_option=...) at /usr/src/mariadb/sql/sql_select.cc:652
#11 0x0000557d563ffe1f in execute_sqlcom_select (thd=..., all_tables=...) at /usr/src/mariadb/sql/sql_parse.cc:6266
#12 0x0000557d563f93a1 in mysql_execute_command (thd=..., is_called_from_prepared_stmt=...) at /usr/src/mariadb/sql/sql_parse.cc:4038
#13 0x0000557d5640481e in mysql_parse (thd=..., rawbuf=..., length=..., parser_state=...) at /usr/src/mariadb/sql/sql_parse.cc:8005
#14 0x0000557d563f3338 in dispatch_command (command=..., thd=..., packet=..., packet_length=..., blocking=...) at /usr/src/mariadb/sql/sql_parse.cc:1903
#15 0x0000557d563f207f in do_command (thd=..., blocking=...) at /usr/src/mariadb/sql/sql_parse.cc:1437
#16 0x0000557d56622c14 in do_handle_one_connection (connect=..., put_in_cache=...) at /usr/src/mariadb/sql/sql_connect.cc:1514
#17 0x0000557d566229a0 in handle_one_connection (arg=...) at /usr/src/mariadb/sql/sql_connect.cc:1426
#18 0x0000557d56c35f50 in pfs_spawn_thread (arg=...) at /usr/src/mariadb/storage/perfschema/pfs.cc:2198
#19 0x00007f2cd9918b84 in ?? () from /lib/x86_64-linux-gnu/libc.so.6
#20 0x00007f2cd99a5ecc in ?? () from /lib/x86_64-linux-gnu/libc.so.6

Read it from the bottom up and the zones of the map line up exactly. The line numbers in the frames also match the lines quoted above (sql_connect.cc:1514, sql_parse.cc:1903·8005·4038·6266, sql_select.cc:5461, handler.cc:4129).

In the same run I also stopped in each engine's rnd_next for the Aria, MyISAM and MEMORY tables and printed only the bottom three frames.

--- 6. gdb: bottom 3 frames for other engines
[t_aria]
#0  ha_maria::rnd_next (this=..., buf=...) at /usr/src/mariadb/storage/maria/ha_maria.cc:2646
#1  0x0000557d5689f504 in handler::ha_rnd_next (this=..., buf=...) at /usr/src/mariadb/sql/handler.cc:4129
#2  0x0000557d56278f03 in rr_sequential (info=...) at /usr/src/mariadb/sql/records.cc:517
[t_myisam]
#0  ha_myisam::rnd_next (this=..., buf=...) at /usr/src/mariadb/storage/myisam/ha_myisam.cc:2060
#1  0x0000557d5689f504 in handler::ha_rnd_next (this=..., buf=...) at /usr/src/mariadb/sql/handler.cc:4129
#2  0x0000557d56278f03 in rr_sequential (info=...) at /usr/src/mariadb/sql/records.cc:517
[t_memory]
#0  ha_heap::rnd_next (this=..., buf=...) at /usr/src/mariadb/storage/heap/ha_heap.cc:425
#1  0x0000557d5689f504 in handler::ha_rnd_next (this=..., buf=...) at /usr/src/mariadb/sql/handler.cc:4129
#2  0x0000557d56278f03 in rr_sequential (info=...) at /usr/src/mariadb/sql/records.cc:517

All four engines are identical down to the address in #1 (0x0000557d5689f504). That means the road splits into four from the very same spot in the same machine code, through a single virtual function call. Everything above ⑦ knows nothing about engines.

In the two blue boxes at the top, all four tables pass the same spot in the machine code; only in the yellow boxes do the engines split. Line numbers and the address are Example 4's actual output.

Example 5: where one result row goes out

COUNT(*) does not send rows one at a time, so this time I sent SELECT id, note FROM lab.t_innodb WHERE note = 'row 3', which returns rows as they are, stopped in Protocol::send_result_set_row, and printed the bottom six frames.

--- 7. gdb: bottom 6 frames at Protocol::send_result_set_row
#0  Protocol::send_result_set_row (this=..., row_items=...) at /usr/src/mariadb/sql/protocol.cc:1350
#1  0x0000557d56356b21 in select_send::send_data (this=..., items=...) at /usr/src/mariadb/sql/sql_class.cc:3483
#2  0x0000557d56356808 in select_result_sink::send_data_with_check (this=..., items=..., u=..., sent=...) at /usr/src/mariadb/sql/sql_class.cc:3381
#3  0x0000557d564baa44 in end_send (join=..., join_tab=..., end_of_records=...) at /usr/src/mariadb/sql/sql_select.cc:26975
#4  0x0000557d564b7421 in evaluate_join_record (join=..., join_tab=..., error=...) at /usr/src/mariadb/sql/sql_select.cc:25808
#5  0x0000557d564b6dbb in sub_select (join=..., join_tab=..., end_of_records=...) at /usr/src/mariadb/sql/sql_select.cc:25612

sub_select in ⑥ Execution reads one row, evaluate_join_record checks the WHERE, and then the final step, end_send, calls select_send::send_data() of ⑨. sql_class.cc:3483 in #1 is the protocol->send_result_set_row(&items) line quoted above. This is what the split of ⑥ into ⑦ and ⑨ on the map looks like: the road down to read rows (⑦) and the road out to send results (⑨) both branch off from sub_select.

The backtraces of Examples 4 and 5 joined at sub_select. Line numbers are the two examples' actual output.

Easy to confuse: JOIN::prepare and JOIN::optimize are missing from the backtrace

The stack in Example 4 shows neither open_and_lock_tables() from ④ nor JOIN::optimize() from ⑤. They are not missing — they have already finished and returned. A call stack only shows "the functions that have not returned yet at this moment". At the moment we reach the engine, mysql_select() has finished prepare() and optimize() and is inside exec() (line 5461 in #9), so the earlier steps are gone from the stack. To see them you have to set a separate breakpoint on those functions, which is what Parts 7 and 8 do.

Easy to confuse: SHOW takes the same road

Right above the case SQLCOM_SELECT: quoted in ④, SHOW statements such as case SQLCOM_SHOW_PLUGINS: and case SQLCOM_SHOW_STORAGE_ENGINES: are lined up into the same block (sql_parse.cc lines 3995–4006). That means these SHOWs also run through execute_sqlcom_select(), the same road as SELECT. How a SHOW turns into reading an information_schema table is not covered separately in this series.

6. THD — everything about one connection

Almost every function in section 5 took THD *thd as its first argument. THD is everything one connection has inside the server. Let's start with the declaration.

class THD: public THD_count, /* this must be first */
           public Sql_used,
           public Statement,
           // ... (7 comment lines omitted)
           public Item_change_list,
           public Open_tables_state,
           public Sp_caches,
           public Statement_rcontext
{

sql/sql_class.h lines 3332–3346

It has seven parents. Their names show that THD has been split up by role: Statement is "the statement currently running", and Open_tables_state is "the tables this connection has open". The class body runs from line 3346 to line 6671 — more than 3,300 lines.

From any zone of the map, a single thd gets you to all of this. Of the seven parents, only the two that appear in this post are drawn.

The table below collects a few fields that come from the parents and from THD itself (declared at: Query_arena::mem_root line 1277, Statement::lex line 1867, Open_tables_state::open_tables line 2289, THD::net and protocol lines 3401–3407).

ha_data: where an engine hangs its per-connection data

In section 3 we called transaction_participant::slot "the index of the slot in each connection where the engine can hang its own data". That slot is THD::ha_data.

  /* container for handler's private per-connection data */
  Ha_data ha_data[MAX_HA];

sql/sql_class.h lines 3723–3724

MAX_HA is 64 (handler.h line 498). InnoDB hangs its own transaction object (trx_t) in this slot and takes it out when it needs it.

trx_t *thd_to_trx(const THD *thd) noexcept
{
  return static_cast<trx_t*>(thd_get_ha_data(thd, innodb_hton_ptr));
}

storage/innobase/handler/ha_innodb.cc lines 1808–1811

thd_get_ha_data() is a one-line function returning thd->ha_data[hton->slot].ha_ptr (sql_class.cc lines 457–461). The server layer has no idea what a trx_t is; it just keeps a void * for the engine. The engine, for its part, doesn't need to know THD's layout — it only has to find its own slot. trx_t is covered in Part 11.

What SHOW PROCESSLIST shows

Here is what Example 4's gdb printed after moving up to the mysql_execute_command frame and looking at thd.

#12 0x0000557d563f93a1 in mysql_execute_command (thd=..., is_called_from_prepared_stmt=...) at /usr/src/mariadb/sql/sql_parse.cc:4038
$4 = 12
$5 = SQLCOM_SELECT
$6 = 0x772c84038cd0 "SELECT COUNT(*) FROM lab.t_innodb WHERE note LIKE 'row%'"

thd->thread_id is 12 — the connection number shown in the Id column of SHOW PROCESSLIST (it may differ between runs). thd->lex->sql_command is SQLCOM_SELECT, and thd->query_string is the statement shown in the Info column. SHOW PROCESSLIST walks the server's THDs and copies these fields into a table (thd_info->thread_id=tmp->thread_id;, sql/sql_show.cc line 2993; the statement text at lines 3025–3031).

Easy to confuse: a THD is a "connection", not a "thread"

The name comes from "thread", but with the thread pool (thread_handling=pool-of-threads) one thread takes turns serving the THDs of several connections. Even in the default one-thread-per-connection mode, a thread whose connection has closed stays in the thread cache and later serves the new THD of another connection (thread_cache.park() after the loop in ①, sql_connect.cc line 1527). So it is more accurate to read THD as "one connection". We'll see this in detail in Part 4.

7. Memory — MEM_ROOT and emptying it after every statement

What a MEM_ROOT looks like

Along the road in section 5, objects poured out: pieces of LEX, Item trees, JOIN, select_send, handlers. You will hardly ever see code that deletes them one by one. The secret is MEM_ROOT.

typedef struct st_mem_root
{
  USED_MEM *free;                  /* blocks with free memory in it */
  USED_MEM *used;                  /* blocks almost without free memory */
  USED_MEM *pre_alloc;             /* preallocated block */
  /* if block have less memory it will be put in 'used' list */
  size_t min_malloc;
  size_t block_size;               /* initial block size */
// ...
} MEM_ROOT;

include/my_alloc.h lines 42–61

It keeps a list of a few large blocks (USED_MEM), and when alloc_root() is called it carves the request out of the free space left in a block (mysys/my_alloc.c, from line 266). There is no function to give back a single allocation; free_root() throws the blocks away all at once (line 485 of the same file).

How to build C++ objects on a MEM_ROOT

The mechanism that routes C++ new to a MEM_ROOT is Sql_alloc — the one in class handler :public Sql_alloc from section 2.

class Sql_alloc
{
public:
  static void *operator new(size_t size) throw ()
  {
    return thd_alloc(_current_thd(), size);
  }
  // ...
  static void *operator new(size_t size, MEM_ROOT *mem_root) throw()
  { return alloc_root(mem_root, size); }
  static void operator delete(void *ptr, size_t size) { TRASH_FREE(ptr, size); }
  static void operator delete(void *, MEM_ROOT *){}
// ...
};

sql/sql_alloc.h lines 23–43

• Written as new Foo, the memory comes from the mem_root of the current thread's THD (thd_alloc() is thd->alloc(size), i.e. alloc_root(thd->mem_root, …)).
• Written as new (mem_root) Foo, it comes from that MEM_ROOT.
• delete does not give memory back. TRASH_FREE is a macro that fills the area with 0x8F (only in builds with TRASH_FREED_MEMORY on) and tells checking tools like valgrind and ASAN that the area is "no access", exposing use-after-delete bugs (include/my_valgrind.h lines 82–98).

We have already seen this pattern several times in section 5: new (thd->mem_root) JOIN(…) (mysql_select()), new (thd->mem_root) select_send(thd) (execute_sqlcom_select(), sql_parse.cc line 6262), and section 3's new (mem_root) ha_innobase(hton, table). Item goes one step further.

class Item :public Value_source,
            public Type_all_attributes
{
  static void *operator new(size_t size);

public:
  static void *operator new(size_t size, MEM_ROOT *mem_root) throw ()
  { return alloc_root(mem_root, size); }
  static void operator delete(void *ptr,size_t size) { TRASH_FREE(ptr, size); }

sql/item.h lines 833–841

The argument-less operator new(size_t) is only declared, in the private part, and the only public one takes a MEM_ROOT *. So an Item can only be created by stating explicitly which MEM_ROOT it goes on, as in new (thd->mem_root) Item_int(…) (for example sql_parse.cc lines 6182–6183).

All at once when the statement ends

The place that empties that thd->mem_root is the end of dispatch_command(), after one command has been fully processed and its result sent (end_statement(), line 2448).

  thd->reset_kill_query();  /* Ensure that killed_errmsg is released */
  /*
    LEX::m_sql_cmd can point to Sql_cmd allocated on thd->mem_root.
    Unlink it now, before freeing the root.
  */
  thd->lex->m_sql_cmd= NULL;
  free_root(thd->mem_root,MYF(MY_KEEP_PREALLOC));

sql/sql_parse.cc lines 2488–2494

MY_KEEP_PREALLOC means "keep the preallocated first block" (include/my_sys.h line 137, and the comment above free_root()). The next statement starts again from that kept block. The comment just before is worth a look too: LEX::m_sql_cmd may point to an object on this root, so the pointer is cut before the root is emptied — with all-at-once freeing, "a pointer left over after emptying" is the most common trap.

thd->mem_root normally points to the main_mem_root that the THD itself holds, because the THD constructor initializes its parent Statement as Statement(&main_lex, &main_mem_root, …) (sql_class.cc lines 721–723).

Three roots with different lifetimes

Not everything lives for just one statement. Counting only the objects in this post, there are three roots.

Yellow boxes are roots, green boxes are where they get emptied. The middle column is why a handler outlives a statement.

As we saw in section 3, the handler object is created on the TABLE's mem_root. When the statement ends, the TABLE is not closed; it goes back to the table cache (table_open_cache) and the next statement reuses it (Part 7). So handlers are not recreated for every statement. Only when the TABLE is really closed does closefrm() delete the handler and empty the root.

int closefrm(TABLE *table)
{
  // ...
  if (table->db_stat)
    error=table->file->ha_close();
  // ...
  delete table->file;
  // ...
  free_root(&table->mem_root, MYF(0));
  DBUG_RETURN(error);
}

sql/table.cc lines 5012–5053

After telling the engine to close with table->file->ha_close(), it calls the handler's destructor with delete table->file, and the memory is given back by the final free_root(&table->mem_root, …). This pairs up with Sql_alloc::operator delete above, which did not give memory back.

Compared with httpd's APR pools

We saw the same idea in httpd Part 4. httpd tied together things with the same lifetime — "one request", "one connection" — in an APR pool and threw them away in one go. MariaDB's MEM_ROOTs are also divided by lifetime: "one statement" (thd->mem_root), "one opened table" (TABLE::mem_root), "one table definition" (TABLE_SHARE::mem_root). There are differences, too. APR pools formed a parent–child tree and could have cleanup functions registered, but the MEM_ROOT struct we saw in this post (st_mem_root) has neither a field pointing to a parent nor a cleanup list. That is why objects whose C++ destructor must run (like a handler, which holds files) are deleted explicitly by code such as closefrm().

What this post leaves out

Things left out to keep the map to a single sheet:

• Partitioned tables. ha_partition is itself a handler and contains a separate handler for each partition — sql/ha_partition.cc, get_ha_partition() (sql/handler.cc line 406)
• Mechanisms that hand a whole query to the engine: handlerton's create_group_by, create_derived, create_select and create_unit (sql/handler.h lines 1766–1789), and find_single_select_handler() in mysql_select()
• Table discovery: discover_table, through which an engine supplies the table definition without a .frm (sql/handler.h line 1809)
• SEQUENCE, and the SQL_SEQUENCE engine (ha_sequence) behind CREATE SEQUENCE — the two seen in Example 3's engine list
• The query cache (query_cache_send_result_to_client() in mysql_parse()) — Part 5
• mysql_union() and SELECT_LEX_UNIT, where UNION and subqueries go (the first branch of handle_select())
• Galera replication (WITH_WSREP blocks). The example image was built without Galera

Summary

What's next

Let's fold the map and start reading from the moment its zones come into being. The next post covers what happens from launching mariadbd until it accepts its first connection: the order of main() → mysqld_main(), where my.cnf is read, where in plugin initialization the ha_initialize_handlerton() whose result we saw here gets called and how it reaches InnoDB's innodb_init(), and the path INSTALL SONAME takes to load a plugin at runtime — checked with the error log and gdb.

Digging into MariaDB's Source 3: Startup and Plugins — Until mariadbd Takes Its First Connection

The series we finished earlier also spread out the same kind of map around their second post. In httpd Part 3, httpd had a core that defines hooks and modules that attach functions to them. The relationship between MariaDB's server layer and its engines is similar, but instead of attaching to many scattered hooks, an engine implements one class, handler, as a whole. As Coyote and Catalina were joined only by the Adapter interface in Tomcat Part 2, the two layers here are narrowed down to a single interface. The direction is reversed, though: in Tomcat the lower Coyote called the upper Catalina through Adapter, while in MariaDB the upper server layer calls the lower engine through handler. You can also connect this to the view from the application side. One line of SQL that Spring Part 18's JdbcTemplate sends through the JDBC driver enters this map at zone ① once it reaches the server, and comes back as a result from zone ⑨.