[lxc-devel] [lxd/master] DB: Adds updateFromV43 patch that adds unique index to storage_pools_config and networks_config table

[tomponline on Github]

The following pull request was submitted through Github.
It can be accessed and reviewed at: https://github.com/lxc/lxd/pull/8268

This e-mail was sent by the LXC bot, direct replies will not reach the author
unless they happen to be subscribed to this list.

=== Description (from pull-request) ===
Prevents duplicate config rows for the same node and key being inserted.

Uses `ifnull(node_id,-1)` to ensure unique index is still enforced for non-node specific config keys where node_id is NULL, and uses `-1` as the pseudo value in order to avoid issues where the node ID is genuinely `0` which @stgraber advises is potentially possible on DBs from older installations.

Fixes #8260

Signed-off-by: Thomas Parrott <thomas.parr...@canonical.com>

From b1b875018f7954b51057492962b0882136665b68 Mon Sep 17 00:00:00 2001
From: Thomas Parrott <thomas.parr...@canonical.com>
Date: Thu, 17 Dec 2020 14:55:57 +0000
Subject: [PATCH] lxd/db/cluster: Adds updateFromV43 patch that adds unique
 index to storage_pools_config and networks_config table

Prevents duplicate config rows for the same node and key being inserted.

Fixes #8260

Signed-off-by: Thomas Parrott <thomas.parr...@canonical.com>
---
 lxd/db/cluster/schema.go |  4 +++-
 lxd/db/cluster/update.go | 13 +++++++++++++
 2 files changed, 16 insertions(+), 1 deletion(-)

diff --git a/lxd/db/cluster/schema.go b/lxd/db/cluster/schema.go
index 897ea06d22..ca5d4d448e 100644
--- a/lxd/db/cluster/schema.go
+++ b/lxd/db/cluster/schema.go
@@ -276,6 +276,7 @@ CREATE VIEW instances_snapshots_devices_ref (
      JOIN instances ON instances.id=instances_snapshots.instance_id
      JOIN projects ON projects.id=instances.project_id
      JOIN instances_snapshots ON 
instances_snapshots.id=instances_snapshots_devices.instance_snapshot_id;
+CREATE UNIQUE INDEX network_id_node_id_key ON networks_config 
(network_id,ifnull(node_id, -1),key);
 CREATE TABLE "networks" (
     id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
     project_id INTEGER NOT NULL,
@@ -468,6 +469,7 @@ CREATE VIEW projects_used_by_ref (name,
     networks.name,
     projects.name)
     FROM networks JOIN projects ON project_id=projects.id;
+CREATE UNIQUE INDEX storage_pool_id_node_id_key ON storage_pools_config 
(storage_pool_id,ifnull(node_id, -1),key);
 CREATE TABLE storage_pools (
     id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
     name TEXT NOT NULL,
@@ -591,5 +593,5 @@ CREATE TABLE storage_volumes_snapshots_config (
     UNIQUE (storage_volume_snapshot_id, key)
 );
 
-INSERT INTO schema (version, updated_at) VALUES (43, strftime("%s"))
+INSERT INTO schema (version, updated_at) VALUES (44, strftime("%s"))
 `
diff --git a/lxd/db/cluster/update.go b/lxd/db/cluster/update.go
index 614f4845a9..e4f13e077a 100644
--- a/lxd/db/cluster/update.go
+++ b/lxd/db/cluster/update.go
@@ -82,6 +82,19 @@ var updates = map[int]schema.Update{
        41: updateFromV40,
        42: updateFromV41,
        43: updateFromV42,
+       44: updateFromV43,
+}
+
+// updateFromV43 adds a unique index to the storage_pools_config and 
networks_config tables.
+func updateFromV43(tx *sql.Tx) error {
+       _, err := tx.Exec(`CREATE UNIQUE INDEX storage_pool_id_node_id_key ON 
storage_pools_config (storage_pool_id,ifnull(node_id, -1),key);;
+               CREATE UNIQUE INDEX network_id_node_id_key ON networks_config 
(network_id,ifnull(node_id, -1),key);;
+       `)
+       if err != nil {
+               return errors.Wrapf(err, "Failed adding unique index to 
storage_pools_config and networks_config tables")
+       }
+
+       return nil
 }
 
 // updateFromV42 removes any duplicated storage pool config rows that have the 
same value.

_______________________________________________
lxc-devel mailing list
lxc-devel@lists.linuxcontainers.org
http://lists.linuxcontainers.org/listinfo/lxc-devel