Debugging 'Malformed Database Schema' in SQLite

I run a biotech investment and competitive intelligence tracking application. The app is a go app run on a bare-bones VPS with a SQLite database as the data store, dead simple.

I was recently working on a major rebuild of the database on a separate server (build-server). When I was satisfied, with the changes, I tried to deploy:

# on the build-server

sqlite3 rxdatalab.db "PRAGMA quick_check;"
# quick_check
# -----------
# ok
sqlite3 rxdatalab.db ".tables"
# list of tables...
# copy to s3:

aws s3 cp rxdatalab.db s3://bucket/rxdatalab.db.new
# OK

Then, copy to my prod server, and start the app:

# prod server
systemctl stop rxdatalab.service
# backup and remove current db, then

aws s3 cp s3://bucket/rxdatalab.db.new /var/lib/db/rxdatalab.db

# OK
systemctl start rxdatalab.service

But then… 404, no response from my website. Checking the logs:

journalctl -u rxdatalab.service

systemd[1]: rxdatalab.service: Consumed 3.348s CPU time.
systemd[1]: Started rxdatalab.service - Rxdatalab Service.
rxdatalab[16309]: Config loaded:
rxdatalab[16309]:   Database: /var/lib/db/rxdatalab.db
rxdatalab[16309]:   Debug Mode: false
rxdatalab[16309]:   Session Timeout: 168h0m0s
rxdatalab[16309]:   Session Cleanup Interval: 1h0m0s
rxdatalab[16309]:   Secure Cookies: true
rxdatalab[16309]: [INFO] Running in PRODUCTION mode - using embedded templates and static files
rxdatalab[16309]: [WARN] Failed to apply PRAGMA journal_mode=WAL;: malformed database schema (NCT06549114)
rxdatalab[16309]: [WARN] Failed to apply PRAGMA synchronous=NORMAL;: malformed database schema (NCT06549114)
rxdatalab[16309]: Failed to ping database:malformed database schema (NCT06549114)
systemd[1]: rxdatalab.service: Main process exited, code=exited, status=1/FAILURE
systemd[1]: rxdatalab.service: Failed with result 'exit-code'.
systemd[1]: rxdatalab.service: Consumed 3.438s CPU time.
systemd[1]: rxdatalab.service: Scheduled restart job, restart counter is at 67.

Oh no! But that didn’t make sense, there was no malformed schema error on the data-server or when I downloaded the file.

So I ran through the typical check steps:

# prod server
sqlite> .tables
Error: malformed database schema (NCT06549114)

Could it be a journal issue? So more troubleshooting:

• Run PRAGMA journal_mode = DELETE; on data-server, then re-transfer to prod via S3 – still malformed

I did run PRAGMA check_integrity, but the database is >20GB and that command takes a very long time, so I left that running while I continued debugging.

Corruption in transit? Surely this can’t be an S3 issue:

# data-server:
sha256sum rxdatalab.db
4943adebef4a4655b8b0fb45e482bbde6f12d8136b125938f2b0916f5af2652d  rxdatalab.db

# prod:
sha256sum /var/lib/db/rxdatalab.db
4943adebef4a4655b8b0fb45e482bbde6f12d8136b125938f2b0916f5af2652d  /var/lib/db/rxdatalab-app.db

Nope, AWS still works.

Other things I tried but didn’t work:

• PRAGMA integrity_check;, took way too long, I solved the issue before it finished
• sqlite .recover failed to parse or recover anything on prod
• Compiling the same version of sqlite on both servers
• Checked database headers: hexdump showed identical first 100 bytes on both servers
• Tried disabling mmap: PRAGMA mmap_size=0; made no difference

Every diagnostic pointed to an identical, uncorrupted file. Yet it only failed on the production server.

Stale Journal Files ¶

My next step was going to be rebuilding the prod server. but then I realized that /var/lib/db/ directory on prod contained old -shm and -wal files from a previous version of the database. When SQLite opened the new database file, it found these journal files and tried to apply them, causing schema inconsistencies.

Delete the old -shm and -wal, and corruption was gone!

Lessons Learned ¶

Rebuilding the server would have solved the issue, and it probably should have been an earlier debugging step but then the mystery would have remained. “Restart/Rebuild the system” has never been a satisfactory answer to me, even though it is often the best solution. This is such a simple application, so few moving parts on my VPS, there is no reason why I shouldn’t be able to solve this.

I zeroed in on the extra files after I moved the prod database to /tmp/ and saw there was no longer corruption. Immediately thought of the journal files! A silly error to waste half a day on. However, I now have a much better understanding of SQLite behavior and journal modes, and I managed to solve the mystery in the end. It is worth noting that the AI’s were not helpful in solving this issue, as they typically aren’t if the problem isn’t in the training set. So, here is my contribution to the training set. Hopefully you will stumble on this post or the AI’s will be able to pass this along if anyone else is running into this strange issue!