Regression on table creation between 2.3 and 2.4

I've noticed a new issue in 2.4, that isn't a problem in 2.3. I had some code that created database tables, in 2.4 I get a 'disk I/O error' and in 2.3 no errors. I've added sample Arduino code that demonstrates the failure. I did notice that if I used very short column names, like just a single letter, it will work in 2.4 without error.

I tested this code on an ESP32-S2 and an ESP32-S3 and they both work with 2.3 but fail on 2.4, they seem to have plenty of memory and disk space available at the time of failure in 2.4. Enable "erase all flash before sketch upload".

Version 2.4 Output (fail)

Opened database successfully Memory Stats Total heap: 394012 Free heap: 328000 Minimum free heap ever: 322828 Largest available block: 294900 SPIFFS Total Bytes: 1318001 SPIFFS Used Bytes: 2510 CREATE TABLE IF NOT EXISTS a (aaaaaaaaaaaaaa TEXT, bbbbbbbbbbb BLOB, cccccccccc BLOB, ddddddddddd INTEGER, eeeeeeeeee INTEGER, ffffffff FLOAT); Operation done successfully Time taken:6348 CREATE TABLE IF NOT EXISTS b (aaaaaaaaaa INTEGER PRIMARY KEY, bbbbbbbbbbb INTEGER, cccccccccc INTEGER); SQL DB_EXEC error: disk I/O error Time taken:146753

Version 2.3 Output (success)

Opened database successfully Memory Stats Total heap: 393980 Free heap: 324216 Minimum free heap ever: 323172 Largest available block: 294900 SPIFFS Total Bytes: 1318001 SPIFFS Used Bytes: 251 CREATE TABLE IF NOT EXISTS a (aaaaaaaaaaaaaa TEXT, bbbbbbbbbbb BLOB, cccccccccc BLOB, ddddddddddd INTEGER, eeeeeeeeee INTEGER, ffffffff FLOAT); Operation done successfully Time taken:171949 CREATE TABLE IF NOT EXISTS b (aaaaaaaaaa INTEGER PRIMARY KEY, bbbbbbbbbbb INTEGER, cccccccccc INTEGER); Operation done successfully Time taken:100911 Memory Stats Total heap: 393324 Free heap: 308924 Minimum free heap ever: 293608 Largest available block: 286708 CREATE TABLE IF NOT EXISTS c (id INTEGER PRIMARY KEY, aaaaaaaaaaaaaaa BLOB NOT NULL, bbbbbbbbbbbbbb BLOB); Operation done successfully Time taken:111601

#include "SPIFFS.h"
#include <sqlite3.h>

#define FORMAT_SPIFFS_IF_FAILED true

sqlite3 *db1 = NULL;
char *zErrMsg = 0;

void setup() {

  int rc;

  delay(1000);

  // Start Serial for development / debugging
  Serial.begin(115200);

  delay(1000);

  start_spiffs();

  sqlite3_initialize();    

  rc = sqlite3_open("/spiffs/test1.db", &db1);

  if (rc) {
    Serial.printf("Can't open database: %s\n", sqlite3_errmsg(db1));
    return;
  } else {
    Serial.printf("Opened database successfully\n");
  }

  printMemoryStats();

  unsigned int totalBytes = SPIFFS.totalBytes();
  unsigned int usedBytes = SPIFFS.usedBytes();

  Serial.println("SPIFFS Total Bytes: " + String(totalBytes));
  Serial.println("SPIFFS Used Bytes: " + String(usedBytes));

  rc = db_exec(db1, "CREATE TABLE IF NOT EXISTS a (aaaaaaaaaaaaaa TEXT, bbbbbbbbbbb BLOB, cccccccccc BLOB, ddddddddddd INTEGER, eeeeeeeeee INTEGER, ffffffff FLOAT);");

  sqlite3_db_release_memory(db1);
  if (rc != SQLITE_OK)
  {
    close_database();
    return;
  }  

  rc = db_exec(db1, "CREATE TABLE IF NOT EXISTS b (aaaaaaaaaa INTEGER PRIMARY KEY, bbbbbbbbbbb INTEGER, cccccccccc INTEGER);");
  sqlite3_db_release_memory(db1);
  if (rc != SQLITE_OK)
  {
    close_database();
    return;
  }  

  printMemoryStats();

   rc = db_exec(db1, "CREATE TABLE IF NOT EXISTS c (id INTEGER PRIMARY KEY, aaaaaaaaaaaaaaa BLOB NOT NULL, bbbbbbbbbbbbbb BLOB);");
  sqlite3_db_release_memory(db1);
  if (rc != SQLITE_OK) 
  {
    Serial.printf("SQL DB_EXEC error: %s\n", sqlite3_errmsg(db1)); 
    close_database();
    return;
  }
}

void loop() {
  delay(5000);
}

int start_spiffs()
{
  if (!SPIFFS.begin(FORMAT_SPIFFS_IF_FAILED)) {
    Serial.println("Failed to mount file system");
    return 0;
  }

  File root = SPIFFS.open("/");
  if (!root) {
    Serial.println("- failed to open directory");
    return 0;
  }

  if (!root.isDirectory()) {
    Serial.println(" - not a directory");
    root.close();
    return 0;
  }

  root.close();

  return 1;
}

int db_exec(sqlite3 *db, const char *sql) {
  Serial.println(sql);
  long start = micros();
  int rc = sqlite3_exec(db, sql, NULL, NULL, &zErrMsg);
  if (rc != SQLITE_OK) {
    Serial.printf("SQL DB_EXEC error: %s\n", zErrMsg);
    sqlite3_free(zErrMsg);
  } else {
    Serial.printf("Operation done successfully\n");
  }
  Serial.print(F("Time taken:"));
  Serial.println(micros() - start);
  return rc;
}

void close_database()
{
  if (db1 != NULL)
  {
    sqlite3_close(db1);
    db1 = NULL;
  }
}

void printMemoryStats() {
    Serial.println("---- Memory Stats ----");
    Serial.print("Total heap: ");
    Serial.println(ESP.getHeapSize());
    Serial.print("Free heap: ");
    Serial.println(esp_get_free_heap_size());
    Serial.print("Minimum free heap ever: ");
    Serial.println(esp_get_minimum_free_heap_size());
    Serial.print("Largest available block: ");
    Serial.println(heap_caps_get_largest_free_block(MALLOC_CAP_8BIT));
    Serial.println("----------------------");
}

siara-cc / esp32_arduino_sqlite3_lib

Regression on table creation between 2.3 and 2.4 #79