[lxc-devel] [lxd/master] Sanitize references to containers table

[freeekanayaka on Github]

A non-text attachment was scrubbed...
Name: not available
Type: text/x-mailbox
Size: 377 bytes
Desc: not available
URL: <http://lists.linuxcontainers.org/pipermail/lxc-devel/attachments/20181016/c81c23c0/attachment.bin>
-------------- next part --------------
From 85b90f788fc383e8430ab4dc5c7dad41bc49e3eb Mon Sep 17 00:00:00 2001
From: Free Ekanayaka <free.ekanayaka at canonical.com>
Date: Tue, 16 Oct 2018 16:17:55 +0200
Subject: [PATCH] Sanitize references to containers table

Signed-off-by: Free Ekanayaka <free.ekanayaka at canonical.com>
---
 lxd/db/cluster/update.go | 41 +++++++++++++++++++++++++++++++++++++++-
 1 file changed, 40 insertions(+), 1 deletion(-)

diff --git a/lxd/db/cluster/update.go b/lxd/db/cluster/update.go
index 19c4e4efb3..036e4b4a84 100644
--- a/lxd/db/cluster/update.go
+++ b/lxd/db/cluster/update.go
@@ -73,6 +73,45 @@ CREATE VIEW profiles_used_by_ref (project,
 }
 
 func updateFromV11(tx *sql.Tx) error {
+	// There was at least a case of dangling references to rows in the
+	// containers table that don't exist anymore. So sanitize them before
+	// we move forward. See #5176.
+	stmts := `
+DELETE FROM containers_config WHERE container_id IN
+  (SELECT containers_config.container_id
+   FROM containers_config
+   LEFT OUTER JOIN containers ON containers.id = containers_config.container_id
+    WHERE containers.id IS NULL);
+
+DELETE FROM containers_backups WHERE container_id IN
+  (SELECT containers_backups.container_id
+   FROM containers_backups
+   LEFT OUTER JOIN containers ON containers.id = containers_backups.container_id
+    WHERE containers.id IS NULL);
+
+DELETE FROM containers_devices WHERE container_id IN
+  (SELECT containers_devices.container_id
+   FROM containers_devices
+   LEFT OUTER JOIN containers ON containers.id = containers_devices.container_id
+    WHERE containers.id IS NULL);
+
+DELETE FROM containers_devices_config WHERE container_device_id IN
+  (SELECT containers_devices_config.container_device_id
+   FROM containers_devices_config
+   LEFT OUTER JOIN containers_devices ON containers_devices.id = containers_devices_config.container_device_id
+    WHERE containers_devices.id IS NULL);
+
+DELETE FROM containers_profiles WHERE container_id IN
+  (SELECT containers_profiles.container_id
+   FROM containers_profiles
+   LEFT OUTER JOIN containers ON containers.id = containers_profiles.container_id
+    WHERE containers.id IS NULL);
+`
+	_, err := tx.Exec(stmts)
+	if err != nil {
+		return errors.Wrap(err, "Remove dangling references to containers")
+	}
+
 	// Before doing anything save the counts of all tables, so we can later
 	// check that we don't accidentally delete or add anything.
 	counts1, err := query.CountAll(tx)
@@ -97,7 +136,7 @@ func updateFromV11(tx *sql.Tx) error {
 	ctx, cancel := context.WithTimeout(context.Background(), time.Minute)
 	defer cancel()
 
-	stmts := fmt.Sprintf(`
+	stmts = fmt.Sprintf(`
 CREATE TABLE projects (
     id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
     name TEXT NOT NULL,