Aura ONE What is the best way to restore Kobo annotations from a backup?

[pl8o]

My Kobo (Aura ONE, 4.15.12920) has thrown out its annotations again. This time, it has thrown out *all* the annotations for *one* sideloaded .epub book.

I have a full SD card backup from a few days before, but I haven’t managed to restore successfully.

---

About a week ago, I noticed that my Kobo had thrown out about a days worth of annotations in this same sideloaded book. In addition, when opening the book, progress was not being saved properly.

Around this point, I made a backup (from now on referred to as "second-to-last backup"). Unfortunately, I don’t recall for sure whether it was made before or after I first noticed the issue.

But it was only a day’s worth of annotations, so I didn’t worry too much.

A few days ago, though, I noticed at around the same time that:

2. *All* my annotations were gone from that book.
1. When I put my device to sleep, it showed the cover for the wrong book (it was showing the cover for a different sideloaded epub).

Around *this* point, I made a backup (from now on referred to as "latest backup"). Unfortunately, I don’t recall for sure whether it was made before or after I first noticed the issue

---

I have been careful not to turn on Wifi, or sync, since then, since I’ve had nightmares with sync conflicts in the past when restoring from a backup.

What I have discovered so far:

- My Kobo has indeed deleted all annotations for the book from the Bookmark table of KoboReader.sqlite.
- When I load them back on (full SD restore from most recent backup) they just disappear again.
- Most recent backup has no annotations in Digital Editions file for that book, even though annotations are in SQLite database.
- Second-to-last backup has annotations in a Digital Editions file as well as database.
- Comparing the most recent annotation from second-to-last backup with the same annotation from latest backup, I noticed that the ContentID field is blank in most recent backup. Everything else, for annotations that are in both backups (id, etc) seem to be identical.
- When I insert a ContentID matching older backup, Kobo still throws them all out.

Maybe the Digital Editions folder is somehow pulling rank over data in the Bookmark table of the SQLite DB?

Does anyone have tips on how to get Kobo to accept my backed up annotations for the book, rather than deleting them?

Thanks!

[pl8o]

Progress! I seem to have mostly fixed it by blindly running SQL queries

I would still like to know what went wrong, and why, so that I can avoid it in the future. I don’t know which of the steps actually fixed it, and which ones were just me running the the maze. Any insight would be much appreciated!

Steps taken:

- Restored from second-to-last backup.
- Opened latest backup in DB Browser for SQLite and deleted everything in the Bookmark table other than the annotations added since second-to-last backup.
- Exported Bookmark table as SQL, and imported into Bookmark table from second-to-last backup.
- Made imported (non functional) annotations match old ones with:

SELECT * FROM Bookmark WHERE ContentID = ""
UPDATE Bookmark SET ContentID = "[content id from last good annotation]" WHERE ContentID = "";

and:

UPDATE Bookmark SET DateModified = "[date modified from last good annotation]" WHERE DateModified IS NULL AND VolumeID = "[book that lost its annotations]";

and:

UPDATE Bookmark SET Annotation = NULL WHERE Annotation = "" AND VolumeID = "[book that lost its annotations]";

I also deleted one fishy looking row in the bookmark table for that book that looked like it had gotten garbled.

I loaded the updated SQLite DB on.

When I clicked annotations from the home screen, they showed up, but when I clicked "Show in Book", it would show a white screen and the book would close / crash. They didn’t show up in the book when I paged through, though, and after paging through they would disappear from the annotations view.

I repeated the process, this time deleting the Digital Editions folder for the author of the book. It had the same issue with "Show in Book", at first: returning a blank screen and crashing. But when I flip through, they’re there. And now, "show in book" seems to be working too!

[davidfor]

Originally Posted by pl8o

My Kobo (Aura ONE, 4.15.12920) has thrown out its annotations again. This time, it has thrown out *all* the annotations for *one* sideloaded .epub book.

I think what happened is caused by a bug in firmware 4.15.12920. Bookmarks are not being stored correctly if the internal file the bookmark is in does not have a ToC entry. In this case, the ContentID column in the Bookmarks table will be empty. Your solution of setting the value to the same as a successful bookmark should have fixed it.

There might also be a problem related to having multiple ToC entries in a file. That has some issues, but I can't remember if it triggers a problem with bookmarks or not.

What I have discovered so far:

- My Kobo has indeed deleted all annotations for the book from the Bookmark table of KoboReader.sqlite.
- When I load them back on (full SD restore from most recent backup) they just disappear again.
- Most recent backup has no annotations in Digital Editions file for that book, even though annotations are in SQLite database.
- Second-to-last backup has annotations in a Digital Editions file as well as database.
- Comparing the most recent annotation from second-to-last backup with the same annotation from latest backup, I noticed that the ContentID field is blank in most recent backup. Everything else, for annotations that are in both backups (id, etc) seem to be identical.
- When I insert a ContentID matching older backup, Kobo still throws them all out.

All that suggests that the device updated the firmware to 4.15.12920 between the good backup and when you noticed the problem.

Maybe the Digital Editions folder is somehow pulling rank over data in the Bookmark table of the SQLite DB?

The "Digital Editions" folder does pull rank over the table, but, not in this way. When you reopen the book, the file and database are merged. Unfortunately, the location details are updated in a way that means that which chapter the annotation is in, and the percent progress through the book is lost. The chapter will always show as the chapter you reopen the book at, and the percent will always be zero. This is a very long standing bug.

There is a simple workaround for this. In the "Digital Editions" folder, remove the "annotations" directory and replace it with a file. The device will no longer be able to create the annot files and the merge errors when opening the book will not happen. I do this on the device I do most annotations on, and have never noticed a problem.

Does anyone have tips on how to get Kobo to accept my backed up annotations for the book, rather than deleting them?

Basically what you have done is what I would have suggested. But, I would have missed the need to fix the ContentID as it was only when I tried to work out why you did it, that I realised what the original cause probably was.

Originally Posted by pl8o

When I clicked annotations from the home screen, they showed up, but when I clicked "Show in Book", it would show a white screen and the book would close / crash. They didn’t show up in the book when I paged through, though, and after paging through they would disappear from the annotations view.

I repeated the process, this time deleting the Digital Editions folder for the author of the book. It had the same issue with "Show in Book", at first: returning a blank screen and crashing. But when I flip through, they’re there. And now, "show in book" seems to be working too!

I think most of that is related to the issues when merging the annot file with the bookmark. This would probably have messed up the ContentID for the books that were missing them. A crash when trying to go to an annotation usually means that the book has change in some way and the annotation points to a location that doesn't exist. It is possible the change you made set things up incorrectly. But, the annotations get rewritten during the reading session and that probably set the correct values. Also, I think I would have used the ContentId for the first ToC entry in the book. That might have avoided some of these issues, but I am not sure.

And congratulations on recovering from this. Hopefully Kobo will fix this in the next firmware update so that it doesn't continue to affect things.

[pl8o]

Thanks for the in-depth reply @davidfor, and the good tip about blocking the creation of the Digital Editions annotation file!

My Kobo is still creating ... ephemeral bookmarks. With the Digital Editions folder left to its own devices, they get deleted — but when I replace the author folder with an empty folder of the same name like you suggested, the invisible annotations stay in the DB until I plug it into my laptop and manually add ContentIDs with SQL. (It seems that only the ContentID needs to be set for bookmarks to show up; the change in annotation and date modified values may have some effect, but I haven’t found out what, if anything, it is.)

I hope Kobo releases a firmware update soon