The SONY database is corrupted

[tetanos]

Very nasty, when I connect a SONY PRS-t1, PRST2 or PRST3 I get in 50% of the cases the error:

calibre, version 1.3.0
ERREUR : Erreur: Erreur pendant la communication avec l'appareil connecté

The SONY database is corrupted.  Delete the file /Volumes/READER/Sony_Reader/database/books.db on your reader and then disconnect  reconnect it. If you are using an SD card, you  should delete the file on the card as well. Note that  deleting this file will cause your reader to forget  any notes/highlights, etc. Underlying error:
Traceback (most recent call last):
  File "site-packages/calibre/devices/prst1/driver.py", line 293, in remove_orphaned_records
DatabaseError: database disk image is malformed


Traceback (most recent call last):
  File "site-packages/calibre/gui2/device.py", line 85, in run
  File "site-packages/calibre/gui2/device.py", line 476, in _books
  File "site-packages/calibre/devices/prst1/driver.py", line 163, in books
  File "site-packages/calibre/devices/usbms/driver.py", line 248, in books
  File "site-packages/calibre/devices/prst1/driver.py", line 246, in sync_booklists
  File "site-packages/calibre/devices/prst1/driver.py", line 276, in update_device_database
  File "site-packages/calibre/devices/prst1/driver.py", line 320, in remove_orphaned_records
DeviceError: The SONY database is corrupted.  Delete the file /Volumes/READER/Sony_Reader/database/books.db on your reader and then disconnect  reconnect it. If you are using an SD card, you  should delete the file on the card as well. Note that  deleting this file will cause your reader to forget  any notes/highlights, etc. Underlying error:
Traceback (most recent call last):
  File "site-packages/calibre/devices/prst1/driver.py", line 293, in remove_orphaned_records
DatabaseError: database disk image is malformed

This makes calibre unusable for SONY PRS devices.
If I have a look on [URL="http://bazaar.launchpad.net/~kovid/calibre/trunk/view/head:/src/calibre/devices/prst1/driver.py"/devices/prst1/driver.py[/URL] I have the impression that the code should be able to continue to work even if remove_orphaned_records generates an exception.

When I let rebuild the books.db, the SONY PRS generates empty collections/collection database entries. I have added the two databases for an investigation.

[kovidgoyal]

Try hard resetting your SONY or reformatting the memory, there's no way the code in the driver can cause a corrupted database on a properly functioning device.

[tetanos]

Originally Posted by kovidgoyal

Try hard resetting your SONY or reformatting the memory,

Again the procedure to avoid the problem:

1. delete books.db
2. disconnect the device
3. wait until the books.db get regenerated
4. reconnect the device

Actually I need to do this each time I want to use calibre. With the drawback that all notes and settings are lost. This is not reliable.

Originally Posted by kovidgoyal

there's no way the code in the driver can cause a corrupted database on a properly functioning device.

This is not true: the database of the SONY device is not corrupt because I can view it with an sqlite viewing tool and the SONY Reader software has no problem to handle the books.db. So they are no reasons why calibre should not be able to handle it.

The only problem is that the database cleanup fails and here the question is why is this cleanup necessary?
Can we skip it? If this is the case then it make sense not to generate an exception on a cleanup failure.

I will do next week try to generate calibre from the sources and investigate more this issue. I will then put my remarks or patch in this forum.

[DoctorOhh]

Originally Posted by tetanos

Again the procedure to avoid the problem:

1. delete books.db
2. disconnect the device
3. wait until the books.db get regenerated
4. reconnect the device

This doesn't avoid the underlying problem that most likely exists with either your device or your computer. Have you tried reformatting your internal memory as suggested? Maybe you can solve your problem instead of simply trying to avoid the problem.

Originally Posted by tetanos

Actually I need to do this each time I want to use calibre. With the drawback that all notes and settings are lost. This is not reliable.

Calibre and Sony devices are very reliable together, the problem you are experiencing is most likely unique to your setup or equipment.

Originally Posted by tetanos

I will do next week try to generate calibre from the sources and investigate more this issue. I will then put my remarks or patch in this forum.

Since 10s of thousands (a complete SWAG) of folks use calibre with PRS-T2 devices without error I'll be interested in to see what you find.

[tetanos]

Originally Posted by DoctorOhh

Since 10s of thousands (a complete SWAG) of folks use calibre with PRS-T2 devices I'll be interested in to see what you find.

I have already find something with is not correct in this function:

1. The cleanup function delete some database entries
2. If one of the deletion fails then an exception occurs

So it is possible that books.db get partially cleanup when a failure occurs.
This is not really good: it would be better to do backup of books.db before the cleanup and revert to the backup once the operation fails.

I'm also not sure if the failure is not a temporary failure: books.db is generated in background by the device. So it is possible that I access to it while it is not completely generated. But it is wrong to alter it if we cannot process it. (and so made it corrupt for the device)

[kovidgoyal]

Originally Posted by tetanos

Again the procedure to avoid the problem:

1. delete books.db
2. disconnect the device
3. wait until the books.db get regenerated
4. reconnect the device

Again, for you

[kovidgoyal]

Originally Posted by tetanos

So it is possible that books.db get partially cleanup when a failure occurs.
This is not really good: it would be better to do backup of books.db before the cleanup and revert to the backup once the operation fails.

An exception that prevents further cleanup cannot cause a malformed database, I suggest you read up on how sqlite works if you dont understand why.

[DoctorOhh]

Originally Posted by tetanos

I have already find something with is not correct in this function:

I meant the previous post (quoted below) to read "Since 10s of thousands (a complete SWAG) of folks use calibre with PRS-T2 devices without error I'll be interested in to see what you find."

Originally Posted by DoctorOhh

Since 10s of thousands (a complete SWAG) of folks use calibre with PRS-T2 devices I'll be interested in to see what you find.

I have since updated the original post.

[tetanos]

Originally Posted by kovidgoyal

An exception that prevents further cleanup cannot cause a malformed database, I suggest you read up on how sqlite works if you dont understand why.

What you saying is wrong:

1. The first cursor.execute is executed
2. The second produces an exception

So the cleanup is partially executed on books.db.

But your reaction is for me suspicious: you simply say that your code work without investigating it. Googgle a little bit and you will see that I'm not alone with this issue, but since I'm able to reproduce it and I plan to investigate some time on it you should be happy to give me some advices on your code. This is obviously not the case!

    def remove_orphaned_records(self, connection, dbpath):
        from sqlite3 import DatabaseError

        try:
            cursor = connection.cursor()

            debug_print("Removing Orphaned Collection Records")

            # Purge any collections references that point into the abyss
            query = 'DELETE FROM collections WHERE content_id NOT IN (SELECT _id FROM books)'
            cursor.execute(query)  <---- this was executed
            query = 'DELETE FROM collections WHERE collection_id NOT IN (SELECT _id FROM collection)'
            cursor.execute(query) <---- location of the exception

        ...........
        except DatabaseError:
           .......

[kovidgoyal]

Sigh.

Let me spell it out for you:

The error you are getting is database disk image is malformed

That error means that sqlite thinks the database is corrupted

That cannot happen if a query fails because of a property of sqlite known as ACID, which means that no matter what failure occurs during a query, the database on disk cannot be corrupted, unless a filesystem operation fails.

Therefore, the only way that you can get that error is through one of the two following scenarios:

1) A bug in sqlite
2) A bug in the filesystem layer that accesses the db on your device

Neither of those are bugs in the t1 driver.

And just by the by, I did not write the t1 driver, nor do I maintain it. I am just trying to help you avoid wasting time looking for problems in the wrong place. But I'm done helping you, all the best in your endeavors.

[tetanos]

Originally Posted by kovidgoyal

Sigh.

That cannot happen if a query fails because of a property of sqlite known as ACID, which means that no matter what failure occurs during a query, the database on disk cannot be corrupted, unless a filesystem operation fails.

I do not think that the query create corrupt database, but since that one query was executed and the second one not, it is possible that the database get in a state that it will never been repaired by the device.

Anyway, next week I will have a try to debug it since I have the problem with all my devices and quite often. IT is certainly the best chance to find what is going wrong.

If I'm able to find something, you will see a get-merge request with a patch.