search

ActivityWatch / aw-server

ActivityWatch server for storage of all your Quantified Self data.
Mozilla Public License 2.0
95 stars 55 forks source link

Malformed sqlite database #89

Open Mte90 opened 2 years ago

Mte90 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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

RundownRhino commented 1 year ago

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.