[lxc-devel] [lxd/master] Database fixes

[stgraber on Github]

A non-text attachment was scrubbed...
Name: not available
Type: text/x-mailbox
Size: 301 bytes
Desc: not available
URL: <http://lists.linuxcontainers.org/pipermail/lxc-devel/attachments/20200114/646d088f/attachment.bin>
-------------- next part --------------
From 7068b83834e6db10c01acd560b276c514aae4c5f Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?St=C3=A9phane=20Graber?= <stgraber at ubuntu.com>
Date: Tue, 14 Jan 2020 10:21:43 -0500
Subject: [PATCH 1/2] lxd/db: Silence normal sql errors
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Signed-off-by: Stéphane Graber <stgraber at ubuntu.com>
---
 lxd/db/db.go          | 10 ----------
 lxd/db/query/retry.go | 15 ++++++++++++---
 2 files changed, 12 insertions(+), 13 deletions(-)

diff --git a/lxd/db/db.go b/lxd/db/db.go
index c0f5d1f5b5..1ca81e60dd 100644
--- a/lxd/db/db.go
+++ b/lxd/db/db.go
@@ -420,16 +420,6 @@ func (c *Cluster) Begin() (*sql.Tx, error) {
 	return begin(c.db)
 }
 
-func isNoMatchError(err error) bool {
-	if err == nil {
-		return false
-	}
-	if err.Error() == "sql: no rows in result set" {
-		return true
-	}
-	return false
-}
-
 func begin(db *sql.DB) (*sql.Tx, error) {
 	for i := 0; i < 1000; i++ {
 		tx, err := db.Begin()
diff --git a/lxd/db/query/retry.go b/lxd/db/query/retry.go
index 240f75e1ac..a67f3dc5ac 100644
--- a/lxd/db/query/retry.go
+++ b/lxd/db/query/retry.go
@@ -1,12 +1,14 @@
 package query
 
 import (
+	"database/sql"
 	"strings"
 	"time"
 
-	"github.com/lxc/lxd/shared/logger"
 	"github.com/mattn/go-sqlite3"
 	"github.com/pkg/errors"
+
+	"github.com/lxc/lxd/shared/logger"
 )
 
 // Retry wraps a function that interacts with the database, and retries it in
@@ -19,8 +21,13 @@ func Retry(f func() error) error {
 	for i := 0; i < 5; i++ {
 		err = f()
 		if err != nil {
-			logger.Debugf("Database error: %#v", err)
+			// No point in re-trying or logging a no-row error.
+			if err == sql.ErrNoRows {
+				break
+			}
 
+			// Process actual errors.
+			logger.Debugf("Database error: %#v", err)
 			if IsRetriableError(err) {
 				logger.Debugf("Retry failed db interaction (%v)", err)
 				time.Sleep(250 * time.Millisecond)
@@ -29,6 +36,7 @@ func Retry(f func() error) error {
 		}
 		break
 	}
+
 	return err
 }
 
@@ -36,10 +44,10 @@ func Retry(f func() error) error {
 // interaction can be safely retried.
 func IsRetriableError(err error) bool {
 	err = errors.Cause(err)
-
 	if err == nil {
 		return false
 	}
+
 	if err == sqlite3.ErrLocked || err == sqlite3.ErrBusy {
 		return true
 	}
@@ -47,6 +55,7 @@ func IsRetriableError(err error) bool {
 	if strings.Contains(err.Error(), "database is locked") {
 		return true
 	}
+
 	if strings.Contains(err.Error(), "bad connection") {
 		return true
 	}

From 364900bfb7cc9bbd1a168715d865c4b834b0f0b7 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?St=C3=A9phane=20Graber?= <stgraber at ubuntu.com>
Date: Tue, 14 Jan 2020 10:50:26 -0500
Subject: [PATCH 2/2] lxd/db: Fix image profile copying logic
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Signed-off-by: Stéphane Graber <stgraber at ubuntu.com>
---
 lxd/db/images.go | 26 +++++++++++++++++++++-----
 1 file changed, 21 insertions(+), 5 deletions(-)

diff --git a/lxd/db/images.go b/lxd/db/images.go
index 4a2f9c00dc..4fd1fbfc26 100644
--- a/lxd/db/images.go
+++ b/lxd/db/images.go
@@ -794,10 +794,26 @@ func (c *Cluster) ImageAliasUpdate(id int, imageID int, desc string) error {
 
 // ImageCopyDefaultProfiles copies default profiles from id to new_id.
 func (c *Cluster) ImageCopyDefaultProfiles(id int, newID int) error {
-	stmt := `INSERT INTO images_profiles (image_id, profile_id) 
-	SELECT ?, profile_id FROM images_profiles WHERE image_id=?`
-	err := exec(c.db, stmt, newID, id)
-	return err
+	err := c.Transaction(func(tx *ClusterTx) error {
+		// Delete all current associations.
+		_, err := tx.tx.Exec("DELETE FROM images_profiles WHERE image_id=?", newID)
+		if err != nil {
+			return err
+		}
+
+		// Copy the entries over.
+		_, err = tx.tx.Exec("INSERT INTO images_profiles (image_id, profile_id) SELECT ?, profile_id FROM images_profiles WHERE image_id=?", newID, id)
+		if err != nil {
+			return err
+		}
+
+		return nil
+	})
+	if err != nil {
+		return err
+	}
+
+	return nil
 }
 
 // ImageLastAccessUpdate updates the last_use_date field of the image with the
@@ -808,7 +824,7 @@ func (c *Cluster) ImageLastAccessUpdate(fingerprint string, date time.Time) erro
 	return err
 }
 
-//ImageLastAccessInit inits the last_use_date field of the image with the given fingerprint.
+// ImageLastAccessInit inits the last_use_date field of the image with the given fingerprint.
 func (c *Cluster) ImageLastAccessInit(fingerprint string) error {
 	stmt := `UPDATE images SET cached=1, last_use_date=strftime("%s") WHERE fingerprint=?`
 	err := exec(c.db, stmt, fingerprint)