Lxd: error creating snapshot

[conloos]

$ lxc snapshot nextcloud "18.02"
Error: Create instance: Insert volume snapshot: UNIQUE constraint failed: storage_volumes_snapshots.id
$ lxd sql global "SELECT * FROM storage_volumes_snapshots;"
+----+-------------------+------------------+-------------+
| id | storage_volume_id |       name       | description |
+----+-------------------+------------------+-------------+
| 74 | 73                | upgrade_to_19.10 |             |
| 76 | 75                | upgrade_to_19.10 |             |
| 92 | 91                | upgade_to_19.10  |             |
| 94 | 93                | upgrade_to_19.10 |             |
+----+-------------------+------------------+-------------+

[stgraber]

@freeekanayaka

[stgraber]

@conloos what LXD version is that and can you show:

• lxd sql global "SELECT * FROM storage_volumes;"
• lxd sql global "SELECT * FROM storage_volumes_snapshots;"
• lxd sql global "SELECT * FROM storage_volumes_all;"

[fwaggle]

@stgraber I have a machine that has the same issue (LXD 3.22, though it was probably updated from a much older version over the weekend) - I would rather not post full dumps of those tables in public (can email them to you if they're really important), but I hope this helps:

# lxd sql global "SELECT seq FROM sqlite_sequence WHERE name = 'storage_volumes' LIMIT 1;"
+------+
| seq  |
+------+
| 5373 |
+------+

In the full database dump, I can see INSERT INTO storage_volumes_snapshots entries with that ID, all the way up to 5443. Would I be correct in thinking I can probably restore snapshots with something like:

lxd sql global "UPDATE sqlite_sequence SET seq = 5443 WHERE name = 'storage_volumes'"

It sounds good to me but my sqlite-fu is terribly weak. I'll keep a full copy of the global database dump in case you have further questions.

[stgraber]

@freeekanayaka should be able to confirm in the morning

[freeekanayaka]

Would I be correct in thinking I can probably restore snapshots with something like:

lxd sql global "UPDATE sqlite_sequence SET seq = 5443 WHERE name = 'storage_volumes'"

@conloos @fwaggle, yes, please stop LXD and make a backup of your current LXD_DIR/database directory (/var/snap/lxd/current/lxd/database if you are using the snap), then restart LXD and run the query you mentioned.

If that works, there might be some issue in data migration logic between 3.21 and 3.22.

[stgraber]

@fwaggle could you send me (stgraber at ubuntu dot com) a tarball of /var/snap/lxd/common/lxd/database? I'm hoping the .bak directory contains your LXD 3.21 state and so lets us figure out exactly what went wrong with the upgrade to 3.22.

Same for anyone else who recently hit this, the full tarball of /var/snap/lxd/common/lxd/database may be enough for us to do a before/after comparison of the database tables and spot what went wrong.

[conloos]

$ lxd sql global "SELECT * FROM storage_volumes;"
+----+----------------+-----------------+---------+------+-------------+------------+
| id |      name      | storage_pool_id | node_id | type | description | project_id |
+----+----------------+-----------------+---------+------+-------------+------------+
| 72 | keepass        | 3               | 1       | 0    |             | 1          |
| 73 | trac           | 3               | 1       | 0    |             | 1          |
| 75 | jupyter        | 3               | 1       | 0    |             | 1          |
| 90 | nextcloud      | 3               | 1       | 0    |             | 1          |
| 91 | gitlab-ce      | 3               | 1       | 0    |             | 1          |
| 93 | gitlab-runners | 3               | 1       | 0    |             | 1          |
+----+----------------+-----------------+---------+------+-------------+------------+

$ lxd sql global "SELECT * FROM storage_volumes_snapshots;"
+----+-------------------+------------------+-------------+
| id | storage_volume_id |       name       | description |
+----+-------------------+------------------+-------------+
| 74 | 73                | upgrade_to_19.10 |             |
| 76 | 75                | upgrade_to_19.10 |             |
| 92 | 91                | upgade_to_19.10  |             |
| 94 | 93                | upgrade_to_19.10 |             |
+----+-------------------+------------------+-------------+

$ lxd sql global "SELECT * FROM storage_volumes_all;"
+----+---------------------------------+-----------------+---------+------+-------------+------------+
| id |              name               | storage_pool_id | node_id | type | description | project_id |
+----+---------------------------------+-----------------+---------+------+-------------+------------+
| 72 | keepass                         | 3               | 1       | 0    |             | 1          |
| 73 | trac                            | 3               | 1       | 0    |             | 1          |
| 74 | trac/upgrade_to_19.10           | 3               | 1       | 0    |             | 1          |
| 75 | jupyter                         | 3               | 1       | 0    |             | 1          |
| 76 | jupyter/upgrade_to_19.10        | 3               | 1       | 0    |             | 1          |
| 90 | nextcloud                       | 3               | 1       | 0    |             | 1          |
| 91 | gitlab-ce                       | 3               | 1       | 0    |             | 1          |
| 92 | gitlab-ce/upgade_to_19.10       | 3               | 1       | 0    |             | 1          |
| 93 | gitlab-runners                  | 3               | 1       | 0    |             | 1          |
| 94 | gitlab-runners/upgrade_to_19.10 | 3               | 1       | 0    |             | 1          |
+----+---------------------------------+-----------------+---------+------+-------------+------------+

[freeekanayaka]

@conloos please can you also paste the output of:

lxd sql global "SELECT seq FROM sqlite_sequence WHERE name = 'storage_volumes'"

?

[conloos]

$ lxd sql global "SELECT seq FROM sqlite_sequence WHERE name = 'storage_volumes'"
+-----+
| seq |
+-----+
| 93  |
+-----+

[fwaggle]

Hey folks,

Using some notes I left internally after I went to bed, another team member successfully fixed the issue by doing:

lxd sql global "SELECT MAX(id) FROM storage_volumes_all LIMIT 1;"
+---------+
| MAX(id) |
+---------+
| 5443    |
+---------+

then taking that value and stuffing it into the sequence:

lxd sql global "UPDATE sqlite_sequence SET seq = 5443 WHERE name = 'storage_volumes'"

They didn't backup the database (we do have disk level snapshots of it though if that helps), but I did grab global.bak and local.db.bak already which I hope are useful to track this issue down. Note that they also did not restart LXD - is that required? It seems to be functioning correctly, snapshots are taken and new containers are added, etc.

I'll send stgraber what I have, hopefully it's enough to track down the source of the issue, otherwise hopefully @conloos can grab a full backup before fixing it. :D

[freeekanayaka]

Thanks @fwaggle, no LXD restart is required.

Then I think it's a data migration problem, as I suspected, for some reason the sqlite_sequence table was behind and didn't get appropriately bumped. We'll need to roll out a new release to fix that for folks that have it broken.

[stgraber]

@freeekanayaka well, we're getting close to releasing 3.23, so as long as we have a DB fix in there, we can have folks jump to candidate once it's there or else manually update the sequence as a temporary fix.

[alci63]

Same problem here. Updating the sequence worked.