Glo HD and Firmware 4.2.8432

[ppowell002]

I recently updated to new firmware and it has wiped out My Collections. Kobo Customer Care tells me there is no way to fix this issue but to rebuild Collections. Does anyone know of an easier solution?
Thanks

[Semwize]

https://www.mobileread.com/forums/sh...d.php?t=254554

works on Kobo Glo HD

[JSWolf]

If you use Calibre, all of your side loaded eBooks can be easily fixed. I'm not 100% sure this will work, but it might and if not, there's another solution.

The first thing to do is make sure you are set to Automatic Management. That option can be found in Sending books to device in preferences. With this set, I believe that Calibre will keep the collections in sync.

If that doesn't work, send all of your side loaded books to your Kobo and that will work.

Any books you've downloaded from Kobo you'll have to handle manually.

[milady133]

I've also lost my collections with this firmware update both in my Kobo Aura (original) and Kobo Aura HD, I had the automatic backups of the TouchExtended Calibre plugin and some knowledge of SQL, but anyway I spent some hours yesterday restoring my Collections and their contents, specially with my Kobo Aura HD, which was the first to be restored, it took much more less with the Aura, once I knew the steps to follow. Not happy, I could have spent those hours reading :-(

[ppowell002]

I figured I had nothing to lose so I did another sync and all my collections were back! I had not stopped the original software download and lengthy library sync before it was finished so not sure why this happened. This second sync took a long time and some of the books in my library had to be downloaded again but no big deal.

It seems strange that the service rep did not know of this as a possible solution. Thanks for input everyone.

[davidfor]

For the people who are losing the collections, is this for sideloaded or purchased books? Or both? And have you done a sync recently? And did you the post update sync complete? How was the firmware installed? Manually or from the Kobo server?

And if anyone who this has affected wants to send me a copy of the database from the device, I can have a look and see if there is anything that can be done. You will need to put the file somewhere and send me a PM with a link for me to download. Or send me a PM for an email address to send the file to.

[milady133]

Hi @davidfor:
I solved it myself meddling with the database, but I have the backups, I can send them to you if you like. I have both sideloaded and store purchased books, and some collections had only sideloaded books, but others had both type of books.

[davidfor]

Originally Posted by milady133

Hi @davidfor:
I solved it myself meddling with the database, but I have the backups, I can send them to you if you like. I have both sideloaded and store purchased books, and some collections had only sideloaded books, but others had both type of books.

Just replied to your PM. I'm interested in what fiddling you did and if I can automate it for others.

[milady133]

It's difficult to replicate it for others, you don't know what changes they have made between databases. Basically the problem was that both tables, the one with the shelves and the one associating books with shelves where empty, so I just took the rows from shelves table from my backup, completed the information with the new columns and inserted them again in the actual database. Then I took the rows from the ShelvesContent table from my backup and inserted them in the table of the actual database, I don't delete books, so I didn't had to check anything more, and I work with databases in my day job, so it wasn't too difficult, I just had to Google how sqllite treats date and boolean columns 😊

[davidfor]

OK, that means it cannot be fixed easily. Anyone with a backup can do it, but otherwise not.

[compurandom]

I've been thinking that a more comprehensive database repair tool would be interesting. Perhaps importing specific sets of data from a backup could be one of those repairs.

Do you have any documentation of what you've discovered about the database schema?
I've had enough database corruption issues that I could be motivated to work on a tool that would let me fix it (including deleted books) without logging out and back in.

[milady133]

OK, for those interested I'm going to write a small guide on how to get your collections and content back, but beware, this is not a step by step guide on purpose, because there may be to many different circunstances and you'll have to tweak yourself, so if you are going to follow my instructions, you need to be tech savvy enough to google for a solution if you have a problem, you don't need to be a database expert, but you need the patience to look for a solution. And also the patience to understand my English, cause it's not my native language, so some phrasing it's going to be weird

What you need:

1. A backup of your KoboReader.sqlite database where the collections are present, if you use Calibre KoboUtilities plugin, you'll have it.
2. A sqllite database manager, I have a firefox add-on named SQLite Manager, but anything that can open .sqlite databases will do.
3. A backup of your Kobo device to restore it if anything goes wrong.

Now we are going to proceed:

1. Connect your Kobo to your PC and open both databases, de Backup and the actual database in Kobo (it's in the .kobo folder), I recommend putting a different name to your backup database so you know wich one you are working on, for example, with SQLite Manager, if I rename the backup database from KoboReader.sqlite to backup.sqlite before opening it, I can see the name of the database file in the window so I avoid making mistakes.
2. First, you are going to restore the content of the table with all the collections, the name of that table is Shelf.
   1. Select the content of that table in both databases to check that the structure of the table hasn't changed (they have the same columns) and the rows in kobo database have mostly dissapeared. You do that writing this order and executing the query:

      SELECT * FROM Shelf ORDER BY Name

   2. When I compare the results, only one of the old collections survived, Wishlist, that collection I wasn't aware it existed, it was created by kobo and I never knew it was there, in the new kobo database I see that collection, and two new ones, another collection with the name Wishlist, but the Type is SystemTag instead of Custom, and another named ReadingList. I also see a new column in the table, _SyncTime, and the old collection has that column empty, so when I insert my collections I'm going to leave it empty.
   3. So now we are going to retrieve all the collections from the backup database, and we are going to create the INSERT order to create them in the Kobo database. Go to the backup and execute this query:

      SELECT "INSERT INTO Shelf (CreationDate, Id, InternalName, LastModified, Name, Type, _IsDeleted, _IsVisible, _IsSynced) VALUES ('" || CreationDate || "', '" || Id || "', '" || InternalName || "', '" || LastModified || "', '" || Name || "', '" || Type || "', '" || _IsDeleted || "', '" || _IsVisible || "', '" || _IsSynced || "');" AS MyInsOrder FROM Shelf ORDER BY Name

   4. You get a list of your collections with the SQL order to insert them in your Kobo database, try only with one first, copy the first row and paste it where you have your Kobo database opened, and execute it, if it all goes well, then copy the rest of the lines (but the first one you have already executed not) and executed them.
   5. A couple of things, when I execute an insert, update or delete, I'm used to getting a message saying it all went well, but SQLite Manager doesn't say anything, only if something goes wrong (at least not the version I'm running), so to make sure everything goes well, I just execute the select order again to see the Kobo database now has the collections (SELECT * FROM Shelf ORDER BY Name). Another thing that puzzles me, is that I don't need to COMMIT my operations to the database, they are recorded inmediatly, I think is the usual with sqlite databases, but it's not the usual for me
3. OK, now you have all your collections restored, we are going to reassociate our books with our collections, that happens in the ShelfContent table.
   1. Again, we are going to compare the tables in both databases, the backup and Kobo one, we run this select order:

      SELECT * FROM ShelfContent ORDER BY ContentId, ShelfName

   2. In the Kobo database, I only see some items in the ReadingList and Wishlist collections, and I don't see new columns, so for me it's easy. You'll have to adapt this part of the instructions if you have deleted books between backups, or just try if trying to add a book that doesn't exists gives you an error (it's not going to break anything, but maybe if you run all the inserts at once, the rest of the inserts after the first error are going to be ignored).
   3. So the same as with the Shelf table, now we run this select in the backup database to get all the book-collection pairs we had:

      SELECT "INSERT INTO ShelfContent (ShelfName, ContentId, DateModified, _IsDeleted, _IsSynced) VALUES ('" || ShelfName || "', '" || ContentId || "', '" || DateModified || "', '" || _IsDeleted || "', '" || _IsSynced ||"');" AS MyInsOrder FROM ShelfContent WHERE ShelfName <> 'Wishlist' ORDER BY ContentId, ShelfName

   4. You take the results, only the first line to test, and run the INSERT statement in the Kobo database to check everything is OK, and then the rest of the insert orders if everything goes well.
4. You are done, as a last precaution, check the Kobo database to see it isn't corrupt (the application probably will have an utility to do that), close the database in the application, and unplug your Kobo from your PC the usual way. You should see now your collections restored.

That's all, I hope it's useful for someone.
Regards

[JSWolf]

if you side load from Calibre and you are set for Automatic Management, then all you need do is plug your Kobo into the computer with Calibre set for your database. All will be fixed automatically. No need to dive into the database.

This won't fix for manually created collections from download eBooks from Kobo, but if it's not that many of them, recreate the collections and manually move the books into the collections.