Files
QuoteForge/internal/models/sql_migration_safety.go

166 lines
6.1 KiB
Go

package models
import (
"crypto/sha256"
"errors"
"fmt"
"io/fs"
"log/slog"
"os"
"path/filepath"
"strings"
"git.mchus.pro/mchus/quoteforge/internal/appstate"
"git.mchus.pro/mchus/quoteforge/internal/mariadb"
"gorm.io/gorm"
)
type plannedSQLMigration struct {
filename string
statements []string
checks []migrationVerification
}
// RunSQLMigrations requires a DBA maintenance window: stop all other writers.
// Backups are mandatory; each file gets per-table snapshots before any DDL.
func RunSQLMigrations(db *gorm.DB, migrations fs.FS, onApply func(string), backupRoot string) ([]string, error) {
var applied []string
err := db.Connection(func(conn *gorm.DB) (err error) {
fresh := func() *gorm.DB { return conn.Session(&gorm.Session{NewDB: true}) }
var schema, mode, zone, engine string
var foreignKeys int
if err := fresh().Raw("SELECT DATABASE(), @@sql_mode, @@time_zone, @@foreign_key_checks, @@default_storage_engine").Row().Scan(&schema, &mode, &zone, &foreignKeys, &engine); err != nil {
return fmt.Errorf("read migration session: %w", err)
}
if schema == "" || strings.Contains(mode, "NO_BACKSLASH_ESCAPES") || engine != "InnoDB" {
return fmt.Errorf("migration requires a selected InnoDB schema and backslash escape SQL mode")
}
lock := fmt.Sprintf("qfs-migrate-%x", sha256.Sum256([]byte(schema)))
var acquired int
if err := fresh().Raw("SELECT GET_LOCK(?,0)", lock).Scan(&acquired).Error; err != nil || acquired != 1 {
return fmt.Errorf("migration lock unavailable for %s", schema)
}
defer func() {
for _, reset := range []struct {
sql string
value any
}{{"SET time_zone=?", zone}, {"SET sql_mode=?", mode}, {"SET foreign_key_checks=?", foreignKeys}} {
if e := fresh().Exec(reset.sql, reset.value).Error; e != nil {
err = errors.Join(err, fmt.Errorf("reset migration session: %w", e))
}
}
if e := fresh().Raw("SELECT RELEASE_LOCK(?)", lock).Scan(&acquired).Error; e != nil {
err = errors.Join(err, fmt.Errorf("release migration lock: %w", e))
}
}()
plans, err := planSQLMigrations(fresh(), migrations)
if err != nil {
return err
}
if err := appstate.ValidateBackupLocation(backupRoot); err != nil {
return err
}
if err := os.MkdirAll(backupRoot, 0700); err != nil {
return fmt.Errorf("create migration backup root: %w", err)
}
journalName, err := migrationJournalName(conn, schema)
if err != nil {
return err
}
journal := filepath.Join(backupRoot, journalName)
if content, e := os.ReadFile(journal); e == nil {
return fmt.Errorf("interrupted migration requires restore before retry; safety snapshot: %s", strings.TrimSpace(string(content)))
} else if !os.IsNotExist(e) {
return fmt.Errorf("read migration recovery marker: %w", e)
}
if len(plans) == 0 {
return nil
}
if err := requireExclusiveMigrationSession(fresh()); err != nil {
return err
}
if err := fresh().Exec("SET time_zone='+00:00'").Error; err != nil {
return fmt.Errorf("set snapshot time zone: %w", err)
}
full, err := mariadb.CreateSnapshot(fresh(), backupRoot, "session")
if err != nil {
return fmt.Errorf("pre-migration snapshot: %w", err)
}
if err := mariadb.VerifySnapshot(fresh(), full); err != nil {
return fmt.Errorf("pre-migration restore verification: %w", err)
}
marker, err := os.OpenFile(journal, os.O_CREATE|os.O_EXCL|os.O_WRONLY, 0600)
if err != nil {
return fmt.Errorf("create migration recovery marker: %w", err)
}
_, writeErr := fmt.Fprintln(marker, full)
syncErr, closeErr := marker.Sync(), marker.Close()
if e := errors.Join(writeErr, syncErr, closeErr); e != nil {
return fmt.Errorf("persist migration recovery marker: %w", e)
}
slog.Info("verified server migration backup", "schema", schema, "path", full)
var steps []string
for _, plan := range plans {
if err := requireExclusiveMigrationSession(fresh()); err != nil {
return recoverSQLMigration(fresh(), journal, full, steps, plan.filename, err)
}
step, err := mariadb.CreateSnapshot(fresh(), backupRoot, plan.filename)
if err == nil {
err = mariadb.VerifySnapshot(fresh(), step)
}
if err != nil {
return recoverSQLMigration(fresh(), journal, full, steps, plan.filename, err)
}
steps = append(steps, step)
if onApply != nil {
onApply(plan.filename)
}
if err := ensureSQLMigrationsTable(fresh()); err != nil {
return recoverSQLMigration(fresh(), journal, full, steps, plan.filename, err)
}
if err := applySQLMigrationOnSession(fresh(), plan.filename, plan.statements, plan.checks); err != nil {
return recoverSQLMigration(fresh(), journal, full, steps, plan.filename, err)
}
applied = append(applied, plan.filename)
}
if err := os.Remove(journal); err != nil {
return fmt.Errorf("remove completed migration marker: %w", err)
}
return nil
})
if err != nil {
return nil, err
}
return applied, nil
}
func recoverSQLMigration(db *gorm.DB, journal, full string, steps []string, filename string, cause error) error {
slog.Error("server migration failed", "step", filename, "error", cause, "backup", full)
if err := requireExclusiveMigrationSession(db); err != nil {
return errors.Join(cause, fmt.Errorf("recovery refused with active clients; keep maintenance window and restore %s: %w", full, err))
}
var recovery error
for i := len(steps) - 1; i >= 0; i-- {
slog.Warn("restoring migration step", "step", filename, "backup", steps[i])
if err := mariadb.RestoreSnapshot(db, steps[i]); err != nil {
recovery = err
break
}
}
if recovery == nil {
recovery = mariadb.CheckSnapshot(db, full)
}
if recovery != nil {
slog.Warn("restoring full migration session", "backup", full, "error", recovery)
recovery = mariadb.RestoreSnapshot(db, full)
}
if recovery != nil {
return errors.Join(cause, fmt.Errorf("recovery failed; database requires restore from %s: %w", full, recovery))
}
if err := os.Remove(journal); err != nil {
return errors.Join(cause, fmt.Errorf("database restored but recovery marker remains: %w", err))
}
slog.Warn("server migration session restored", "step", filename, "state", "before-session", "backup", full)
return fmt.Errorf("migration %s failed; all session changes restored: %w", filename, cause)
}