Kobo Firmware 3.11.0

[PeterT]

I've taken a look, and the problem is that the content table has some corruption. A count of the rows in it shows 15117, however I'm only able to retrieve 4,757 rows from it so attempts to build a new database from a dump of the old one fail.

An integrity check shows

*** in database main ***
Page 17620: btreeInitPage() returns error code 11
On tree page 16705 cell 80: Child page depth differs
On tree page 16705 cell 81: Child page depth differs
Page 17650: btreeInitPage() returns error code 11
On tree page 16705 cell 92: Child page depth differs
On tree page 16705 cell 93: Child page depth differs
On tree page 6819 cell 20: 2nd reference to page 7209
On tree page 6819 cell 20: Child page depth differs
On tree page 20 cell 1: Child page depth differs
Page 17619 is never used
Page 17621 is never used
Page 17648 is never used
Page 17649 is never used
rowid 19971 missing from index analytics_events_timestamp
rowid 19971 missing from index sqlite_autoindex_AnalyticsEvents_1
wrong # of entries in index analytics_events_timestamp
wrong # of entries in index sqlite_autoindex_AnalyticsEvents_1

If davidfor sees this, any thoughts?

I know you don't want to reset the database but you probably will have to sooner or later.

[davidfor]

Originally Posted by PeterT

I've taken a look, and the problem is that the content table has some corruption. A count of the rows in it shows 15117, however I'm only able to retrieve 4,757 rows from it so attempts to build a new database from a dump of the old one fail.

An integrity check shows

*** in database main ***
Page 17620: btreeInitPage() returns error code 11
On tree page 16705 cell 80: Child page depth differs
On tree page 16705 cell 81: Child page depth differs
Page 17650: btreeInitPage() returns error code 11
On tree page 16705 cell 92: Child page depth differs
On tree page 16705 cell 93: Child page depth differs
On tree page 6819 cell 20: 2nd reference to page 7209
On tree page 6819 cell 20: Child page depth differs
On tree page 20 cell 1: Child page depth differs
Page 17619 is never used
Page 17621 is never used
Page 17648 is never used
Page 17649 is never used
rowid 19971 missing from index analytics_events_timestamp
rowid 19971 missing from index sqlite_autoindex_AnalyticsEvents_1
wrong # of entries in index analytics_events_timestamp
wrong # of entries in index sqlite_autoindex_AnalyticsEvents_1

If davidfor sees this, any thoughts?

I know you don't want to reset the database but you probably will have to sooner or later.

I don't think it can be recovered. From what I have seen, the following errors can be fixed:
- "Page x is never used" - Fixed by compressing the database. In SQLite, this is the VACUUM command.
- "rowid x missing from index y" - Fixed by rebuilding the index or a compressing the database
- "wrong # of entries in index" - Should be fixed by rebuilding the index or a compressing the database.

I am pretty sure there is no way to recover from the tree errors. That means an internal data structure has been broken and no longer points to valid data. It could be restricted to one table or it could be all the tables. A compress might fix the error but it will almost definitely mean data loss. From where the error is, the "content" table is corrupt. I don't think a recovery of that will leave the database in a consistent state. And I'm pretty sure that if Paola powers off and on, some books are going to be lost or at least the status will be. And it wouldn't surprise me if the device decided the database was to bad and created a new one and prompted for login.

At this point, the best thing would be to dump the Bookmark table, or at least the rows for the sideloaded books (VolumeID LIKE 'file://mnt%' should get them), do the signout and in and then reload the Bookmark rows. I would do this sooner rather than later.

[hleo12]

Originally Posted by PeterT

Unfortunately that is showing it that some errors exist in the database on the Kobo. The standard approach to fixing that is to logout on the Kobo and then redo the setup. Some day it would be great if we had a tool that would recover as much data as possible from a corrupted database and even build a new one.

I also sometimes get those types of error. But I read something somewhere on this forums about a solution.

I copy the device database on my PC then open it with SQLite Expert and choose Reindex All Tables under Database. Then I save it and transfer it to my Kobo. It works all of the times that I've encountered that problem.

[PeterT]

That can work with some errors; unfortunately in Paola's case this would not have helped.

[paola]

Peter, David, thank you so much - yes, the behaviour david described (powering off, offer to login) did indeed happen, and I "cheated" by replacing with the last functioning database.

The annotations are what I care about, so I'll try the dump option.

Again, thanks a million! I'll let you know how I get on as soon as I have a chance to try.

[Ebri]

PeterT: Thank you, I will check them.

Yesterday I also had to use a backup of my database to recover (removing the corrupt parts had some vital side-effects), and I did only highlighting on EPUBs, but if highlighting didn't happen while I have read all the page, I made the device (Aura HD) sleep and wake, which made it responsive again, but I am unsure whether it also killed the database. Do you have such experience? Especially does anyone have a cure, that would make highlighting faster? (I read quickly, but reading a page takes some time still.)

[PeterT]

Corruption of the database is unfortunately a totally random event; no rhyme or reason as to why or when they happen.

About the best thing you can do is to ensure you have backup(s). I have calibre and the Kobo Utilities configured to take a backup upon connection; unfortunately it only takes one backup a day.

[Jellby]

I even take backups through wifi (telnet), to avoid the chance of the USB connection, mounting, etc. causing the corruption.

[JSWolf]

Originally Posted by Jellby

I even take backups through wifi (telnet), to avoid the chance of the USB connection, mounting, etc. causing the corruption.

How do you take backup over WiFi (Telnet)?

[davidfor]

Originally Posted by PeterT

Corruption of the database is unfortunately a totally random event; no rhyme or reason as to why or when they happen.

Yes, other then a disconnect from the PC without ejecting, I've never been able to find a pattern either.

About the best thing you can do is to ensure you have backup(s). I have calibre and the Kobo Utilities configured to take a backup upon connection; unfortunately it only takes one backup a day.

So you want an "on every connect" option? I've been think about it, so I'll add it when I get a chance.

[davidfor]

Originally Posted by Ebri

PeterT: Thank you, I will check them.

Yesterday I also had to use a backup of my database to recover (removing the corrupt parts had some vital side-effects), and I did only highlighting on EPUBs, but if highlighting didn't happen while I have read all the page, I made the device (Aura HD) sleep and wake, which made it responsive again, but I am unsure whether it also killed the database. Do you have such experience? Especially does anyone have a cure, that would make highlighting faster? (I read quickly, but reading a page takes some time still.)

I haven't noticed it much recently, but I was having a problem like this. Occasionally when I tried to highlight something, the device would hang. But, putting it to sleep and waking it would get it going. And highlighting would work. I never worked out pattern, but I felt like it was more likely to happen if I started from the right edge and swiped left. Of course, when I deliberately did this, it always worked.

But, I would have said this went away with 3.11.0. I put some of the blame on something in the database. When certain things are done, an entry is added or updated in the Events table. For highlighting, eventid 9 is used to count them. Up to 3.11.0, this was done incorrectly and a row could be added to the database for each highlight and the count updated on all of them. After a while, there could be a lot of rows. I found about 3000 one time I looked. Deleting these sped up the highlighting and dictionary lookup a lot.

As of 3.11.0, the count has been changed to a per book count. But, it's possible the old rows are still there. That would have a performance hit due to the size of the table.

[PeterT]

Ah; so David, the two of us should be safe from the multiple rows in Events!

[JSWolf]

What is the best way to compress an SQL database?

[davidfor]

Originally Posted by JSWolf

What is the best way to compress an SQL database?

That really depends on the "SQL database". You should use the tools supplied as part of the RDBMS. They all have them. Or a third party tool that does it.

And yes, that is me being a smart-arse (but it is a completely accurate answer). "SQL database" has almost no meaning in a question like that because there are a lot of different SQL databases. DB2, MySQL, Progresss, Oracle, SQL Server could all be called SQL databases.

Of course, the context does tell me that you are probably talking about the SQLite database that the Kobo devices use. In that case, the simplest way to compress it is using my Kobo Utilities plugin in calibre. Other than that, any SQLite database tool will have an option to do it or allow you to run VACUUM.

[davidfor]

Originally Posted by PeterT

Ah; so David, the two of us should be safe from the multiple rows in Events!

I have no idea what you mean But, maybe you're right. Maybe the change wasn't in 3.11.0. I can't remember when I noticed the change. There were two or three other events that had a similar problem. They seem to have been fixed as well.