Digging into MariaDB's Source 7: Preparation — Checking Privileges, Opening Tables, Resolving Names

What this post covers

This is the seventh post in the "Digging into MariaDB's Source" series. In Part 6 we followed one SQL statement through the lexer and the Bison grammar until it became a LEX and some Item trees. Those trees are still just text turned into structure. Nobody has checked yet whether the t in SELECT name FROM t WHERE id = 1 is a real table, whether name is one of its columns, or whether this user is allowed to read it.

This post zooms into the ④ Preparation zone of the Part 2 map: the three things the server does after parsing and before handing the statement to the optimizer — checking privileges, opening tables and resolving names.

Blue boxes are what this post reads; grey boxes are covered by the posts around it.

There are four questions to answer.

• What does the server do first with the LEX the parser hands over — what does the big switch in mysql_execute_command() look like?
• When, where and in how many layers are privileges checked? Does the server read the GRANT tables for every statement?
• What does "opening a table" actually mean? What do table_open_cache and table_definition_cache each hold?
• Why does ALTER TABLE wait behind an open transaction, and why does a perfectly ordinary SELECT that arrives afterwards stop too? And where does the "Unknown column" error come from?

All line numbers in this post refer to that commit. Part 1 shows how to get the same source and build the Docker image used for the examples.

sql_acl.cc is 17,000 lines long and sql_base.cc is over 10,000. This post picks only the main path that a single SELECT takes. What it leaves out is listed by name and location in "What this post leaves out" at the end.

Terms to know first

THD and LEX. THD is the object holding every bit of state for one connection (Part 2), and thd->lex is what the parser left behind after reading one statement (Part 6). lex->sql_command holds the kind of statement (SQLCOM_SELECT, SQLCOM_ALTER_TABLE …).

TABLE_LIST is the slot the parser creates for each table in the FROM clause. At first it holds only the database name, the table name, the alias and "which metadata lock to take on this table" (mdl_request). In the end, the whole job of this post is to fill that slot with an open table (TABLE_LIST::table) and checked privileges (TABLE_LIST::grant). All tables in a statement are chained through next_global, starting at lex->query_tables; mysql_execute_command() calls that chain all_tables.

TABLE_SHARE and TABLE. Both are "a table", but they are different things. TABLE_SHARE is the table definition — the column list, the indexes, which engine — and there is one per table. TABLE is an actually opened instance built from that definition — an engine object (handler) and buffers for one row (record[]) — and everyone using the same table at the same time gets their own. The source comment says it plainly.

/**
  This structure is shared between different table objects. There is one
  instance of table share per one table in the database.
*/

struct TABLE_SHARE

sql/table.h lines 748–753

The setting table_definition_cache decides how many TABLE_SHAREs to keep around, and table_open_cache how many TABLEs (section 3).

Metadata lock (MDL). A lock that protects not rows but a table's definition. While someone is using a table, nobody else may drop a column or drop the table. It is a server-layer lock (sql/mdl.cc), separate from InnoDB's row locks (Part 11), and it applies the same way to every engine. When you get a lock, you receive a ticket called MDL_ticket.

Item::fix_fields() is the function called on every node of an Item tree to "bind the name to the real thing". The Item_field the parser built holds only the text name; once fix_fields() is done, it points to a specific Field object in a specific table. This is called name resolution.

1. mysql_execute_command() — a big switch over the kind of statement

When dispatch_command() from Part 5 receives COM_QUERY, mysql_parse() runs the parser (Part 6) and, on success, calls mysql_execute_command(). That function starts at line 3540 and ends at line 6297 — about 2,700 lines — and 2,000 of them (lines 3946–5958) are a single switch (lex->sql_command). Counting only this switch's own case SQLCOM_…: labels gives 162 (not counting the two labels of a small nested switch inside it).

Here is the slot a SELECT goes to. Several SHOW commands share it, because SHOW TABLES and friends are turned into a SELECT over information_schema.

  case SQLCOM_SHOW_SLAVE_STAT:
  case SQLCOM_SELECT:
  {
    // ... (WSREP block omitted)
    thd->status_var.last_query_cost= 0.0;

    /*
      lex->exchange != NULL implies SELECT .. INTO OUTFILE and this
      requires FILE_ACL access.
    */
    if (lex->exchange && (res= check_global_access(thd, FILE_ACL, false)))
      break;

    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);

    break;
  }

sql/sql_parse.cc lines 4005–4041

The order is clear. Privileges first (check_table_access()); if they pass, execute_sqlcom_select() opens the tables and carries on.

  if (!(res= open_and_lock_tables(thd, all_tables, TRUE, 0)))
  {
    lex->resolve_optimizer_hints();
    if (lex->describe)
    {
    // ... (for EXPLAIN, only build and print the plan — Part 8)
    }
    else
    {
      // ... (for ANALYZE, swap in a Protocol that discards the result)
      query_cache_store_query(thd, all_tables);
      res= handle_select(thd, lex, result, 0);

sql/sql_parse.cc lines 6186–6266

handle_select() calls mysql_select(), which creates a JOIN object and calls JOIN::prepare() → JOIN::optimize() → JOIN::exec() in turn (sql_select.cc lines 5427–5461). Of these, JOIN::prepare() handles name resolution (section 5). On one page:

Sections 2, 3, 4 and 5 of this post are this diagram, one chunk at a time from top to bottom.

Other statements go through Sql_cmd objects

Not every statement keeps its code inside the switch. For statements like ALTER TABLE, GRANT and CREATE TABLE, the parser builds an object derived from Sql_cmd (Sql_cmd_alter_table and so on, Part 6) in lex->m_sql_cmd, and the switch just calls one virtual function on it.

  case SQLCOM_GRANT:
    if (thd->variables.option_bits & OPTION_IF_EXISTS)
      lex->create_info.set(DDL_options_st::OPT_IF_EXISTS);
    DBUG_ASSERT(lex->m_sql_cmd != NULL);
    res= lex->m_sql_cmd->execute(thd);

sql/sql_parse.cc lines 5943–5947

You can think of it as the giant switch slowly being moved into one class per statement. Whichever road a statement takes, it goes through the same three steps of this post (privileges, opening tables, name resolution). In the example in section 4 we will see ALTER TABLE pass through exactly this line 5947 into Sql_cmd_alter_table::execute() in a backtrace.

2. Checking privileges — the tables are read once, at startup

The privilege tables live in memory

GRANT SELECT ON lab.t TO u1 adds a row to a privilege table in the mysql database. Global privileges and accounts are in mysql.global_priv, database privileges in mysql.db, table privileges in mysql.tables_priv and column privileges in mysql.columns_priv. But the server does not read these tables for each statement. mysqld_main() reads them twice at startup and keeps them in memory (Part 3).

  if (mysql_rm_tmp_tables() || acl_init(opt_noacl) ||
      my_tz_init((THD *)0, default_tz_name, opt_bootstrap))
    unireg_abort(1);

  if (!opt_noacl)
    (void) grant_init();

sql/mysqld.cc lines 6449–6454

acl_init() calls acl_reload(), which opens the account and database tables and copies them into arrays (acl_users, acl_dbs …) with acl_load().

  const uint tables_to_open= Table_host | Table_user | Table_db |
                             Table_proxies_priv | Table_roles_mapping;
  if ((result= tables.open_and_lock(thd, tables_to_open, TL_READ)))

sql/sql_acl.cc lines 3800–3802

The constant is called Table_user, but what it opens is global_priv: the last entry of the table-name array is "global_priv" (sql_acl.cc lines 825–834), and the old name mysql.user is now a view over global_priv (scripts/mariadb_system_tables.sql line 51). grant_init() → grant_reload() does the same for tables_priv, columns_priv and procs_priv, loading them into hash tables (column_priv_hash …) with grant_load() (sql_acl.cc line 10015).

FLUSH PRIVILEGES simply calls those two functions again (sql_reload.cc lines 99–100). GRANT and REVOKE update memory at the same time as they write the tables (column_priv_insert() in mysql_table_grant(), line 9021), so they take effect without a FLUSH. If you edit the tables directly with DELETE or UPDATE, memory knows nothing about it.

The yellow boxes are the privileges loaded into memory. Running a statement only ever looks at these.

Three layers: global and database → table → column

check_table_access() calls check_access() for each table in the statement, and then check_grant() once at the end.

    if (check_access(thd, want_access, table_ref->get_db_name().str,
                     &table_ref->grant.privilege,
                     &table_ref->grant.m_internal,
                     0, no_errors))
      return 1;
  }
  return check_grant(thd,requirements,org_tables,
                     any_combination_of_privileges_will_do,
                     number, no_errors);

sql/sql_parse.cc lines 7137–7145

1. check_access() (sql_parse.cc line 6676) looks at the global privileges that landed in the THD's Security_context at login (master_access) and at the database privileges for that database. If they cover everything, we are done. If they don't, but the missing privileges are of a kind that table or column privileges could still supply (such as SELECT), it lets the statement through for now and leaves it to the next layer. This is also where the thread state you know from SHOW PROCESSLIST becomes checking permissions (lines 6715–6716).
2. check_grant() (sql_acl.cc line 10114) finds the table's entry in column_priv_hash and adds the table privileges. If something is still missing, it writes what's left into TABLE_LIST::grant.want_privilege — a note saying "check this again column by column".
3. Column privileges can't be checked yet, because we don't know which columns will be used. When name resolution in section 5 binds a column name to a Field, check_column_grant_in_table_ref() checks them (sql_acl.cc line 10478; name resolution calls it inside find_field_in_table_ref(), sql_base.cc line 7087).

These three layers line up directly with the three shapes of GRANT (ON *.*, ON db.*, ON db.t) and with the column list in GRANT SELECT (id, name) ON ….

Example 1: column privileges, and privileges in memory

This example and every other example 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 example folder is ex/u07-privileges. We create a user that may read only two columns, id and name.

# An example written for this post (not part of the MariaDB source)
# ... (after starting the server with lab-init and lab-start)
mariadb -e "CREATE DATABASE lab;
  CREATE TABLE lab.t  (id INT PRIMARY KEY, name VARCHAR(20), secret VARCHAR(20));
  CREATE TABLE lab.t2 (id INT PRIMARY KEY);
  INSERT INTO lab.t VALUES (1,'one','s1'),(2,'two','s2');
  CREATE USER u1@localhost IDENTIFIED BY 'pw';
  GRANT SELECT (id, name) ON lab.t TO u1@localhost;"
# ... (then the statements sent as u1 and the grant-table edits — see the output below)

Then we send four statements as u1 (the mariadb client echoes the failing statement before the error; those echoes are removed).

--- 1. column privilege only: id, name
id	name
1	one
2	two
--- 2. a column without privilege
ERROR 1143 (42000) at line 1: SELECT command denied to user 'u1'@'localhost' for column 'secret' in table 't'
--- 3. SELECT * also needs every column
ERROR 1142 (42000) at line 1: SELECT command denied to user 'u1'@'localhost' for table `lab`.`t`
--- 4. a table without privilege
ERROR 1142 (42000) at line 1: SELECT command denied to user 'u1'@'localhost' for table `lab`.`t2`

Statement 2 is stopped at the column layer with 1143 (the column name is shown); statement 4 is stopped at the table layer with 1142. u1 has not even a column privilege on t2, so check_grant() refuses right away with "no privileges at all".

Next, as root, we delete the grant rows directly and send the same statement before and after FLUSH PRIVILEGES.

--- 5. delete the grant row directly (no FLUSH PRIVILEGES)
rows_in_columns_priv
0
id	name
1	one
2	two
--- 6. after FLUSH PRIVILEGES
ERROR 1044 (42000): Access denied for user 'u1'@'localhost' to database 'lab'

The table has zero rows, yet u1 still reads. What the server looks at is column_priv_hash in memory. Only after FLUSH PRIVILEGES reloads it does the privilege disappear — and now u1 is stopped even earlier, when connecting with lab as the default database, with 1044.

Easy to confuse: SELECT * gives 1142, not 1143

Statement 3, SELECT * FROM t, was blocked because of the secret column, yet the error doesn't name the column; it comes out as a table denial (1142). check_grant_all_columns(), which checks all columns at once when * is expanded, does this on purpose.

  /*
    Do not give an error message listing a column name unless the user has
    privilege to see all columns.
  */
  if (using_column_privileges)
    my_error(ER_TABLEACCESS_DENIED_ERROR, MYF(0),
             command, sctx->priv_user,
             sctx->host_or_ip, db_name, table_name);
  else
    my_error(ER_COLUMNACCESS_DENIED_ERROR, MYF(0),

sql/sql_acl.cc lines 10644–10653

As the comment says, a user who can't see every column doesn't get a column name in the error message. In effect, a user with only column privileges is never told "there is a column called secret". Statement 2, where the user typed the column name themselves, gets 1143 with that name from check_grant_column() during name resolution (sql_acl.cc line 10448).

Easy to confuse: the reference commit already has DENY

Reading check_access() and check_grant(), you run into names like access_t, denied_privs() and allow_bits(). This version can keep a "denies" list in the JSON of mysql.global_priv (see the comment around sql_acl.cc line 1365), so privileges are handled as two sets of bits, "allow" and "deny". According to the git history of the reference commit, these names arrived with commit 934108aab5c ("MDEV-14443 DENY statement") in June 2026. It is a recent addition, so the LTS versions people usually run may differ here (I did not compare against an LTS source). This post does not follow the deny path.

3. Opening tables — TABLE_SHARE and TABLE, two layers of cache

Once privileges pass, open_and_lock_tables() opens the tables.

  if (open_tables(thd, options, &tables, &counter, flags, prelocking_strategy))
    goto err;
  // ...
  if (lock_tables(thd, tables, counter, flags))
    goto err;

sql/sql_base.cc lines 6047–6057

open_tables() walks the all_tables chain and calls open_and_process_table() → open_table() for each table; once all are open, lock_tables() calls handler::external_lock() on each engine to say "this statement is using this table" (this is where the engine learns when a statement starts and ends — Part 9, Part 11). Entering open_tables() sets the thread state to Opening tables (line 4892).

What one call to open_table() does

open_table() starts at line 1960 and runs for more than 400 lines, but in the ordinary case its backbone fits in three lines: get the metadata lock, get the definition, get a TABLE.

  if (! (flags & MYSQL_OPEN_HAS_MDL_LOCK))
  {
    DEBUG_SYNC(thd, "before_open_table_get_mdl_lock");
    if (open_table_get_mdl_lock(thd, ot_ctx, &table_list->mdl_request,
                                flags, &mdl_ticket) ||
        mdl_ticket == NULL)
    {
      DEBUG_SYNC(thd, "before_open_table_wait_refresh");
      DBUG_RETURN(TRUE);
    }
  // ...
retry_share:

  share= tdc_acquire_share(thd, table_list, gts_flags, &table);

sql/sql_base.cc lines 2125–2166

Metadata locks get their own section (section 4). Here we start at tdc_acquire_share(). The tdc in its name is the table definition cache, the cache that holds TABLE_SHAREs. The function returns the TABLE_SHARE, and if an idle TABLE is available it hands that out through &table as well.

One definition (TABLE_SHARE), many instances (TABLE). There is one engine object handler per TABLE.

  if (out_table && (flags & GTS_TABLE))
  {
    if ((*out_table= tc_acquire_table(thd, element)))
    {
      // ...
      status_var_increment(thd->status_var.table_open_cache_hits);
      DBUG_RETURN(element->share);
    }
    status_var_increment(thd->status_var.table_open_cache_misses);
  }

sql/table_cache.cc lines 926–939

If the cache has no definition at all (a table seen for the first time), the code enters the while loop at line 870, makes an empty TABLE_SHARE with alloc_table_share() and fills it from the .frm file with open_table_def() (lines 890–897). That bumps Opened_table_definitions by one (table.cc line 3694).

Back in open_table(), if no idle TABLE came back, a new one is built.

  else
  {
    enum open_frm_error error;
    /* make a new table */
    if (!(table=(TABLE*) my_malloc(key_memory_TABLE, sizeof(*table),
                                   MYF(MY_WME))))
      goto err_lock;

    error= open_table_from_share(thd, share, &table_list->alias,
                                 HA_OPEN_KEYFILE | HA_TRY_READ_ONLY,
                                 EXTRA_RECORD,
                                 thd->open_options, table, FALSE,
                                 IF_PARTITIONING(table_list->partition_names,0));
    // ...
    /* Add table to the share's used tables list. */
    tc_add_table(thd, table);
    from_share= true;
  }

sql/sql_base.cc lines 2308–2349

open_table_from_share() (sql/table.cc) copies the column definitions from the TABLE_SHARE into Field objects, gets a handler object from the engine's handlerton (the one-per-engine table of functions an engine hands to the server; get_new_handler(), Part 2) and opens it with ha_open(). For InnoDB, this is where ha_innobase::open() gets called. That work is expensive, which is why TABLEs are kept in a cache. Each new one bumps Opened_tables by one (table.cc line 4896).

Yellow boxes are the expensive work (reading files, opening engine objects); green boxes are the cases served straight from the cache.

At the end of a statement, the TABLE goes back

A connection does not keep the TABLE it opened forever. At the end of mysql_execute_command() it is returned after every statement.

  /* Free tables. Set stage 'closing tables' */
  close_thread_tables_for_query(thd);

sql/sql_parse.cc lines 6044–6045

The returned TABLE goes into its definition's free_tables list through tc_release_table() and waits for the next user. So the "open table cache" is one per server, not one per connection, and table_open_cache is closer to "how many TABLE objects may be open at the same time". If ten connections read the same table at once, the same TABLE_SHARE gets ten TABLEs.

Example 2: Opened_tables and Table_open_cache_hits

ex/u07-table-cache empties the caches with FLUSH TABLES, runs the same SELECT twice on one connection, and prints session status variables in between. First the table's files and the cache sizes:

--- 0. files of table t
db.opt
t.frm
t.ibd
--- 0. cache sizes
@@table_definition_cache	@@table_open_cache	@@table_open_cache_instances	@@use_stat_tables
400	2000	8	PREFERABLY_FOR_QUERIES

t.frm is the server layer's definition file (the raw material of the TABLE_SHARE), t.ibd is InnoDB's data file. The defaults 400 and 2000 match TABLE_DEF_CACHE_DEFAULT and TABLE_OPEN_CACHE_DEFAULT in sql_const.h (lines 145–147). The status variables changed like this (the three SHOW SESSION STATUS tables are merged into one).

The second SELECT created neither a definition nor a TABLE; it took them from the cache (hits 1). But why did the first SELECT, which reads one table, give 4? The SHOW OPEN TABLES printed in between answers that.

+----------+--------------+--------+-------------+
 Database | Table        | In_use | Name_locked |
+----------+--------------+--------+-------------+
 mysql    | table_stats  |      0 |           0 |
 mysql    | index_stats  |      0 |           0 |
 mysql    | column_stats |      0 |           0 |
 lab      | t            |      0 |           0 |
+----------+--------------+--------+-------------+

After opening all tables, open_and_lock_tables() calls read_statistics_for_tables_if_needed() (lines 6060–6062). Because use_stat_tables is at its default PREFERABLY_FOR_QUERIES, it also opened and read the three optimizer statistics tables (mysql.table_stats, column_stats, index_stats). Statistics are a story for Part 8. After the second SELECT these tables were not opened again (hits went up by only one).

Finally, while another connection holds t with SELECT COUNT(*), SLEEP(3) FROM lab.t, we send a statement that uses the same table twice (t and t AS t_again) (the COUNT(*) result in the middle is shortened to ...).

--- 4. while another session is reading t
+----------+-------+--------+-------------+
 Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
 lab      | t     |      1 |           0 |
+----------+-------+--------+-------------+
...
+------------------------+-------+
 Variable_name          | Value |
+------------------------+-------+
 Open_table_definitions | 4     |
 Open_tables            | 6     |
+------------------------+-------+

In_use 1 is the TABLE held by the other connection. There are still 4 definitions but 6 open TABLEs — 3 statistics tables plus 3 TABLEs for t (one for the other connection, two for this statement's t and t_again). One definition, many instances, in numbers.

Easy to confuse: the table cache is split into several pieces

table_open_cache_instances (default 8, sys_vars.cc lines 4486–4489) is how many pieces the cache is split into. tc_acquire_table() picks a piece with thd->thread_id % n_instances and locks only that piece's mutex (table_cache.cc lines 398–415). The point is to keep many connections from piling onto one mutex. As a result, "idle TABLEs" are kept per piece (TDC_element::free_tables[]), and an idle TABLE in another piece may not be usable. Not all 8 pieces are in use from the start; more are activated when contention shows up (lock_and_check_contention(), tc_active_instances).

Easy to confuse: "Table doesn't exist" comes before name resolution

Using a table that doesn't exist gives 1146. That error comes not from the name resolution of section 5 but from here, when tdc_acquire_share() fails to find the .frm (and the engine's own table discovery). Example 4 prints it next to 1054. Column names can only be looked at after all tables are open, so this is the natural order.

4. Metadata locks — why ALTER TABLE waits

Kinds and durations of locks

For each table in FROM, the parser writes "which MDL to take and for how long" into TABLE_LIST::mdl_request (sql_parse.cc lines 8309–8310). There are quite a few kinds (enum_mdl_type, sql/mdl.h lines 54–187), but this post only needs four.

There are three durations (enum_mdl_duration): MDL_STATEMENT, released at the end of the statement; MDL_TRANSACTION, released at the end of the transaction; and MDL_EXPLICIT, which must be released by hand (mdl.h lines 260–277). Table MDLs are requested as MDL_TRANSACTION. When they are actually released is decided at the end of mysql_execute_command().

  else if (! thd->in_sub_stmt_ps_unsafe() &&
           ! thd->in_active_multi_stmt_transaction())
  {
    /*
      - If inside a multi-statement transaction,
      defer the release of metadata locks until the current
      transaction is either committed or rolled back. This prevents
      other statements from modifying the table for the entire
      duration of this transaction.  This provides commit ordering
      and guarantees serializability across multiple transactions.
      - If in autocommit mode, or outside a transactional context,
      automatically release metadata locks of the current statement.
    */
    thd->release_transactional_locks();
  }
  else if (! thd->in_sub_stmt_ps_unsafe())
  {
    thd->mdl_context.release_statement_locks();
  }

sql/sql_parse.cc lines 6084–6102

For an autocommit statement, the locks are released right at the end of the statement. But for a statement after BEGIN, they are held until the transaction ends. The TABLE from section 3 goes back at the end of the statement, but the MDL stays — everything in this section starts from that fact. The reason the comment gives is "so nobody can change the table definition in the middle of the transaction". It would be a problem if a table you read twice in one transaction had different columns the second time.

The TABLE is returned after every statement; the MDL is released when the transaction ends. The order was checked against close_thread_tables_for_query() at the end of mysql_execute_command() (line 6045) and the excerpt above (lines 6084–6102).

Compatible and incompatible locks

Whether two locks may coexist is decided by two tables in mdl.cc. The first is about locks already granted.

     Request  |  Granted requests for lock         |
      type    | S  SH  SR  SW  SU  SRO SNW SNRW X  |
    ----------+------------------------------------+
    S         | +   +   +   +   +   +   +   +   -  |
    SH        | +   +   +   +   +   +   +   +   -  |
    SR        | +   +   +   +   +   +   +   -   -  |
    SW        | +   +   +   +   +   -   -   -   -  |
    SU        | +   +   +   +   -   +   -   -   -  |
    SRO       | +   +   +   -   +   +   +   -   -  |
    SNW       | +   +   +   -   -   +   -   -   -  |
    SNRW      | +   +   -   -   -   -   -   -   -  |
    X         | -   -   -   -   -   -   -   -   -  |

sql/mdl.cc lines 2179–2190

The SR cell in the SU row is +. Even while someone is reading, ALTER TABLE can get its SU and step in. The X row, however, is all -. The second table is about locks being waited for — in other words, the queueing rule.

     Request  |  Pending requests for lock        |
      type    | S  SH  SR  SW  SU  SRO SNW SNRW X |
    ----------+-----------------------------------+
    S         | +   +   +   +   +   +   +   +   - |
    SH        | +   +   +   +   +   +   +   +   + |
    SR        | +   +   +   +   +   +   +   -   - |

sql/mdl.cc lines 2199–2204

The X cell in the SR row is -. If someone is waiting for X, a new SR queues behind it, even though it is compatible with every granted lock. The rule keeps the party that wants X from being pushed back by readers forever (the SH row, for parties that only peek at the definition such as information_schema, is exempt).

MDL_context::acquire_lock()

The function that takes a lock is long, but its skeleton looks like this.

bool
MDL_context::acquire_lock(MDL_request *mdl_request, double lock_wait_timeout)
{
  // ...
  if (try_acquire_lock_impl(mdl_request, lock_wait_timeout ? &ticket : nullptr))
  {
    DBUG_PRINT("mdl", ("OOM: %s", mdl_lock_name));
    DBUG_RETURN(TRUE);
  }

  if (mdl_request->ticket)
  {
    // ...
    DBUG_RETURN(FALSE);
  }
  // ...
  will_wait_for(ticket);
  // ...
  find_deadlock();
  // ...
  for (;;)
  {
    // ...
    wait_status= m_wait.timed_wait(m_owner, &abs_shortwait, FALSE,
                                   mdl_request->key.get_wait_state_name());
    // ...
  }
  // ...
  if (wait_status != MDL_wait::GRANTED)
  {
    // ...
    case MDL_wait::VICTIM:
      // ...
      my_error(ER_LOCK_DEADLOCK, MYF(0));
      break;
    case MDL_wait::TIMEOUT:
      DBUG_PRINT("mdl", ("Timeout:  %s", ticket_msg));
      my_error(ER_LOCK_WAIT_TIMEOUT, MYF(0));
      break;
    // ...
  }
  // ...
  m_tickets[mdl_request->duration].push_front(ticket);

sql/mdl.cc lines 2895–3083

1. try_acquire_lock_impl() tries to get the lock without waiting. If this context already holds the same or a stronger lock (find_ticket()), that's it; if the two tables say it's compatible, it gets an MDL_ticket on the spot.
2. Otherwise it waits. First find_deadlock() looks in the wait-for graph among MDLs for "is the one I wait for waiting for me?". Then it calls timed_wait() in one-second slices. Every second it wakes up, checks whether the client is still connected and nudges the lock holders again. The thread state during this is Waiting for table metadata lock — the per-namespace wording lives in an array at mdl.cc lines 101–112.
3. After lock_wait_timeout seconds (default 86400, i.e. one day — sys_vars.cc lines 1613–1617) you get 1205 Lock wait timeout exceeded; if chosen as a deadlock victim, 1213 (ER_LOCK_DEADLOCK).
4. Once granted, the ticket goes into the per-duration list m_tickets[duration]. Later release_transactional_locks() empties that list.

There is one MDL_lock object per lock (per table), containing a list of granted tickets m_granted and a list of waiting tickets m_waiting (mdl.cc lines 991–994). The two tables above are checked against these two lists.

Example 3: a SELECT queued behind ALTER TABLE

ex/u07-mdl-wait starts three connections one second apart.

• A: BEGIN; SELECT COUNT(*) FROM lab.t; SELECT SLEEP(20); COMMIT; — reads once and doesn't commit for 20 seconds.
• B: ALTER TABLE lab.t ADD COLUMN c INT
• C: SELECT COUNT(*) FROM lab.t — just reads.

One second later we print the state. To see the locks, the plugin was loaded with INSTALL SONAME 'metadata_lock_info' (times differ on every run; the connection IDs 5, 6 and 7 came out the same on a rerun because each run uses a fresh container, but they change if other connections reached the server first).

--- 1. processlist at 03:23:54
+----+---------------------------------+------------------------------------+
 ID | STATE                           | INFO                               |
+----+---------------------------------+------------------------------------+
  5 | User sleep                      | SELECT SLEEP(20)                   |
  6 | Waiting for table metadata lock | ALTER TABLE lab.t ADD COLUMN c INT |
  7 | Waiting for table metadata lock | SELECT COUNT(*) FROM lab.t         |
+----+---------------------------------+------------------------------------+
--- 2. granted metadata locks
+-----------+-------------------------+---------------+----------------------+--------------+------------+
 THREAD_ID | LOCK_MODE               | LOCK_DURATION | LOCK_TYPE            | TABLE_SCHEMA | TABLE_NAME |
+-----------+-------------------------+---------------+----------------------+--------------+------------+
         5 | MDL_SHARED_READ         | transaction   | Table metadata lock  | lab          | t          |
         6 | MDL_BACKUP_ALTER_COPY   | statement     | Backup lock          |              |            |
         6 | MDL_INTENTION_EXCLUSIVE | transaction   | Schema metadata lock | lab          |            |
         6 | MDL_SHARED_UPGRADABLE   | transaction   | Table metadata lock  | lab          | t          |
+-----------+-------------------------+---------------+----------------------+--------------+------------+

A (ID 5) is in SLEEP, not even reading t, yet holds SR with transaction duration. B (ID 6) got SU (SU×SR = + in the table), and C (ID 7) holds nothing. At the same moment we also sent D, with lock_wait_timeout lowered to 2 seconds.

--- 3. D gives up after lock_wait_timeout=2
ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction

To see exactly where B and C are waiting, we printed backtraces of all threads with gdb and kept only those containing MDL_wait::timed_wait (bt-waiting.sh; addresses and arguments removed, only #4–#14 kept).

#4  MDL_wait::timed_wait at sql/mdl.cc:1874
#5  MDL_context::acquire_lock at sql/mdl.cc:3018
#6  MDL_context::upgrade_shared_lock at sql/mdl.cc:3263
#7  mysql_inplace_alter_table at sql/sql_table.cc:8570
#8  mysql_alter_table at sql/sql_table.cc:12206
#9  Sql_cmd_alter_table::execute at sql/sql_alter.cc:705
#10  mysql_execute_command at sql/sql_parse.cc:5947
#11  mysql_parse at sql/sql_parse.cc:8005
#12  dispatch_command at sql/sql_parse.cc:1903
#13  do_command at sql/sql_parse.cc:1437
#14  do_handle_one_connection at sql/sql_connect.cc:1514

#4  MDL_wait::timed_wait at sql/mdl.cc:1874
#5  MDL_context::acquire_lock at sql/mdl.cc:3018
#6  open_table_get_mdl_lock at sql/sql_base.cc:1712
#7  open_table at sql/sql_base.cc:2128
#8  open_and_process_table at sql/sql_base.cc:4442
#9  open_tables at sql/sql_base.cc:4963
#10  open_and_lock_tables at sql/sql_base.cc:6047
#11  open_and_lock_tables at sql/sql_base.h:545
#12  execute_sqlcom_select at sql/sql_parse.cc:6186
#13  mysql_execute_command at sql/sql_parse.cc:4038
#14  mysql_parse at sql/sql_parse.cc:8005

The top one is B, the bottom one C. B enters through the Sql_cmd road from section 1 (line 5947) and waits in mysql_inplace_alter_table() while trying to upgrade SU to X (upgrade_shared_lock()). C walks exactly through sections 2 and 3 of this post — execute_sqlcom_select() line 6186 → open_and_lock_tables() → open_table() line 2128 — and waits for its own SR. As soon as A committed, both finished within a second.

--- 5. waiting for A to commit
A committed   at 03:24:10
B ALTER done  at 03:24:11
C SELECT done at 03:24:11

In production, "I ran one ALTER and every query on that table froze" is this picture. The cause is not the ALTER but the open transaction A in front of it.

Easy to confuse: even an instant ALTER needs X

InnoDB can perform ADD COLUMN c INT the INSTANT way, changing only the definition without touching the data (in a separate run, the same statement with ALGORITHM=INSTANT added succeeds and Innodb_instant_alter_column goes from 0 to 1). The actual work is over in a moment. It still waited. Line 8570 of sql_table.cc in the backtrace is why.

  else if (inplace_supported == HA_ALTER_INPLACE_COPY_LOCK ||
           inplace_supported == HA_ALTER_INPLACE_COPY_NO_LOCK ||
           inplace_supported == HA_ALTER_INPLACE_NOCOPY_LOCK ||
           inplace_supported == HA_ALTER_INPLACE_NOCOPY_NO_LOCK ||
           inplace_supported == HA_ALTER_INPLACE_INSTANT)
  {
    /*
      Storage engine has requested exclusive lock only for prepare phase
      and we are not under LOCK TABLES.
      Don't mark TABLE_SHARE as old in this case, as this won't allow opening
      of table by other threads during main phase of in-place ALTER TABLE.
    */
    if (thd->mdl_context.upgrade_shared_lock(table->mdl_ticket, MDL_EXCLUSIVE,
                                             thd->variables.lock_wait_timeout))
      goto cleanup;

sql/sql_table.cc lines 8558–8572

Whether INSTANT or online (NO_LOCK), the prepare phase must hold X briefly. The catch is that "briefly" can stretch until the transaction in front of it ends. That is why schema changes in production are often run with a short SET SESSION lock_wait_timeout=… and retried on failure.

Easy to confuse: MDL deadlock detection sees only MDLs

The wait-for graph that find_deadlock() searches contains only MDL waits. It can't see cycles that include InnoDB row-lock waits. A comment in open_table_get_mdl_lock() gives an example (three connections waiting crosswise on MDLs and row locks) and notes that "such circular waits are currently only resolved by timeouts (innodb_lock_wait_timeout or lock_wait_timeout)" (sql_base.cc lines 1697–1707). I did not build an example of this case.

Easy to confuse: METADATA_LOCK_INFO doesn't show the waiters

C (ID 7) was missing from table 2 of the example, and B showed only its granted SU, not the X it was waiting for. The plugin's fill function skips tickets that were not granted (if (!granted) DBUG_RETURN(0);, plugin/metadata_lock_info/metadata_lock_info.cc lines 80–81). To see who is waiting, look at STATE in PROCESSLIST. Also, the LOCK_DURATION column is filled only in Debug builds (#ifndef DBUG_OFF, lines 85–91 of the same file) and is NULL in normal builds. The output in this post shows transaction only because the example server is a Debug build.

5. Resolving names — JOIN::prepare() and fix_fields()

Now that the tables are open, the names in the Item trees can be bound to real columns. The first part of JOIN::prepare(), called from mysql_select(), does this step by step.

  /* Check that all tables, fields, conds and order are ok */
  if (!(select_options & OPTION_SETUP_TABLES_DONE) &&
      setup_tables_and_check_access(thd, &select_lex->context, join_list,
                                    tables_list, select_lex->leaf_tables,
                                    false, SELECT_ACL, SELECT_ACL, false))
      DBUG_RETURN(-1);
  // ...
  if (setup_wild(thd, tables_list, fields_list, &all_fields, select_lex, false))
    DBUG_RETURN(-1);
  // ...
  if (setup_fields(thd, ref_ptrs, fields_list, select_lex->item_list_usage,
                   &all_fields, &select_lex->pre_fix, 1))
    DBUG_RETURN(-1);
  thd->lex->current_select->context_analysis_place= save_place;

  if (setup_without_group(thd, ref_ptrs, tables_list,
                          select_lex->leaf_tables, fields_list,
                          all_fields, &conds, order, group_list,
                          select_lex->window_specs,
                          select_lex->window_funcs,
                          &hidden_group_fields))
    DBUG_RETURN(-1);

sql/sql_select.cc lines 1497–1625

1. setup_tables_and_check_access() → setup_tables() numbers the FROM tables from 0, and setup_table_map() gives each table one bit with table->map = 1 << tablenr (sql_base.h lines 373–374). This is what the optimizer later uses to compute "which tables does this condition use" with a bitwise OR (Part 8). Index hints (USE INDEX) are also bound to real indexes here. Tables inside a view get their privileges checked once more.
2. setup_wild() expands the * in SELECT * into the real column list (insert_fields()). The "SELECT * gives 1142" error from section 2 comes from here. A * inside EXISTS (SELECT * …) is not expanded but replaced by the constant 1 (lines 8478–8488) — EXISTS only cares whether a row exists.
3. setup_fields() calls fix_fields() on each Item of the SELECT list, and setup_without_group() does the same for WHERE (setup_conds()), ORDER BY (setup_order()), GROUP BY and window functions.

Item_field::fix_fields() — the order in which one name is looked up

fix_fields() is virtual: for Item_func_eq (=) it calls fix_fields() on its arguments, and eventually reaches a leaf Item_field. The first thing Item_field::fix_fields() does is find_field_in_tables().

    if ((from_field= find_field_in_tables(thd, this,
                                          context->first_name_resolution_table,
                                          context->last_name_resolution_table,
                                          context->ignored_tables,
                                          reference,
                                          thd->lex->use_only_table_context ?
                                            REPORT_ALL_ERRORS :
                                            IGNORE_EXCEPT_NON_UNIQUE,
                                          !any_privileges,
                                          TRUE)) ==

sql/item.cc lines 6714–6723

Normally it is called with IGNORE_EXCEPT_NON_UNIQUE, meaning "don't report a name that isn't found yet; only report a name found more than once" — if it isn't found, there are still other places to look. find_field_in_tables() walks the tables attached to the name-resolution context (Name_resolution_context), searches each with find_field_in_table_ref(), and raises 1052 on the spot when it finds the name a second time.

      if (unlikely(found))
      {
        if (report_error == REPORT_ALL_ERRORS ||
            report_error == IGNORE_EXCEPT_NON_UNIQUE)
          my_error(ER_NON_UNIQ_ERROR, MYF(0),
                   table_name ? item->full_name() : name.str, thd_where(thd));
        return (Field*) 0;
      }
      found= cur_field;

sql/sql_base.cc lines 7413–7421

If nothing is found, fix_fields() looks in two more places: aliases in the SELECT list (only when select->is_item_list_lookup is on, from line 6729) and tables of the outer query (fix_outer_field() — this is how outer.col in a correlated subquery is found). If it's still missing, fix_outer_field() calls find_field_in_tables() once more, this time with REPORT_ALL_ERRORS, to raise 1054 (item.cc lines 6511–6528). The trailing 'WHERE' or 'ORDER BY' in the message is thd->where — a note of which clause is being processed — turned into text by thd_where() (sql_class.cc lines 667–719).

The red boxes are the two errors we have all seen many times. They come from different boxes.

Example 4: 1054 and 1052, and their call stack

ex/u07-name-resolution sends deliberately wrong statements against two tables, t1(id, name) and t2(id, t1_id) (mariadb --force keeps going after an error; the step-label tables and echoed statements are shortened).

--- 1. unknown column in the select list
ERROR 1054 (42S22) at line 4: Unknown column 'nope' in 'SELECT'
--- 2. unknown column in WHERE / ORDER BY
ERROR 1054 (42S22) at line 6: Unknown column 'nope' in 'WHERE'
ERROR 1054 (42S22) at line 7: Unknown column 'nope' in 'ORDER BY'
--- 3. ambiguous column (both t1 and t2 have id)
ERROR 1052 (23000) at line 9: Column 'id' in SELECT is ambiguous
--- 4. qualified with a table that is not in FROM
ERROR 1054 (42S22) at line 11: Unknown column 't3.id' in 'SELECT'
--- 5. a select-list alias: ORDER BY yes, WHERE no
+---+
 k |
+---+
 1 |
 2 |
+---+
ERROR 1054 (42S22) at line 14: Unknown column 'k' in 'WHERE'
--- 6. unknown table (this one fails earlier, in open_tables)
ERROR 1146 (42S02) at line 16: Table 'lab.no_such_table' doesn't exist

The same 1054 ends with 'SELECT', 'WHERE' or 'ORDER BY' — that's thd->where. Statement 3 found id in both t1 and t2, hence 1052. Statement 4 prefixed a t3 that isn't in FROM, and still got 1054 — not 1052 and not an "unknown table" error.

We checked with gdb that 1054 really comes along the path described above: a breakpoint at line 7475, where find_field_in_tables() raises ER_BAD_FIELD_ERROR, then SELECT id FROM t1 WHERE nope = 1.

#0  find_field_in_tables at sql/sql_base.cc:7475
#1  Item_field::fix_outer_field at sql/item.cc:6521
#2  Item_field::fix_fields at sql/item.cc:6808
#3  Item::fix_fields_if_needed at sql/item.h:1162
#4  Item_func::fix_fields at sql/item_func.cc:394
#5  Item::fix_fields_if_needed at sql/item.h:1162
#6  Item::fix_fields_if_needed_for_scalar at sql/item.h:1171
#7  Item::fix_fields_if_needed_for_bool at sql/item.h:1175
#8  setup_conds at sql/sql_base.cc:9456
#9  setup_without_group at sql/sql_select.cc:970
#10  JOIN::prepare at sql/sql_select.cc:1619
#11  mysql_select at sql/sql_select.cc:5432
#12  handle_select at sql/sql_select.cc:652
#13  execute_sqlcom_select at sql/sql_parse.cc:6266

Read from the bottom up, it is this post's path exactly: handle_select() at line 6266 of execute_sqlcom_select() → setup_without_group() at line 1619 of JOIN::prepare() → setup_conds() calls fix_fields() on nope = 1 in WHERE → Item_func::fix_fields() of = on its argument nope → Item_field::fix_fields() → not found in the tables, not an alias, so only after going as far as fix_outer_field() → find_field_in_tables() raises the error. You can see the error coming not from the first call to find_field_in_tables() (line 6714) but from the second (line 6521).

Easy to confuse: an alias works in ORDER BY but not in WHERE

In statement 5, SELECT id AS k FROM t1 ORDER BY k works, while … WHERE k = 1 gives 1054. That's because setup_conds() switches alias lookup off while it processes WHERE.

  select_lex->is_item_list_lookup= 0;
  select_lex->context.resolve_in_select_list= false;

sql/sql_base.cc lines 9424–9425

WHERE filters rows one by one and is evaluated before the SELECT list is computed, when the alias k has no value yet. ORDER BY, on the other hand, goes through setup_order() → find_order_in_list(), which looks in the SELECT list first (sql_select.cc lines 29829–29831). The logical processing order of standard SQL (FROM → WHERE → SELECT → ORDER BY) shows up in the source as "while processing which clause is the alias switch on".

Easy to confuse: names are resolved only once

Searching through every table again each time a prepared statement (PREPARE) runs would be wasteful. So the result is written into the Item_field — item->cached_table (which TABLE_LIST it was) and cached_field_index (which column). On the next run, find_field_in_tables() looks at these first (from line 7239). The source comment also states the price: "we also miss case when addition of one more field makes some prepared query ambiguous and so erroneous, but we accept this trade off" (lines 7242–7250).

What this post leaves out

• Opening a view by parsing its definition and splicing it in, mysql_make_view(): the share->is_view branch of open_table(), sql_base.cc lines 2199–2234, sql/sql_view.cc
• Preparing derived tables (FROM (SELECT …)) and CTEs, mysql_handle_derived(): sql_base.cc lines 6064–6071, sql/sql_derived.cc
• "Prelocking", which also opens the tables used by triggers and stored functions up front: the sroutine_to_open loop in open_tables(), DML_prelocking_strategy
• Closing everything and reopening when an outdated definition is met during FLUSH TABLES or ALTER: Open_table_context::request_backoff_action() and recover_from_failed_open(), tdc_wait_for_old_version()
• Opening under LOCK TABLES (thd->locked_tables_mode) and the THR_LOCK of lock_tables() → mysql_lock_tables() (sql/lock.cc)
• Backup locks (MDL_key::BACKUP — the MDL_BACKUP_ALTER_COPY seen in the example) and BACKUP STAGE: sql/backup.cc, MDL_backup_lock in mdl.cc
• The MDL "fast road" (MDL_lock::m_fast_road, try_fast_upgrade()) — a path that handles granted tickets outside the m_granted list: sql/mdl.cc
• Roles (SET ROLE) and PUBLIC, views and routines running with definer rights (Switch_to_definer_security_ctx): sql/sql_acl.cc
• "Discovery", which asks the engine for a table definition and creates the .frm: GTS_USE_DISCOVERY in open_table_def(), ha_discover_table()
• System versioning (FOR SYSTEM_TIME) adding conditions in vers_setup_conds(): line 1516 of JOIN::prepare()

Summary

What's next

Now the JOIN knows which columns of which tables it uses and which tables each condition touches (the map bits). The remaining question is "so in what order, and through which indexes, do we read?" Finding constant tables, the range optimizer, choosing a join order and where EXPLAIN is built are in Part 8: The Optimizer — JOIN::optimize and Choosing a Plan. The three statistics tables that quietly opened in Example 2 are used there.

We have met the same job in earlier series. If Tomcat Part 7's Mapper binds a URL to a Host, Context and servlet, and httpd Part 10's translate_name and map_to_storage bind a URL to a disk path and its configuration, MariaDB's preparation stage binds SQL names to a TABLE and a Field. All three finish "finding" before "executing", write the result into the request object (MappingData, request_rec, TABLE_LIST/Item_field), and let the next stage pick it up. Privileges look alike too. Just as httpd Part 13 and Tomcat Part 14 separated authentication (who are you) from authorization (what may you do), MariaDB authenticates once at connect time (acl_authenticate() in Part 4) and checks privileges for every statement in the three layers of this post.

From the application side: the longer a Spring Part 17 @Transactional method runs, the longer it holds the MDLs of the tables it has read. If an ALTER TABLE issued by a migration tool during a deploy stalls and requests pile up behind it, Example 3 of this post is that scene.

• Part 2: The Big Picture — the Server Layer, Storage Engines, and the Life of One Query
• Part 6: The Parser — How an SQL Statement Becomes LEX and Item Trees
• Part 9: Execution and the Handler API — How the Nested-Loop Join Calls the Engine
• Part 11: InnoDB Part 2 — Transactions, MVCC, Undo and Locks