SQLite Forum

Deleted rows not in FreeList

Deleted rows not in FreeList

(1) By anonymous on 2021-07-08 12:05:27 [link]


I'm trying to view deleted data from a SQLite DB.

I can view the deleted data from a DB given to me just fine but I can't seem to view additional data - so when I delete rows myself they aren't appearing in the free list. Although I can see the DB's headers change with a updated version??

(2) By Bill Wade (billwade) on 2021-07-08 19:59:52 in reply to 1 [link]

You may want to describe in more detail what you are doing (provide the SQL and/or other scripts that reproduce your actions), the results you are seeing, and the results you expect to see.

For instance, depending on your journal mode, even a committed transaction may not yet be in the main database file.

(3) By anonymous on 2021-07-09 08:45:54 in reply to 2 [link]


So my goal is to recover deleted rows from SQLite DB's that a particular application generates.

Some python code I've found on github: https://github.com/aramosf/recoversqlite has been useful as a ready made solution to do this.

That repo has a example DB with it in dumplite/personas.sqlite that has page(s) which are marked as in the freelist (ie the deleted rows).
If on that DB I run say:
DELETE FROM personas where nick = 'bruce';

then when rerunning that recoversqlite program I can see the DB's headers have an incremented version.
Obviously SELECT * FROM personas; hasn't the record anymore.

BUT - I'd have expected a deleted row to then also be in the freelist page(s) but it isn't?

If I create a new DB from scratch, insert some rows and then delete a subset - again freelist is empty.

pragma auto_vacuum is none, the default but I've also explicitly set it off as well. 
And when deleting rows I've confirmed that file size doesn't change so don't think its vacuuming.

So bit perplexed, all I really want is the ability to create some synthetic data where there is some deleted 'stuff' in the freelist pages that is available for my own code to test its recovery against.

(4) By Stephan Beal (stephan) on 2021-07-09 08:52:35 in reply to 3 [link]

> So my goal is to recover deleted rows from SQLite DB's that a particular application generates.

How sqlite deletes rows is an internal implementation detail which applications cannot sensibly rely upon. If you don't want it deleted, don't delete it. If your application didn't delete it, you've no business trying to recover it, nor any guaranty (from sqlite) that you can do so. A row deleted by sqlite must be treated like a block of memory which has been passed to `free()`: any use of it after that point has undefined behaviour.

(5) By Keith Medcalf (kmedcalf) on 2021-07-09 09:09:20 in reply to 3 [link]

The freelist contains a list of free **pages** not free **rows**.  If you delete a **row** from a **page** and there are other **non-deleted rows** on that same **page**, then the **page** is not free and hence is not moved to the list of free pages.

In the scenario where **deleting a row** does not **free the page** the only thing you can be sure of it that the **row which has been deleted** is no longer referenced in the **btree index** for the table.  Whether the **space that the row used to occupy** is now free, re-packed, or overwritten in whole or in part is an implementation detail over which you have no control (although you can enable the secure overwrite feature to overwrite the space the row used to occupy with gibberish thus obfuscating any prior contents).

(6) By anonymous on 2021-07-09 09:30:37 in reply to 5

Thank you - that makes perfect sense.