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

tomponline on Github lxc-bot at linuxcontainers.org
Thu Dec 17 14:58:49 UTC 2020


A non-text attachment was scrubbed...
Name: not available
Type: text/x-mailbox
Size: 754 bytes
Desc: not available
URL: <http://lists.linuxcontainers.org/pipermail/lxc-devel/attachments/20201217/a76a2b20/attachment.bin>
-------------- next part --------------
From b1b875018f7954b51057492962b0882136665b68 Mon Sep 17 00:00:00 2001
From: Thomas Parrott <thomas.parrott at 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.parrott at 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.


More information about the lxc-devel mailing list