Skip to main content
SQLOZ
Funcționalități Documentație Prețuri Foaie de parcurs Blog Povestea noastră Disponibil în Mac App Store
Chapter 17 DBA & Production Safety

Chapter 17: OzOSC - Zero-Downtime Schema Changes

Info: This chapter describes the OzOSC (Online Schema Change) design and its current support boundary. Native execution and recovery controls are available for supported MySQL and MariaDB tables, subject to live safety checks and explicit approval.

In this chapter:

17.1 Overview

Running a standard ALTER TABLE command on a large, live table creates the following risks:

  1. Table lock: The database engine closes the table to reads and writes.
  2. Service interruption: The application cannot write to the database, so user requests time out (downtime).
  3. High risk: The operation can run for a long time and, if interrupted, can corrupt the database.

OzOSC plan review performs live, read-only connection, metadata, server-identity, and primary-key checks. The guarded execution path creates a verified backup, prepares a plan-owned shadow table, copies rows in chunks, and performs the final swap only after its live checks pass. Incomplete or uncertain proof remains fail-closed.

17.2 Current support status

OzOSC currently supports the native MySQL/MariaDB path for eligible tables with a verified primary key and supported metadata. SQLite and PostgreSQL do not use this native OSC path. Tables with unsupported generated columns, foreign keys, or existing triggers are rejected during review. Explicit destructive-operation approval, a recovery plan, and the database vendor’s actual capabilities must still be verified before execution.

17.3 Planned operation

[ Live Table: orders ] --(Trigger delta copy)--> [ Shadow Table: _orders_sqloz_osc ]
   (Live reads/writes continue)                      (ALTER TABLE applied)
                                                               |
                                                   (Atomic table swap − 1 ms)
                                                               v
                                                 [ Live New Table: orders ]

These are the guarded execution phases. The destructive phases run only after all safety proofs are verified:

  1. Create shadow table: After eligibility and backup proof, an empty plan-owned shadow table identical to the target is created.
  2. Apply schema change to shadow: The ALTER TABLE is applied to the empty shadow table instead of the live one (takes milliseconds).
  3. Delta sync with triggers: Temporary triggers are installed that mirror INSERT, UPDATE and DELETE operations on the live table to the shadow while copying.
  4. Chunked data copy: Existing data is moved to the shadow in chunks while applications continue reading and writing without interruption.
  5. Atomic table swap: After copying and final delta verification, the journal and live schema evidence are checked again before owned tables are atomically swapped. The previous live table is retained until the verified cleanup phase completes.

If the application is interrupted, open Refresh Recovery in the OzOSC panel. The panel exposes only the action proven by the durable inventory:

  • Complete cleanup finishes cleanup after a proven swap.
  • Reconcile swap advances a requested rename after live identity and layout checks. A successful reconciliation can leave the operation in swapped until cleanup is run.
  • Resume copy continues only a checkpoint-backed, pre-swap copy with verified backup, schema, triggers, helpers, and cursor state.
  • Abort preparation requires explicit confirmation and removes only fully proven, plan-owned pre-swap artifacts. It retains the source table and verified backup; it never deletes the source table.

Corrupt, incomplete, or ambiguous durable records remain visible as blocked diagnostics. They require manual investigation and are not silently retried or treated as completed.

Warning: MySQL/MariaDB DDL does not provide general transaction rollback. OzOSC does not promise zero downtime or automatic cleanup. A failed or ambiguous proof blocks the operation and may require manual recovery.

17.4 Live Progress Tracking

During execution, the panel shows:

  • Live copy speed (Rows/sec): How many rows are moved per second.
  • Estimated time remaining (ETA): When the operation will complete.
  • Cancel: Cancellation is propagated to the throwing operation and the panel reports the cleanup or recovery outcome explicitly.

Warning: OzOSC works only on the specified table. Do not interfere with shadow table names or copy parameters; other ALTER commands during the operation can cause conflicts.

This chapter documents the implemented path and its safety boundary. It does not claim that a live end-to-end production run has passed release QA; release acceptance still requires verification in the real SQLOZApp UI.