SQLITE_READONLY Error Code Not Persisting via sqlite3_errcode After Failed Write Operations

Error Code Retention Failure in sqlite3_errcode After SQLITE_READONLY Errors

Issue Overview: SQLITE_READONLY Error Code Reset to SQLITE_OK Prematurely

When working with SQLite databases in version 3.35.5, developers may encounter a scenario where the sqlite3_errcode() and sqlite3_extended_errcode() APIs return SQLITE_OK (0) instead of retaining the SQLITE_READONLY (8) error code after a failed write operation. This behavior deviates from prior versions (e.g., 3.32.1), where these APIs correctly preserved the last error code until explicitly cleared. The issue manifests specifically during write operations targeting read-only database files, such as attempting to execute BEGIN EXCLUSIVE TRANSACTION via sqlite3_backup_step() or other write-oriented APIs. While other error codes (e.g., SQLITE_BUSY, SQLITE_LOCKED) persist as expected until new operations occur, SQLITE_READONLY appears to be cleared prematurely. This creates challenges for error-handling logic that relies on accurate historical error codes for debugging, logging, or conditional recovery workflows.

The problem arises from a combination of SQLite’s internal error code management logic and changes introduced between versions 3.32.1 and 3.35.5. When a database connection (sqlite3* db_instance) is opened in read-only mode (either explicitly via flags or implicitly due to file system permissions), any write operation triggers an immediate SQLITE_READONLY error. However, subsequent calls to sqlite3_errcode(db_instance) return SQLITE_OK instead of reflecting the prior error. This contradicts SQLite’s documented behavior, where error codes remain set until overwritten by subsequent API calls or explicitly reset. The inconsistency suggests a regression in how specific error conditions are retained within the connection’s state machine, particularly for errors related to write permissions.

Key observations include:

• The issue is isolated to SQLITE_READONLY; other error codes persist as expected.
• The problem occurs when using sqlite3_backup_step() with operations requiring exclusive transactions, but it may also affect direct write APIs like sqlite3_exec() or sqlite3_step().
• Version 3.32.1 correctly retains SQLITE_READONLY, indicating a behavioral change in later releases.
• The premature resetting of the error code disrupts applications that inspect historical errors after failure points, leading to misdiagnosis of root causes.

Possible Causes: Internal Error State Management and Version-Specific Regressions

The premature reset of SQLITE_READONLY to SQLITE_OK stems from one or more underlying factors in SQLite’s error-handling pipeline. These include changes to the backup API’s transaction management logic, alterations in how read-only errors are classified, or optimizations that inadvertently clear error states under specific conditions.

1. Transaction Rollback Logic in Backup Operations
The sqlite3_backup_step() function initiates a backup process that may require exclusive transactions. When targeting a read-only database, the attempt to start an exclusive transaction fails with SQLITE_READONLY. However, SQLite’s backup API might implicitly handle this failure by rolling back internal state changes, inadvertently resetting the error code. In versions prior to 3.35.5, the error code might have been preserved longer due to differences in rollback cleanup logic. Changes in transaction lifecycle management between versions could explain why the error code is now cleared prematurely.

2. Error Code Classification and Subcode Handling
SQLite differentiates between primary error codes (e.g., SQLITE_READONLY) and extended error codes (e.g., SQLITE_READONLY_CANTLOCK). The sqlite3_extended_errcode() API is designed to return these granular codes, while sqlite3_errcode() strips subcodes to return the base error. A regression in how extended error codes are mapped to base codes could cause SQLITE_READONLY to be misclassified or reset after subcode extraction. For example, if the internal representation of SQLITE_READONLY is tied to a transient subcode that gets cleared after API calls, the base code might revert to SQLITE_OK.

3. Connection State Machine Resets
SQLite maintains a per-connection error state that updates with each API call. Certain operations, even those that fail, might reset the error code to SQLITE_OK as part of their cleanup routines. If a failed sqlite3_backup_step() call triggers such a reset, the subsequent sqlite3_errcode() would reflect SQLITE_OK instead of the actual error. This could occur if the backup API’s error-handling logic assumes that errors are transient and clears the state prematurely.

4. Version-Specific Optimizations or Bug Fixes
SQLite 3.35.0 introduced significant changes, including the RETURNING clause and improvements to the backup API. A bug fix or optimization in this version might have altered the timing of error state updates. For instance, a patch intended to resolve memory leaks or concurrency issues might have introduced a side effect where SQLITE_READONLY is handled differently than other errors.

Troubleshooting Steps, Solutions & Fixes: Diagnosing and Resolving Error Code Retention Issues

Step 1: Reproduce the Issue with a Minimal Test Case
Create a minimal example that isolates the problem. This involves:

• Opening a database file with read-only permissions (e.g., chmod 0444 test.db).
• Attempting a write operation via sqlite3_backup_step() or sqlite3_exec() with a BEGIN EXCLUSIVE transaction.
• Checking sqlite3_errcode() immediately after the failed operation.

Example code snippet:

sqlite3* db;
int rc = sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READONLY, NULL);
if (rc != SQLITE_OK) { /* handle error */ }

sqlite3_backup* backup = sqlite3_backup_init(db, "main", db, "main");
if (!backup) {
    // Check error code immediately after failure
    int err = sqlite3_errcode(db);
    printf("Error code after backup_init: %d\n", err); // Should be 8 (SQLITE_READONLY)
}

Step 2: Cross-Version Validation
Compile the test case against SQLite 3.32.1 and 3.35.5 to confirm the behavioral difference. This verifies whether the issue is indeed a regression introduced in the newer version.

Step 3: Inspect SQLite’s Source Code Changes
Review the SQLite changelog and source code diffs between 3.32.1 and 3.35.5, focusing on:

• Modifications to sqlite3_errcode() and sqlite3_extended_errcode().
• Changes in the backup API (sqlite3_backup_step(), sqlite3_backup_init()).
• Updates to transaction management logic, especially for exclusive transactions.

Key files to examine include main.c, backup.c, and error.c.

Step 4: Analyze Error State Lifetime
SQLite’s error codes are stored in the sqlite3 struct’s errCode field. Trace how this field is updated during failed write operations. In particular, identify whether SQLITE_READONLY is being overwritten by subsequent internal API calls that reset the error code to SQLITE_OK.

Step 5: Workarounds and Mitigations
If the issue stems from a regression, consider these temporary fixes:

• Use sqlite3_extended_errcode(): This API might retain the extended error code even if the base code is reset.
• Capture Errors Immediately: Store the error code in application memory right after the failing operation, before any subsequent API calls.
• Downgrade to 3.32.1: If feasible, revert to a version where the error code persists correctly.

Step 6: Patch SQLite Source Code
For advanced users, modify SQLite’s source to prevent premature clearing of SQLITE_READONLY:

1. Locate the code responsible for resetting db->errCode after read-only errors.
2. Add conditional checks to preserve SQLITE_READONLY until explicitly overwritten.

Example patch in error.c:

// Modify sqlite3ApiExit to prevent resetting SQLITE_READONLY
if (db->errCode != SQLITE_READONLY) {
    db->errCode = rc;
}

Step 7: Report the Issue to SQLite Maintainers
File a detailed bug report with the SQLite team, including the test case, version comparisons, and source code analysis. This accelerates the official fix’s release.

Final Solution: Update to a Patched SQLite Version
Once the SQLite team addresses the regression, upgrade to the fixed version (e.g., 3.35.6 or later). Monitor the SQLite website and changelogs for updates.