[lxc-devel] [lxd/master] db/cluster: Bump the value of sqlite_sequence for storage_volumes

[freeekanayaka on Github]

A non-text attachment was scrubbed...
Name: not available
Type: text/x-mailbox
Size: 376 bytes
Desc: not available
URL: <http://lists.linuxcontainers.org/pipermail/lxc-devel/attachments/20200317/0ca2d1ab/attachment.bin>
-------------- next part --------------
From 79ad1f0d396adb1ce4b6dfe8c08b079d0740bb0e Mon Sep 17 00:00:00 2001
From: Free Ekanayaka <free.ekanayaka at canonical.com>
Date: Tue, 17 Mar 2020 10:07:40 +0000
Subject: [PATCH] db/cluster: Bump the value of sqlite_sequence for
 storage_volumes

Fixes #7024

Signed-off-by: Free Ekanayaka <free.ekanayaka at canonical.com>
---
 lxd/db/cluster/schema.go      |  2 +-
 lxd/db/cluster/update.go      | 12 ++++++++++
 lxd/db/cluster/update_test.go | 45 +++++++++++++++++++++++++++++++++++
 3 files changed, 58 insertions(+), 1 deletion(-)

diff --git a/lxd/db/cluster/schema.go b/lxd/db/cluster/schema.go
index 5553b3709f..3c54edee78 100644
--- a/lxd/db/cluster/schema.go
+++ b/lxd/db/cluster/schema.go
@@ -552,5 +552,5 @@ CREATE TABLE storage_volumes_snapshots_config (
     UNIQUE (storage_volume_snapshot_id, key)
 );
 
-INSERT INTO schema (version, updated_at) VALUES (26, strftime("%s"))
+INSERT INTO schema (version, updated_at) VALUES (27, strftime("%s"))
 `
diff --git a/lxd/db/cluster/update.go b/lxd/db/cluster/update.go
index 87d929c20e..05cd8faeed 100644
--- a/lxd/db/cluster/update.go
+++ b/lxd/db/cluster/update.go
@@ -63,6 +63,18 @@ var updates = map[int]schema.Update{
 	24: updateFromV23,
 	25: updateFromV24,
 	26: updateFromV25,
+	27: updateFromV26,
+}
+
+// Bump the sqlite_sequence value for storage volumes, to avoid unique
+// constraint violations when inserting new snapshots.
+func updateFromV26(tx *sql.Tx) error {
+	ids, err := query.SelectIntegers(tx, "SELECT coalesce(max(id), 0) FROM storage_volumes_all")
+	if err != nil {
+		return err
+	}
+	_, err = tx.Exec("UPDATE sqlite_sequence SET seq = ? WHERE name = 'storage_volumes'", ids[0])
+	return err
 }
 
 // Create new storage snapshot tables and migrate data to them.
diff --git a/lxd/db/cluster/update_test.go b/lxd/db/cluster/update_test.go
index 2b35b26186..d4519e7ade 100644
--- a/lxd/db/cluster/update_test.go
+++ b/lxd/db/cluster/update_test.go
@@ -672,3 +672,48 @@ func TestUpdateFromV25(t *testing.T) {
 	assert.Len(t, config, 1)
 	assert.Equal(t, config["k"], "v-old")
 }
+
+func TestUpdateFromV26_WithoutVolumes(t *testing.T) {
+	schema := cluster.Schema()
+	db, err := schema.ExerciseUpdate(27, func(db *sql.DB) {})
+	require.NoError(t, err)
+	defer db.Close()
+}
+
+func TestUpdateFromV26_WithVolumes(t *testing.T) {
+	schema := cluster.Schema()
+	db, err := schema.ExerciseUpdate(27, func(db *sql.DB) {
+		// Insert a node.
+		_, err := db.Exec(
+			"INSERT INTO nodes VALUES (1, 'n1', '', '1.2.3.4:666', 1, 32, ?, 0, 1)",
+			time.Now())
+		require.NoError(t, err)
+
+		// Insert a pool
+		_, err = db.Exec("INSERT INTO storage_pools VALUES (1, 'p1', 'zfs', '', 0)")
+		require.NoError(t, err)
+
+		// Create a volume v1 on pool p1
+		_, err = db.Exec("INSERT INTO storage_volumes VALUES (1, 'v1', 1, 1, 1, '', 1)")
+		require.NoError(t, err)
+
+		// Create a snapshot snap0.
+		_, err = db.Exec("INSERT INTO storage_volumes_snapshots VALUES (2, 1, 'snap0', '')")
+		require.NoError(t, err)
+
+		// Mess up the sqlite_sequence value.
+		_, err = db.Exec("UPDATE sqlite_sequence SET seq = 1 WHERE name = 'storage_volumes'")
+		require.NoError(t, err)
+	})
+	require.NoError(t, err)
+	defer db.Close()
+
+	tx, err := db.Begin()
+	require.NoError(t, err)
+
+	defer tx.Rollback()
+	ids, err := query.SelectIntegers(tx, "SELECT seq FROM sqlite_sequence WHERE name = 'storage_volumes'")
+	require.NoError(t, err)
+
+	assert.Equal(t, ids[0], 2)
+}