ESP32 STACKOVERFLOW FOR SELECT QUERY #20

Yukselzngn · 2023-08-18T05:50:37Z

Hello ,

I am using your SQLite database in my ESP32 project and would like to thank you for your help. However, I ran into some problems using it and would be very grateful for your help:

I store the data in SPIFFS and it works fine when I use it as in the example. But I want to preserve the previous data by not rebuilding the database every time I run the program. For this purpose I am removing the unlink function and not creating a "CREATE " query, I just want to query the data using "SELECT" to read it. But when I do this I get a stack overflow error and the ESP restarts.

It works when the first query is "CREATE", "INSERT" , "DROP" or "UPDATE". But when the first 'SELECT' is flood t, I get a stackoverflow error.

I would be very grateful if you could help me.
Kind regards

Yukselzngn · 2023-08-23T05:49:44Z

@siara-cc can you help me ?

siara-cc · 2023-08-23T09:25:14Z

@Yukselzngn Hi, Sorry I was unable to reply earlier. There is not much to say from what you have mentioned.
If you can post the stack trace or code or query it may be possible to find out why you are facing this issue.

Yukselzngn · 2023-08-23T09:51:49Z

@siara-cc Thank you for response. example code:

void app_main()
{
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");

sqlite3_initialize();

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


rc = db_exec(db1, "INSERT INTO test1 VALUES (1, 'Hello, World from test1');");
if (rc != SQLITE_OK) {
    sqlite3_close(db1);
    sqlite3_close(db2);
    return;
}

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


sqlite3_close(db1);

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

}

I am reading data from a pre-established memory database and writing data to it as well. As wrote above, if I send a query other than a 'SELECT' statement to the table first and then send a 'SELECT' query, it works. However, if the first query sent to the database is a 'SELECT' statement, it gives an error like the one below:

CODE

void app_main()
{
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");

sqlite3_initialize();

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



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


sqlite3_close(db1);

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

}

ERROR:

entry 0x4008064c
I (65) boot: ESP-IDF v5.1-dirty 2nd stage bootloader
I (65) boot: compile time Aug 23 2023 06:32:30
I (65) boot: Multicore bootloader
I (71) boot: chip revision: v3.0
I (74) boot.esp32: SPI Speed : 40MHz
I (79) boot.esp32: SPI Mode : DIO
I (84) boot.esp32: SPI Flash Size : 4MB
W (88) boot.esp32: PRO CPU has been reset by WDT.
W (93) boot.esp32: WDT reset info: PRO CPU PC=0x400d23f0
0x400d23f0: esp_panic_handler at /home/yuksel/esp/esp-idf/components/esp_system/panic.c:238

W (99) boot.esp32: WDT reset info: APP CPU PC=0x40087950
0x40087950: esp_cpu_compare_and_set at /home/yuksel/esp/esp-idf/components/esp_hw_support/cpu.c:406

I (105) boot: Enabling RNG early entropy source...
I (111) boot: Partition Table:
I (115) boot: ## Label Usage Type ST Offset Length
I (122) boot: 0 nvs WiFi data 01 02 00009000 00006000
I (130) boot: 1 phy_init RF data 01 01 0000f000 00001000
I (137) boot: 2 factory factory app 00 00 00010000 00200000
I (145) boot: 3 storage Unknown data 01 82 00210000 00100000
I (152) boot: End of partition table
I (157) esp_image: segment 0: paddr=00010020 vaddr=3f400020 size=2cbd0h (183248) map
I (231) esp_image: segment 1: paddr=0003cbf8 vaddr=3ffb0000 size=03420h ( 13344) load
I (237) esp_image: segment 2: paddr=00040020 vaddr=400d0020 size=d57e0h (874464) map
I (553) esp_image: segment 3: paddr=00115808 vaddr=3ffb3420 size=01428h ( 5160) load
I (555) esp_image: segment 4: paddr=00116c38 vaddr=40080000 size=1553ch ( 87356) load
I (606) boot: Loaded app from partition at offset 0x10000
I (606) boot: Disabling RNG early entropy source...
I (617) cpu_start: Multicore app
I (618) cpu_start: Pro cpu up.
I (618) cpu_start: Starting app cpu, entry point is 0x4008136c
0x4008136c: call_start_cpu1 at /home/yuksel/esp/esp-idf/components/esp_system/port/cpu_start.c:154

I (0) cpu_start: App cpu up.
I (638) cpu_start: Pro cpu start user code
I (638) cpu_start: cpu freq: 160000000 Hz
I (638) cpu_start: Application information:
I (643) cpu_start: Project name: deneme
I (647) cpu_start: App version: 1
I (652) cpu_start: Compile time: Aug 23 2023 06:32:19
I (658) cpu_start: ELF file SHA256: 3ded88e7a7578bb1...
I (664) cpu_start: ESP-IDF: v5.1-dirty
I (669) cpu_start: Min chip rev: v0.0
I (674) cpu_start: Max chip rev: v3.99
I (679) cpu_start: Chip rev: v3.0
I (684) heap_init: Initializing. RAM available for dynamic allocation:
I (691) heap_init: At 3FFAE6E0 len 00001920 (6 KiB): DRAM
I (697) heap_init: At 3FFBDCE8 len 00022318 (136 KiB): DRAM
I (703) heap_init: At 3FFE0440 len 00003AE0 (14 KiB): D/IRAM
I (709) heap_init: At 3FFE4350 len 0001BCB0 (111 KiB): D/IRAM
I (716) heap_init: At 4009553C len 0000AAC4 (42 KiB): IRAM
I (723) spi_flash: detected chip: generic
I (727) spi_flash: flash io: dio
I (731) app_start: Starting scheduler on CPU0
I (736) app_start: Starting scheduler on CPU1
I (736) main_task: Started on CPU0
I (746) main_task: Calling app_main()
I (776) DATABASE: Initializing SPIFFS
I (896) DATABASE: Partition size: total: 956561, used: 53965
Opened database successfully

ERROR A stack overflow in task main has been detected.

Backtrace: 0x400819c2:0x3ffbf750 0x40088c75:0x3ffbf770 0x4008b986:0x3ffbf790 0x4008a343:0x3ffbf810 0x4008bad4:0x3ffbf830 0x4008ba86:0x00000018 |<-CORRUPTED
0x400819c2: panic_abort at /home/yuksel/esp/esp-idf/components/esp_system/panic.c:452

0x40088c75: esp_system_abort at /home/yuksel/esp/esp-idf/components/esp_system/port/esp_system_chip.c:84

0x4008b986: vApplicationStackOverflowHook at /home/yuksel/esp/esp-idf/components/freertos/FreeRTOS-Kernel/portable/xtensa/port.c:581

0x4008a343: vTaskSwitchContext at /home/yuksel/esp/esp-idf/components/freertos/FreeRTOS-Kernel/tasks.c:3729

0x4008bad4: _frxt_dispatch at /home/yuksel/esp/esp-idf/components/freertos/FreeRTOS-Kernel/portable/xtensa/portasm.S:450

0x4008ba86: _frxt_int_exit at /home/yuksel/esp/esp-idf/components/freertos/FreeRTOS-Kernel/portable/xtensa/portasm.S:245

ELF file SHA256: 3ded88e7a7578bb1

Rebooting...
ets Jul 29 2019 12:21:46

siara-cc · 2023-08-23T17:58:03Z

@Yukselzngn If it is just about reading from a simple table it should work so the only reason for such crashes usually are memory allocation issues. There is a tool that can show the exact location of crash in the code when input with the crash dump but I have not used it much.
I assume this is just sample code otherwise it won't compile because db2 is not defined. Please look for such issues and clean up the code whatever is not needed. Also try to isolate the problem by creating another sample program which does only reading from existing table.
Also please delete the table once and recreate it to eliminate possibility of corrupted db.

SpazCode153 · 2023-08-24T06:46:55Z

Hi @Yukselzngn @siara-cc I have this same problem as well, I can create a table and insert data but a select causes a stack overflow. I'm storing the data on a SD card though but what I found is that the file created on the SD card is corrupt I think. If I insert the SD card into a PC and try to delete the file from the SD card, then I get a "Invalid file handle" error.

SpazCode153 · 2023-08-24T07:41:23Z

Okay so I realized my mistake, the file name I used was a windows reserved name, that's why I got the "invalid file handle" error. I can confirm that I can create a database file and table as well as insert data but any kind of select causes a stack overflow. I have also tried recreating the table and database file. I Always get the same result, even if I use a database file I created on my PC.

SpazCode153 · 2023-08-24T08:45:17Z

So digging into sqlite3_exec I found that the stack overflow occurs when sqlite3LockAndPrepare returns its result code. I can't tell why its causing a stack overflow.

SpazCode153 · 2023-08-24T08:59:00Z

It turns out I needed a sanity check, I thought I had increased the stack size setting but it never changed. @Yukselzngn increase your main task stack size to something like 8096. My selects work after doing that

siara-cc · 2023-08-24T09:50:48Z

@SpazCode153 I thought the default stack size for esp32 was 8192? Was it reduced in the recent versions?
I would recommend a minimum stack size of 6144 bytes. Also use 512 page size unless impossible.
I changed the default page size for DBs created with this lib to 512 now with this commit: 9752258.
@Yukselzngn

SpazCode153 · 2023-08-24T09:52:56Z

@siara-cc I'm using ESP-IDF V5.1 and the default setting there is 3584.

Okay thank you.

Yukselzngn · 2023-08-24T10:08:12Z

@SpazCode153 and @siara-cc thank you very much, my default stack size value was 3584, after you told me, my problem was solved when I changed it. Thank you for your advice.

SpazCode153 · 2023-08-24T10:20:34Z

@Yukselzngn its pleasure. Enjoy your day.

torabian · 2023-09-02T23:18:26Z

Hello Guys,

Suffer from the same situation over here.
I have updated the stack sizes (I am using idf 5, not 4) but still if the first one is 'select', I get crash.

Can anyone provide step by step fix for this?

I also changed the page size from the library.

Kind Regards, ALi

SpazCode153 · 2023-09-02T23:31:59Z

Hi @torabian, are you sure the stack size is increased? I found that sometimes when I change the settings it doesn't always save after I actually click save. If your using VS code, try closing VS code and opening again and checking the stack size setting.

What have you increased the stack size too?

May I ask how much data your selecting? 1000 rows etc

Also can you send your terminal output with the error here?

torabian · 2023-09-02T23:41:30Z

@siara-cc I believe changing esp pager to 512 is a mistake. When I changed it to 512, after 3-4 subsequent db_exec I had an stack overflow, I changed it to 4096 as it was.

Maybe you consider that to be reverted?

torabian · 2023-09-02T23:42:55Z

I am running all these queries at once. It's super unreliable:

    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_data_node_type_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`name` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_data_node_type_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`name` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_data_node_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`name` text,`value` text,`type_id` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`),CONSTRAINT `fk_fb_data_node_entities_type` FOREIGN KEY (`type_id`) REFERENCES `fb_data_node_type_entities`(`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_node_datum_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`node_id` text,`value_float64` real,`value_int64` integer,`value_string` text,`ingested_at` integer,`ingested_at_formatted` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`),CONSTRAINT `fk_fb_node_datum_entities_node` FOREIGN KEY (`node_id`) REFERENCES `fb_data_node_entities`(`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_data_node_readers_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`fn` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`),CONSTRAINT `fk_fb_data_node_entities_readers` FOREIGN KEY (`linker_id`) REFERENCES `fb_data_node_entities`(`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_data_node_writers_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`fn` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`),CONSTRAINT `fk_fb_data_node_entities_writers` FOREIGN KEY (`linker_id`) REFERENCES `fb_data_node_entities`(`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_data_node_type_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`name` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_data_node_type_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`name` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_data_node_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`name` text,`value` text,`type_id` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`),CONSTRAINT `fk_fb_data_node_entities_type` FOREIGN KEY (`type_id`) REFERENCES `fb_data_node_type_entities`(`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_node_datum_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`node_id` text,`value_float64` real,`value_int64` integer,`value_string` text,`ingested_at` integer,`ingested_at_formatted` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`),CONSTRAINT `fk_fb_node_datum_entities_node` FOREIGN KEY (`node_id`) REFERENCES `fb_data_node_entities`(`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_data_node_readers_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`fn` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`),CONSTRAINT `fk_fb_data_node_entities_readers` FOREIGN KEY (`linker_id`) REFERENCES `fb_data_node_entities`(`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_data_node_writers_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`fn` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`),CONSTRAINT `fk_fb_data_node_entities_writers` FOREIGN KEY (`linker_id`) REFERENCES `fb_data_node_entities`(`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_data_node_type_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`name` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_data_node_type_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`name` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_data_node_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`name` text,`value` text,`type_id` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`),CONSTRAINT `fk_fb_data_node_entities_type` FOREIGN KEY (`type_id`) REFERENCES `fb_data_node_type_entities`(`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_node_datum_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`node_id` text,`value_float64` real,`value_int64` integer,`value_string` text,`ingested_at` integer,`ingested_at_formatted` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`),CONSTRAINT `fk_fb_node_datum_entities_node` FOREIGN KEY (`node_id`) REFERENCES `fb_data_node_entities`(`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_data_node_readers_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`fn` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`),CONSTRAINT `fk_fb_data_node_entities_readers` FOREIGN KEY (`linker_id`) REFERENCES `fb_data_node_entities`(`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_data_node_writers_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`fn` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`),CONSTRAINT `fk_fb_data_node_entities_writers` FOREIGN KEY (`linker_id`) REFERENCES `fb_data_node_entities`(`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_data_node_type_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`name` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_data_node_type_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`name` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_data_node_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`name` text,`value` text,`type_id` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`),CONSTRAINT `fk_fb_data_node_entities_type` FOREIGN KEY (`type_id`) REFERENCES `fb_data_node_type_entities`(`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_node_datum_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`node_id` text,`value_float64` real,`value_int64` integer,`value_string` text,`ingested_at` integer,`ingested_at_formatted` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`),CONSTRAINT `fk_fb_node_datum_entities_node` FOREIGN KEY (`node_id`) REFERENCES `fb_data_node_entities`(`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_data_node_readers_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`fn` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`),CONSTRAINT `fk_fb_data_node_entities_readers` FOREIGN KEY (`linker_id`) REFERENCES `fb_data_node_entities`(`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_data_node_writers_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`fn` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`),CONSTRAINT `fk_fb_data_node_entities_writers` FOREIGN KEY (`linker_id`) REFERENCES `fb_data_node_entities`(`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_data_node_type_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`name` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_data_node_type_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`name` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_data_node_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`name` text,`value` text,`type_id` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`),CONSTRAINT `fk_fb_data_node_entities_type` FOREIGN KEY (`type_id`) REFERENCES `fb_data_node_type_entities`(`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_node_datum_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`node_id` text,`value_float64` real,`value_int64` integer,`value_string` text,`ingested_at` integer,`ingested_at_formatted` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`),CONSTRAINT `fk_fb_node_datum_entities_node` FOREIGN KEY (`node_id`) REFERENCES `fb_data_node_entities`(`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_data_node_readers_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`fn` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`),CONSTRAINT `fk_fb_data_node_entities_readers` FOREIGN KEY (`linker_id`) REFERENCES `fb_data_node_entities`(`unique_id`));");
    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_data_node_writers_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`fn` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`),CONSTRAINT `fk_fb_data_node_entities_writers` FOREIGN KEY (`linker_id`) REFERENCES `fb_data_node_entities`(`unique_id`));");

    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_gpio_mode_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`key` text,`index` integer,`description` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`));");

    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_gpio_mode_entity_polyglots` (`linker_id` text NOT NULL,`language_id` text NOT NULL,`description` text,CONSTRAINT `fk_fb_gpio_mode_entities_translations` FOREIGN KEY (`linker_id`) REFERENCES `fb_gpio_mode_entities`(`unique_id`));");

    test_sqlite("CREATE TABLE IF NOT EXISTS `fb_gpio_entities` (`visibility` text,`workspace_id` text,`linker_id` text,`parent_id` text,`unique_id` text NOT NULL UNIQUE,`user_id` text,`name` text,`index` integer,`analog_function` text,`rtc_gpio` text,`comments` text,`mode_id` text,`rank` integer,`updated` integer,`created` integer,`created_formatted` text,`updated_formatted` text,PRIMARY KEY (`unique_id`),CONSTRAINT `fk_fb_gpio_entities_mode` FOREIGN KEY (`mode_id`) REFERENCES `fb_gpio_mode_entities`(`unique_id`));");

torabian · 2023-09-02T23:43:14Z

void test_sqlite(const char *sql)
{
    sqlite3 *db1;
    int rc;

    esp_vfs_spiffs_conf_t conf = {
        .base_path = "/storage",
        .partition_label = "storage",
        .max_files = 15,
        .format_if_mount_failed = true};

    esp_err_t ret = esp_vfs_spiffs_register(&conf);

    if (ret != ESP_OK)
    {

        ESP_LOGE(TAG2, "Failed to initialize SPIFFS (%s)", esp_err_to_name(ret));

        return;
    }

    sqlite3_initialize();

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

    rc = db_exec(db1, "CREATE TABLE IF NOT EXISTS `bugg_table` (`visibility` text)");
    if (rc != SQLITE_OK)
    {
        sqlite3_close(db1);
        esp_vfs_spiffs_unregister("storage");
        return;
    }

    rc = db_exec(db1, sql);
    if (rc != SQLITE_OK)
    {
        sqlite3_close(db1);
        esp_vfs_spiffs_unregister("storage");

        return;
    }

    sqlite3_close(db1);
    esp_err_t err4 = esp_vfs_spiffs_unregister("storage");
}

torabian · 2023-09-02T23:44:11Z

Also another issue is, if I mount the spiffs in another function, it would say IO/Write error, that's also strange.

I create table 'bugg table' before each sql statement, it's really weird.

I wonder, how can we have a high load example of the library, to test 100 table creation,
inserting 10,000 records, querying using joins, etc to actually see the problems.

torabian · 2023-09-03T00:18:46Z

I think using the sql queries inside http methods causing the problem...

SpazCode153 · 2023-09-03T00:31:55Z

@torabian I'm just confirming, you did change the "Main task stack size" under "ESP System Settings" in your SDK config to something larger or equal to 6144? Can you perhaps paste your "sdkconfig" file here so we can see all your settings?

torabian · 2023-09-03T00:49:28Z

@SpazCode153

I am quite tired for today, but I got good results so far - unfortunately, the mystery for me still remains, why database calls inside the http call backs are causing the stackoverflow.

Inside app_main function, as many as sql queries are fine - just some times I/O error but 3 out of 100 operations.

I changed stack size to 8000, maybe that helped to remove initial INSERT function.

I will prepare an example tomorrow and share.

@siara-cc Are you planning to make the library ready for v5 as well? It seems only 1-2 tweaks are required, maybe we can merge it into the repo?

siara-cc · 2023-09-05T07:27:28Z

@torabian Sorry I saw your queries only now. SPIFFS is known to throw IO errors. Why not use LITTLEFS?
Are you still facing issues with 512 page size? As far as I know it should not cause trouble unless its specifically because your schema has a lot of tables which is bound to make the schema b-tree into multiple pages.
If you have a PR for v5 I will merge it.

torabian · 2023-09-06T22:41:41Z

@siara-cc Hello Arun;

Sorry for late reply, I just managed to get CRU-(D?) working on the ESP32 with HTTP server support, and, this library.
Seems to be fine.

I have used the SPIFFS for now, it seems 95% + of time working. But I am not sure how good it keeps data, or will I be able to upload files to it.

I will opened a merge request soon.

Yukselzngn · 2023-10-14T10:11:48Z

Hello @siara-cc @SpazCode153 ,
I have this error :
SQL error: out of memory
Time taken: 15538
SQL error: out of memory
Time taken: 12297
SQL error: out of memory
Time taken: 11922

Can you help me please
Thank you

SpazCode153 · 2023-10-14T10:59:54Z

Hi @Yukselzngn, Can you more or less pin point which queries are causing the timeouts? Also how large is your database in terms of file size?

Yukselzngn · 2023-10-14T12:18:33Z

@SpazCode153
I use basic select queries,example ""SELECT * FROM Transactions". Also my database tables are very simple.
but I'm calling multiple queries one after the other

SpazCode153 · 2023-10-14T12:32:24Z

@Yukselzngn

Can you share that snippet of code?

Yukselzngn · 2023-10-14T12:34:50Z

int getNumberOfChargPoint(){

int numberOfChargPoint = 0;

char *query = (char *)calloc(200, 1);

sprintf(query, "SELECT * FROM ChargPointsId");

db_exec(db1, query);

char **received_data;

while (uxQueueMessagesWaiting(dataQueue) != 0)
{
	if (xQueueReceive(dataQueue, &received_data, portMAX_DELAY) == pdTRUE)
	{
		numberOfChargPoint++;
		free(received_data);
		
	}		
}
return numberOfChargPoint;

}

db_exec function:
int db_exec(sqlite3 *db, const char *sql)
{

char *data = "";

int64_t start = esp_timer_get_time();
int rc = sqlite3_exec(db, sql, callback, 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);
tableLine = 0;
return rc;

}

calllback function:

static int callback(char *tableName, int argc, char **argv, char **azColName)
{

char **data_copy = (char **)malloc(sizeof(char *) * argc);
for (int i = 0; i < argc; i++)
{
	if (argv[i] != NULL)
		data_copy[i] = strdup(argv[i]);
	else
		data_copy[i] = '\0';
}

if (xQueueSend(dataQueue, &data_copy, portMAX_DELAY) != pdTRUE)
{

	printf("Queue is full, data not sent\n");
	for (int i = 0; i < argc; i++)
	{
		free(data_copy[i]);
	}
	free(data_copy);
}
return 0;

}

torabian · 2023-10-16T09:21:55Z

You cannot use callback system. You need to go the prepare sqlite3_prepare_v2 instead for long term and reliable usage.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

ESP32 STACKOVERFLOW FOR SELECT QUERY #20

ESP32 STACKOVERFLOW FOR SELECT QUERY #20

Yukselzngn commented Aug 18, 2023

Yukselzngn commented Aug 23, 2023

siara-cc commented Aug 23, 2023

Yukselzngn commented Aug 23, 2023

siara-cc commented Aug 23, 2023

SpazCode153 commented Aug 24, 2023

SpazCode153 commented Aug 24, 2023

SpazCode153 commented Aug 24, 2023

SpazCode153 commented Aug 24, 2023

siara-cc commented Aug 24, 2023

SpazCode153 commented Aug 24, 2023 •

edited

Loading

Yukselzngn commented Aug 24, 2023

SpazCode153 commented Aug 24, 2023

torabian commented Sep 2, 2023

SpazCode153 commented Sep 2, 2023

torabian commented Sep 2, 2023

torabian commented Sep 2, 2023

torabian commented Sep 2, 2023

torabian commented Sep 2, 2023 •

edited

Loading

torabian commented Sep 3, 2023

SpazCode153 commented Sep 3, 2023 •

edited

Loading

torabian commented Sep 3, 2023

siara-cc commented Sep 5, 2023

torabian commented Sep 6, 2023

Yukselzngn commented Oct 14, 2023

SpazCode153 commented Oct 14, 2023

Yukselzngn commented Oct 14, 2023

SpazCode153 commented Oct 14, 2023 •

edited

Loading

Yukselzngn commented Oct 14, 2023 •

edited

Loading

torabian commented Oct 16, 2023

ESP32 STACKOVERFLOW FOR SELECT QUERY #20

ESP32 STACKOVERFLOW FOR SELECT QUERY #20

Comments

Yukselzngn commented Aug 18, 2023

Yukselzngn commented Aug 23, 2023

siara-cc commented Aug 23, 2023

Yukselzngn commented Aug 23, 2023

siara-cc commented Aug 23, 2023

SpazCode153 commented Aug 24, 2023

SpazCode153 commented Aug 24, 2023

SpazCode153 commented Aug 24, 2023

SpazCode153 commented Aug 24, 2023

siara-cc commented Aug 24, 2023

SpazCode153 commented Aug 24, 2023 • edited Loading

Yukselzngn commented Aug 24, 2023

SpazCode153 commented Aug 24, 2023

torabian commented Sep 2, 2023

SpazCode153 commented Sep 2, 2023

torabian commented Sep 2, 2023

torabian commented Sep 2, 2023

torabian commented Sep 2, 2023

torabian commented Sep 2, 2023 • edited Loading

torabian commented Sep 3, 2023

SpazCode153 commented Sep 3, 2023 • edited Loading

torabian commented Sep 3, 2023

siara-cc commented Sep 5, 2023

torabian commented Sep 6, 2023

Yukselzngn commented Oct 14, 2023

SpazCode153 commented Oct 14, 2023

Yukselzngn commented Oct 14, 2023

SpazCode153 commented Oct 14, 2023 • edited Loading

Yukselzngn commented Oct 14, 2023 • edited Loading

torabian commented Oct 16, 2023

SpazCode153 commented Aug 24, 2023 •

edited

Loading

torabian commented Sep 2, 2023 •

edited

Loading

SpazCode153 commented Sep 3, 2023 •

edited

Loading

SpazCode153 commented Oct 14, 2023 •

edited

Loading

Yukselzngn commented Oct 14, 2023 •

edited

Loading