Kobo Glo SQLite Database

[jbaumann]

Hi,

I wanted to play with my Kobo Glo a bit, and thus exchanged the internal SD card with one that holds 32 GB. To do this I used the hack that allows to duplicate the internal SD card to an external one and then to simply swap them. Worked like a charm. Added KSM and the koreader package, but still had nearly 30 GBytes left. I'm using firmware 3.15.

So what I started now is to fill the reader with books (don't ask whether this makes any sense). In the past I had only a few books on the Kobo and managed the rest with calibre. Now I've put roughly 3000 on it. Performance is still ok, if you do not have collections that are too large.

Now the problem: Every time I try this the internal database gets corrupted. It is either indexes or problems with constraints, I wasn't able yet to put all the books on the reader and get a correct database. This leads to missing collections, missing books and most problematic, calibre not wanting to talk to the Kobo Glo.

My question: Is the internal database management to unstable and it is simply a bad idea to put that many books on the reader, or is there anything I did wrong?

Cheers, Joe

[davidfor]

You should be OK with 3000 books. There have been a few people here claiming to have 5000 or more books with no real problems. Personally, I think the maximum I have had is about 2000. The Glo HD is at about 1800 at the moment with no problems.

Do you have an idea of when the corruption happens? At eject time, or are you seeing a hang or crash? Are you putting all 3000 books on the device and letting them process? Or building up to it in smaller batches?

Batches of a few hundred would be what I would try. Between batches you can connect and backup the database. That will let you recover if the next batch has problems.

[jackie_w]

Originally Posted by jbaumann

Now the problem: Every time I try this the internal database gets corrupted.

You're not alone. It's a long time since I experimented with koreader (on an AuraHD and a Glo), but when I did, sooner or later - usually within a few days of installing - I ended up with a corrupt database when I connected to calibre.

I never did track down what the problem was and I do know many people don't have this problem. In the end I decided I preferred the default Kobo software for reading epubs anyway and I really didn't have enough PDFs I wanted to read in black-and-white to make further troubleshooting worthwhile.

FWIW, I never did any hacking to increase my SD card capacity nor did I have very many books (only 100-ish). I did have a suspicion that my problems may have been caused by trying to access books (EPUB and PDF) that were visible to both Kobo Nickel and koreader. I know that many (all?) koreader users place their books in a hidden directory so that Nickel can't see them. Whether that would have solved my corruption problems, I don't know because, as I said, I ran out of patience for what would have been, for me, a purely academic exercise.

[Ken Maltby]

There are those who use the same files with both Nickel and the alternate readers, and report no problems. That said; I think it is a bad idea, there are too many advantages to hiding the files you want to read with the alternate readers, from the Nickel processing. It is easy to do, just make a folder that starts with a dot/period like .Library, and put your book folders/files into that "hidden" folder. The ebooks are available to the alternate readers, as quickly as it takes your PC to copy them over, no Kobo processing needed. Calibre can use your hidden folder as if it were a drive.

This thread contains some tips that might help.

Luck;
Ken

[jbaumann]

Hi,

thanks for your suggestions. I already use a hidden directory for koreader (and Coolreader) so the problem shouldn't come from some access collision... And Ken, I actually used your thread to originally set up the reader, so thanks a lot for that, it really helped.

I think that when adding large numbers of books to the reader at some point the database gets corrupted. I don't think that it is calibre's handling of the database, because in those few cases I checked the database integrity directly after side-loading the books (without first unplugging) the database was ok. So it most probably happens in the firmware at some point. And either calibre inserts values that lead to that (I don't believe that) or the firmware is buggy in that area. If I knew it is the second and that the maximum number of books that can be side-loaded in one batch is e.g., 100, then I would be happier and would know how to use it.

Cheers, Joachim

[jbaumann]

Btw., how can I force a rescan of the books stored on the sd card. This might be important if I experiment with moving versions of the sqlite file back and forth.

Cheers, Joe

[davidfor]

The KoboTouch driver updates the database in three ways. The two main ways are for the collections and to setting the series information. Both are done during the "Send metadata to device" stage of the connection. This is also run after books are sent to the device. The collections will be done for books just sent, but the series info has to wait for the next connection as the database doesn't contain records for the new books. If you have changed any series info on books already on the device, they will be updated at this point.

The other change is if you resend book already on the device and in the database. When you do this, the file size stored in the database is update to match the copy being sent. If this isn't done, the firmware will remove the book from the database and then process it as a new book.

None of this should cause a database corruption. About the only thing I can think of would be if the eject was done to quickly before the database was completely written to the device. Safely ejecting the device should prevent that.

The device will process all new books on the main memory and SD card when it is disconnected from the PC. It will also process all books on the SD card when insert it. So, ejecting and reinserting it should get any books on the card that are not in the inserted database processed.

[Ken Maltby]

One of the fmon pngs tshering provided in Kobo Start Menu (KSM7), to aid working with Nickel, was "simulate_usb_connection".

One of the reasons that I make no use of the stock readers, may be that I have had no problems with using my 7000+ "hidden" library in Koreader. My kobo database only manages the fmon .pngs and a small 600+ calibre generated library, for testing purposes. All the rest of my ebook files are behind hidden folders, and not processed into the kobo database.

Luck;
Ken

[jbaumann]

Hi,

I did the following now:
- booted into KSM and started the usb connection
- verified that the database is correct (with sqlite and pragma integrity_check; )
- used calibre to transfer roughly 100 books
- checked that the database is correct
- ejected the reader and stopped the usb connection
- started the usb connection, plugged the reader in and verified that the database is correct (i.e., there is no problem with the cabling, with writing the data or with whatever kind of interaction from cached data on the computer)
- booted into Nickel
- nothing happened (since Nickel didn't know about the transferred books)
- plugged the reader in and ejected it again
- Nickel processed the books
- plugged in the reader and the database was malformed (high number of invalid page numbers and unused pages)

I'm a bit stumped now.

What it shouldn't be (I think):
- Cabling
- Calibre

What it could be:
- The sd card
- The transfer of the firmware from the original sd card to this sd card
- Buggy firmware (3.15)

Is there a simple way to determine this?

I do not really think that the sd card is the culprit, because as long as I don't boot into Nickel everything stays readable (koreader and coolreader work fine).

Cheers, Joe

[davidfor]

Originally Posted by jbaumann

What it could be:
- The sd card
- The transfer of the firmware from the original sd card to this sd card

Did you do a factory reset after the transfer? Strictly it shouldn't be needed, but the two times I have restored an image, I had some strange behaviour until I did this.

- Buggy firmware (3.15)

Is there a simple way to determine this?

I do not really think that the sd card is the culprit, because as long as I don't boot into Nickel everything stays readable (koreader and coolreader work fine).

Of course Koreader and Coolreader are fine. They don't go near the database.

Are you starting nickel from KSM? If so remove that and see what happens.

[Ken Maltby]

Originally Posted by jbaumann

Hi,

I did the following now:
- booted into KSM and started the usb connection
- verified that the database is correct (with sqlite and pragma integrity_check; )
- used calibre to transfer roughly 100 books
- checked that the database is correct
- ejected the reader and stopped the usb connection
- started the usb connection, plugged the reader in and verified that the database is correct (i.e., there is no problem with the cabling, with writing the data or with whatever kind of interaction from cached data on the computer)
- booted into Nickel
- nothing happened (since Nickel didn't know about the transferred books)
- plugged the reader in and ejected it again
- Nickel processed the books
- plugged in the reader and the database was malformed (high number of invalid page numbers and unused pages)

I'm a bit stumped now.

What it shouldn't be (I think):
- Cabling
- Calibre

What it could be:
- The sd card
- The transfer of the firmware from the original sd card to this sd card
- Buggy firmware (3.15)

Is there a simple way to determine this?

I do not really think that the sd card is the culprit, because as long as I don't boot into Nickel everything stays readable (koreader and coolreader work fine).

Cheers, Joe

It sounds like this has moved beyond my pay grade, especially as the database processing is a major suspect. I don't really have much experience with the Kobo database, as I don't use Nickel for reading nor make much use of anything the database is managing. This sounds like something Davidfor & company is better suited to handle. I go to great lengths to avoid interactions with the Kobo database.

Luck;
Ken

[tshering]

Originally Posted by davidfor

Are you starting nickel from KSM? If so remove that and see what happens.

In order to temporarily disable KSM you can select:
tools > activate > set runmenu settings.msh > never

In order to enable KSM again, you would have to edit runsettings.txt with a unix style editor.

[davidfor]

I thought of a couple of other things to try. They might help work out when the corruption happens.

Firstly, connect to the PC without calibre running. Then use a SQLite tool to do the database check. I use a Firefox plugin called "SQLite Manager", but there are native applications for all OSes.

A little harder would be installing SQLite on the device and then running the check while it is running. There already is an SQLite library, but I don't think there is a command line tool to run it. I know I have seen mention of it in the developers section, but haven't followed it up. After that, telnet could be enabled and used to run it. For this, I am thinking that checking after the processing is finished and before connecting to the PC might help to work out when the corruption happens.

[jbaumann]

Hi,

it seems that KSM is the culprit.

What I did:

- Factory reset
- Starting with small batches of around 100 ebooks and going on to larger ones with 500 ebooks I copied 1500 books without problems
- Installed KSM again
- Copied 100 books to the reader with calibre
- Database was ok after copying
- Unplugged and waited for the reader to finish processing the books
- Plugged it in again and lo and behold, the database was corrupted
- Disabled KSM
- Copied back the last version of the database
- Unplugged the reader, plugged it in again and checked the database (everything ok)
- Copied another 500 books
- Database is still ok

I'm not sure what KSM does (or the file monitor) that corrupts the database, but it definitely does something.

@tshering: How can I determine what happens in more detail? I'm willing to experiment to some extent, because I would very much like to have the ability to choose koreader (for PDF reflow) and Coolreader (simply because I can) for my reading experience :-)

Cheers, Joe
PS: Comparing koreader, CoolReader and the Kobo-own reader implementation, which one renders books the best?
PPS: I really like the ability to sort books in collections. Is this possible with either CoolReader or koreader?
PPPS: I should probably start an own thread for this discussion...

[tshering]

Originally Posted by jbaumann

I'm not sure what KSM does (or the file monitor) that corrupts the database, but it definitely does something.

@tshering: How can I determine what happens in more detail? I'm willing to experiment to some extent, because I would very much like to have the ability to choose koreader (for PDF reflow) and Coolreader (simply because I can) for my reading experience :-)

Originally Posted by jbaumann

What I did:
...
- Copied 100 books to the reader with calibre
- Database was ok after copying
- Unplugged and waited for the reader to finish processing the books
- Plugged it in again and lo and behold, the database was corrupted

Difficult to say what is happening here. At this point, apart from the original kobo stuff, only fmon and two or three shell scripts are running (the scripts do basically nothing but waiting that nickel exits, so that the KSM application can be started again.)
I was first thinking that there might be situations that nickel gets killed (when choosing exit_nickel.png), before it has finished writing to the database. At least in your 4 steps above, this seems however not to be the case.

As for sqlite3, there is an executable included in KSM (.adds\kbmenu\tools\sqlite3). You can do a simple integrity check of the database by selecting tools > kobo diverse.msh > db chk integrity.sh. For the above described situation this will however not help you (since nickel is running at that point). So you would have to do it with telnet via wifi (you might need to look around in this forum on how to prevent nickel from disconnecting the wifi connection).

Maybe I should mention that I was using KSM since it was available. I never had any problems with the database since then (except by my own fault when experimenting). But I am not a good test case, because I never use caliber to put books on the reader. So maybe it is somehow the combination of using KSM and calibre. But I have no idea of what exactly could be the cause of the corruption.

Edit: By the way, you can start nickel from KSM via tools > kobo diverse.msh > start nickel no fmon.sh. In this case fmon will not be involved at all. (You will have to power off by using the power button, there is no way to get back from nickel to KSM in this situation.