Add full-primary MySQL mutation capture through a delegating database backend

[chubes4]

Problem

The MySQL content backend can make selected posts file-primary through WordPress lifecycle events, but full MDI primary semantics also cover options, users, comments, schemas, and arbitrary plugin tables. Those writes can bypass WordPress content hooks and require successful database-level mutation capture.

This is a later child of #57. It follows the MySQL content backend and does not block it.

Desired Capability

Add a delegating MySQL backend that captures successful mutations at the WordPress database boundary while preserving normal MySQL execution:

WordPress wpdb
      -> MDI delegating backend
      -> normal MySQL execution
      -> normalized successful mutation events
      -> canonical persistence core

The backend owns MySQL result normalization, transactions, schema introspection, affected-resource discovery, and mutation capture. The canonical core remains SQL- and database-neutral.

Required Design Evidence

Before implementation, verify the supported deployment boundary for stock WordPress, custom db.php, and managed hosts. The backend must define:

• which direct $wpdb writes are captured;
• transaction and rollback timing;
• read-after-write behavior;
• schema DDL handling;
• auto-increment identity reconstruction;
• mixed captured/uncaptured deployment behavior;
• operational recovery when canonical persistence fails after MySQL succeeds.

Acceptance Criteria

1. Direct plugin $wpdb DML and DDL mutations produce normalized canonical persistence events after successful execution.
2. Failed and rolled-back SQL produces no canonical state change.
3. Options, users, comments, taxonomy state, and representative plugin schemas/tables reconstruct from canonical files into an empty supported MySQL database.
4. Existing callers observe compatible wpdb return values, errors, insert IDs, row counts, timing, and query logging.
5. Mixed-version deployment behavior is documented and tested.
6. Doctor distinguishes full-primary MySQL from content-primary MySQL using capabilities, not mode-name assumptions.
7. Hosts that cannot install the required database boundary receive an explicit unsupported result and retain content-primary operation.
8. SQLite compatibility remains unchanged.

AI Assistance

OpenAI GPT-5.6 Sol via OpenCode was used to inspect MDI's existing full-state persistence guarantees and draft this issue. Chris Huber directed the architecture and remains responsible for the proposed capability boundary.

[chubes4]

Design Investigation Result

The required database-boundary investigation is complete. The first bounded implementation slice is #195.

Deployment Boundary

WordPress loads wp-includes/class-wpdb.php, includes exactly one wp-content/db.php, and only creates stock wpdb when that drop-in did not set $wpdb. This happens before MU plugins and normal plugins. Core also documents that early queries cannot be captured by the later query filter.

Evidence:

• Core require_wp_db()
• Core wpdb::query() and early-query filter limitation
• Official wpdb replacement contract

Decision: full-primary MySQL requires an MDI-owned or explicitly composed db.php. A normal plugin wrapper is too late and would miss bootstrap queries. WordPress has no native drop-in chain.

Supported:

• stock WordPress where MDI can install and own wp-content/db.php;
• environments that explicitly compose MDI at their existing database drop-in boundary;
• queries executed through the global wpdb object, including insert(), update(), delete(), and replace() helpers;
• multisite through the same mutable wpdb instance and dynamic prefix APIs.

Explicitly unsupported:

• replacing an unrelated db.php without an explicit composition adapter;
• hosts that prohibit custom database drop-ins;
• direct $wpdb->dbh / raw mysqli calls, separately instantiated database connections, server-side jobs, and external writers;
• claiming full-primary capability when any of those uncaptured paths are required.

Unsupported environments retain mysql-content; doctor must report the missing full-primary boundary by capability rather than silently degrading.

Compatibility Surface

The implementation must remain a real wpdb subclass and delegate original execution to parent::query(). It must preserve:

• return values (true, affected-row count, selected-row count, or false);
• last_error, rows_affected, insert_id, last_query, last_result, num_rows, and num_queries;
• SAVEQUERIES timing/caller records and reconnect/retry behavior;
• escaping/preparation, table properties, base/blog prefixes, set_blog_id(), and normal read-after-write behavior.

The wrapper observes only after parent::query() proves success. Internal bookkeeping must not replace the caller-visible last query or insert ID.

Mutation Scope

Capture DML/DDL routed through global wpdb from bootstrap onward. The SQL classifier currently embedded in the SQLite runtime should become backend-neutral; affected-resource discovery and SHOW CREATE TABLE remain backend-operation responsibilities. MDI already has the correct semantic target: WP_Markdown_Backend_Operations::mutations_for_query() feeds normalized mutations into database-neutral canonical persistence.

Relevant merged seams:

• backend capability declarations
• semantic backend operations
• normalized canonical mutation intake

Transaction and Recovery Contract

DML:

• Track START TRANSACTION / BEGIN, SET autocommit, savepoints, COMMIT, and ROLLBACK on the same connection.
• Store normalized pending mutations in an InnoDB outbox inside the caller transaction. Rollback/savepoint rollback therefore removes matching pending work; commit publishes database state and outbox atomically.
• For autocommit DML, the delegating boundary must own a short transaction containing the original statement plus outbox write if strict crash-gap elimination is enabled. Compatibility tests must prove return values, locking behavior, query timing, and insert identity remain acceptable.
• Drain committed outbox records idempotently into canonical persistence. A canonical failure leaves the record pending; explicit flush throws, shutdown logs, and startup recovery retries. Mark complete only after canonical publication.

DDL:

• MySQL DDL causes implicit commits and cannot share the DML outbox transaction (MySQL 8.4 implicit commit contract).
• After successful DDL, immediately introspect the resulting schema and queue canonical schema persistence.
• Startup/doctor performs a schema audit to repair the unavoidable crash window between committed DDL and queue publication. Atomic InnoDB DDL protects the database operation itself but does not make filesystem publication transactional.

Read-after-write remains normal MySQL behavior. Canonical visibility is guaranteed at explicit flush and recovered asynchronously after ordinary shutdown failures.

Auto-Increment and Sensitive State

Capture the original insert ID before internal bookkeeping and use client-assigned outbox IDs, not auto-increment outbox keys. Restore all public wpdb state after internal work. No raw SQL text needs to be stored in the outbox; persist normalized table, operation, resource scope, transaction identity, and ordering metadata.

Mixed Versions

The drop-in needs a small bootstrap ABI. Activation order is plugin code first, compatible drop-in second, mysql-full configuration last. A stale/new mismatch must instantiate normal MySQL wpdb, set an explicit fallback diagnostic, and keep content-primary available; it must never fall through into the SQLite branch or claim table capture. Mixed plugin/drop-in versions require executable bootstrap fixtures.

Implementation Sequence

1. Add capability-gated MySQL wpdb mutation boundary #195: capability-gated delegating wpdb, backend-neutral SQL classification, exact compatibility matrix, no canonical side effects.
2. MySQL backend operations plus transactional DML outbox and idempotent drain/recovery.
3. DDL schema capture/audit and empty-MySQL reconstruction for core and representative plugin tables.
4. Mixed-version/unsupported-host doctor behavior and the full SQLite + MariaDB compatibility gate.

Acceptance Evidence Required

• native MariaDB, not a mock protocol, for return/error/transaction/DDL behavior;
• direct plugin $wpdb DML and DDL;
• failed SQL and explicit rollback produce no canonical mutation;
• crash/failure after MySQL success leaves recoverable pending work;
• options, users, comments, taxonomy, plugin schema/table cold reconstruction;
• stock and delegating wpdb observable-state comparison;
• mixed drop-in/plugin versions and unsupported-host fallback;
• unchanged released/trunk SQLite compatibility suite.

AI Assistance

OpenAI GPT-5.6 Sol via OpenCode inspected WordPress core, official MySQL/WordPress documentation, and MDI source/tests; it mapped the supported boundary, transaction/recovery model, compatibility surface, and implementation sequence. Chris Huber directed the investigation and remains responsible for the design.