SQLITE_MISUSE (error code 21) indicates the SQLite library is being used incorrectly, typically from using finalized statements, accessing connections from multiple threads, or calling API functions in the wrong order.
SQLITE_MISUSE is SQLite result code 21, returned when the application uses any SQLite interface in a way that is undefined or unsupported. This error indicates a programming mistake rather than a runtime condition. Common scenarios include using a prepared statement after it has been finalized, calling SQLite functions with invalid or closed connection pointers, or attempting to use the same database connection simultaneously from multiple threads without proper synchronization. SQLite attempts to detect misuse probabilistically, but detection is not guaranteed. If your application ever returns SQLITE_MISUSE, it contains potentially serious bugs that must be fixed before deployment. This is not an error to catch and ignore—it requires code correction.
SQLite provides extended error information that can help pinpoint the exact cause. Enable it in your code:
sqlite3* db;
sqlite3_open("database.db", &db);
sqlite3_extended_result_codes(db, 1); // Enable extended codesCheck error messages immediately after operations:
int rc = sqlite3_step(stmt);
if (rc != SQLITE_DONE && rc != SQLITE_ROW) {
printf("Error: %s\n", sqlite3_errmsg(db));
printf("Error code: %d\n", rc);
}This helps identify which specific API call is being misused.
Ensure statements are not used after being finalized:
Incorrect:
sqlite3_stmt *stmt;
sqlite3_prepare_v2(db, "SELECT * FROM users", -1, &stmt, NULL);
sqlite3_step(stmt);
sqlite3_finalize(stmt);
sqlite3_step(stmt); // SQLITE_MISUSE - statement already finalized!Correct:
sqlite3_stmt *stmt;
sqlite3_prepare_v2(db, "SELECT * FROM users", -1, &stmt, NULL);
while (sqlite3_step(stmt) == SQLITE_ROW) {
// Process row
}
sqlite3_finalize(stmt);
// Do not use stmt after this pointFor reusable statements, use sqlite3_reset() instead of finalizing:
sqlite3_reset(stmt); // Reset for reuse
sqlite3_clear_bindings(stmt); // Clear previous bindings
// Now safe to bind new parameters and execute againSQLite connections are NOT thread-safe by default. Never share a connection across threads without synchronization.
Option 1: One connection per thread (recommended)
import threading
import sqlite3
thread_local = threading.local()
def get_connection():
if not hasattr(thread_local, 'conn'):
thread_local.conn = sqlite3.connect('database.db')
return thread_local.connOption 2: Enable serialized mode
// Compile with SQLITE_THREADSAFE=1 or 2
sqlite3_config(SQLITE_CONFIG_SERIALIZED);Option 3: Mutex protection
import threading
db_lock = threading.Lock()
with db_lock:
cursor.execute("INSERT INTO users VALUES (?, ?)", (name, email))
conn.commit()For .NET applications using Microsoft.Data.Sqlite:
// Create new connection per operation (pooling handles efficiency)
using (var connection = new SqliteConnection("Data Source=database.db"))
{
connection.Open();
// Perform operations
}Do not use connections that have been closed:
sqlite3* db;
sqlite3_open("database.db", &db);
// ... operations ...
sqlite3_close(db);
// db is now invalid - do not use it!
// If you need to reopen:
sqlite3_open("database.db", &db); // Get fresh connectionIn languages with automatic resource management, ensure proper scoping:
# Incorrect - connection closed before statement executes
with sqlite3.connect('db.sqlite') as conn:
cursor = conn.cursor()
cursor.execute("SELECT * FROM users") # MISUSE - connection closed!
# Correct
with sqlite3.connect('db.sqlite') as conn:
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()Ensure SQLite functions are called in the correct order:
Correct preparation and execution flow:
sqlite3_stmt *stmt;
// 1. Prepare
int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
if (rc != SQLITE_OK) {
// Handle error
return;
}
// 2. Bind parameters (if needed)
sqlite3_bind_text(stmt, 1, "value", -1, SQLITE_TRANSIENT);
// 3. Execute
while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
// Process results
}
// 4. Finalize
sqlite3_finalize(stmt);Do not skip steps or call them out of order. Always check return codes at each step.
Threading Modes: SQLite supports three threading modes configured at compile time: single-thread (SQLITE_THREADSAFE=0), multi-thread (SQLITE_THREADSAFE=2), and serialized (SQLITE_THREADSAFE=1). Check your build configuration with sqlite3_threadsafe() which returns 0, 1, or 2 corresponding to these modes. In multi-thread mode, connections cannot be shared between threads. In serialized mode, SQLite automatically handles locking, but performance may be impacted.
Connection Pooling: In high-concurrency applications, consider using connection pooling libraries that handle thread-local storage or provide thread-safe connection management. Most modern database wrappers (Python's sqlite3, .NET's Microsoft.Data.Sqlite) implement connection pooling automatically.
WAL Mode Considerations: When using Write-Ahead Logging (WAL mode with PRAGMA journal_mode=WAL), SQLite handles concurrent reads more efficiently, but SQLITE_MISUSE can still occur from improper API usage. WAL mode does not protect against misuse errors.
Debugging Strategy: Use tools like Valgrind, AddressSanitizer, or ThreadSanitizer to detect memory errors and race conditions that might cause SQLITE_MISUSE. Enable SQLite's built-in debugging with sqlite3_trace_v2() to log all SQL operations and identify where misuse occurs.
Production Implications: SQLITE_MISUSE should never occur in production. If it does, treat it as a critical bug requiring immediate investigation. Implement comprehensive error logging and monitoring to catch these errors during testing phases.
SQLITE_CORRUPT_VTAB: Content in virtual table is corrupt
Content in virtual table is corrupt
SQLITE_IOERR_WRITE: Disk I/O error during write
Disk I/O error during write operation
SQLITE_READONLY: Attempt to write a readonly database
How to fix "SQLITE_READONLY: Attempt to write a readonly database" in SQLite
SQLITE_CONSTRAINT_PRIMARYKEY: PRIMARY KEY constraint failed
How to fix "SQLITE_CONSTRAINT_PRIMARYKEY" in SQLite
SQLITE_READONLY_DBMOVED: Database file has been moved since opened
How to fix 'SQLITE_READONLY_DBMOVED: Database file has been moved since opened'