MySQL

Announcing VillageSQL Server 0.0.6

Adam Levin

Adam Levin

— 7 min read
Share
Announcing VillageSQL Server 0.0.6

VillageSQL Server 0.0.6 is now available. This release advances the mainline to MySQL Server 8.4.11 and adds support for MySQL Server 9.7.2 and Percona Server 8.4.10.

Highlights of this release include the following:

  • An extension can now log you in, so your identity provider decides who gets into the database.
  • Extensions can call the server's own internal component services, which opens up a large part of the MySQL surface area.
  • A privilege of its own now covers extension management, so you can grant that and nothing else.
  • If you build a custom type incorrectly, you find out when you compile the extension or when you install it, rather than when someone runs a query.

VillageSQL now runs on MySQL Server 9.7 and Percona Server 8.4

VillageSQL Server 0.0.6 adds a mysql-9.7 build that tracks the MySQL Server 9.7.2 tree, and a percona-8.4 build with Percona Server 8.4.10 merged in. The mysql-8.4 build tracks MySQL Server 8.4.11.

You pick a codebase when you download a server tarball. Every tarball on the release page carries its codebase in the file name, so you know which tree the build came from before you unpack it. The running server reports the same name as a prefix on the VillageSQL version:

SHOW VARIABLES LIKE 'villagesql_server_version';
-- villagesql_server_version    mysql-9.7_0.0.6

A data directory belongs to one codebase. The server refuses to start against a data directory that a build from a different codebase initialized, rather than attempting an upgrade across trees.

Logging in through an extension

Until now, accepting an OIDC token from your identity provider required a server plugin. In 0.0.6 an extension can register a login method through the vsql::preview::auth capability. The server calls it while the client is still connecting. The extension answers two questions: whether the connection is allowed, and which account it runs as.

Pointing an account at that method looks like any other IDENTIFIED WITH. The example below uses vsql_auth_test, the test extension that ships with the server source. The example shows what the statements look like without an identity provider in the picture. The auth capability is preview, so the first statement turns preview extensions on and persists that across restarts.

SET PERSIST vsql_allow_preview_extensions = ON;
INSTALL EXTENSION vsql_auth_test;
CREATE USER auth_user IDENTIFIED WITH vsql_auth_test;
CREATE USER vsql_auth_test_user;
GRANT SELECT ON *.* TO vsql_auth_test_user;
GRANT PROXY ON vsql_auth_test_user TO auth_user;

The connection runs as the account the extension mapped it to, and the identity the client presented stays visible for the audit trail:

SELECT CURRENT_USER(), @@external_user;
-- vsql_auth_test_user@%    auth_user

The server rejects a wrong token, an empty token, or a login whose extension has been uninstalled.

An authentication extension such as vsql-oauth2 can name the roles to switch on for the session, so the claims inside a token decide what the connection starts with. By default the server activates only roles the account already holds, so a login starts with the granted set or a subset of it. An extension can also opt in, one login at a time, to have the server grant the roles it stages, so a token can carry a role the account was never given. Leave that switch alone and your DBA keeps ownership of grants.

An extension can also create the account on the first successful login, which makes sense if you want your identity provider to be the place that decides who exists. The server accepts any client plugin that passes the secret unscrambled, so you connect with the client you already have.

What lands in 0.0.6 is the hook the server calls at login, role activation, account creation, and the SDK pieces you need to write a login method of your own. You can also use the vsql-oauth2 extension, bundled with this release, which provides these features out of the box. If you installed VillageSQL with the install script, the Docker image, or a release tarball, its .veb file is already on disk, so you just run INSTALL EXTENSION vsql_oauth2. If your login flow needs something this shape doesn't cover, tell us on Discord or in Issues.

Extensions can call the server's own services

MySQL keeps a central registry of internal services that its components can call. Until now only a component could reach them. A VillageSQL extension can now declare the services it wants, and the server acquires them at load and writes the pointers into the extension. We will keep folding these services into the extension framework as first-class features, but this gives extension authors a way to reach all of what MySQL already supports today.

Three examples:

  • an extension can write to the server error log through mysql_simple_error_log, and the log line lands where you would expect, in performance_schema.error_log
  • an extension can store a secret and read it back through the keyring services
  • an extension can reach the session it is running in and read a piece of its state, such as which command that session is running

Read more about services in this blog post.

Running code when an extension loads and unloads

The extension builder gained on_init() and on_deinit(). on_init() runs once each time the extension loads, at install and again at every server startup, after the server has validated and accepted the extension. on_deinit() runs when it unloads, at uninstall or at server shutdown.

Both run inside the extension with no access to the server, so they are for the extension's own private housekeeping. You might use them to pick function pointers for the CPU you are running on or to allocate state the extension owns and free it again. Setup that needs to talk to the server belongs in a capability's on_populate instead.

Names now follow MySQL's rules

All six VillageSQL system tables now use utf8mb4_bin, so identifiers compare byte for byte and two names match only when MySQL says they match. Starting 0.0.6 on an older data directory converts the tables and bumps the stored schema version.

The old collation folded some distinct names together. For example, MySQL treats the German ß and ss as two different column names, so a plain ss INT sitting beside a custom-type ß column matched the wrong metadata row, and SHOW FIELDS reported the integer as a custom type. Names differing only in case or in accents went the same way.

Extension management has its own privilege

Installing an extension used to require SUPER, which is a broad privilege to hand someone so they can run one statement. INSTALL EXTENSION, UNINSTALL EXTENSION, and ALTER EXTENSION can now be granted through a dynamic EXTENSION_ADMIN privilege instead of SUPER:

GRANT EXTENSION_ADMIN ON *.* TO deploy_bot;

Without it you get ERROR 1227 (42000): Access denied; you need (at least one of) the EXTENSION_ADMIN or SUPER privilege(s) for this operation. An in-place upgrade grants the new privilege to everyone who already holds SUPER, so nothing that worked before stops working.

Setting the root password in Docker

The Docker image accepts the root password as a hash. Set MYSQL_ROOT_PASSWORD_CACHING_SHA2_HASH_HEX to the hex encoding of a caching_sha2_password hash, and the plaintext password then stays outside the container. The image checks the value at startup and refuses a value with the wrong prefix or with an odd number of hex digits. The server starts with networking and mysqlx switched off while it sets up the database. No client can connect until the root password is in place.

Custom-type checks

A custom type built incorrectly used to fail at INSTALL EXTENSION or in the middle of a CREATE TABLE or ALTER TABLE. We expanded the checks that could be done earlier, at extension compile time when possible, and moved others to extension install time. The type builder now catches its own rules being broken when you compile and install-time checks cover other scenarios.

More capabilities for Rust extensions

In this release, the Rust SDK gained additional capabilities. Custom types can take parameters now, the way TVECTOR(3) does, and a Rust function can aggregate rows or accept any number of arguments. A Rust extension can now register its own system and status variables, store a secret and read it back through the server's keyring, and run a background worker thread. Those are preview, so they need a server started with vsql_allow_preview_extensions=ON.

Stability fixes

The server and every bundled extension now build nightly under ASan, UBSan, and LSan, which catch memory errors, undefined behavior, and leaks. The server test suite and the extension suite run against all three every night, so a new leak or overflow shows up in the next run. For a list of fixes made in this release, consult the release notes.

New extensions

The extension catalog continued to grow this cycle:

  • vsql-mcp: turns a MySQL database into a Model Context Protocol (MCP) server for AI agents.
  • vsql-currency: ISO 4217 currency codes as a column type, 164 of them in a single byte, with case-insensitive input, canonical uppercase output, alphabetical ordering, and index support. A Rust port of adjust/pg-currency.
  • vsql-oauth2: the OAuth2/OIDC authentication extension described above.
  • vsql-stat-ch: per-statement telemetry shipped to ClickHouse, over the native protocol on port 9000 or HTTP on 8123, switchable while the server is running. It uses a preview capability, so it needs vsql_allow_preview_extensions=ON.
  • vsql-slugify (community, by @intojhanurag): URL-safe slugs from arbitrary text, folding accents, lowercasing, and collapsing runs of non-alphanumerics into a single separator.
  • vsql-pgjwt (community, by @intojhanurag): signs, verifies, and decodes JSON Web Tokens in SQL, with HMAC (HS256, HS384, and HS512) and RSA (RS256).
  • vsql-address-standardizer (community, by @jobala): parses freeform US postal addresses into house number, street, suffix, city, state, and ZIP. Inspired by PostgreSQL's contrib address_standardizer.

What's ahead

The next release will focus on hardening the extension framework for production workloads and introducing custom indexing and vector support. You can see the vector and indexing work in progress in the vsql-vector repo, and in the server repo on the PRs labeled area/indexing. We are also focused on more stabilization fixes on our path to general availability.

Special thanks

Thanks to @samkhn for fixing a documentation comment that broke the Clang -Wdocumentation build, and to @catalinbp for correcting the test command in the vsql-vector README. @addityeah walked through the project setup as a new contributor and reported what was missing, from the clone path in the README to a build step that assumed you had already built the MySQL client, and @jtomaszon and @vgrippa reviewed the Portuguese documentation and sent corrections. Three community-authored extensions joined the catalog this cycle: vsql-slugify and vsql-pgjwt from @intojhanurag, and vsql-address-standardizer from @jobala.

There's more in this release than fits here. The release notes carry the per-PR detail. Our roadmap is published on GitHub, and we'd like to hear what you're building and what we should work on next, either there or on Discord. If you want to support VillageSQL, please star the GitHub repo.

Read more