Why can´t I implement Primary key in my SQL Table?

Question

I try to save Data in my esp32. In my table there should be a primary key so that I can´t save something twice with the same id. But I get an error

The Error Code:

SQL error: disk I/O error

That´s the whole Code :

#include <stdio.h>
#include <string.h>
#include <sys/unistd.h>
#include <sys/stat.h>
#include "esp_err.h"
#include "esp_log.h"
#include "esp_spiffs.h"
#include "esp_timer.h"
#include "sqlite3.h"

static const char *TAG = "sqlite3_spiffs";

const char* data = "Callback function called";
static int callback(void *data, int argc, char **argv, char **azColName) {
   int i;
   printf("%s: ", (const char*)data);
   for (i = 0; i<argc; i++){
       printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("\n");
   return 0;
}

int db_open(const char *filename, sqlite3 **db) {
   int rc = sqlite3_open(filename, db);
   if (rc) {
       printf("Can't open database: %s\n", sqlite3_errmsg(*db));
       return rc;
   } else {
       printf("Opened database successfully\n");
   }
   return rc;
}

char *zErrMsg = 0;
int db_exec(sqlite3 *db, const char *sql) {
   printf("%s\n", sql);
   int64_t start = esp_timer_get_time();
   int rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
   if (rc != SQLITE_OK) {
       printf("SQL error: %s\n", zErrMsg);
       sqlite3_free(zErrMsg);
   } else {
       printf("Operation done successfully\n");
   }
   printf("Time taken: %lld\n", esp_timer_get_time()-start);
   return rc;
}

void setup()
{
    sqlite3 *db1;

    int rc;

    ESP_LOGI(TAG, "Initializing SPIFFS");
    
    esp_vfs_spiffs_conf_t conf = {
      .base_path = "/spiffs",
      //.partition_label = "storage",
      .partition_label = NULL,
      .max_files = 5,
      .format_if_mount_failed = true
    };
    
    // Use settings defined above to initialize and mount SPIFFS filesystem.
    // Note: esp_vfs_spiffs_register is an all-in-one convenience function.
    esp_err_t ret = esp_vfs_spiffs_register(&conf);

    if (ret != ESP_OK) {
        if (ret == ESP_FAIL) {
            ESP_LOGE(TAG, "Failed to mount or format filesystem");
        } else if (ret == ESP_ERR_NOT_FOUND) {
            ESP_LOGE(TAG, "Failed to find SPIFFS partition");
        } else {
            ESP_LOGE(TAG, "Failed to initialize SPIFFS (%s)", esp_err_to_name(ret));
        }
        return;
    }
    
    size_t total = 0, used = 0;
    ret = esp_spiffs_info(NULL, &total, &used);
    if (ret != ESP_OK) {
        ESP_LOGE(TAG, "Failed to get SPIFFS partition information (%s)", esp_err_to_name(ret));
    } else {
        ESP_LOGI(TAG, "Partition size: total: %d, used: %d", total, used);
    }

    // remove existing file
    unlink("/spiffs/test1.db");
    unlink("/spiffs/test2.db");

    sqlite3_initialize();

    if (db_open("/spiffs/test1.db", &db1))
        return;
    

    rc = db_exec(db1, "CREATE TABLE entries(id STRING PRIMARY KEY, value ,section)");
    if (rc != SQLITE_OK) {
        sqlite3_close(db1);
        return;
    }

    
    rc = db_exec(db1, "INSERT INTO entries VALUES ('ROOMNAME', 'HH180L23G', 'general');");
    if (rc != SQLITE_OK) {
        sqlite3_close(db1);
        return;
    }

    rc = db_exec(db1, "SELECT * FROM entries");
    if (rc != SQLITE_OK) {
        sqlite3_close(db1);
        return;
    }


    sqlite3_close(db1);
 

    // All done, unmount partition and disable SPIFFS
    esp_vfs_spiffs_unregister(NULL);
    ESP_LOGI(TAG, "SPIFFS unmounted");

    //while(1);
}

void loop() {
  // put your main code here, to run repeatedly:

}

I have ideas like that it has something to do with spiffs or with the mounting .. I tried both partition.labels .. it´s the same error .. I can´t figure out why it doesn´t let me take a primary key .. the same thing as I can´t implement unique Text or something I get the same error..

Did someone have experienced it before ? It would be such a huge support

Log Monitor:

Opened database successfully
CREATE TABLE entries(id STRING PRIMARY KEY, value ,section)
SQL error: disk I/O error
Time taken: 179044

Answer 1

Looks like a known problem: https://github.com/siara-cc/esp32_arduino_sqlite3_lib/issues/18

Somebody has posted a workaround/fix, which doesn't look very promising to me - they explicitly specify the label of the SPIFFS partition when mounting it. Try it, see if it works. https://github.com/siara-cc/esp32-idf-sqlite3/issues/13