Regression on table creation between 2.3 and 2.4

[chris-accupill]

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]

Thank you for reporting this. I will check.
The only change in 2.4 is to upgrade SQLite to latest version. Not sure if that could have caused this.