[prev in list] [next in list] [prev in thread] [next in thread] 

List:       lxc-devel
Subject:    [lxc-devel] [lxd/master] DB: Adds updateFromV43 patch that adds unique index to storage_pools_config
From:       tomponline on Github <lxc-bot () linuxcontainers ! org>
Date:       2020-12-17 14:58:49
Message-ID: 5fdb7229.1c69fb81.840ec.0ba9SMTPIN_ADDED_MISSING () mx ! google ! com
[Download RAW message or body]

[Attachment #2 (text/x-mailbox)]

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.parrott@canonical.com>


[Attachment #3 (text/plain)]

From b1b875018f7954b51057492962b0882136665b68 Mon Sep 17 00:00:00 2001
From: Thomas Parrott <thomas.parrott@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@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.


[Attachment #4 (text/plain)]

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


[prev in list] [next in list] [prev in thread] [next in thread]