Add unique constraint on schema_version.lock schema. Use conflict clause in sql.

2 files changed, 5 insertions, 4 deletions

diff --git a/synapse/storage/__init__.py b/synapse/storage/__init__.py

index d637e8c4f7..c09228c37f 100644
--- a/synapse/storage/__init__.py
+++ b/synapse/storage/__init__.py
@@ -645,7 +645,7 @@ def _setup_new_database(cur):
     cur.executescript(sql_script)
 
     cur.execute(
-        "INSERT INTO schema_version (version, upgraded)"
+        "INSERT OR REPLACE INTO schema_version (version, upgraded)"
         " VALUES (?,?)",
         (max_current_ver, False)
     )
@@ -748,7 +748,7 @@ def _upgrade_existing_database(cur, current_version, delta_files, upgraded):
             )
 
             cur.execute(
-                "INSERT INTO schema_version (version, upgraded)"
+                "INSERT OR REPLACE INTO schema_version (version, upgraded)"
                 " VALUES (?,?)",
                 (v, True)
             )
@@ -803,7 +803,7 @@ def prepare_sqlite3_database(db_conn):
 
             if row and row[0]:
                 db_conn.execute(
-                    "INSERT INTO schema_version (version, upgraded)"
+                    "INSERT OR REPLACE INTO schema_version (version, upgraded)"
                     " VALUES (?,?)",
                     (row[0], False)
                 )
diff --git a/synapse/storage/schema/schema_version.sql b/synapse/storage/schema/schema_version.sql

index 83a8c7b7ce..20b1481ba5 100644
--- a/synapse/storage/schema/schema_version.sql
+++ b/synapse/storage/schema/schema_version.sql
@@ -17,7 +17,8 @@ CREATE TABLE IF NOT EXISTS schema_version(
     Lock char(1) NOT NULL DEFAULT 'X',  -- Makes sure this table only has one row.
     version INTEGER NOT NULL,
     upgraded BOOL NOT NULL,  -- Whether we reached this version from an upgrade or an initial schema.
-    CONSTRAINT schema_version_lock CHECK (Lock='X') ON CONFLICT REPLACE
+    CONSTRAINT schema_version_lock_x CHECK (Lock='X')
+    CONSTRAINT schema_version_lock_uniq UNIQUE (Lock)
 );
 
 CREATE TABLE IF NOT EXISTS schema_deltas(