SQLite: disk I/O error

What it means

SQLite asked the operating system to read, write, sync, lock or resize a file, and the call failed in a way SQLite didn’t expect. It returns SQLITE_IOERR (code 10), “disk I/O error”. The file can be the database or one of its side files: the -journal, the -wal and -shm of a WAL database, or a temporary file.

The extended result code says which operation failed: SQLITE_IOERR_READ, SQLITE_IOERR_WRITE (778), SQLITE_IOERR_FSYNC, SQLITE_IOERR_SHMOPEN and many more. A full disk normally gives database or disk is full instead.

The statement fails. SQLite tries to undo only that statement, but an I/O error can also roll back the whole transaction.

Common causes

The storage went away or is failing: an external or USB drive unplugged or asleep, a network share that dropped, a disk with read errors.
The database is on a network share (SMB, NFS). Every read and write travels over the network and can fail when the network does. SQLite’s documentation warns that locking and syncing over a network are unreliable, and WAL mode needs every process using the database to be on the same computer.
The operating system won’t let the file grow: a disk quota, or a file-size limit on the process (ulimit -f). That’s what we reproduced below.

How to fix it

Find which operation failed

Read the extended code. In Python 3.11 and later it’s e.sqlite_errorname; in C, sqlite3_extended_errcode(db) (or turn extended codes on with sqlite3_extended_result_codes). READ, WRITE and FSYNC point at the storage; SHMOPEN, SHMSIZE and SHMMAP at the -shm file of a WAL database; LOCK at file locking.

Roll back, then retry once

After the error, run ROLLBACK so the connection is in a known state (it fails harmlessly if SQLite already rolled back). If the retry fails the same way, the cause is still there.

Check the drive and the folder

df -h /path/to/folder
ls -l /path/to/folder/app.db*

Make sure the volume is mounted and has space, that the database, its -wal and -shm files sit together, and that the process can write all of them. If the drive itself is suspect, check it with Disk Utility’s First Aid before writing anything else.

Keep the database on a local disk

Move the file off the network share. If several machines need the data, have one machine own the file and the others ask it, or use a database server.

Check file limits

ulimit -f

It should print unlimited. For services, also check the limits your process manager or container sets, and any disk quota.

Check the database afterwards

A failed write is normally rolled back cleanly, but after storage trouble run:

PRAGMA integrity_check;

ok means the file is sound. Anything else: see database disk image is malformed.

Reproduce it

macOS /usr/bin/sqlite3, SQLite 3.51.0. To make the operating system refuse a write without harming a disk, we limited the size of files the process may write, and ignored the signal that would otherwise stop it (without the trap, the shell killed sqlite3 silently):

bash -c 'trap "" XFSZ; ulimit -f 200; sqlite3 io.db "INSERT INTO logs (body) SELECT randomblob(1000) FROM generate_series(1, 1000);"'

Error: stepping, disk I/O error (10)

The shell adds “Error: stepping,” and the result code; SQLite’s message is disk I/O error. The insert was rolled back: logs still had no rows, and PRAGMA integrity_check printed ok. A smaller insert of 50 rows, which fitted under the limit, succeeded.

The same limit through Python’s sqlite3 module (SQLite 3.53.4) raised sqlite3.OperationalError: disk I/O error with sqlite_errorname SQLITE_IOERR_WRITE (778).

In Inlet

Inlet shows SQLite’s message when a statement fails this way. Once the storage is sorted out, run PRAGMA integrity_check; in a query tab before you carry on editing.

disk I/O error