Malformed sqlite database

[Mte90]

So it happened, I have on that data from the end of 2019 and I was sad to delete but it is possible to recover the data on it.

sqlite3 /home/[your-user]/.local/share/activitywatch/aw-server/peewee-sqlite.v2.db ".dump" > dump.sql
rm -f /home/[your-user]/.local/share/activitywatch/aw-server/peewee-sqlite.v2.db
sqlite3 -init dump.sql /home/[your-user]/.local/share/activitywatch/aw-server/peewee-sqlite.v2.db
.quit

With this commands I was able to recreate a database and now activity watch starts. In case of this issue you have aw-qt reporting an error, if you press restart server aw-qt crashes...
With the more details button you see the error on peewee about the malformed database, so you need to do those commands and restart aw-qt.

[johan-bjareholt]

But this does not actually recover the old database right, only initializes a new one?

To just get a new one, you can just delete the database while aw-server is not running and a new one will automatically be created next time aw-server starts.

[Mte90]

I was able to recover the majority of the database in this way.
The sqlite db was 570mb, after the dump and creating a new one it was 517mb.

I don't know what I lost but at least I didn't had to reset everything.

[johan-bjareholt]

That's very interesting, thanks for the tip!
This should probably be documented in the official documentation somewhere.

[RundownRhino]

EDIT: This comment technically relates to aw-server-rust, but it's about advice for restoring databases, so.

Note - if you end up restoring your database this way, you may then encounter a problem where the rust server (maybe the python one too, haven't checked) falsely considers it to be v1, tries to upgrade it, and crashes with an error message like this one:

[2023-07-29 02:33:35][INFO][aw_datastore::datastore]: Upgrading database to v2, adding data field to buckets
thread '<unnamed>' panicked at 'Failed to upgrade database when adding data field to buckets: SqliteFailure(Error { code: Unknown, extended_code: 1 }, Some("duplicate column name: data"))', aw-datastore/src/datastore.rs:126:6

This is because the version is determined by the user version pragma, which seems to not be copied by .dump. Manually setting that pragma to the right one (whatever the original database had, but currently the latest is 4) fixes it.