create_migrations_table() committed right after BEGIN IMMEDIATE, and executescript() commits before running, so gunicorn workers applied migrations concurrently; the v2.8.0 deploy failed to boot once with 'duplicate column name'. Run migration statements with conn.execute inside the transaction and don't commit when creating the tracking table. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
87 lines
2.9 KiB
Python
87 lines
2.9 KiB
Python
"""
|
|
Migrations under concurrent gunicorn workers (v2.8.1)
|
|
|
|
The v2.8.0 deploy failed to boot: one worker applied migration 014 while
|
|
another applied it again ("duplicate column name"), because
|
|
conn.executescript() committed and released the migration lock early.
|
|
"""
|
|
|
|
import multiprocessing
|
|
import sqlite3
|
|
from pathlib import Path
|
|
|
|
from starpunk.database.schema import INITIAL_SCHEMA_SQL
|
|
from starpunk.migrations import execute_in_transaction, run_migrations, split_sql_statements
|
|
|
|
MIGRATIONS = Path(__file__).parent.parent / "migrations"
|
|
|
|
|
|
def test_script_runs_inside_the_transaction(tmp_path):
|
|
conn = sqlite3.connect(tmp_path / "t.db")
|
|
conn.execute("CREATE TABLE t (a INTEGER)")
|
|
conn.commit()
|
|
|
|
conn.execute("BEGIN IMMEDIATE")
|
|
execute_in_transaction(conn, "ALTER TABLE t ADD COLUMN b TEXT;\nALTER TABLE t ADD COLUMN c TEXT;")
|
|
conn.rollback()
|
|
|
|
assert [row[1] for row in conn.execute("PRAGMA table_info(t)")] == ["a"]
|
|
|
|
|
|
def test_split_keeps_trigger_bodies_together():
|
|
script = """
|
|
-- comment; with a semicolon
|
|
CREATE TABLE t (a TEXT DEFAULT 'x;y');
|
|
CREATE TRIGGER tr AFTER DELETE ON t BEGIN
|
|
DELETE FROM t WHERE a = old.a;
|
|
SELECT 1;
|
|
END;
|
|
"""
|
|
statements = split_sql_statements(script)
|
|
assert len(statements) == 2
|
|
assert statements[1].startswith("CREATE TRIGGER") and statements[1].endswith("END;")
|
|
|
|
|
|
def _migrate(db_path, barrier, errors):
|
|
try:
|
|
barrier.wait()
|
|
run_migrations(db_path)
|
|
except Exception as e: # report to the parent
|
|
errors.put(repr(e))
|
|
|
|
|
|
import pytest
|
|
|
|
|
|
@pytest.mark.parametrize("trial", range(5)) # the race hit ~1 in 5 runs
|
|
def test_concurrent_workers_apply_each_migration_once(tmp_path, trial):
|
|
# A database at migration 013, like production before v2.8.0
|
|
db_path = tmp_path / "starpunk.db"
|
|
conn = sqlite3.connect(db_path)
|
|
conn.executescript(INITIAL_SCHEMA_SQL) # as init_db does
|
|
conn.close()
|
|
run_migrations(db_path)
|
|
conn = sqlite3.connect(db_path)
|
|
conn.execute("DELETE FROM schema_migrations WHERE migration_name = '014_webmention_source_details.sql'")
|
|
conn.commit()
|
|
columns = [row[1] for row in conn.execute("PRAGMA table_info(webmentions)")]
|
|
if "source_post_url" in columns:
|
|
conn.execute("ALTER TABLE webmentions DROP COLUMN source_post_url")
|
|
conn.execute("ALTER TABLE webmentions DROP COLUMN source_published")
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
ctx = multiprocessing.get_context("fork")
|
|
barrier, errors = ctx.Barrier(4), ctx.Queue()
|
|
workers = [ctx.Process(target=_migrate, args=(db_path, barrier, errors)) for _ in range(4)]
|
|
for w in workers:
|
|
w.start()
|
|
for w in workers:
|
|
w.join(timeout=60)
|
|
|
|
assert errors.empty(), errors.get()
|
|
conn = sqlite3.connect(db_path)
|
|
assert conn.execute(
|
|
"SELECT COUNT(*) FROM schema_migrations WHERE migration_name = '014_webmention_source_details.sql'"
|
|
).fetchone()[0] == 1
|