Sqlite drop table error: database or disk is full

user_7128910 0
Viewed 325

I have a SQLite db where I copied a table (~80gb) in order to add an autoincrement index to it. The resulting db has a size of 167GB. After successfully copying the table, I would now like to drop the original table. However, I get an error:

sqlite3.OperationalError: database or disk is full

This is my query:

drop table listens;

there is 18GB of space left on my disk and 32GB of RAM. As a newbie it seems unintuitive to me that the removal of a table would error out because of a lack of disk space. Any ideas what is happening and how I can fix it?

sqlite
1 Answers

I suspect that SQLite wants to write the resulting DB in a temporary file first and temporarily needs the disk space to be able to do this.

Where this temporary file (or maybe multiple files) will be stored is taken from the variable SQLITE_TMPDIR (if set—I'm not sure about the default value, maybe /tmp). Set it to some directory which has approximately as much disk space free as the database is big. (Maybe it suffices if the free disk space is the DB size minus the size of to be deleted table.)

Code example (assuming you have this issue on a Linux or other unix-like system):

$ env SQLITE_TMPDIR=/mnt/tmp sqlite3 some.db
sqlite> drop table listens;

The actual directory will likely differ. I used /mnt/tmp in the example as that is a common temporary mount point, e.g. for an empty USB disk, USB stick, LVM Logical Volume, etc.

user_7931720
Related