-
NodeJS MySQL Create Database
Create a MySQL database from Node.js with one query. The trick is connecting without naming a database first, running CREATE DATABASE IF NOT EXISTS, then verifying the schema exists. Here is how to do it with mysql2 and handle the failures you will hit in production. The SQL you need IF NOT EXISTS is the […]
-
Access MySQL over MCP with vsql-mcp
The Model Context Protocol (MCP) has become a standard way for AI agents to discover the tools an application, database, or service offers. Claude Code, Codex, Cursor, Antigravity, et al. can connect to an MCP server, ask it what tools are available, and call these tools instead of figuring out what they can or can't do on the fly or piecing together API calls. Setting up an MCP server for a database usually means running a separate process that holds a database credential and turns tool calls into queries. Then you have another process to deploy, another place a credential lives, and a set of guardrails that sit outside the database.
The vsql-mcp extension from VillageSQL takes a different approach. It puts the MCP server inside the database. Install it, set a handful of global variables, and your MySQL server itself serves MCP over Streamable HTTP from a background worker in the database server process. There's no sidecar to run, and the rules about what an agent can see and do are server state: you set them with SET GLOBAL, read them with SHOW VARIABLES, and watch them work with SHOW STATUS.
VillageSQL is the innovation platform for MySQL that adds an extension framework (similar to PostgreSQL's extension framework) to enable permissionless innovation. Instead of waiting for a feature to be implemented in a few years in a future version of MySQL, new functionality can be dynamically added to a version of MySQL you run today.
The extension comes packaged with a set of six tools. list_schemas, list_tables, and describe_table browse what you expose. query runs one read-only SELECT, and explain returns its plan. write is the sixth, and it stays off until you turn it on; until then an agent cannot see that it exists. Two MCP resources come with the tools: a schema overview and a table's CREATE TABLE. Guidance on adding your own tools is at the bottom of this post.
The rest of this post walks through using the extension end to end.
If you would rather stop reading here and have your AI agent demonstrate this for you, open this dropdown and copy the prompt into your preferred AI coding tool.
Set up a working demo of the vsql-mcp extension for VillageSQL, which serves
the Model Context Protocol from inside the database server. Work only against
a local throwaway server, and if the only VillageSQL or MySQL server you find
looks like something I depend on, stop and ask me before touching it.
Do all of this yourself, and show me the real output of each step:
1. Find a VillageSQL server, or install one. The install script needs a
codebase and a method:
`curl -fsSL https://install.villagesql.com | VSQL_CODEBASE=mysql-8.4 INSTALL_METHOD=prebuilt bash`.
Start it with `--vsql_allow_preview_extensions=ON`; on a server already
running, `SET PERSIST vsql_allow_preview_extensions=ON` takes effect at
once. Confirm with `SELECT VERSION()` before continuing.
2. Build the extension. It is not bundled with the server yet. Install the
tooling with `cargo install cargo-vsql`, clone
https://github.com/villagesql/vsql-mcp, run `cargo vsql package` in it,
and copy `dist/vsql_mcp.veb` into the directory
`SHOW VARIABLES LIKE 'veb_dir'` names. You need a Rust toolchain, 1.87 or
newer. The SDK comes from crates.io.
3. Create a small demo schema with two related tables and a few dozen rows,
then run `INSTALL EXTENSION vsql_mcp;` and show me the settings that
appeared under `SHOW GLOBAL VARIABLES LIKE 'vsql_mcp%'`.
4. Configure it the least-privilege way: a dedicated database account with
SELECT on the demo schema only, `vsql_mcp.db_url` pointing at that
account, `vsql_mcp.schema` set to the demo schema, `require_auth` ON with
a random bearer token, and a free port. Turn `vsql_mcp.vsql_mcp_enabled`
ON and prove it is listening with `SELECT vsql_mcp.info();`.
5. Register the endpoint with yourself (for Claude Code:
`claude mcp add --transport http vsql http://127.0.0.1:PORT/mcp --header
"Authorization: Bearer TOKEN"`), then answer a question about the demo
data using only the MCP tools, and show me which tools you called.
6. Try to break it. Send a request with no bearer token and show the 401.
Ask the query tool to run an UPDATE. Query a table in another schema.
Set `vsql_mcp.allowed_tables` to one table and show that the others
disappear from list_tables and refuse describe_table. Show me each
refusal message.
Then give me a table of what you ran and what came back, tell me anything
that did not behave the way this asked, and drop every user, schema, and
setting you created, remove the MCP registration, and leave my machine the
way you found it.
Build it
vsql-mcp is written in Rust and uses "preview capabilities" of the VillageSQL Extension Framework (VEF). It isn't bundled with the server as of this writing, so you must build it yourself. You need a Rust toolchain (1.87 or newer) and the cargo-vsql tool. The VillageSQL Rust SDK comes from crates.io, so there is nothing else to clone:
cargo install cargo-vsql
git clone https://github.com/villagesql/vsql-mcp
cd vsql-mcp
cargo vsql package
That produces dist/vsql_mcp.veb. Copy it into your server's extension directory, which the server can help you identify:
SHOW VARIABLES LIKE 'veb_dir';
Connect an agent
The examples below use a toy shop schema. Create it to follow along:
CREATE DATABASE shop;
USE shop;
CREATE TABLE customers (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(80) NOT NULL,
country CHAR(2) NOT NULL,
joined DATE NOT NULL
);
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
placed_on DATE NOT NULL,
status ENUM('pending','shipped','delivered','cancelled') NOT NULL,
total DECIMAL(10,2) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
INSERT INTO customers (name, country, joined) VALUES
('Carla Reyes', 'MX', '2024-02-11'),
('Ben Osei', 'GH', '2024-03-02'),
('Grace Liu', 'SG', '2024-03-19'),
('Tomas Vrba', 'CZ', '2024-05-07'),
('Priya Nair', 'IN', '2024-06-23'),
('Mei Tanaka', 'JP', '2024-08-14');
INSERT INTO orders (customer_id, placed_on, status, total) VALUES
(1, '2024-04-03', 'delivered', 150.00),
(1, '2024-07-21', 'delivered', 270.00),
(1, '2024-09-02', 'cancelled', 80.00),
(2, '2024-04-18', 'delivered', 99.95),
(2, '2024-08-30', 'delivered', 210.50),
(3, '2024-05-26', 'delivered', 76.25),
(3, '2024-09-11', 'delivered', 200.00),
(4, '2024-06-14', 'delivered', 180.00),
(4, '2024-10-05', 'shipped', 60.00),
(5, '2024-07-08', 'delivered', 120.00),
(5, '2024-10-19', 'pending', 900.00),
(6, '2024-09-27', 'delivered', 95.50),
(6, '2024-10-23', 'cancelled', 40.00);
Exposing it to agents is one server setting, one INSTALL EXTENSION, one least-privilege account, and six extension settings, all run as an administrator:
-- vsql-mcp declares VEF preview capabilities. SET PERSIST takes effect at
-- once; a server you start yourself can take --vsql_allow_preview_extensions=ON.
SET PERSIST vsql_allow_preview_extensions = ON;
INSTALL EXTENSION vsql_mcp;
-- A dedicated least-privilege account the server runs tool queries as.
CREATE USER 'mcp'@'127.0.0.1' IDENTIFIED BY 'change-me';
GRANT SELECT ON shop.* TO 'mcp'@'127.0.0.1';
SET GLOBAL vsql_mcp.db_url = 'mysql://mcp:change-me@127.0.0.1:3399';
SET GLOBAL vsql_mcp.schema = 'shop';
SET GLOBAL vsql_mcp.require_auth = ON;
SET GLOBAL vsql_mcp.bearer_token = 'a-long-random-token';
SET GLOBAL vsql_mcp.port = 3400;
SET GLOBAL vsql_mcp.vsql_mcp_enabled = ON;
The db_url line is the important one. Queries, EXPLAINs, writes, and a table's CREATE TABLE all reach the database over a loopback connection as that account. Schema and table browsing runs inside the server instead, so it does not need db_url. That account's GRANTs are the real access boundary; the read-only and allowlist checks on top are defense in depth. Here the account can read the shop schema and nothing else.
The port defaults to 3100; these examples use 3400 because something else already had 3100. The server is now listening:
SELECT vsql_mcp.info();
{"enabled":true,"http_port":3400,"https_port":0,"port":3400,
"protocol_version":"2025-06-18","schema":"shop","sessions_active":0,"ssl_port":3143}
http_port and https_port are what's actually listening. ssl_port is only the configured default (3143), and https_port stays 0 because HTTPS starts only when vsql_mcp.ssl_cert and vsql_mcp.ssl_key are set.
Registering it with Claude Code is one command, and any client that speaks MCP over Streamable HTTP connects the same way, with a URL and a bearer header. There's no stdio transport, since there's no child process to spawn, so a stdio-only client needs a bridge in front (the README covers that, plus the equivalent setup for Codex and Antigravity):
claude mcp add --transport http vsql http://127.0.0.1:3400/mcp \
--header "Authorization: Bearer a-long-random-token"
That's the whole integration. Ask a question:
claude -p "Which customer has the highest total value of delivered orders? \
Use the vsql MCP tools." --allowedTools mcp__vsql
**Carla Reyes** has the highest total value of delivered orders: **$420.00**.
I joined `shop.customers` to `shop.orders`, kept only the rows with
`status = 'delivered'`, and summed `total` for each customer. The top results are:
| Customer | Delivered total |
|---|---|
| Carla Reyes | $420.00 |
| Ben Osei | $310.45 |
| Grace Liu | $276.25 |
A trace of the run shows the agent calling list_schemas, list_tables, and describe_table on both tables before writing the join and running it through the query tool. The database credential never left the server; the client got a URL and a bearer token, and nobody pasted a schema into a prompt. The query tool hands back JSON an agent can reason over. Here's the same question asked through it directly:
SELECT c.name, c.country, SUM(o.total) AS delivered_total
FROM shop.customers c
JOIN shop.orders o ON o.customer_id = c.id
WHERE o.status = 'delivered'
GROUP BY c.id, c.name, c.country
ORDER BY delivered_total DESC
LIMIT 3;
{
"columns": ["name", "country", "delivered_total"],
"row_count": 3,
"rows": [
{"name": "Carla Reyes", "country": "MX", "delivered_total": "420.00"},
{"name": "Ben Osei", "country": "GH", "delivered_total": "310.45"},
{"name": "Grace Liu", "country": "SG", "delivered_total": "276.25"}
],
"truncated": false
}
Push on the guardrails
Sooner or later an agent asks for something you didn't intend to allow. vsql-mcp answers with a refusal that names the rule it hit, so an agent can read the message and adjust course instead of retrying blind.
The query tool accepts a single read-only statement. Asked to run an UPDATE, it answers:
only a single read-only statement is allowed by the query tool
Writes have their own tool, and it's off by default. Until you set vsql_mcp.allow_write = ON, the write tool is absent from tools/list entirely, so an agent never plans around a tool it cannot use. With this configuration, tools/list returns five tools.
With vsql_mcp.schema = 'shop', a query that reaches for another schema is refused:
schema 'mysql' is outside the exposed schema; only 'shop' is available,
and table names must be qualified with it
You can narrow it further. Setting vsql_mcp.allowed_tables = 'orders' confines the agent to named tables. The check runs on the statement's actual plan, so a join or subquery that touches an excluded table is caught too:
table 'customers' is not in vsql_mcp.allowed_tables
The one place the check doesn't reach is a stored function's body, which EXPLAIN doesn't descend into. The README covers it.
The allowlist also governs what an agent can learn, not only what it can read: describe_table refuses an excluded table, and list_tables omits it entirely.
Beyond those, max_rows caps every query result (and marks it truncated so the agent knows), query_timeout bounds each call, and requests without the bearer token get HTTP 401 before any of this runs. The transport also validates the Origin header, as the MCP spec requires; a request claiming to come from a non-local origin gets HTTP 403. The listener binds to 127.0.0.1 only, so exposing it beyond the machine is a decision you make deliberately, with a reverse proxy in front. The README lists the remaining boundaries plainly, and the boundary to design around is the grants. Guardrails narrow what an agent can do, and the db_url account's grants are what it can never exceed.
Watch the agent from SQL
Because the MCP server is server state, you can monitor it the way you monitor everything else in MySQL. After the agent calls above:
SHOW STATUS LIKE 'vsql_mcp%';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| vsql_mcp.http_port | 3400 |
| vsql_mcp.https_port | 0 |
| vsql_mcp.rows_returned_total | 11 |
| vsql_mcp.sessions_active | 5 |
| vsql_mcp.tool_calls_total | 15 |
| vsql_mcp.tool_errors_total | 6 |
+------------------------------+-------+
The refusals shown above are counted in tool_errors_total, along with any tool call that fails for ordinary reasons, like malformed SQL. If tool errors spike, something is off on the tool surface, whether that's an agent pushing at the rules or queries that simply fail, and you find out from the database rather than from a sidecar's log file.
Make it your own
The six tools are a list in the extension's own source, and the extension is GPL-2.0 Rust you can fork and modify. src/tools.rs holds a JSON array that advertises each tool, and a match that routes a call to its handler — so adding one of your own is a few edits.
Say you want an agent to see a table's indexes before it writes a join. Advertise it in tool_definitions():
{
"name": "list_indexes",
"description": "Indexes on a table, one row per indexed column.",
"inputSchema": {
"type": "object",
"properties": {
"schema": { "type": "string" },
"table": { "type": "string" }
},
"required": ["table"]
}
},
Route it in call():
"list_indexes" => list_indexes(args, cfg, exec),
Then write the handler. It receives the call arguments, the live configuration, and the executor, and every seam the built-in tools use is available to it:
fn list_indexes(args: &Json, cfg: &RequestConfig, exec: &dyn QueryExecutor) -> Result<Json, String> {
let table = arg_str(args, "table")?;
let schema = effective_schema(args.get("schema").and_then(Json::as_str), cfg)?;
if !guardrails::table_allowed(table, &cfg.allowed_tables) {
return Err(format!("table '{table}' is not in vsql_mcp.allowed_tables"));
}
let rows = exec.read_params(
"SELECT INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, NON_UNIQUE \
FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? \
ORDER BY INDEX_NAME, SEQ_IN_INDEX",
vec![MyValue::from(schema.clone()), MyValue::from(table.to_owned())],
&[
col("INDEX_NAME"),
numeric_col("SEQ_IN_INDEX"),
col("COLUMN_NAME"),
numeric_col("NON_UNIQUE"),
],
cfg.query_timeout,
)?;
let indexes: Vec<Json> = rows
.rows
.iter()
.map(|r| {
json!({
"name": field(r, "INDEX_NAME"),
"column": field(r, "COLUMN_NAME"),
"position": field(r, "SEQ_IN_INDEX"),
"unique": r.get("NON_UNIQUE").and_then(Json::as_i64) == Some(0)
})
})
.collect();
Ok(json!({ "schema": schema, "table": table, "indexes": indexes }))
}
Three of those lines are why you extend the extension instead of writing a sidecar. guardrails::table_allowed applies the operator's allowed_tables list, so the new tool honors a rule nobody had to teach it. effective_schema pins the call to the configured schema. And read_params runs a fixed-shape statement in-process through the sql_query capability, so a tool like this needs no db_url and opens no connection; arbitrary SQL goes through exec.read() over the loopback connection instead. The one import to add is numeric_col, alongside col at the top of the file.
Rebuild, then swap the package in while the extension is uninstalled:
cargo vsql package
UNINSTALL EXTENSION vsql_mcp;
Copy the new dist/vsql_mcp.veb into your veb_dir, replacing the old one, then:
INSTALL EXTENSION vsql_mcp;
The next tools/list includes list_indexes. Reinstalling returns every vsql_mcp.* setting to its default, including values you set with SET PERSIST, so re-apply the configuration afterward. Bump the version in manifest.json while you are in the tree, and run cargo vsql test: the mysql-test/ suite covers the protocol, the guardrails, and the refusals, so a change that breaks one of them fails before an agent finds it.
Try it out
The vsql-mcp repository covers every setting, the client setup for Codex and Antigravity, and the current limitations. Point an agent at a schema you know well, watch what it asks for, and tell us where the guardrails helped or got in the way, on Discord or in a GitHub issue.
To get started with VillageSQL Server, go to villagesql.com.
-
Join MySQL Public Discussion #6 and the November Contributor Summit
The MySQL Community conversation continues this fall with two opportunities to connect, share feedback, and help shape the future of MySQL. Following the recent Contributor Summits and Public Discussions, we are continuing to expand practical ways for users, developers, DBAs, partners, and contributors to participate in technical discussions, share ideas, and collaborate more openly across the MySQL […]
-
Hardening EmergencyReparentShard in v25
EmergencyReparentShard operations are being hardened in upcoming release v25. In this blog, we cover how ERS works and the upcoming changes that make recovery safer, faster and less brittle
What is EmergencyReparentShard? # EmergencyReparentShard (ERS) is the Vitess failover process used when a shard's current primary is dead or unreachable. While PlannedReparentShard gets a clean handoff from a healthy primary, ERS has to pick a replacement using only surviving tablets. It compares their transaction histories, promotes an eligible replacement, updates the topology and points the other tablets at the new primary.
-
How Atomic DDL Works in MySQL 8.0
Before MySQL 8.0, DDL was not crash-safe. There were three main problems:
The metadata in the server layer and the metadata/data in InnoDB could become inconsistent.
The server-layer metadata was stored in files — for example, table definitions were kept in .frm files — while InnoDB kept its own copy of the metadata in its tables. A crash could leave the server-layer metadata inconsistent with InnoDB’s metadata or even with the table data. For example, the server layer still had the table’s .frm file and believed the table existed, but InnoDB’s .ibd data file was gone, so InnoDB believed the table did not exist.
InnoDB’s metadata and data could become inconsistent.
The binlog and the data could become inconsistent.
For example, after a crash and restart the table already existed, but the CREATE TABLE had not been written to the binlog.
To implement Atomic DDL and fully solve these problems, MySQL 8.0 made changes in three areas:
It removed the server-layer metadata files and stored all metadata in InnoDB tables.
These tables are called the Data Dictionary. Users, the server layer, and the storage engines all query or update metadata through the Data Dictionary access interface.
The DDL log.
InnoDB implements a DDL log table that records DDL operation entries during a DDL. InnoDB uses the DDL log to guarantee the atomicity of the file operations and metadata operations within a DDL.
Binlog DDL crash safety.
The binlog event for a DDL records the DDL’s transaction Xid, and the Xid is used to make the binlog crash-safe.
The DDL Transaction
The basic idea behind DDL atomicity is to turn the DDL process into a transaction, and to use the atomicity of the transaction to guarantee the atomicity of the DDL. MySQL 8.0 stores all metadata in InnoDB tables, so operating on metadata is really just performing INSERT, UPDATE, or DELETE on InnoDB tables. Metadata operations can therefore be carried out entirely within a single transaction, and for DDL that only modifies metadata, one transaction is enough to guarantee atomicity — for example, creating, altering, or dropping a view, function, or trigger.
A DDL statement opens a transaction while it runs; we call it the DDL transaction (DDL Trx). Committing the DDL transaction is equivalent to the DDL succeeding.Everything the DDL does within the transaction can be rolled back before it commits, but not once it has committed.
The InnoDB DDL Log
For DDL that involves file operations, InnoDB writes the file operations as entries into the DDL log table, and places the DDL log operations and the metadata operations in the same transaction to make the DDL atomic.
The DDL statements that involve file operations are:
CREATE TABLE
ALTER TABLE
DROP TABLE
RENAME TABLE
CREATE INDEX
DROP INDEX
DROP DATABASE
The DDL Log Table
The DDL log table is defined as follows:
1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE mysql.innodb_ddl_log (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
thread_id BIGINT UNSIGNED NOT NULL,
type INT UNSIGNED NOT NULL,
space_id INT UNSIGNED,
page_no INT UNSIGNED,
index_id BIGINT UNSIGNED,
table_id BIGINT UNSIGNED,
old_file_path VARCHAR(512) COLLATE UTF8_BIN,
new_file_path VARCHAR(512) COLLATE UTF8_BIN,
KEY(thread_id)
);
The DDL log table records the following kinds of operation:
DELETE SPACE
Delete the specified tablespace file.
DROP
Delete the specified table’s entry from mysql.innodb_dynamic_metadata.
RENAME SPACE
Rename the specified table’s tablespace file.
RENAME TABLE
Rename the specified table, including updating its metadata and the table name in the statistics tables.
FREE
Delete the specified index.
REMOVE CACHE
Remove the specified table from the table cache.
How the DDL Log Is Used
A DDL statement is executed in two phases:
The DDL transaction phase.
During the DDL transaction, some operation entries are written into the DDL log table.
The post-DDL phase.
The entries written into the DDL log table are read back and executed in reverse order. Whether the DDL transaction succeeds or fails, the entries recorded in the DDL log table (if any) are executed.
The DDL log can be viewed as a combination of a redo log and an undo log. Some DDL uses it as redo, some uses it as undo, and some uses it as both redo and undo.
Using the DDL Log as Redo
DROP TABLE uses the DDL log as redo.
During the DDL transaction phase, DROP TABLE deletes the metadata and inserts a DELETE SPACE entry into the DDL log. Once the DDL transaction commits, it can no longer be rolled back.
During the post-DDL phase, the actual file deletion is performed according to the DELETE SPACE entry in the DDL log.
After the file deletion completes, the entry in the DDL log table is deleted.
The entries in the DDL log table are idempotent, so executing them more than once does not affect correctness. That is why executing an entry from the DDL log table and deleting that entry from the DDL log table need not be atomic.
If an error occurs during the DDL transaction phase, the DDL transaction rolls back, and the DELETE SPACE entry in the DDL log table is rolled back with it. The post-DDL phase then does nothing.
Using the DDL Log as Undo
CREATE TABLE, by contrast, uses the DDL log as undo.
During the DDL transaction phase, CREATE TABLE first records a DELETE SPACE entry in the DDL log. This entry is written and committed by a separate transaction, which we call the DDL log transaction (DDL Log Trx).
It then deletes the corresponding DELETE SPACE entry from the DDL log table within the DDL transaction.
Finally it creates the table file and, on success, commits the DDL transaction.
If the DDL transaction commits, the DELETE SPACE entry in the DDL log table has already been deleted, so the post-DDL phase does nothing.
If an error occurs, the DDL transaction rolls back, and the DELETE SPACE entry in the DDL log table is retained. The post-DDL phase then deletes the table file according to the entry in the DDL log table and deletes the entry from the DDL log table.
innodb_print_ddl_logs
To make debugging easier, InnoDB provides an option that records all operations on the DDL log table to the error log. It can be turned on and off dynamically through the innodb_print_ddl_logs variable.
Let us now look at the detailed process of several DDL statements.
CREATE TABLE
CREATE TABLE uses the DDL log as undo; when the DDL fails, it uses the DDL log to roll back the file-creation operation.
1
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 VARCHAR(20), INDEX(c2));
This DDL performs the following operations on the DDL log table:
The DDL log transaction (1802) records a DELETE SPACE rollback entry, with record ID 7.
The DDL transaction (1801) deletes record ID 7 from the DDL log table.
t1 is added to the table cache. Rolling back the table cache relies on the DDL log table, so a REMOVE CACHE entry is first added to the DDL log table by the DDL log transaction (1803), and then deleted by the DDL transaction (1801).
The two FREE entries that follow are the rollback logs for the clustered B-tree and the secondary-index B-tree. For CREATE TABLE, dropping the B-trees is unnecessary — deleting the file is enough — so DROP TABLE has no step for dropping the B-trees.
After the DDL transaction (1801) commits, all the entries added to the DDL log earlier have been deleted, so the post-DDL phase does nothing.
DROP TABLE / DROP DATABASE
DROP TABLE uses the DDL log as redo; after the DDL succeeds, the post-DDL phase performs the file deletion according to the entries in the DDL log table.
1
DROP TABLE t1, t2;
This DDL performs the following operations on the DDL log table:
A DROP entry is recorded to delete t1’s entry from mysql.innodb_dynamic_metadata. innodb_dynamic_metadata is a special table: operations on it are not undo-logged and cannot be performed within the DDL transaction. An entry is therefore recorded in the DDL log table and executed in the post-DDL phase.
A DELETE SPACE entry is then recorded to delete db1/t1.ibd.
The same operations are repeated for table t2.
After all tables have been processed, the DDL transaction (1916) commits. At this point four entries have been inserted into the DDL log table.
In the post-DDL phase, these four entries are executed in reverse order to perform the actual deletions. Redo entries can in fact be executed in any order, but undo entries must be executed in reverse order because of their dependencies. Presumably for simplicity and uniformity, the post-DDL phase always executes entries in reverse order.
DROP DATABASE operates on the DDL log table the same way as dropping every table in the specified database.
CREATE INDEX
CREATE INDEX uses the DDL log as undo; when index creation fails, it uses the entry in the DDL log table to roll back the index being created.
1
ALTER TABLE t2 ADD INDEX ind1(c3);
This DDL performs the following operations on the DDL log table:
Before creating the B-tree, the DDL log transaction (1872) records a FREE rollback entry, with record ID 30.
The DDL transaction (1871) deletes the FREE entry from the DDL log table.
The DDL transaction updates the metadata and creates the B-tree.
Finally the DDL transaction (1871) commits. After the commit, the entry in the DDL log table has already been deleted, so the post-DDL phase does nothing.
DROP INDEX
DROP INDEX uses the DDL log as redo.
1
ALTER TABLE t2 DROP INDEX ind1;
This DDL performs the following operations on the DDL log table:
The DDL transaction inserts a FREE entry into the DDL log table.
It updates the metadata and commits the DDL transaction.
In the post-DDL phase, the index’s B-tree is deleted according to the FREE entry in the DDL log table.
RENAME TABLE
RENAME TABLE uses the DDL log as undo.
1
RENAME TABLE t2 TO t20;
This DDL performs the following operations on the DDL log table:
Before renaming the file, the DDL log transaction (1909) inserts a RENAME SPACE entry into the DDL log table. This is an undo entry, so it renames t20.ibd back to t2.ibd.
The DDL transaction deletes the RENAME SPACE entry written by the DDL log transaction.
The DDL log transaction (1909) inserts a RENAME TABLE entry into the DDL log table. This is an undo entry, so it renames t20 back to t2.
A rename has to update some in-memory structures, and the table name in innodb_table_stats is updated by a separate transaction that cannot be rolled back. A RENAME TABLE entry is therefore recorded so that the rollback can be done by renaming in the reverse direction.
The DDL transaction deletes the RENAME TABLE entry written by the DDL log transaction.
After the DDL transaction (1908) commits, the entries in the DDL log table have already been deleted, so the post-DDL phase does nothing.
ALTER TABLE
There are many kinds of ALTER TABLE; the most complex is the case that requires rebuilding the table. In that case the DDL log is used as both redo and undo.
1
ALTER TABLE t2 ADD COLUMN c3 int ALGORITHM = INPLACE;
This DDL performs the following operations on the DDL log table:
ALTER TABLE combines the operations of CREATE TABLE, RENAME TABLE, and DROP TABLE:
Create the temporary table db1/#sql-ib1066-677171833, and record an undo entry to delete it.
Rename db1/t2 to db1/#sql-ib1071-677171834, and record an undo entry for the reverse rename.
Rename db1/#sql-ib1066-677171833 to db1/t2, and record an undo entry for the reverse rename.
Delete the table db1/#sql-ib1071-677171834, and record a redo entry to delete the file.
Summary of DDL Log Usage
A DDL is executed in two phases: the DDL transaction phase and the post-DDL phase.
The DDL transaction phase inserts redo and/or undo entries into the DDL log table.
The post-DDL phase executes the entries in the DDL log table.
When used as redo, the redo entries are inserted into the DDL log table by the DDL transaction, and the post-DDL phase executes them.
When used as undo, the undo entries are inserted into the DDL log table by the DDL log transaction, and then deleted by the DDL transaction. If the DDL transaction commits, the post-DDL phase does nothing; if the DDL transaction rolls back, the post-DDL phase performs the rollback according to the entries in the DDL log table.
The entries in the DDL log table are idempotent and can be executed more than once without affecting correctness.
DDL Crash Recovery
A crash can happen before the post-DDL phase finishes, so during recovery after a restart, the server must check the DDL log table and complete all outstanding post-DDL operations according to its entries.
Binlog Crash Safety
Every DDL is now a transaction: if it fails midway it can be rolled back, and if it commits the DDL has succeeded. When the binlog is enabled, the DDL transaction also uses two-phase commit. MySQL 8.0 extended the binlog’s Query_log_event so that the DDL transaction’s Xid is stored in the Query_log_event.
During crash recovery, the Xid in the DDL’s Query_log_event can then be used to decide whether to commit or roll back a DDL transaction that is in the prepared state.
CREATE TABLE … SELECT
In the binlog, CREATE TABLE … SELECT is split into a CREATE TABLE part and an INSERT part (in row format), as shown below:
1
2
3
4
5
CREATE TABLE t1 // Query_log_event
BEGIN // Query_log_event
// Table_map_log_event
INSERT // Write_rows_log_event
COMMIT // Xid_log_event
Before MySQL 8.0.21, the CREATE part and the INSERT part were two independent transactions, so atomicity was impossible. For that reason CREATE TABLE … SELECT was disallowed when GTID was enabled.
After implementing Atomic DDL, MySQL 8.0.21 improved CREATE TABLE … SELECT. The CREATE TABLE part and the INSERT part can now run in the same transaction, with atomicity guaranteed. CREATE TABLE … SELECT can therefore be used when GTID is enabled.
1
2
3
4
5
BEGIN // Query_log_event
CREATE TABLE t1 (......) START TRANSACTION // Query_log_event
// Table_map_log_event
INSERT // Write_rows_log_event
COMMIT // Xid_log_event
A real example is shown below:
As we can see, MySQL extended the CREATE TABLE statement so that CREATE TABLE and the DML run in the same transaction.
1
CREATE TABLE ... START TRANSACTION;
A DDL automatically commits the current transaction when it finishes. With the
START TRANSACTION extension, the CREATE TABLE statement no longer ends the
current transaction automatically. So the following DML runs in the same
transaction as the CREATE TABLE. Currently, CREATE TABLE … START TRANSACTION
is only for replica to replay CREATE TABLE … SELECT. But This improvement shows that once Atomic DDL is in place, making DDL transactional is not difficult, and in the future more DDL may be able to run in the same transaction as DML.
References
Atomic Data Definition Statement Support
WL#13355: Make CREATE TABLE…SELECT atomic and crash safe
WL#9536: InnoDB: Support crash-safe DDL
|
