Weird support request: restoring lost data in the Nextcloud DB from the Deck Android App

[shtrom]

tl;dr

How can I change the local sqlite3 data to mark all cards as unsynced, so the Android app resyncs everything to the Nextcloud app?

Context

For some reason I'm still to diagnose, my Nextcloud server DB has a corrupted oc_deck_cards that I cannot restore (and, of course, my backups are 🤷🏼 because I ingored the issue for too long)... I have, however, a copy of the data, up-to-date, in my Deck Android app!

I am now looking at a way to resync the data from the Android app back into the Nextcloud server. What I want to do is

1. Create a new empty oc_deck_cards on the server side,
2. Mark all the cards in the Android app as unsynced
3. ...
4. Profit? (i.e., the Android app syncs back all the cards to the Nextcloud app).

So, question (while I'm reading the app code, and trying to answer it myself): Could this actually work? And if so, how could I do step 2? I can reasonably adb shell into my phone, and change the SQL DB to do what's needed to mark the cards as unsynced... but I don't know what that change should be.

[desperateCoder]

Well, that's a very special request you have here. But i think we got your back.

It should work if you remove the (remote- not local-)IDs in the db and set the sync-state to locally edited. Locally edited would cause it to sync and the missing id will treat it as a new entry and insert instead of update.

I'll update here in my lunch break in like 2.5 hours and tell you the exact steps.

The only prerequisite is that your server app is working again properly, you can work on that in the meantime if not already done.

[desperateCoder]

Oh and a really important thing: DON'T Open the app in the meantime! If the sync is triggered and the server won't say it knows the synced data (which will be the case if your server db is blank), the app will delete the data as well!

Once the remote IDs are resetted, your data will be safe

[shtrom]

I'll update here in my lunch break in like 2.5 hours and tell you the exact steps.

Thanks mate. Your answer is already useful. I'll have a look at the DB and see if I can make sense of it.

DON'T Open the app in the meantime

Yup, I expected as much. At least in this case, I have a backup... Maybe I'll make a backup now...

[desperateCoder]

Maybe I'll make a backup now...

Excellent idea! You should be able to make a copy of your DB via ADB. In case we mess something up, we maybe can still restore everything from the last-resort copy.

I already have a plan, if the data is still in your apps DB, I'm quite convinced we can force it to work again. I'll write here later.

[desperateCoder]

OK, here you go:

update AccessControl set id=NULL, etag=NULL, state=2;
update Activity set id=NULL, etag=NULL, state=2;
update Attachment set id=NULL, etag=NULL, state=2;
update Board set id=NULL, etag=NULL, state=2;
update Card set id=NULL, etag=NULL, state=2;
update DeckComment set id=NULL, etag=NULL, state=2;
update Label set id=NULL, etag=NULL, state=2;
update OcsProject set id=NULL, etag=NULL, state=2;
update OcsProjectResource set id=NULL, etag=NULL, state=2;
update Stack set id=NULL, etag=NULL, state=2;

update JoinBoardWithLabel set state=2;
update JoinBoardWithPermission set state=2;
update JoinBoardWithUser set state=2;
update JoinCardWithLabel set state=2;
update JoinCardWithProject set state=2;
update JoinCardWithUser set state=2;

that's a quite naive way i'd try first. I checked the tables, threw out anything non-related, checked for relevant fields and the above is the outcome.

If i didn't mess up my little copy-paste session, this should trick the app to sync all up again.

There are some related tables not mentioned above and i can't gaurantee they won't mess up something, but i think it should work. It kind of depends on what the server does with it. AND if my logic is correct :sweat_smile:

In any case: make sure to have your DB backed up, like... 5 times... :laughing:

If anything fails or something, feel free to to post it here for further support.

[shtrom]

Cheers, I'll try this in a moment.

As I have only lost the oc_deck_cards table, should/could I limit myself to the Card and JoinCard* tables? -- Olivier Mehani @.***> Sent from my mobile, please excuse my brevity.

[desperateCoder]

As I have only lost the oc_deck_cards table,

oh, i missed that. totally correct, to get your cards back you'll need the card table and all depending tables:

update Card set id=NULL, etag=NULL, state=2;
update DeckComment set id=NULL, etag=NULL, state=2; -- to upload all comments if you have any
update Attachment set id=NULL, etag=NULL, state=2; -- to (re-)upload and connect all attached files

update JoinCardWithLabel set state=2;
update JoinCardWithProject set state=2;
update JoinCardWithUser set state=2;

[shtrom]

I don't seem to have a state column in any of the tables. Did you mean status?

[desperateCoder]

I don't seem to have a state column in any of the tables. Did you mean status?

Yep, you're right, I'm dumb. Best dev ever. 😓

[shtrom]

Well, you know what? I think that worked.

After running all those (s/state/status) in the sqlite DB on the phone, and

create table oc_deck_cards_new like oc_deck_cards;
rename table oc_deck_cards to oc_deck_cards_bad, oc_deck_cards_new to oc_deck_cards;

on the server side, I restarted the app (which I had force-stopped before).

It started up and every task was marked as to sync 🔄, but that then disappeared, and

select count(1) from oc_deck_cards;
+----------+
| count(1) |
+----------+
| 85       |
+----------+
1 row in set
Time: 0.367s

I'm spot-checking now, but this is all looking pretty good!

[desperateCoder]

That's at least some progress... You can activate the logging in the app in the settings, maybe that helps. You can post the output here if needed. But be aware that it contains your cards data, so be careful what you post. It's nearly at the bottom of the settings.

Then you can trigger the sync and have a little bug button on the header of the sidebar. After the sync, hit the bug to retrieve the logs

[shtrom]

Nah, I think it's done. It's all there, synced back to the server 😌

[desperateCoder]

Nah, I think it's done. It's all there, synced back to the server 😌

Aw man! Glad it worked! ❤️

Btw.: You nicely thought along, covering my mistakes. Wish all our users would be like that.

That was unconventional and fun! Finally the offline-capabilities I invested so much time in visibly paid off.

If you think we're done here, feel free to close this issue and have a nice day!

[shtrom]

Yeah, to me offline caps is a must-have for this sort of apps. Otherwise there's no real point in a native app when a web view can do.

Hey, I'd like to send a token of gratitude. Should I just use the Sponsor link for this repo? I see the repo is not in your account. I am grateful for the original code for sure, but even more so for your help right then, so I want to make sure the love is shared. What's the best way to do this?

[desperateCoder]

Aw man, thats really not necessary, but I won't resist either 😊

It's mostly a two-man-project, driven by me as the DB/server-communication guy and my buddy @stefan-niedermann as the UI-sorcerer. It started as two bros having a fun project and grew to... THIS.

Its Stefan's repo and he is also managing the incomes, so @stefan-niedermann: whats the best way without paying unncessary commissions?

@shtrom your love is highly appreciated, thank you very much, mate! I also had fun solving this tho 😉

[stefan-niedermann]

Hey @shtrom

Glad to hear that you successfully managed to restore your server state 🙂

The easiest way to support us is PayPal and LiberaPay, though both do have commissions.

For us the easiest way would be a SEPA transaction, if you want to do this, let me know at info@niedermann.it (I will answer the target bank account then).

I'll forward all of the received money to @desperateCoder then.

Kind regards

[desperateCoder]

I'll forward all of the received money to @desperateCoder then.

Nah, gonna be shared as usual. And if it's a odd number, we'll saw that last penny in two.

[shtrom]

I'll just make it an odd number just for the photo.

[desperateCoder]

Chellange accepted. But be aware that we have Euros here, i don't have an Australian penny here to saw apart 😅

But you'll get your photo!

[desperateCoder]

Cant believe you actually made me do this...

As promised:

[stefan-niedermann]

brot kann ja jeder brechen 🤷‍♂️

[shtrom]

🤣 cheers guys!

[shtrom]

brot kann ja jeder brechen man_shrugging

und geld auch?

[desperateCoder]

Only if you got a money-breaking machine like i bought just for this occasion 💪

JK, smart people would just use a metal saw and a penny coming from the other end of the world, where everything is trying to kill you

Thanks again mate!