Database rebuild

Jellby · 08-17-2014, 05:10 PM

I had a little issue with my Aura. I connected it to calibre, removed some books from the main memory and added them to the card. When I disconnected it, it took forever to process the new books (probably around 150), and at the end the database was corrupt: couldn't connect it back to calibre, no "currently reading" books, no cover thumbnails...

Fortunately, the Kobo utilities plugin had saved a backup of the database, so I could copy it back to the device and now at least everything looks normal, and I hope I can recover my annotations.

Anyway, since I removed some books in the last session, I expect the restored database contains references for these books, which are no longer in the device. So my question is: Is it possible to fix this? Rebuilding the database will probably take forever again, but can it be done? Or will the device it fix it by itself? I guess the books in the card will be reprocessed again, if needed, when I insert the card, right?

PeterT · 08-17-2014, 05:18 PM

I'd actually had the same happen to me the other night. I restored the database, power cycled the Glo and then for the books that the Glo thought were still present (but I had dropped via calibre) did a long tap in the library view and selected to remove the book from the device.

davidfor · 08-17-2014, 08:37 PM

The recent firmware should remove the database records for any books where the files are missing. But, when I have replaced it with a backup, it seemed to need something to trigger it. It either happened after an extra power cycle or when I put another book on the device.

Jellby · 08-18-2014, 03:44 AM

How does it work with the memory card? Does it hold another database or is the main database refreshed every time I insert or extract the card?

davidfor · 08-18-2014, 03:56 AM

All the book details are in one database on the main memory. The entries for the books on the SD card include an id for the card. When you insert a card, it checks if the card has been seen before and then if the books have changed. If the cards is not the same, it assumes that all the books are new. Or at least that's what it did the last time I tested. The details have changed with firmware versions, but the overall function is the same.

Jellby · 08-18-2014, 05:05 AM

OK, so it may be safer to remove the books from the card before inserting it back in the device, and add them in smaller batches. I wouldn't like to spend two hours waiting for the books to be processed, and find the database corrupted again

davidfor · 08-18-2014, 07:17 AM

Yes, if that's the concern. But, I've never had that problem. Nearly every database problem I have had I can blame on not ejecting from the PC properly. The others seem to be related to crashes on the device.

As to the external card, have a card with about 1100 books on it that I move between the devices when I want to test something. I haven't had any problems. I also have a nearly empty card that I put in to flush the records from the other card before reinserting my test card. I've done that several times in a day without a problem.

Jellby · 08-21-2014, 03:00 PM

I'm in trouble again. I deleted a bunch of files (20-30) from the device with calibre, and the device started to process all the content again (1-2 hours). Books I were reading are marked as unread.

Restored the backup of the whole device (not just the database) I had just made before that. Rebooted the device, nothing happened. Good sign. Deleted the books one by one from nickel. Rebooted. All fine.

Plugged it to calibre, compressed the database, unplugged. Again started to process all the books (1-2 hours again, I expect, it's not finished yet).

Maybe I was just unlucky, but I'm afraid of trying a third time...

Jellby · 08-23-2014, 09:36 AM

I could finally move a few more books, but now the Kobo Utilities plugin refuses to backup the database, it gives this error:

Code:

Traceback (most recent call last):
  File "site-packages/calibre/gui2/device.py", line 86, in run
  File "calibre_plugins.koboutilities.jobs", line 179, in do_device_database_backup
Exception: 
*** in database main ***
Page 13934 is never used
Page 13935 is never used
Page 13936 is never used
Page 13939 is never used
Page 13941 is never used
Page 13943 is never used
Page 13945 is never used

The device seems to behave OK. I've deleted a couple of books on it, rebooted, added them back with calibre, and it's still the same. Can the database be fixed somehow, without removing it?

PeterT · 08-23-2014, 10:36 AM

@Jellby: Welcome to my world! This is the same type of issue I've been battling with 3.5, and it's what made me create the thread Database Corruption / FW 3.5.0

Jellby · 08-23-2014, 10:42 AM

For the record, I fixed it with sqlitebrowse, using the "Compact Database" option. I replaced the database with the compacted one and nobody complained

I guess this is the same Kobo Utilities does when compressing the database, but I didn't try (and I suspect it would also refuse to do it if the database does not pass the integrity check to start with).

davidfor · 08-24-2014, 03:25 AM

The utilities plugin uses "VACUUM" on the database to compress it. I would expect any SQLite database manager to do the same thing. From memory, this works by copying the data to an empty database. I would expect some integrity problems to be OK with this. An index error would not cause a problem. A page marked as in use, but with nothing pointing to it would probably be OK. The error you had looks to be one of these.

I doubt the compress would fix a problem if a pointer to a page was broken. That is what is usually reported when there is an error during the sync with calibre. In these cases, even if the compress worked, there would probably be some data loss.

Jellby · 08-30-2014, 08:35 AM

Now I got another kind of error:

Code:

row 16458 missing from index volume_shortcovers_shortcoverId
row 16459 missing from index volume_shortcovers_shortcoverId
row 16460 missing from index volume_shortcovers_shortcoverId
...

And compressing the database didn't fix it:

Code:

wrong # of entries in index content_bookid_index
wrong # of entries in index sqlite_autoindex_content_1
row 16458 missing from index volume_shortcovers_shortcoverId
row 16459 missing from index volume_shortcovers_shortcoverId
row 16460 missing from index volume_shortcovers_shortcoverId
...

Any ideas?

davidfor · 08-30-2014, 08:47 AM

You can try rebuilding the indexes. The initial three errors will be fixed by this. I think the two extra in the second set will be fixed as well. Some SQLite managers will have it as an option in the GUI. Otherwise, you need to execute the command "REINDEX" to reindex all the indexes in the database. Or "REINDEX index_name" for just one index.

Jellby · 08-30-2014, 09:21 AM

After a few tries (communication failures, sqlitebrowser crashes, device resetting to "welcome to Kobo" status, etc.) I think I managed to do it.

Checking cover directories, I found a few *.parsed file that should not be there (the books had been moved to the SD card). It looks like the device (or calibre) didn't remove them automatically, and the common feature I see is all of them have an apostrophe in the filename. Of course, an apostrope or quote is probably more dangerous than spaces in filenames, so I wonder:

a. Why doesn't calibre avoid them?

b. Was I just unlucky or is this maybe a sign of a bug in the firmwar (3.5.0) or in calibre?

08-17-2014, 05:10 PM	#1
Jellby frumious Bandersnatch Posts: 7,515 Karma: 18512745 Join Date: Jan 2008 Location: Spaniard in Sweden Device: Cybook Orizon, Kobo Aura	Database rebuild I had a little issue with my Aura. I connected it to calibre, removed some books from the main memory and added them to the card. When I disconnected it, it took forever to process the new books (probably around 150), and at the end the database was corrupt: couldn't connect it back to calibre, no "currently reading" books, no cover thumbnails... Fortunately, the Kobo utilities plugin had saved a backup of the database, so I could copy it back to the device and now at least everything looks normal, and I hope I can recover my annotations. Anyway, since I removed some books in the last session, I expect the restored database contains references for these books, which are no longer in the device. So my question is: Is it possible to fix this? Rebuilding the database will probably take forever again, but can it be done? Or will the device it fix it by itself? I guess the books in the card will be reprocessed again, if needed, when I insert the card, right?

08-23-2014, 09:36 AM	#9
Jellby frumious Bandersnatch Posts: 7,515 Karma: 18512745 Join Date: Jan 2008 Location: Spaniard in Sweden Device: Cybook Orizon, Kobo Aura	I could finally move a few more books, but now the Kobo Utilities plugin refuses to backup the database, it gives this error: Code: Traceback (most recent call last): File "site-packages/calibre/gui2/device.py", line 86, in run File "calibre_plugins.koboutilities.jobs", line 179, in do_device_database_backup Exception: * in database main * Page 13934 is never used Page 13935 is never used Page 13936 is never used Page 13939 is never used Page 13941 is never used Page 13943 is never used Page 13945 is never used The device seems to behave OK. I've deleted a couple of books on it, rebooted, added them back with calibre, and it's still the same. Can the database be fixed somehow, without removing it?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Rebuild ePub after tweaking	hollowayhouse	Conversion	8	08-05-2011 08:09 PM
How to Rebuild Database	apswartz	Calibre	12	03-28-2010 01:11 PM
How to rebuild database in metafile	gandor62	Calibre	3	03-27-2010 08:31 PM
Force Database Rebuild?	davidm_uk	Calibre	2	01-13-2010 02:59 PM
Rebuild Database? & Use Path instead of Filename?	gai-jin	Calibre	0	07-20-2009 02:58 PM

08-17-2014, 05:18 PM	#2
PeterT Grand Sorcerer Posts: 12,160 Karma: 73448616 Join Date: Nov 2007 Location: Toronto Device: Nexus 7, Clara, Touch, Tolino EPOS	I'd actually had the same happen to me the other night. I restored the database, power cycled the Glo and then for the books that the Glo thought were still present (but I had dropped via calibre) did a long tap in the library view and selected to remove the book from the device.

08-17-2014, 08:37 PM	#3
davidfor Grand Sorcerer Posts: 24,907 Karma: 47303748 Join Date: Jul 2011 Location: Sydney, Australia Device: Kobo:Touch,Glo, AuraH2O, GloHD,AuraONE, ClaraHD, Libra H2O; tolinoepos	The recent firmware should remove the database records for any books where the files are missing. But, when I have replaced it with a backup, it seemed to need something to trigger it. It either happened after an extra power cycle or when I put another book on the device.

08-18-2014, 03:44 AM	#4
Jellby frumious Bandersnatch Posts: 7,515 Karma: 18512745 Join Date: Jan 2008 Location: Spaniard in Sweden Device: Cybook Orizon, Kobo Aura	How does it work with the memory card? Does it hold another database or is the main database refreshed every time I insert or extract the card?

08-18-2014, 03:56 AM	#5
davidfor Grand Sorcerer Posts: 24,907 Karma: 47303748 Join Date: Jul 2011 Location: Sydney, Australia Device: Kobo:Touch,Glo, AuraH2O, GloHD,AuraONE, ClaraHD, Libra H2O; tolinoepos	All the book details are in one database on the main memory. The entries for the books on the SD card include an id for the card. When you insert a card, it checks if the card has been seen before and then if the books have changed. If the cards is not the same, it assumes that all the books are new. Or at least that's what it did the last time I tested. The details have changed with firmware versions, but the overall function is the same.

08-18-2014, 05:05 AM	#6
Jellby frumious Bandersnatch Posts: 7,515 Karma: 18512745 Join Date: Jan 2008 Location: Spaniard in Sweden Device: Cybook Orizon, Kobo Aura	OK, so it may be safer to remove the books from the card before inserting it back in the device, and add them in smaller batches. I wouldn't like to spend two hours waiting for the books to be processed, and find the database corrupted again

08-18-2014, 07:17 AM	#7
davidfor Grand Sorcerer Posts: 24,907 Karma: 47303748 Join Date: Jul 2011 Location: Sydney, Australia Device: Kobo:Touch,Glo, AuraH2O, GloHD,AuraONE, ClaraHD, Libra H2O; tolinoepos	Yes, if that's the concern. But, I've never had that problem. Nearly every database problem I have had I can blame on not ejecting from the PC properly. The others seem to be related to crashes on the device. As to the external card, have a card with about 1100 books on it that I move between the devices when I want to test something. I haven't had any problems. I also have a nearly empty card that I put in to flush the records from the other card before reinserting my test card. I've done that several times in a day without a problem.

08-21-2014, 03:00 PM	#8
Jellby frumious Bandersnatch Posts: 7,515 Karma: 18512745 Join Date: Jan 2008 Location: Spaniard in Sweden Device: Cybook Orizon, Kobo Aura	I'm in trouble again. I deleted a bunch of files (20-30) from the device with calibre, and the device started to process all the content again (1-2 hours). Books I were reading are marked as unread. Restored the backup of the whole device (not just the database) I had just made before that. Rebooted the device, nothing happened. Good sign. Deleted the books one by one from nickel. Rebooted. All fine. Plugged it to calibre, compressed the database, unplugged. Again started to process all the books (1-2 hours again, I expect, it's not finished yet). Maybe I was just unlucky, but I'm afraid of trying a third time...

08-23-2014, 10:36 AM	#10
PeterT Grand Sorcerer Posts: 12,160 Karma: 73448616 Join Date: Nov 2007 Location: Toronto Device: Nexus 7, Clara, Touch, Tolino EPOS	@Jellby: Welcome to my world! This is the same type of issue I've been battling with 3.5, and it's what made me create the thread Database Corruption / FW 3.5.0

08-23-2014, 10:42 AM	#11
Jellby frumious Bandersnatch Posts: 7,515 Karma: 18512745 Join Date: Jan 2008 Location: Spaniard in Sweden Device: Cybook Orizon, Kobo Aura	For the record, I fixed it with sqlitebrowse, using the "Compact Database" option. I replaced the database with the compacted one and nobody complained I guess this is the same Kobo Utilities does when compressing the database, but I didn't try (and I suspect it would also refuse to do it if the database does not pass the integrity check to start with).

08-24-2014, 03:25 AM	#12
davidfor Grand Sorcerer Posts: 24,907 Karma: 47303748 Join Date: Jul 2011 Location: Sydney, Australia Device: Kobo:Touch,Glo, AuraH2O, GloHD,AuraONE, ClaraHD, Libra H2O; tolinoepos	The utilities plugin uses "VACUUM" on the database to compress it. I would expect any SQLite database manager to do the same thing. From memory, this works by copying the data to an empty database. I would expect some integrity problems to be OK with this. An index error would not cause a problem. A page marked as in use, but with nothing pointing to it would probably be OK. The error you had looks to be one of these. I doubt the compress would fix a problem if a pointer to a page was broken. That is what is usually reported when there is an error during the sync with calibre. In these cases, even if the compress worked, there would probably be some data loss.

Advert

Advert

08-30-2014, 08:47 AM	#14
davidfor Grand Sorcerer Posts: 24,907 Karma: 47303748 Join Date: Jul 2011 Location: Sydney, Australia Device: Kobo:Touch,Glo, AuraH2O, GloHD,AuraONE, ClaraHD, Libra H2O; tolinoepos	You can try rebuilding the indexes. The initial three errors will be fixed by this. I think the two extra in the second set will be fixed as well. Some SQLite managers will have it as an option in the GUI. Otherwise, you need to execute the command "REINDEX" to reindex all the indexes in the database. Or "REINDEX index_name" for just one index.

08-30-2014, 09:21 AM	#15
Jellby frumious Bandersnatch Posts: 7,515 Karma: 18512745 Join Date: Jan 2008 Location: Spaniard in Sweden Device: Cybook Orizon, Kobo Aura	After a few tries (communication failures, sqlitebrowser crashes, device resetting to "welcome to Kobo" status, etc.) I think I managed to do it. Checking cover directories, I found a few *.parsed file that should not be there (the books had been moved to the SD card). It looks like the device (or calibre) didn't remove them automatically, and the common feature I see is all of them have an apostrophe in the filename. Of course, an apostrope or quote is probably more dangerous than spaces in filenames, so I wonder: a. Why doesn't calibre avoid them? b. Was I just unlucky or is this maybe a sign of a bug in the firmwar (3.5.0) or in calibre?