incorrect DB scheme "corrupts" existing data when updated to 1.6.0

[peterazmanov]

Yesterday I got auto-updated by Google Play to version v1.6.0+36 (Android v8.0.0). After that my old projects disappeared the same way as described in #40.

I believe the issue is caused by incorrect scheme of projects table:

timecop/lib/data_providers/data/database_provider.dart

Line 40 in a69d277

archived boolean not null default false

After DB upgrade for all old projects archived contains string false but the application expects integer there (0 or 1).

The scheme is incorrect from the application POV but I think that SQL doesn't support such tables either though I don't know SQL that much.

As a workaround I exported my DB then manually edited the scheme in sqlite editor: changed the archived field description in the projects table to the following:

archived boolean not null default 0

Then I cleared data of the application on the phone and imported modified DB file. After small delay I can now use the app again.

[drawtheliner]

@hamaluik , if this can't be fixed quickly, can you please revert to the previous version?

Google Play auto-updated, and I don't know how to rollback (if that's even possible), nor I know how to do what @peterazmanov did

Thanks!

[hamaluik]

@peterazmanov thank you for the detailed report!

This appears to only be an issue with older Android releases; where the system SQLite is fairly outdated, which is why I didn't catch it before release.

I have a hotfix update on the way for testing. Once it is approved for testing, you can opt into the test by visiting https://play.google.com/apps/testing/ca.hamaluik.timecop (the testing version is 37 / 1.6.1)

Before doing this, I suggest you export the entire database file and put it somewhere safe so you can roll back to it if the update fails. I've tested the new schema & migration code locally and it seems good but you never know 🤷

This testing release contains the following changes: hotfix/schema-41

Please let me know as soon as possible if this fixes the issue or not; if so I will release it.

[peterazmanov]

I tested the version without success.

Made backup of my current DB, cleared and reinstalled the app - this restored the DB from the week before and upgraded it to version 3. Then I joined testing via the link you provided and installed v1.6.1 - the projects are restored but the relation between existing timers and projects is gone (checked via export and external editor - all project_id fields contain NULL). After this import of the backup DB works correctly, don't know why...

Maybe the problem is in the fixed _onUpgrade, the SQLite docs state that

PRAGMA foreign_keys = boolean;
...
This pragma is a no-op within a transaction

[hamaluik]

Hmm.. I've uploaded a new version to open testing with the pragmas outside of the transactions. When it is out of review, are you able to test this again for me please? Same procedure—go back to database version 3 then upgrade. Thanks!

[peterazmanov]

Hmm.. I've uploaded a new version to open testing with the pragmas outside of the transactions. When it is out of review, are you able to test this again for me please?

Can you ping when it is out of review? Don't know where to check this.

[hamaluik]

Should be available now. Still 1.6.1 but build 38

[peterazmanov]

Tested again, fix isn't working...

I lost my DB auto-backed up by Android during switch to/from beta version. So i tested via import of DB file with version 3 that I manually backed up.

I debugged with Android Studio and Android emulator (Pixel 4 API 27) and found two problems:

• minor problem: when you open database in isValidDatabaseFile sqflite caches options for the DB, all calls to open database after that ignores onUpgrade and all other options, easily fixed it by providing same options as in the open method
• major problem: foreign_keys aren't turned off during upgrade, see Create, upgrade and downgrade cannot change pragma foreign_keys tekartik/sqflite#297

[laurentS]

I have the same issue (google playstore upgraded it without me noticing, on android 7.1).
I tried to export the data to test the versions above, but my export only includes a handful of old entries (probably ones which had no project attached to them). Interestingly, I still have 20 entries shown in the app (different from the ones exported), and those definitely had a project attached to them. They also seem to be the 20 most recent entries I made prior to the upgrade.

Is there another way to recover all the data? Maybe copying the sqlite file (I don't know where it is)?

Look forward to the app working again, it's great! (and happy to help with testing if I can recover my data)

Edit: incorrect info in this message, see 2 messages down.

[drawtheliner]

@laurentS are you sure you lost time entries?

To me it looks like the only change is that the new version of the app doesn't seem to recognize Project-related data.
It seems that all my previous entries are there, they just appear as if they don't have projects attached to them.

I'm hoping the project data is still there, it just can't be shown by the new version of the app.

[laurentS]

@DanielPFerreira I think I'm having a bad day. There was a filter on the screen somehow. Old entries are still there, luckily. So I'm exactly like you, no project data visible. As mentioned, the export only includes 6 entries, apparently the ones with no project attached to them, which tells me the other ones still have a project value in the db, and are being excluded by the export.
Sorry about the noise :(

[drawtheliner]

@laurentS Good to know! Hopefully the project data is still there, and will be recovered once the issue is fixed.

I've been creating new time entries based on existing ones (from before the bug), so hopefully project info is being copied over as well (even though I don't see it).

[peterazmanov]

Maybe copying the sqlite file (I don't know where it is)?

There is DB-icon at the right-top of the Export screen, when clicked it allows to export/import to/from SQlite DB file. This and desktop SQlite editor (I use this one) allowed me to workaround the problem (as I described in the first message).

I've been creating new time entries based on existing ones (from before the bug), so hopefully project info is being copied over as well (even though I don't see it).

Don't want to disappoint you but I would not count on it )

[drawtheliner]

@peterazmanov thank for the heads-up!

Well, I already have the habit of adding the project name to the beginning of all my time entries. So at least they won't be that lost

And I had done a backup a couple weeks before the update, so at least I'll have that!

[hamaluik]

Another test fix should be coming this morning, just giving it some more testing.

[hamaluik]

Ok, new test version is up for testing on Google play. Let me know if this solves it. I've tested it locally with a bunch of test data and it seems to be working now.

[laurentS]

@hamaluik just grabbed v1.6.1+39 and it's working fine for me (android 7.1), great job! All my data seems to be there.

[hamaluik]

Great! I'll promote that build to release tonight.

Thanks for all the help, everyone!

[peterazmanov]

I can confirm @laurentS' conclusion: v1.6.1+39 fixes the issue (tested on Android 8.0.0 and on emulator + checked the DB file to be sure)

Enhancement suggestion: it's a good idea to signal user that something went wrong and not swallow exceptions then it won't be that surprising to suddenly realize that part of the carefully entered data is missing.

[drawtheliner]

Thanks @hamaluik !