3

A couple days ago I've been working with an sqlite3 database and noticed that after deleting a lot of data (an entire table (DROP) to be exact) the file size did not change (the table used 5MB) at all. Not the database I'm asking about, but maybe this is relevant to my question.

I'm having a bunch of databases that I was looking through using grep -a for a specific message, I successfully found the database in question having 50MB in size, seems perfect! However, opening the database in an sqlite3 database viewer (or the sqlite3 command line tool) shows only a little over 800 entries in the messages table. The current auto increment index is above 18.000, which is the amount of entries the table should have. Going through the .db file with grep, everything I want seems to be there, but I'm guessing it's somehow "hidden"(?).

How can I retrieve ALL the data from an sqlite database? Note that .dump <table> does also not include the messages I'm looking for. For a quick explanation on this and why data doesn't actually get deleted/filesize won't shrink when I delete a 5MB table I'd be thankful too.

sqlite> .dbinfo
database page size:  4096
write format:        1
read format:         1
reserved bytes:      0
file change counter: 366
database page count: 11405
freelist page count: 10372
schema cookie:       2
schema format:       4
default cache size:  0
autovacuum top root: 0
incremental vacuum:  0
text encoding:       1 (utf8)
user version:        7
application id:      0
software version:    3008005
number of tables:    3
number of indexes:   1
number of triggers:  0
number of views:     0
schema size:         737

(Note that I did not create nor ever edited this database, it's Instagram's direct messages database.)

confetti
  • 1,062
  • 2
  • 12
  • 27
  • It seems like you are asking the wrong question. It seems to me you cannot retrieve data you delete, and searching for deleted messages does not make a lot of sense. Maybe you should try [How to vacuum sqlite database?](https://stackoverflow.com/q/18126997/608639) first. – jww Dec 25 '17 at 17:48
  • What I want to do is pretty much the opposite of vacuum, check my last comment on the accepted answer to see what I ended up doing now to fit my needs for this. I didn't delete the data myself, instagram just seems to delete messages very quickly from the cache again. Vacuum is definitely something I was interested in too though so thank you for the link! – confetti Dec 25 '17 at 18:07

1 Answers1

2

Deleted data is not immediately removed from the database file; see change sqlite file size after "DELETE FROM table".

As shown by the freelist page count, that file is mostly empty, and running VACUUM is likely to throw away the remains of the data you're searching for.

There is no easy way to recover deleted data (because some important management information is missing). If you are interested in only a small amount of data, try a hex editor.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • Thank you very much for your answer and great explanation. I'm a little confused by the "empty space" though, shouldn't that mean the deleted data is "gone" (or, at least not directly visible by `cat` or `grep -a`)? To me this looks like it is just deleting some sort of indexing/metadata/references, wouldn't it be possible to re-create that or at least recover the data without those in an easy way (doesn't have to actually restore the database how it was before deletion)? – confetti Dec 25 '17 at 14:51
  • "Empty" means that the database is not actually using the data. And all that deleted metadata is needed to find and interpret the data. – CL. Dec 25 '17 at 14:53
  • Thanks, that answered my question. To solve my issue with trying to get a database of my instagram messages I will make a script that dumps the database every minute (since I don't know how (often) instagram clears the cache database) while I'm scrolling through on my phone and afterwards merges the dumps and deletes duplicates. – confetti Dec 25 '17 at 15:02