Skip to content

mysql.md

Latest commit

 

History

History
460 lines (349 loc) · 24 KB

mysql.md

File metadata and controls

460 lines (349 loc) · 24 KB
Raw

MySQL Provider

MySQL support for LibreDB Studio, built on the mysql2 driver. This document is the single reference point for the MySQL provider: design, architecture, usage, and tests. MySQL is a SQL-family provider; it shares SQLBaseProvider with PostgreSQL — read the PostgreSQL doc first if you want the canonical SQL walkthrough, then this doc for the MySQL-specific deltas.
Status ✅ Implemented & shipped
Database type id mysql
Family SQL (relational)
Driver mysql2/promise
Query language sql
Default port 3306
Connection pooling Yes — mysql2 pool (connectionLimit = pool max, default 10)
Connection string Supported (mysql://, via the pool uri option)
Transactions Yes — explicit begin/commit/rollback with auto-rollback timeout
Query cancellation Yes — thread-id tracking + KILL QUERY
Source src/lib/db/providers/sql/mysql.ts
Base src/lib/db/providers/sql/sql-base.ts
Tests tests/integration/db/mysql-provider.test.ts

1. Overview

MySQL is a relational database and maps onto the DatabaseProvider interface much like PostgreSQL. It extends the shared SQLBaseProvider (identifier quoting with backticks, automatic LIMIT injection, ? placeholders, cloud SSL auto-detection) and layers MySQL-specific introspection and monitoring on top of mysql2.

The most useful way to read this doc is as a diff against the PostgreSQL provider, which is the SQL reference implementation. The headline differences:

Aspect PostgreSQL MySQL
Schema introspection One MATERIALIZED-CTE round-trip + two-phase (getSchemaList/getSchemaRelations) Single getSchema(), N+1 (1 + 3 queries per table), no two-phase split
Schema scope All non-system schemas, cross-schema FKs Single database (TABLE_SCHEMA = <db>), bare table names
Maintenance ops vacuum, analyze, reindex, kill analyze, optimize, check, kill
Query timeout statement_timeout from queryTimeout Not wired — no server-side query timeout
Pool config honored min/max/idleTimeout/acquireTimeout max only (connectionLimit)
Queries-per-second metric undefined (needs sampling) Reported (Queries/Uptime)
BLOB/binary values driver-native sanitized to 0x… hex strings

2. Architecture

Same Strategy-Pattern hierarchy as the other SQL providers:

DatabaseProvider (interface) → BaseDatabaseProvider → SQLBaseProvider → MySQLProvider

MySQLProvider inherits the shared SQL helpers from sql-base.ts — see the PostgreSQL doc §2.2 for the full table. The two that matter most here:

  • escapeIdentifier() quotes MySQL identifiers with backticks (`ident`), doubling any embedded backtick.
  • prepareQuery() injects LIMIT into bare SELECTs; the underlying analyzeQuery() also understands MySQL's LIMIT offset, count syntax (see §5.2).

Registration

Loaded on demand by the factory (factory.ts:67):

case 'mysql': {
  const { MySQLProvider } = await import('./providers/sql/mysql');
  return new MySQLProvider(connection, options);
}

3. Design decisions

3.1 N+1 schema introspection (no MATERIALIZED CTEs, no two-phase split)

Unlike PostgreSQL, getSchema() (mysql.ts:326) runs one query for the table list and then three queries per table (columns, foreign keys, indexes) — the classic 1 + N*3 pattern. MySQL also does not implement getSchemaList() / getSchemaRelations(), so the two-phase fast-tree loading that PostgreSQL uses is unavailable; the /api/db/schema/list route falls back to the single getSchema(). On a very large schema this is more round-trips than the Postgres approach — see Known limitations.

3.2 Single-database scope

Every introspection query is parameterized with TABLE_SCHEMA = ? bound to config.database. MySQL "schemas" are databases, so the provider only ever sees the connected database, and table display names are bare (no schema.table prefixing). There is no cross-schema FK resolution to worry about.

3.3 BLOB / binary values sanitized to hex

sanitizeRow() (mysql.ts:170) walks every result row and converts Buffer values to 0x<hex> strings (empty buffers → ''). MySQL returns BLOB/BINARY columns as Node Buffers; without this they would not serialize cleanly to the JSON grid. This runs on both query() and queryInTransaction().

3.4 Prepared statements via execute()

Both query paths use conn.execute(sql, params) (mysql2 server-side prepared statements) rather than query(), so parameterized queries are bound by the server. rowCount is rows.length only when the driver returns a row array (i.e. SELECT); for non-SELECT statements (INSERT/UPDATE/DELETE) mysql2 returns a ResultSetHeader rather than an array, and the provider reports rowCount: 0 (Array.isArray(result.rows) ? result.rows.length : 0) — affected-rows is not surfaced.

3.5 No server-side query timeout

The pool config (mysql.ts:114) intentionally sets only mysql2-specific options and does not translate ProviderOptions.queryTimeout into a server-side timeout (MySQL has no direct statement_timeout pool option like Postgres). A runaway query is not auto-killed by the provider; cancellation is explicit via cancelQuery().

3.6 Maintenance over all tables when no target

analyze/optimize/check without a target run against all base tables in the database (getAllTablesForMaintenance(), capped at 50 tables, mysql.ts:577), each name quoted via escapeIdentifier(). With a target, the single quoted table is used.

4. Connection

4.1 Configuration

Two forms (validate(), mysql.ts:66). validate() requires host and database only when no connectionString is given — it does not reject supplying both; if both are present the connection string is used (passed to the pool as uri).

// Discrete fields (host + database required when no connection string)
const a = { id: 'my-1', name: 'App DB', type: 'mysql',
  host: 'localhost', port: 3306, database: 'app',
  user: 'root', password: 'secret', createdAt: new Date() };

// Connection string
const b = { id: 'my-1', name: 'App DB', type: 'mysql',
  connectionString: 'mysql://root:secret@localhost:3306/app', createdAt: new Date() };

4.2 Connection pooling

connect() builds a mysql2 pool and validates it by acquiring/releasing one connection. The pool options (mysql.ts:114):

mysql2 option Value Source
connectionLimit pool max (default 10) ProviderOptions.pool.max
waitForConnections true fixed
queueLimit 0 (unbounded queue) fixed
enableKeepAlive true fixed
keepAliveInitialDelay 10000 ms fixed
timezone 'Z' ProviderOptions.timezone ?? 'Z' (discrete form only — see below)

⚠️ Only max from DEFAULT_POOL_CONFIG is honored. min, idleTimeout, and acquireTimeout are not mapped (the mysql2 pool model differs from pg), and queryTimeout is not applied (see §3.5).

⚠️ When a connectionString is supplied, buildPoolConfig() returns { ...baseConfig, uri } and takes the discrete-fields branch not at all — so timezone, ssl/connection.ssl, and cloud SSL auto-detect are ignored; those settings must be encoded in the URI itself.

connect() is idempotent. Unlike the PostgreSQL provider, MySQL exposes no getPoolStats().

4.3 SSL

buildSSLConfig() (mysql.ts:142) — applied only in the discrete-fields form (the connectionString path bypasses it entirely). Note disable returns undefined (mysql2's "off"), not false:

  1. Explicit connection.ssl (SSLConfig): disable → undefined; verify-ca/verify-full → rejectUnauthorized: true (otherwise false); caCert/clientCert/clientKey → ca/cert/key.
  2. options.ssl === true or cloud auto-detect — shouldEnableSSL() (options.ssl === true or a known managed host) enables { rejectUnauthorized: false }.
  3. Otherwise undefined.

5. Query interface

5.1 Execution

query(sql, params?, queryId?) (mysql.ts:185) acquires a pooled connection, optionally records its threadId for cancellation, runs the prepared statement, sanitizes binary values, and returns the standard envelope:

{ rows, fields: string[], rowCount: rows.length, executionTime }

Native mysql2 errors are normalised via mapDatabaseError() into the shared errors.ts classes.

5.2 Automatic LIMIT injection

Inherited from SQLBaseProvider.prepareQuery() (see PostgreSQL doc §5.2). The shared analyzeQuery() recognises both standard LIMIT n [OFFSET m] and MySQL's LIMIT offset, count form, so an already-limited MySQL query is respected rather than double-limited. Default page size DEFAULT_QUERY_LIMIT = 500; unlimited caps at MAX_UNLIMITED_ROWS = 100000.

MySQL's # line comment is skipped when the statement type is read, alongside -- and /* … */ (leading-keyword.ts). This is the dialect that marker exists for: a # note-led SELECT used to classify as an unknown statement type and reach the server with no LIMIT at all (#275).

Every # is a comment marker here, and this provider now says so: prepareQuery() passes its own type to the shared readers, which resolve # under MySQL's grammar instead of the dialect-less compromise they used to apply to everyone (see Which dialect the readers are reading). Three readings change on this provider, and each was wrong in a way only MySQL sees:

Statement Before Now
SELECT … # note not bounded at all — the bound has to go before the comment, and the reader could not rule out #tmp/ID#/XOR bounded, with the clause before the comment
SELECT * FROM t # LIMIT 10 the commented-out bound read as a real one, so the statement ran unbounded the comment is a comment; a real bound is added before it
WITH t AS ( + #- drop the ) SELECT here + …) DELETE FROM users the #- read as a PostgreSQL jsonb operator, so the ) inside the comment closed the CTE body, the statement typed SELECT and a LIMIT was appended to a DELETE — which MySQL 8 accepts and commits typed DELETE, not bounded

The third row is the one that cost more than rows: a bound on a DELETE commits part of it while the UI reports a truncated result set. A trailing -- note was always bounded normally and is unchanged.

5.3 Query cancellation

A query issued with a queryId records its connection threadId. cancelQuery(queryId) (mysql.ts:215) issues KILL QUERY <threadId> and returns true on success (it does not verify the target was actually mid-query). The killed query surfaces to its caller as a QueryCancelledError (MySQL emits "Query execution was interrupted", which mapDatabaseError() classifies as cancellation). Exposed via POST /api/db/cancel.

6. Transactions

Identical lifecycle to PostgreSQL, on a dedicated connection checked out from the pool and held for the transaction's duration (so every statement runs on the same connection; it is not returned to the pool until commit/rollback). Surfaced via POST /api/db/transaction.

Method Behaviour
beginTransaction() pool.getConnection() + beginTransaction(), arms a 5-minute auto-rollback timer (mysql.ts:41). Throws if one is active.
queryInTransaction(sql, params?) Runs on the transaction's connection (with the same binary sanitization). Throws if none active.
commitTransaction() / rollbackTransaction() Ends it, clears the timer, releases the connection. Throws if none active.
expireTransaction() Timeout callback — auto-rollback() to prevent leaked locks.
isInTransaction() Current state.

7. Schema introspection

getSchema() returns one TableSchema per BASE TABLE in the connected database. Per table it issues three follow-up queries:

Data Source Notes
Tables information_schema.TABLES TABLE_ROWS (engine estimate), DATA_LENGTH + INDEX_LENGTH
Columns information_schema.COLUMNS first 100 (LIMIT 100); isPrimary = COLUMN_KEY = 'PRI'
Foreign keys information_schema.KEY_COLUMN_USAGE rows where REFERENCED_TABLE_NAME IS NOT NULL
Indexes information_schema.STATISTICS GROUP_CONCAT columns by SEQ_IN_INDEX; unique = NOT NON_UNIQUE

There is no getSchemaList()/getSchemaRelations() — see §3.1.

8. Monitoring & health

All monitoring reads from SHOW STATUS/SHOW VARIABLES, information_schema, and performance_schema. getMonitoringData() (inherited) fans these out in parallel.

Method Primary source Notes
getHealth() SHOW STATUS, information_schema.TABLES/PROCESSLIST, performance_schema connections, size (MB), InnoDB buffer hit %, top-5 slow queries, 10 sessions
getOverview() VERSION(), SHOW STATUS/VARIABLES, information_schema version, uptime, conns, max_conns, size, table/index counts
getPerformanceMetrics() performance_schema.global_status cache-hit %, queries/sec (Queries/Uptime), buffer-pool %, deadlocks
getSlowQueries() performance_schema.events_statements_summary_by_digest per-digest stats
getActiveSessions() information_schema.PROCESSLIST pid, user, db, host, command, duration
getTableStats() information_schema.TABLES sizes; bloat estimated from DATA_FREE (no live/dead tuples, no last-vacuum/analyze)
getIndexStats() information_schema.STATISTICS (+ optional INNODB_*) columns, unique/primary; scans = CARDINALITY (a proxy, not a real scan counter)
getStorageStats() information_schema.TABLES, SHOW BINARY LOGS Data size, Binary Logs (if enabled), InnoDB data file (size N/A)

Graceful degradation — note the different failure modes:

  • getHealth() slow-queries: try/catch → a single placeholder row ("Performance schema not available").
  • getSlowQueries(): try/catch → empty array [].
  • getPerformanceMetrics(): the whole method is wrapped — on any failure it returns static defaults (cacheHitRatio: 99, queriesPerSecond/bufferPoolUsage/deadlocks: 0). These defaults can read as "healthy" even when performance_schema is simply off — see Known limitations.

9. Maintenance

runMaintenance(type, target?) (mysql.ts:525); targets are backtick-quoted via escapeIdentifier():

Type With target Without target
analyze ANALYZE TABLE <t> ANALYZE TABLE <all base tables, ≤50>
optimize OPTIMIZE TABLE <t> OPTIMIZE TABLE <all base tables, ≤50>
check CHECK TABLE <t> CHECK TABLE <all base tables, ≤50>
kill KILL <connection-id> throws (id required)

getCapabilities().maintenanceOperations = ['analyze', 'optimize', 'check', 'kill']. kill validates that the target parses as an integer connection id.

10. Capabilities & labels

getCapabilities() (mysql.ts:52)

Capability Value
queryLanguage sql
supportsExplain true
explainFormat mysql-json
supportsExternalQueryLimiting true (from base)
supportsCreateTable true (from base)
supportsInlineRowEdit true — UPDATE t SET c = v WHERE pk = v is core MySQL DML
declaresForeignKeys true — inherited from the base capabilities; InnoDB declares them, so an empty list means this schema (or this role) has none, not the engine
supportsMaintenance true
maintenanceOperations ['analyze', 'optimize', 'check', 'kill']
supportsConnectionString true
defaultPort 3306
schemaRefreshPattern (CREATE|DROP|ALTER|TRUNCATE)\b (from base)

Labels

MySQL uses the default SQL getLabels() from BaseDatabaseProvider (entity → Table, Select Top 50, etc.); it is not overridden. (The default analyzeAction/vacuumAction wording is generic SQL phrasing; MySQL's actual maintenance verbs are optimize/check/analyze.)

11. Error handling

Native mysql2 errors are mapped by the shared mapDatabaseError() (errors.ts). What reliably maps for MySQL:

Situation Error
Missing host/database (no connection string) DatabaseConfigError
Operation before connect() DatabaseConfigError (via ensureConnected())
connect() fails ConnectionError (carries host/port)
Access denied (ER_ACCESS_DENIED, message contains access denied) AuthenticationError
Connection refused / DNS (ECONNREFUSED, getaddrinfo) ConnectionError
Killed query ("Query execution was interrupted") QueryCancelledError
Driver message contains timeout / timed out (e.g. Lock wait timeout exceeded, connection-acquire timeout) TimeoutError
Other server errors (most ER_* codes) QueryError / DatabaseError carrying the original message

The mapper is text-heuristic, so MySQL ER_* codes that don't match a known phrase fall through to a generic QueryError/DatabaseError with the driver's message preserved. Note the nuance on timeouts: a driver error whose message contains timeout/timed out does map to TimeoutError (the mapping is provider-agnostic). What MySQL lacks is a server-side query timeout derived from queryTimeout — the provider never configures one (§3.5), so it won't auto-kill a long-running query on its own.

12. Testing

12.1 How the tests work

Integration tests live in tests/integration/db/mysql-provider.test.ts. The mysql2/promise module is replaced with an in-process mock via mock.module('mysql2/promise', …) before the provider is imported — there is no live MySQL in the suite. The mock's pool/connection returns canned [rows, fields] tuples, exercising the same provider code paths as a real server.

⚠️ Mock isolation: bun's mock.module() is process-wide, so files mocking different drivers cross-contaminate when they share a process. A single file is safe (one file = one process). The full bun run test script runs the core group in one process and is load-order flaky, so CI does not use it — the deterministic runner is bun run test:ci (per-file isolation via tests/run-core.sh); the coverage workflow uses bun run test:coverage. See CLAUDE.md.

12.2 Coverage

20+ describe blocks cover: validation (incl. connection-string bypass), connect/disconnect, capabilities, getSchema() (columns/FKs/indexes, primary-key detection), health, maintenance (all types + kill validation), the full transaction lifecycle, queryInTransaction, query cancellation, overview, performance metrics, slow queries, active sessions, table/index/storage stats, every SSL branch, prepareQuery, and error mapping (ER_ACCESS_DENIED, ECONNREFUSED).

12.3 Run it

bun test tests/integration/db/mysql-provider.test.ts   # just this file (single process — safe)
bun run test:ci                                         # CI publish gate — per-file isolation (tests/run-core.sh)
bun run test:coverage                                   # CI coverage workflow — per-file core + components

12.4 Optional: verifying against a live MySQL

docker run --rm -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=app -p 3306:3306 mysql:8
# then point a connection at localhost:3306 (db=app, user=root) in the Studio UI

13. Usage examples

import { createDatabaseProvider } from '@/lib/db/factory';

const provider = await createDatabaseProvider({
  id: 'my1', name: 'App', type: 'mysql',
  host: 'localhost', port: 3306, database: 'app',
  user: 'root', password: 'secret', createdAt: new Date(),
});

await provider.connect();
const res = await provider.query('SELECT id, email FROM users WHERE active = ?', [1]);
const schema = await provider.getSchema();   // single call (no two-phase split)
await provider.disconnect();

Over the API: POST /api/db/query, POST /api/db/transaction, POST /api/db/cancel, POST /api/db/maintenance (admin), and POST /api/db/schema/list (falls back to getSchema()).

14. Known limitations & future work

  • No server-side query timeout. The pool ignores queryTimeout; a runaway query is not auto-killed (only explicit cancelQuery()/KILL QUERY). Future: derive a per-statement MAX_EXECUTION_TIME (the SELECT execution limit) from queryTimeout. (Note wait_timeout is unrelated — it bounds idle connections, not query execution.)
  • N+1 schema introspection, no two-phase loading. getSchema() issues 1 + 3×tables queries and there is no getSchemaList()/getSchemaRelations(), so large schemas are slower than the Postgres MATERIALIZED-CTE path and the tree cannot stream relationships in.
  • Pool tuning is limited to max (connectionLimit); min/idleTimeout/acquireTimeout are ignored.
  • Index scans is CARDINALITY, an estimate of distinct values — not a real index-usage/scan counter (MySQL has no pg_stat_user_indexes.idx_scan equivalent).
  • Row counts (TABLE_ROWS) are engine estimates for InnoDB, not exact counts.
  • Table bloat is estimated from DATA_FREE (free space), an approximation.
  • getPerformanceMetrics() falls back to static defaults (99/0/0/0) when performance_schema is unavailable, which can misleadingly read as a healthy server.
  • cancelQuery() returns true on KILL QUERY success without confirming the target was actually executing.
  • Cloud SSL auto-detect uses rejectUnauthorized: false — encrypted but not authenticated (MITM-exposed). For verified TLS, set an explicit connection.ssl with mode verify-ca/verify-full and a caCert.

15. References