Libra How to fix a (minor?) database error?

Mister L · 12-01-2021, 03:24 PM

Kobo Libra H2O, firmware 4.24.15676
I only side-load books, either using Calibre or manually in the file explorer (for testing purposes), and never activate the wifi.
I do not read CBR / CBZ format books.

I am using the Kobo Utilities Calibre plugin to backup my database every time I connect to Calibre. Recently I started getting an error which prevents the backup: "Failed to backup the database".

Spoiler:

I am still able to load books to the device and Calibre can read all the books already on it, and the plugin appears to continue to update the reading positions, which is why I think it must be a relatively minor error. But series information does not seem to be written. And I would prefer to have an up-to-date backup.

The first time it happened I was in a hurry and everything but the backup seemed to work fine so I crossed my fingers it would resolve itself. It has not.

But in the meantime I have read quite a lot, made lots of annotations, and added several books which means at this point the last backup is quite out of date.

After checking the forum I have tried:

- Compact the database:

Spoiler:

- check database:

Spoiler:

I would really like to avoid losing my notes, reading position, and datestamps of loaded / read because that is my preferred sort order, so I have not logged out.

Questions:
1. Can I fix the error(s) in the database somehow? (How?)

1a. What could have caused this error (how to avoid it in future)? I always safely eject the device before unplugging the usb.

2. If I cannot fix the database, I think I have to logout/login to recreate a new database (thus losing all my activity data): can I reimport the annotations, datestamps, reading positions / status (new, currently reading...) of the books on the device? I have added columns in Calibre for all the options available in the plugin and exported all of this information so at least in theory it is available. If yes, how?

Thanks in advance for any help you can give me. I haven't had this Kobo for very long and am unfamiliar with the various tricks and secrets of using it.

davidfor · 12-01-2021, 09:22 PM

As the database errors are in only indexes, I am surprise the compress did not fix it. What you can do is use a SQLite database management tool to rebuild the indexes. If you run the following, it should fix it:

Code:

REINDEX content_bookid_index;
REINDEX sqlite_autoindex_content_1;

Mister L · 12-02-2021, 11:10 AM

Thank you davidfor for this answer. I tried doing what you suggested (disclaimer: I have never worked with SQLite databases before). I installed SQLite tools (https://www.sqlite.org/download.html) and followed instructions on this page
https://www.sqlitetutorial.net/download-install-sqlite/

The first command seems to have worked, the second gave me an error message :

Spoiler:

After running these commands I again launched Calibre but I get the same errors.

When attempting to save the database at connection:

Spoiler:

Check database:

Spoiler:

Is there something obvious I've done wrong?

Quoth · 12-02-2021, 11:44 AM

Copy the database file to your PC.
Fix it there. Maybe delete the offending row.
Copy it back overwriting original.

Mister L · 12-02-2021, 02:36 PM

Quote:

Originally Posted by Quoth

Copy the database file to your PC.
Fix it there. Maybe delete the offending row.
Copy it back overwriting original.

Thanks, I tried doing it locally, but i still got the same error on the second command:

Spoiler:

When I tried opening the database in a GUI editor (SQLite Expert Personal) to delete the row 103877, possibly because I have no idea what I am doing, it made it worse:

Spoiler:

So I put back the original version from before I started tinkering with it and I am back to the original 1 error again.

I am not sure what I'm doing wrong, maybe this is too complicated for my (lack of) skill.

Plan B: if I logout/login (effectively deleting the database and creating a new one, if I've understood correctly), is there a way to import from Calibre to the Kobo my annotations, collections, reading positions / dates, and statuses? These are all saved in Calibre using the Kobo Utilities plugin.

Quoth · 12-02-2021, 03:30 PM

You need to rebuild the index(es) if you delete a row.

Collections can certainly be easily recreated from Calibre to the Kobo*.
@Davidfor would know about the other things.

I personally only care about annotations FROM the Kobo to PC via Kobo Utilities, which are saved in plain text files. You could note current % read for any open books. I did that for a couple of books the time I loaded an earlier backup of the database when it broke (rare). Obviously a backed up database has nothing about what you did since. The backup was renamed to the standard name on the kobo after the broken database was deleted. My backup names is <name of kobo, there are 3 here><date><version if more than one that day>

[* That and series is why 2 of the 3 main reasons I use a kobo not a Kindle]

PeterT · 12-02-2021, 08:01 PM

It's quite possible that if the index is damaged or the database lacks consistency that it will be impossible to delete a row.

Sent from my Pixel 4a using Tapatalk

davidfor · 12-02-2021, 10:48 PM

Quote:

Originally Posted by Mister L

Thanks, I tried doing it locally, but i still got the same error on the second command:

Spoiler:

When I tried opening the database in a GUI editor (SQLite Expert Personal) to delete the row 103877, possibly because I have no idea what I am doing, it made it worse:

Spoiler:

So I put back the original version from before I started tinkering with it and I am back to the original 1 error again.

I am not sure what I'm doing wrong, maybe this is too complicated for my (lack of) skill.

I think the main issue is the error in "sqlite_autoindex_content_1". The unique constraint error is because there are two rows in the "content" table with the same value in the "ContentID" column. That, of course, shouldn't happen. The content table contains the details of all the books on the device. There is one row for every book plus rows for each ToC entry in the books (with some complications). Removing the book that is causing the problem might fix it.

You should be able to find the book by running the SQL statement:

Code:

SELECT ContentId, COUNT(*)
FROM content
GROUP BY ContentId
HAVING COUNT(*) > 1

That will hopefully produce one result for the book causing the problem. The ContentId will tell us which book it is. It should start with "/mnt/onboard". If you are using calibre to sideload, then I'd expect it to look something like:

Code:

file:///mnt/onboard/Testing/Fussell, Paul/Great War and Modern Memory, The - Paul Fussell.kepub.epub

The title and author are in that and you can find it and delete it in the device. Or, find it in when the device is connected and delete it. If you do that, when you disconnect, the device should detect the missing book and remove it.

Quote:

Plan B: if I logout/login (effectively deleting the database and creating a new one, if I've understood correctly), is there a way to import from Calibre to the Kobo my annotations, collections, reading positions / dates, and statuses? These are all saved in Calibre using the Kobo Utilities plugin.

If you are saving the reading status, the can be restored to the device. Collections can be maintained through the driver. If you are doing this manually, then you can get the collections from the device and store them in calibre and then restore them later.

The annotations can be fetched using the Annotations plugin, but, they can't be restored. There isn't enough information stored to be able to do this. They are stored in the database and the table can be manually copied with a bit of work.

Mister L · 12-03-2021, 11:41 AM

Quote:

Originally Posted by Quoth

I personally only care about annotations FROM the Kobo to PC via Kobo Utilities, which are saved in plain text files.

Are they automatically exported? How do you do that? I thought I had somehow managed to configure something for that because I have 2 annotations .txt files in the kobo memory that I do not remember manually exporting, but only 2, when I have annotated many many more books than 2. I vaguely remember activating an option in the conf file (I think?) to allow exporting annotations but as far as I can tell it must be done manually from inside the book and if you open a book to export the annotations the datestamp is reset which ruins my sort order so it's not ideal. Plus I have to remember to do it.

Quote:

Originally Posted by Quoth

You could note current % read for any open books. I did that for a couple of books the time I loaded an earlier backup of the database when it broke (rare). Obviously a backed up database has nothing about what you did since.

I think / hope I have noted current % read for open books, using the plugin. I also have saved "date" but I think that only concerns the date the book was opened, not the date it was sent to the device, unfortunately. I open books sometimes to move them to the top of the list, and I also deliberately don't finish a book to the last page sometimes if there is something in it I want to remember to check. Plus the books I am actually currently reading. So I don't want to lose that information if I can avoid it.

Quote:

Originally Posted by PeterT

It's quite possible that if the index is damaged or the database lacks consistency that it will be impossible to delete a row.

It might be user error. I am attempting to learn how to manage databases as I go along here so I am just blindly trying things and hoping they work. But certainly some / a lot of things are not working as I would expect. Almost nothing is working in fact. :/

Quote:

Originally Posted by davidfor

I think the main issue is the error in "sqlite_autoindex_content_1". The unique constraint error is because there are two rows in the "content" table with the same value in the "ContentID" column. That, of course, shouldn't happen. The content table contains the details of all the books on the device. There is one row for every book plus rows for each ToC entry in the books (with some complications). Removing the book that is causing the problem might fix it.

You should be able to find the book by running the SQL statement:

Code:

SELECT ContentId, COUNT(*)
FROM content
GROUP BY ContentId
HAVING COUNT(*) > 1

That will hopefully produce one result for the book causing the problem. The ContentId will tell us which book it is. It should start with "/mnt/onboard". If you are using calibre to sideload, then I'd expect it to look something like:

Code:

file:///mnt/onboard/Testing/Fussell, Paul/Great War and Modern Memory, The - Paul Fussell.kepub.epub

The title and author are in that and you can find it and delete it in the device. Or, find it in when the device is connected and delete it. If you do that, when you disconnect, the device should detect the missing book and remove it.

That makes sense. I tried running the statement you gave me but the result said "no data to display" (see screencap attached). I don't know why. There very clearly should be data to display.

I accidentally ran it once halfway through (only the first 2 lines) and that did give one result, so I deleted that book and all associated files with the same filename (some images, and annotations file...), but this did not fix the problem.

Quote:

Originally Posted by davidfor

If you are saving the reading status, the can be restored to the device. Collections can be maintained through the driver. If you are doing this manually, then you can get the collections from the device and store them in calibre and then restore them later.

The annotations can be fetched using the Annotations plugin, but, they can't be restored. There isn't enough information stored to be able to do this. They are stored in the database and the table can be manually copied with a bit of work.

I set the plugin to save everything there was an option for (4 columns) so if reading status is one of those it is saved. I know Collections are saved. Is it possible to restore the datestamp as well, for sorting by "Recent"? I have the Annotations plugin, it saves the annotations to a custom column, but I would prefer to have them on the device as well ideally. How complicated is it to manually copy a table?

Today the problem is worse:
Database check

Spoiler:

I don't know why, and although I copied the database from yesterday with only the one error back to the device the new errors reappear each time after I eject / reconnect.

At this point I am not sure it is possible for me given my lack of database skills to correct this problem. I have not managed to do any of the things suggested, the problem is getting worse, and I am getting pretty frustrated. I think it's probably best if I try to salvage as much as I can and just start fresh with a new database, much as it annoys me.

This is my first experience with a device that uses a database, my previous devices (Sony and Cybook) did not work that way as far as I know, and although I have been mostly happy with the Kobo so far this is really putting a damper on my enthusiasm, I never had any problem even remotely like this with the others.

davidfor · 12-05-2021, 08:11 AM

Quote:

Originally Posted by Mister L

Are they automatically exported? How do you do that? I thought I had somehow managed to configure something for that because I have 2 annotations .txt files in the kobo memory that I do not remember manually exporting, but only 2, when I have annotated many many more books than 2. I vaguely remember activating an option in the conf file (I think?) to allow exporting annotations but as far as I can tell it must be done manually from inside the book and if you open a book to export the annotations the datestamp is reset which ruins my sort order so it's not ideal. Plus I have to remember to do it.

None of the annotation export options are automatic. You have to run them either on the device or when the device is connected to a PC. The export annotations on the device can be run from any of the book lists. Long-press on the book and it should be on the menu.

Quote:

I think / hope I have noted current % read for open books, using the plugin. I also have saved "date" but I think that only concerns the date the book was opened, not the date it was sent to the device, unfortunately. I open books sometimes to move them to the top of the list, and I also deliberately don't finish a book to the last page sometimes if there is something in it I want to remember to check. Plus the books I am actually currently reading. So I don't want to lose that information if I can avoid it.

If you have all the columns configured in the book status fetch, then you can restore the status and the reading position. Which includes the last read date. That is the case for both read and unread books. But, if you are opening the book and then marking it as unread to get it to the top of the "Recent" sort, then that is not stored.

Quote:

It might be user error. I am attempting to learn how to manage databases as I go along here so I am just blindly trying things and hoping they work. But certainly some / a lot of things are not working as I would expect. Almost nothing is working in fact. :/

That makes sense. I tried running the statement you gave me but the result said "no data to display" (see screencap attached). I don't know why. There very clearly should be data to display.

I accidentally ran it once halfway through (only the first 2 lines) and that did give one result, so I deleted that book and all associated files with the same filename (some images, and annotations file...), but this did not fix the problem.

After I posted the SQL statement, it occurred to me that it might not give the desired result because the index was corrupt. The query is probably optimised to use the index, which would give the wrong result.

Quote:

I set the plugin to save everything there was an option for (4 columns) so if reading status is one of those it is saved. I know Collections are saved. Is it possible to restore the datestamp as well, for sorting by "Recent"? I have the Annotations plugin, it saves the annotations to a custom column, but I would prefer to have them on the device as well ideally. How complicated is it to manually copy a table?

There are no tools that I know of that export the annotations in a way that can be imported again. You can be done by exporting the table and importing it into a new database.

For the "Recent" sort, if I populate the device again, I use the "Update metadata in the device library" to set the "Date Added" to one of the dates in the calibre library. If you are opening and then marking books as unread, it won't help.

Quote:

Today the problem is worse:
Database check

Spoiler:

I don't know why, and although I copied the database from yesterday with only the one error back to the device the new errors reappear each time after I eject / reconnect.

At this point I am not sure it is possible for me given my lack of database skills to correct this problem. I have not managed to do any of the things suggested, the problem is getting worse, and I am getting pretty frustrated. I think it's probably best if I try to salvage as much as I can and just start fresh with a new database, much as it annoys me.

This is my first experience with a device that uses a database, my previous devices (Sony and Cybook) did not work that way as far as I know, and although I have been mostly happy with the Kobo so far this is really putting a damper on my enthusiasm, I never had any problem even remotely like this with the others.

At this point, I'm not sure if it can be fixed. If I had a copy of the database, I could try. It would involve dropping the indexes, finding the duplicates as above followed by some cleanup and then recreating the indexes. That should work, but, there is a lot to do.

Mister L · 12-06-2021, 11:11 AM

Quote:

Originally Posted by davidfor

None of the annotation export options are automatic. You have to run them either on the device or when the device is connected to a PC. The export annotations on the device can be run from any of the book lists. Long-press on the book and it should be on the menu.

Okay, that's what I thought but I don't think I had noticed I could export them without opening the book (and changing the read date), so that is useful information. I'll have to go through all the "finished" books and export them all and then try and remember to do that immediately from now on.

Quote:

Originally Posted by davidfor

If you have all the columns configured in the book status fetch, then you can restore the status and the reading position. Which includes the last read date. That is the case for both read and unread books. But, if you are opening the book and then marking it as unread to get it to the top of the "Recent" sort, then that is not stored.

That is good news. I'm not marking the opened books as "unread", I just leave them in the "currently reading" list until I get to them. The only thing missing now would be the date first imported but maybe I can figure out something with the dates in Calibre even if it's not a perfect match.

Quote:

Originally Posted by davidfor

After I posted the SQL statement, it occurred to me that it might not give the desired result because the index was corrupt. The query is probably optimised to use the index, which would give the wrong result.

Ha right, that makes sense. I actually am glad to know that since if it's down to the broken database it's not me who couldn't manage to do it correctly.

Quote:

Originally Posted by davidfor

There are no tools that I know of that export the annotations in a way that can be imported again. You can be done by exporting the table and importing it into a new database.

Yes, I understood that. I am just wondering if it's very complicated to export the table / import it to a new database (i.e. worth me trying to figure it out or way beyond what I could conceivably manage).

Quote:

Originally Posted by davidfor

For the "Recent" sort, if I populate the device again, I use the "Update metadata in the device library" to set the "Date Added" to one of the dates in the calibre library. If you are opening and then marking books as unread, it won't help.

That is a good option, it won't be a perfect match but it is better than having all the books with the same add date. As long as the date added doesn't override the reading date / status (like I said, I don't mark the books as unread after I open them, I just leave them in the reading list) that is a big improvement to just lumping them all together on the same date.

Quote:

Originally Posted by davidfor

At this point, I'm not sure if it can be fixed. If I had a copy of the database, I could try. It would involve dropping the indexes, finding the duplicates as above followed by some cleanup and then recreating the indexes. That should work, but, there is a lot to do.

If that is an offer, I very much appreciate it

but since there is a lot to do, I wouldn't want to make you spend all that time after you've already spent so much time answering my questions (and thank you again for all the help).

A last question about annotations: when I export them to a .txt file, they wind up at the root of the device storage. Are they linked to the original epub file at all? Or can I move them to a folder without causing any problems? I don't like the mess.

davidfor · 12-07-2021, 04:53 AM

Quote:

Originally Posted by Mister L

Yes, I understood that. I am just wondering if it's very complicated to export the table / import it to a new database (i.e. worth me trying to figure it out or way beyond what I could conceivably manage).

You can export the table "Bookmarks" from one database and import it into the other. SQLite Expert Personal should have something to do that. If you have any purchased books, select just the sideloaded books and export them. These have the "ContentID" starting with "file://".

Quote:

That is a good option, it won't be a perfect match but it is better than having all the books with the same add date. As long as the date added doesn't override the reading date / status (like I said, I don't mark the books as unread after I open them, I just leave them in the reading list) that is a big improvement to just lumping them all together on the same date.

The date added and the last reading date are stored separately for each book. And I specifically wanted to be able to do this, so I made sure it worked.

Quote:

If that is an offer, I very much appreciate it

but since there is a lot to do, I wouldn't want to make you spend all that time after you've already spent so much time answering my questions (and thank you again for all the help).

A last question about annotations: when I export them to a .txt file, they wind up at the root of the device storage. Are they linked to the original epub file at all? Or can I move them to a folder without causing any problems? I don't like the mess.

In one of the recent firmware versions, Kobo changed to put the text file in a directory "Exported Annotations". But, there isn't any way to connect them other than the names. As you are using calibre, you could add the text file to the book in the library as a second format. Calibre will then maintain them together.

Mister L · 12-07-2021, 02:08 PM

Quote:

Originally Posted by davidfor

You can export the table "Bookmarks" from one database and import it into the other. SQLite Expert Personal should have something to do that. If you have any purchased books, select just the sideloaded books and export them. These have the "ContentID" starting with "file://".

I couldn't find how to do that in SQLite Expert Personal but I installed SQLiteStudio and DB Browser and it is possible to do it there. I made a test export in csv format and it worked fine (yay). I even was able to modify the bookmarks in the database (filepath) for one book which had been accidentally transferred at the root of the memory, so I could put it in the "books" folder where it belongs without losing the annotations.

However then I tried to import the exported table back to the database, just to check how it worked and I got that bleeding unique error again:

Code:

Error while importing data: UNIQUE constraint failed: Bookmark.BookmarkID

Is this because the database I'm importing into has that error (not a problem since I am not keeping it), or is the Bookmarks table itself corrupted (problem)? I tried to sort the csv file by BookmarkID and then skim quickly the file to see if I could find the duplicate and I didn't see one (using a proportional-width font so that 2 same IDs would be easy(-ier) to see) but that doesn't mean it definitely isn't there.

Quote:

Originally Posted by davidfor

The date added and the last reading date are stored separately for each book. And I specifically wanted to be able to do this, so I made sure it worked.

Brilliant, thank you for that.

Quote:

Originally Posted by davidfor

In one of the recent firmware versions, Kobo changed to put the text file in a directory "Exported Annotations". But, there isn't any way to connect them other than the names. As you are using calibre, you could add the text file to the book in the library as a second format. Calibre will then maintain them together.

Oh excellent, that's a great idea, I hadn't thought of adding them in Calibre.

I must not have the latest firmware because I don't have the "Exported Annotations" folder, should I update first? Will the database error be affected one way or another if I do?

davidfor · 12-07-2021, 10:48 PM

Quote:

Originally Posted by Mister L

I couldn't find how to do that in SQLite Expert Personal but I installed SQLiteStudio and DB Browser and it is possible to do it there. I made a test export in csv format and it worked fine (yay). I even was able to modify the bookmarks in the database (filepath) for one book which had been accidentally transferred at the root of the memory, so I could put it in the "books" folder where it belongs without losing the annotations.

That's good.

Quote:

However then I tried to import the exported table back to the database, just to check how it worked and I got that bleeding unique error again:

Code:

Error while importing data: UNIQUE constraint failed: Bookmark.BookmarkID

Is this because the database I'm importing into has that error (not a problem since I am not keeping it), or is the Bookmarks table itself corrupted (problem)? I tried to sort the csv file by BookmarkID and then skim quickly the file to see if I could find the duplicate and I didn't see one (using a proportional-width font so that 2 same IDs would be easy(-ier) to see) but that doesn't mean it definitely isn't there.

As the error is happening while importing the data, it will be because the BookmarkID is already in the table and you are adding a second. The BookmarkID is generated for each entry and looks to be a GUID. Generating the same on two devices would be pretty surprising. It is most likely for a purchased book. These are synced between the devices. In the CSV, the entries for the sideloaded books will have the VolumeID starting with "file:///". If the VolumeID does not start with that, it is for a purchased book. If you delete all the entries in the file that are not for your sideloaded books, then it should remove the potential duplicates.

Quote:

Oh excellent, that's a great idea, I hadn't thought of adding them in Calibre.

I must not have the latest firmware because I don't have the "Exported Annotations" folder, should I update first? Will the database error be affected one way or another if I do?

Updating the firmware won't affect this error. But, there is a slight chance that it could cause problems with the upgrade. Some firmware upgrades alter the tables by adding columns. It's possible that this could fail because of the error. It would depend on what firmware you are currently running and hence what changes need to be made. As the error is with the content table, it could be a problem if this needs to be changed. I'd probably wait to do the upgrade until you sort everything else out.

Mister L · 12-09-2021, 02:52 PM

Quote:

Originally Posted by davidfor

As the error is happening while importing the data, it will be because the BookmarkID is already in the table and you are adding a second. The BookmarkID is generated for each entry and looks to be a GUID. Generating the same on two devices would be pretty surprising. It is most likely for a purchased book. These are synced between the devices. In the CSV, the entries for the sideloaded books will have the VolumeID starting with "file:///". If the VolumeID does not start with that, it is for a purchased book. If you delete all the entries in the file that are not for your sideloaded books, then it should remove the potential duplicates.

This is my fault, I tried to import data into a table which already contained the same data... I thought it would be replaced. A bit stupid of me. I deleted all the data from the table and then imported it again and then it worked perfectly.

Quote:

Originally Posted by davidfor

Updating the firmware won't affect this error. But, there is a slight chance that it could cause problems with the upgrade. Some firmware upgrades alter the tables by adding columns. It's possible that this could fail because of the error. It would depend on what firmware you are currently running and hence what changes need to be made. As the error is with the content table, it could be a problem if this needs to be changed. I'd probably wait to do the upgrade until you sort everything else out.

Thanks I will wait then.

I think now I've got everything figured out that I want to do.

Am I missing any steps?

verify all books on device are matched to books in Calibre library ; if necessary, match missing books
eject device so Kobo can update the database
export tables in csv using SQLiteStudio : Bookmarks, Shelf, ShelfContent, WordList (done)
backup (manual copy) damaged database just in case (done)
make a copy of the entire Books partition just in case, VERY IMPORTANT FOR CUSTOM DICTIONARIES, etc. (done)
add tag in Calibre to all books on device so I know which ones to restore just in case
on device, logout then login
(if necessary) send books from Calibre to device
using the plugin, update the metadata of the books on the device (read dates/status, sent date, collections...)
import the saved bookmarks, etc. to the table from the csv file using SQLiteStudio
copy back custom dictionaries, Kobo eReader.conf file (word list), analytics.conf

Thank you again for all your help, I really appreciate it.

12-01-2021, 03:24 PM	#1
Mister L Groupie Posts: 179 Karma: 91148 Join Date: Jun 2010 Device: Sony 350	How to fix a (minor?) database error? Kobo Libra H2O, firmware 4.24.15676 I only side-load books, either using Calibre or manually in the file explorer (for testing purposes), and never activate the wifi. I do not read CBR / CBZ format books. I am using the Kobo Utilities Calibre plugin to backup my database every time I connect to Calibre. Recently I started getting an error which prevents the backup: "Failed to backup the database". Spoiler: calibre, version 5.24.0 (win32, embedded-python: True) Echec à la sauvegarde de la base de données du dispositif: Échoué: Sauvegarde de la base de données du dispositif Kobo en cours * in database main * On tree page 56787 cell 0: Rowid 103877 out of order wrong # of entries in index content_bookid_index wrong # of entries in index sqlite_autoindex_content_1 Traceback (most recent call last): File "calibre\gui2\device.py", line 89, in run File "calibre_plugins.koboutilities.jobs", line 200, in do_device_database_backup Exception: * in database main * On tree page 56787 cell 0: Rowid 103877 out of order wrong # of entries in index content_bookid_index wrong # of entries in index sqlite_autoindex_content_1 I am still able to load books to the device and Calibre can read all the books already on it, and the plugin appears to continue to update the reading positions, which is why I think it must be a relatively minor error. But series information does not seem to be written. And I would prefer to have an up-to-date backup. The first time it happened I was in a hurry and everything but the backup seemed to work fine so I crossed my fingers it would resolve itself. It has not. But in the meantime I have read quite a lot, made lots of annotations, and added several books which means at this point the last backup is quite out of date. After checking the forum I have tried: - Compact the database: Spoiler: calibre, version 5.24.0 Utilitaires Kobo - Compactage de la base de données du dispositif: La base de données du dispositif a été compactée. Taille originale = 167.294MB Taille compactée = 158.503MB - check database: Spoiler: Résultat de l'exécution de la commande 'PRAGMA integrity_check' sur la base de données du dispositif Kobo: * in database main * On tree page 56787 cell 0: Rowid 103877 out of order wrong # of entries in index content_bookid_index wrong # of entries in index sqlite_autoindex_content_1 I would really like to avoid losing my notes, reading position, and datestamps of loaded / read because that is my preferred sort order, so I have not logged out. Questions: 1. Can I fix the error(s) in the database somehow? (How?) 1a. What could have caused this error (how to avoid it in future)? I always safely eject the device before unplugging the usb. 2. If I cannot fix the database, I think I have to logout/login to recreate a new database (thus losing all my activity data): can I reimport the annotations, datestamps, reading positions / status (new, currently reading...) of the books on the device? I have added columns in Calibre for all the options available in the plugin and exported all of this information so at least in theory it is available. If yes, how? Thanks in advance for any help you can give me. I haven't had this Kobo for very long and am unfamiliar with the various tricks and secrets of using it.

12-01-2021, 09:22 PM	#2
davidfor Grand Sorcerer Posts: 24,905 Karma: 47303824 Join Date: Jul 2011 Location: Sydney, Australia Device: Kobo:Touch,Glo, AuraH2O, GloHD,AuraONE, ClaraHD, Libra H2O; tolinoepos	As the database errors are in only indexes, I am surprise the compress did not fix it. What you can do is use a SQLite database management tool to rebuild the indexes. If you run the following, it should fix it: Code: REINDEX content_bookid_index; REINDEX sqlite_autoindex_content_1;

12-02-2021, 11:10 AM	#3
Mister L Groupie Posts: 179 Karma: 91148 Join Date: Jun 2010 Device: Sony 350	Thank you davidfor for this answer. I tried doing what you suggested (disclaimer: I have never worked with SQLite databases before). I installed SQLite tools (https://www.sqlite.org/download.html) and followed instructions on this page https://www.sqlitetutorial.net/download-install-sqlite/ The first command seems to have worked, the second gave me an error message : Spoiler: sqlite> .cd H:\.kobo sqlite> .open KoboReader.sqlite sqlite> REINDEX content_bookid_index; sqlite> REINDEX sqlite_autoindex_content_1; Error: stepping, UNIQUE constraint failed: content.ContentID (19) sqlite> After running these commands I again launched Calibre but I get the same errors. When attempting to save the database at connection: Spoiler: calibre, version 5.24.0 (win32, embedded-python: True) Echec à la sauvegarde de la base de données du dispositif: Échoué: Sauvegarde de la base de données du dispositif Kobo en cours * in database main * On tree page 56787 cell 0: Rowid 103877 out of order row 81795 missing from index content_bookid_index wrong # of entries in index sqlite_autoindex_content_1 Traceback (most recent call last): File "calibre\gui2\device.py", line 89, in run File "calibre_plugins.koboutilities.jobs", line 200, in do_device_database_backup Exception: * in database main * On tree page 56787 cell 0: Rowid 103877 out of order row 81795 missing from index content_bookid_index wrong # of entries in index sqlite_autoindex_content_1 Check database: Spoiler: Résultat de l'exécution de la commande 'PRAGMA integrity_check' sur la base de données du dispositif Kobo: * in database main * On tree page 56787 cell 0: Rowid 103877 out of order row 81795 missing from index content_bookid_index wrong # of entries in index sqlite_autoindex_content_1 Is there something obvious I've done wrong?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Fix Kobo corrupt database	Chris Jones	Related Tools	4	10-06-2014 12:19 AM
Minor Fix For Calibre?	Marcs1102	Library Management	7	02-16-2014 11:30 PM
How to manually fix a (probably) corrupted database?	Lucas Malor	Kobo Reader	13	05-09-2013 08:01 PM
minor date error	nello	Calibre	1	01-02-2011 01:17 PM
Minor bug fix release now available?	borisb	enTourage Archive	6	05-24-2010 03:05 PM

12-02-2021, 11:44 AM	#4
Quoth Still reading Posts: 14,033 Karma: 105092227 Join Date: Jun 2017 Location: Ireland Device: All 4 Kinds: epub eink, Kindle, android eink, NxtPaper	Copy the database file to your PC. Fix it there. Maybe delete the offending row. Copy it back overwriting original.

12-02-2021, 03:30 PM	#6
Quoth Still reading Posts: 14,033 Karma: 105092227 Join Date: Jun 2017 Location: Ireland Device: All 4 Kinds: epub eink, Kindle, android eink, NxtPaper	You need to rebuild the index(es) if you delete a row. Collections can certainly be easily recreated from Calibre to the Kobo. @Davidfor would know about the other things. I personally only care about annotations FROM the Kobo to PC via Kobo Utilities, which are saved in plain text files. You could note current % read for any open books. I did that for a couple of books the time I loaded an earlier backup of the database when it broke (rare). Obviously a backed up database has nothing about what you did since. The backup was renamed to the standard name on the kobo after the broken database was deleted. My backup names is <name of kobo, there are 3 here><date><version if more than one that day> [ That and series is why 2 of the 3 main reasons I use a kobo not a Kindle]

12-02-2021, 08:01 PM	#7
PeterT Grand Sorcerer Posts: 13,515 Karma: 78910202 Join Date: Nov 2007 Location: Toronto Device: Libra H2O, Libra Colour	It's quite possible that if the index is damaged or the database lacks consistency that it will be impossible to delete a row. Sent from my Pixel 4a using Tapatalk

Advert

Advert