SQLite not freeing Heap memory

[FarhanAhmedEmbedded]

Im using a 122MB nand flash for storing database file. I was getting error in my main firmware where database would get corrupted randomly and give error "file is not a database" and "disk I/O error".

Because i cannot share the original firmware i made a rough sketch to stress test the database. I noticed that after certain number of database query executions i would get "out of memory" error. Upon further inspection i noticed that the heap memory is almost exhausted at that point. I want to know how is sqlite library managing the heap memory or if user is supposed to manually free the heap.

Here is the code snippet

while(flag)
    {
        create_query(&QUERY, id, engine_id);

        openDB();
        int rc = sqlite3_exec(db, QUERY, callback, (void*)data, &zErrMsg);
        closeDB();
        if (rc != SQLITE_OK) {
            printf("SQL error: ");
            printf(zErrMsg);
            printf("\n");
            printf("id: %" PRIu64 "", id);
            printf(" engine_id: %" PRIu64 "\n", engine_id);
            sqlite3_free(zErrMsg);

            stats();
            printHeap();
            closeDB();
            printHeap();
            // openDB();
            flag = false;
        }
        id++;
        engine_id++;
    }

[siara-cc]

@FarhanAhmedEmbedded Not sure if you are still facing this issue since you have closed it. Usually it goes out of memory due to memory fragmentation. I made an example for testing bulk insert which involves prepared statements and carefully allocating values to insert: https://github.com/siara-cc/esp32_arduino_sqlite3_lib/tree/master/examples/sqlite3_bulk_data_insert It may also help to see other issues in the same line such as: https://github.com/siara-cc/esp32_arduino_sqlite3_lib/issues/7

[FarhanAhmedEmbedded]

@siara-cc I wasn't expecting this quick of a response. I closed this issue because there was memory leak in the test setup and was not the correct representation of this error.

[FarhanAhmedEmbedded]

This is my first time using a database in embedded project so let me know if im making any mistake here.

In my firmware i have two threads which are accessing the database. One thread "SensorThread" is reading sensors and placing these values in the database, Second thread "WebsocketThread" is fetching data from the database and sending it to connected clients. I have also applied mutex on database to ensure only one thread have access at a time as i was having issues otherwise. During firmware startup, database is opened for the entirety of the firmware lifecycle, not sure if threads should be opening/closing database separately. Database errors only occur when i have more than one client connected and this error is random, it might happen at start or after a couple of minutes.

[siara-cc]

This library a wrapper for SQLite official code and does not do any thread sync by itself. SQLite definitely does apply mutexes but whether the conditions work with esp32 and work with esp32 posix functions is another thing. So far to my knowledge this repo has not been used like this. One suggestion would be to have a readonly database handle (PRAGMA readonly) for the reading thread and read/write database handle for the read/write thread. You have not mentioned what error is faced now, but if it is out of memory I think it is unlikely to be because of thread sync.