MySQL/MariaDB adapter — deferred follow-ups

[ihistand]

The MySQL/MariaDB adapter shipped in 1.5.0 (PR #34) with a deliberately minimal surface: portable MySQL DDL (table CTAS, CREATE OR REPLACE VIEW, incremental ON DUPLICATE KEY UPDATE upsert), information_schema introspection, and EXPLAIN-based evaluate, validated against MySQL 8 + MariaDB 11.

These four items were scoped out of the MVP and tracked here as follow-ups (each can be split into its own issue when picked up):

• mysql: {} config block — first-class engine/charset, secondary indexes, table options, and partitioning, parallel to postgres: {}. Today these require raw MySQL DDL in operations/post_operations. This is the largest item.
• Materialized views — currently type: "view", materialized: true throws on MySQL (MySQL has no native matviews). Decide on an emulation strategy (table + scheduled refresh) or keep the explicit error.
• COMMENT metadata — description: / columns: compile but setMetadata is a no-op on MySQL; wire them to COMMENT ON/ALTER TABLE ... COMMENT and read them back via information_schema (mirrors the Postgres path).
• introspect for MySQL sources — let MySQL be a named cross-warehouse source. Note the FDW bridge is Postgres-only today, so this needs a separate mechanism.

Context: PR #34, the design spec (sqlanvil-private/planning/specs/2026-06-17-mysql-mariadb-adapter-design.md), and the V2 roadmap umbrella #16. Authoring guidance documents all four as "deferred" in AGENTS.md / the sqlanvil-engineering-fundamentals skill.

[ihistand]

Status reconciliation (2026-06-24):

• ✅ mysql: {} config block — shipped in 1.6.0 (configs.proto MysqlOptions + mysql_execution_sql.ts).
• ✅ Materialized views — shipped in 1.6.0 (emulated as a refreshed table snapshot, mysql_execution_sql.ts).
• ✅ COMMENT metadata — shipped in 1.6.0 (mysql.ts setMetadata → ALTER TABLE … COMMENT / MODIFY COLUMN … COMMENT, read back via information_schema).
• ◑ introspect for MySQL sources — dev-time half done (commit 28c41e5a, on main, unreleased): sqlanvil introspect now supports platform: mysql connections (readMysqlSchema + mapMysqlType) to scaffold a declaration from a MySQL table's schema/types/comments. Still deferred: the runtime cross-warehouse MySQL source (a non-MySQL project actually reading MySQL data) — there's no MySQL FDW, so it needs a data-movement bridge, which overlaps the planned DuckDB bridge work. Tracking that remainder here (or split into its own issue when picked up).

[ihistand]

MySQL/MariaDB as a cross-warehouse source shipped in c1f14a7 (runner-extract mode — the fourth checklist item's cross-warehouse half):

• platform: mysql connections are runner-extract only (mode: fdw = compile error); the CLI reads database.table via mysql2 (1M row / 512MB caps, row cap in the SQL) and materializes <conn>_ext.<name> in the Postgres/Supabase warehouse via the shared extract_load loader.
• sqlanvil introspect <conn> <db>.<table> scaffolds declarations and now maps MySQL types → Postgres types (raw datetime/double would fail the extract's CREATE TABLE).
• Proven e2e vs MySQL 8 + MariaDB 11 (introspect → extract → downstream aggregate, re-runs included).

Also fixed two latent bugs found along the way: extract re-runs over an existing plain table failed on drop foreign table if exists (relkind-aware drops now, affected BigQuery extracts too), and an explicit schema: on a connection'd declaration was silently dropped (Declaration ctor renames it to dataset).

Still open from this item: a mysql warehouse reading connections, and the remaining three checklist items.

[ihistand]

All four items are now shipped — closing.

• mysql: {} config block — engine/charset/collation (1.6.0), partitioning (1.11.0), and as of 1.19.0 the final pieces: FULLTEXT/SPATIAL index types, per-column prefix lengths ("body(50)"), and rowFormat. Bonus fix: the block now compiles through on materialized views (ViewConfig.mysql was missing — the documented example was silently ignored).
• Materialized views — emulated as a refreshed table snapshot (1.6.0).
• COMMENT metadata — ALTER TABLE … COMMENT / MODIFY COLUMN … COMMENT + information_schema read-back (1.6.0).
• MySQL as a cross-warehouse source — introspect scaffolding (1.11.0) + runtime runner-extract (1.18.0).

1.19.0 changes integration-proven against MySQL 8 and MariaDB 11 (information_schema: index_type=FULLTEXT, sub_part=50, row_format=DYNAMIC). Docs: sqlanvil.com/docs/guides/mysql + whats-new. Still intentionally out of the block: exotic table options (AUTO_INCREMENT seeds etc.) — raw DDL in operations remains the escape hatch. A mysql warehouse reading cross-warehouse connections stays out of scope (single-write-warehouse model).