Failed at database upgrade from version 31 to 32

[HydraGene]

Problem Definition

Login fails with following message:
Failed at database upgrade from version 31 to 32: UNIQUE constraint failed: history_events_copy.event_identifier, history_events_copy.sequence_index

Logs

Which logs do you need, if you need any?

System Description

Operating system: Debian Buster (using the AppImage)
Rotki version: 1.24.0

[LefterisJP]

Hey hello @HydraGene. This is quite weird. I am not sure why this would happen but we can investigate if you can provide us with the database and the password. You can do so via a discord DM: https://discord.com/invite/aGCxHG7
(have DMs open there is a captcha challenge)

What we need

rotki would have taken a copy of the database right before attempting the DB upgrade. So we need this. You can also revert to this and use the previous version until we figure out what's wrong.

Since you are in Linux the database is:

~/.local/share/rotki/data/XXX where XXX is your username.

The current database is rotkehlchen.db. Right before a DB upgrade a backup is taken so it should look like 1653690238_rotkehlchen_db_v31.backup where the initial number is timestamp of the backup and vXX is the version of the DB at the time.

Please send me that file and the password in order to be able to debug this. Logs will not help with such an error.

How to keep using rotki in the meantime

You can copy that backup file and rename rotkehlchen.db, replacing the database with the backup eventually. And then open rotki again with 1.23.4 until we figure out what's wrong.

[LefterisJP]

For the devs: By looking at the code it has to be a special case of data that the function _update_history_entries_from_kraken
does not properly take care of.

rotki/rotkehlchen/db/upgrades/v31_v32.py

Line 134 in 6b85c5c

def _update_history_entries_from_kraken(cursor: 'Cursor') -> None:

I tried to just add a duplicate sequence index and event identifier in the DB upgrade kraken data and the sequence index was changed and upgrade worked. So the only logical explanation is that there must be a case not covered by the upgrade.

[LefterisJP]

Hey @HydraGene I may have found the problem and a solution.

Can you download this nightly? It's the same as v1.24.0 with only a small added fix to see if it can solve your problem at upgrade.

(Links expire 1 week from this post)
Linux: https://we.tl/t-UVuC3yXvFy
OSX: https://we.tl/t-8wZRz6kdrr
Windows: https://we.tl/t-cAqvdDQjqd

Once you have the binary, make sure to have all copies of rotki closed. Then find your rotki data directory, depending on your operating system as per the usage guide.

Under there there should be a directory with your username. For example in linux:
~/.local/share/rotki/data/XXX where XXX is your username.

The current database is rotkehlchen.db. Right before a DB upgrade a backup is taken so it should look like 1653690238_rotkehlchen_db_v31.backup where the initial number is the timestamp (won't be the same -- can be any number) of the backup and vXX is the version of the DB at the time. The number you need should be v31.

Once you have all those:

1. Copy and replace the rotkehlchen.db file with the v31 backup
2. Open rotki again with the nightly I gave you to test and let me know if it works.

[HydraGene]

Hey @LefterisJP
I see the comment for the devs. Looks like you already have a possible solution. Will it already be fixed without sending my DB file?
I got 1.23.4 running again and have access to my database again. Thanks.

[LefterisJP]

Hey @LefterisJP I see the comment for the devs. Looks like you already have a possible solution. Will it already be fixed without sending my DB file? I got 1.23.4 running again and have access to my database again. Thanks.

Check my comment which I must have typed at the same time as you. Perhaps we found a solution. Check it out and let me know!

[HydraGene]

Great! I got in without any problem. Everything seems to work like a charm.

[LefterisJP]

Great! I got in without any problem. Everything seems to work like a charm.

Just to make sure, you can confirm that using the nightly above and upgrading from v1.23.4 worked fine?

If so yay! problem solved :)

Also if so, then you can actually even revert back to using the original 1.24.0. This nightly only had a code change that took effect during the upgrade of your database and using the normal 1.24.0 should be fine.

[HydraGene]

Yes, I can confirm.
I've downloaded the nightly linux appimage, used the 1.23.4 database, ran the nightly, and got to login with no errors.
I am using the official 1.24.0 build now, and indeed it is still working fine.
Thanks a lot!

[LefterisJP]

This is confirmed fixed by the user and will be deployed in 1.24.1. Until then other affected users can follow the steps outlined here: #4381 (comment)