SQLite corrupts easily

[muuris]

I'm using this library for a home automation system which uses SQLite for storing and retrieving device states and control data. I have a few tables, I'm using indexes + begin transaction/commit for those I often do queries with. The system runs on ESP32-CAM and stores db on SD-card. The db has a few tables and has indexes for frequently queried data.

The database often gets corrupted, this error usually occurs during a SELECT query (but I bet the malformation has happened without noticing before that during an INSERT or UPDATE). I've written my own SQLite execute -wrapper function based on the examples provided, this handles interlocking (makes sure that simultanious operations or other SD-card operations for ie. web server shouldn't happen). I've done the same for the callback function for easy retrieval of data for the rest of the code.

I'm not getting other errors such as out of memory etc. It's almost always the same table that gets corrupted, it now has about 100k rows in it and the size of the whole db is about ~5MB. Looking at the corrupt db with SQLite Browser, it seems the latest ~50 rows are malformed and I can export the rest to .sql file and import it back into a db. I've tried two different SD-cards so I don't think it's related to those either. I also had the problems when db size was less than half of the current size.

What should I look at next?

[siara-cc]

@muuris There was an issue which was addressed by another user: #81 (comment)
However, I think you may be using the latest version of this repo so I am not sure what else could be the issue.
If you think it happens during a SELECT query, please try making the database readonly PRAGMA query_only before running the SELECT.
Also please check how much memory is being used, whether there are leaks and if the memory getting fragmented.
Please try minimising the number of indexes (using WITHOUT ROWID clause). Also check if page size is 512.

[muuris]

Thanks for the quick replay and suggestions. I thought I had updated to the latest version already, but it seems I'm missing #87 change in mine, got to try that first and readonly next.

I'm having ~150kB of free heap when the system is up and running, I've got an alert if memory usage is a problem -- but that alarm would go to db also, so it's not bulletproof. Nevertheless, at best the system has been up and running continuoysly for more than a month with no errors and it makes rougly a 1000 inserts/updates a day, so the problem is quite occasional. I've already tried another SD card to rule that failure out. I'll have to look up how to do memory fragmentation check.

Forgot to mention that this library is the best addition I've had in any of my projects, it boosts ESP32 capabilities to totally another level.

[HankLloydRight]

Forgot to mention that this library is the best addition I've had in any of my projects, it boosts ESP32 capabilities to totally another level.

Just wanted to chime in and also acknowledge this! Having a fully functional SQL database on an ESP32 is mind-blowing! I'm using it to index thousands of WAV files on the SD card so I can do things like searching, filtering, sorting, and random/shuffle play. Thank you!

[siara-cc]

@muuris I am not sure how much RAM is playing a part for your issue. I made an example quite a while back for testing bulk inserts:
https://github.com/siara-cc/esp32_arduino_sqlite3_lib/tree/master/examples/sqlite3_bulk_data_insert
Since it is running fine for quite a while, it may be going out of memory and causing strange issues. This example uses a prepared statement and avoids fragmentation.
Also you may want to go through other issues where people have reported the same issue.

[muuris]

@siara-cc I added memory monitoring as in your example, but found no issues there. Instead your suggestion of adding PRAGMA query_only before each SELECT (if not already query only) seems to have done the trick, I haven't managed to corrupt the db anymore!