SQLITE vfs db file issue #1

asamuta commented 2025年12月01日 20:05:51 +01:00

Hi,
I am trying to adopt the code to my custom hardware.
As I can see the version 3.42.0 is used.
Initialization uses

if(SQLITE3_AZURE_CONFIG_SCRATCH)
 sqlite3_config(SQLITE_CONFIG_SCRATCH, SQLITE3_AZURE_CONFIG_SCRATCH, SQLITE3_AZURE_CONFIG_SCRATCH_SIZE);

Version 3.42.0 doesn't do anything with SQLITE_CONFIG_SCRATCH.
Are you sure it make sence?

Thanks in advance.

Hi, I am trying to adopt the code to my custom hardware. As I can see the version 3.42.0 is used. Initialization uses ``` if(SQLITE3_AZURE_CONFIG_SCRATCH) sqlite3_config(SQLITE_CONFIG_SCRATCH, SQLITE3_AZURE_CONFIG_SCRATCH, SQLITE3_AZURE_CONFIG_SCRATCH_SIZE); ``` Version 3.42.0 doesn't do anything with SQLITE_CONFIG_SCRATCH. Are you sure it make sence? Thanks in advance.

dmitryrozhd commented 2025年12月02日 23:51:42 +01:00

Hi,

when I try to improve my code I read the latest documentation so that may happen that I implement something that is not already used. Just now there is a new version of MCU software for STM32 so I will update the toolchain and the SQLite as well. It will however take some time because it seems that I have understood the source of some rare bugs. You may monitor it here: https://sqlite.org/forum/forumpost/478d271597

Could you please also tell me what MCU and what board do you use?

With best regards,
Dmitry

Hi, when I try to improve my code I read the latest documentation so that may happen that I implement something that is not already used. Just now there is a new version of MCU software for STM32 so I will update the toolchain and the SQLite as well. It will however take some time because it seems that I have understood the source of some rare bugs. You may monitor it here: https://sqlite.org/forum/forumpost/478d271597 Could you please also tell me what MCU and what board do you use? With best regards, Dmitry

asamuta commented 2025年12月03日 20:39:46 +01:00

I use custom STM32H723 board with 32MB SDRAM and 4GB eMMC.
Previously tried https://github.com/luismeruje/SQLite-STM32 with no luck. (Somehow it was not able to reopen closed db file).
This implementation looks more promising but it does something unexpected with file system. Investigating ...

Anyway my config:
DB initialization (.cdb - in SDRAM.):

#define SQLITE3_AZURE_CONFIG_PAGE_POOL_ALIGNMENT 32
#define SQLIGHT_PSIZE 512
char sqlHeapBuffer[1024 * (1024) * 12] __attribute__((section(".cdb"), aligned (32)));
char sqlPageBuffer[1024 * 1024 * 4] __attribute__((section(".cdb"), aligned (32)));
void sqlight_init(void) {
 logInfo("Initializing SQLite3 database...\r\n");
 memset(sqlHeapBuffer, 0, sizeof(sqlHeapBuffer));
 memset(sqlPageBuffer, 0, sizeof(sqlPageBuffer));
 sqlite3_config(SQLITE_CONFIG_HEAP, sqlHeapBuffer, sizeof(sqlHeapBuffer), 64);
 int header_size;
 sqlite3_config(SQLITE_CONFIG_PCACHE_HDRSZ, &header_size);
 logInfo("Header size=%d\r\n", header_size);
 if (header_size & (SQLITE3_AZURE_CONFIG_PAGE_POOL_ALIGNMENT - 1)) {
 header_size += SQLITE3_AZURE_CONFIG_PAGE_POOL_ALIGNMENT;
 header_size &= ~(SQLITE3_AZURE_CONFIG_PAGE_POOL_ALIGNMENT - 1);
 }
 logInfo("Header size=%d\r\n", header_size);
 sqlite3_config(SQLITE_CONFIG_PAGECACHE, sqlPageBuffer, SQLIGHT_PSIZE + header_size, sizeof(sqlPageBuffer) / (SQLIGHT_PSIZE + header_size));
 sqlite3_config(SQLITE_CONFIG_LOOKASIDE, 64, 64);
 sqlite3_initialize();
}

SQLight config:

#define SQLITE_THREADSAFE 0
#define SQLITE_OS_OTHER 1
#define SQLITE_OMIT_DESERIALIZE 1
#define SQLITE_MAX_MMAP_SIZE 0
#define SQLITE_DEFAULT_MMAP_SIZE 0
//#define SQLITE_DEFAULT_CACHE_SIZE 450
#define SQLITE_STRICT_SUBTYPE 1
//#define SQLITE_MEMDEBUG 1
#define SQLITE_OMIT_AUTOINIT 1
#define SQLITE_OMIT_SHARED_CACHE 1
#define SQLITE_MAX_DEFAULT_PAGE_SIZE 512
#define SQLITE_DEFAULT_PAGE_SIZE 512
#define SQLITE_ENABLE_MEMSYS5 1
#define SQLITE_DEFAULT_LOCKING_MODE 0

eMMC formated with:

fx_media_format(
 &flashMedia,
 fx_stm32_custom_driver,
 &this->driverInfo,
 (UCHAR *) fxDataBuffer,
 sizeof(fxDataBuffer),
 const_cast<char *>("DB fs"),
 1, 32, 0, 1024*1024*1024/512, 512, 1, 1, 1);
 

I use 1 thread for testing so far (SQLITE_THREADSAFE 0) .

I use custom STM32H723 board with 32MB SDRAM and 4GB eMMC. Previously tried https://github.com/luismeruje/SQLite-STM32 with no luck. (Somehow it was not able to reopen closed db file). This implementation looks more promising but it does something unexpected with file system. Investigating ... Anyway my config: DB initialization (.cdb - in SDRAM.): ``` #define SQLITE3_AZURE_CONFIG_PAGE_POOL_ALIGNMENT 32 #define SQLIGHT_PSIZE 512 char sqlHeapBuffer[1024 * (1024) * 12] __attribute__((section(".cdb"), aligned (32))); char sqlPageBuffer[1024 * 1024 * 4] __attribute__((section(".cdb"), aligned (32))); void sqlight_init(void) { logInfo("Initializing SQLite3 database...\r\n"); memset(sqlHeapBuffer, 0, sizeof(sqlHeapBuffer)); memset(sqlPageBuffer, 0, sizeof(sqlPageBuffer)); sqlite3_config(SQLITE_CONFIG_HEAP, sqlHeapBuffer, sizeof(sqlHeapBuffer), 64); int header_size; sqlite3_config(SQLITE_CONFIG_PCACHE_HDRSZ, &header_size); logInfo("Header size=%d\r\n", header_size); if (header_size & (SQLITE3_AZURE_CONFIG_PAGE_POOL_ALIGNMENT - 1)) { header_size += SQLITE3_AZURE_CONFIG_PAGE_POOL_ALIGNMENT; header_size &= ~(SQLITE3_AZURE_CONFIG_PAGE_POOL_ALIGNMENT - 1); } logInfo("Header size=%d\r\n", header_size); sqlite3_config(SQLITE_CONFIG_PAGECACHE, sqlPageBuffer, SQLIGHT_PSIZE + header_size, sizeof(sqlPageBuffer) / (SQLIGHT_PSIZE + header_size)); sqlite3_config(SQLITE_CONFIG_LOOKASIDE, 64, 64); sqlite3_initialize(); } ``` SQLight config: ``` #define SQLITE_THREADSAFE 0 #define SQLITE_OS_OTHER 1 #define SQLITE_OMIT_DESERIALIZE 1 #define SQLITE_MAX_MMAP_SIZE 0 #define SQLITE_DEFAULT_MMAP_SIZE 0 //#define SQLITE_DEFAULT_CACHE_SIZE 450 #define SQLITE_STRICT_SUBTYPE 1 //#define SQLITE_MEMDEBUG 1 #define SQLITE_OMIT_AUTOINIT 1 #define SQLITE_OMIT_SHARED_CACHE 1 #define SQLITE_MAX_DEFAULT_PAGE_SIZE 512 #define SQLITE_DEFAULT_PAGE_SIZE 512 #define SQLITE_ENABLE_MEMSYS5 1 #define SQLITE_DEFAULT_LOCKING_MODE 0 ``` eMMC formated with: ``` fx_media_format( &flashMedia, fx_stm32_custom_driver, &this->driverInfo, (UCHAR *) fxDataBuffer, sizeof(fxDataBuffer), const_cast<char *>("DB fs"), 1, 32, 0, 1024*1024*1024/512, 512, 1, 1, 1); ``` I use 1 thread for testing so far (SQLITE_THREADSAFE 0) .

asamuta commented 2025年12月03日 22:12:50 +01:00

I run custom test to insert 10k rows in a simple table. Use 128 MB disk formated with fileX.
As result I have the disk is almost full:

 info: Took 58902 ms to INSERT 10000 cards
 info: Entry: , Attr: 0x20, Size: 17970688 Bytes
 info: DB drive space available: 3584.

before inserting (just after format)

 info: Entry: , Attr: 0x20, Size: 0 Bytes
 info: DB drive space available: 132119040.

Get space as follows:

 ULONG freeBytes;
 status = fx_media_space_available(&flashMedia, &freeBytes);
 if (status == FX_SUCCESS) {
 logInfo("DB drive space available: %lu.\r\n", freeBytes);
 }

May be you have any idea what is going on? The DB file size is just 17970688 bytes.

BTW integrity check works fine for it:

 info: INTEGRITY: ok
 info: MASTER: index | idx_person_name | person | CREATE INDEX idx_person_name ON person(name)
 info: MASTER: table | cards | cards | CREATE TABLE cards(card_id BLOB(16),active_from INTEGER NOT NULL DEFAULT 0,active_to INTEGER NOT NULL DEFAULT 0,group_id INTEGER NOT NULL DEFAULT 0,is_active INTEGER NOT NULL DEFAULT 0,metadata TEXT default '',user_id INTEGER NOT NULL DEFAULT 0)
 info: MASTER: table | migrations | migrations | CREATE TABLE migrations (id INTEGER PRIMARY KEY AUTOINCREMENT,version INTEGER NOT NULL,name TEXT,applied_at INTEGER NOT NULL)
 info: MASTER: table | person | person | CREATE TABLE person (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, created_at INTEGER DEFAULT 0)
 info: MASTER: table | sqlite_sequence | sqlite_sequence | CREATE TABLE sqlite_sequence(name,seq)
 info: DB opened successfully!
 info: DB version: 3.42.0
 info: Current DB user_version = 4
 info: Migrations completed. Final user_version = 4
 info: Execute: 'SELECT group_id FROM cards'
 info: Read: 1000
 info: Read: 2000
 info: Read: 3000
 info: Read: 4000
 info: Read: 5000
 info: Read: 6000
 info: Read: 7000
 info: Read: 8000
 info: Read: 9000
 info: Read: 10000

I run custom test to insert 10k rows in a simple table. Use 128 MB disk formated with fileX. As result I have the disk is almost full: ``` info: Took 58902 ms to INSERT 10000 cards info: Entry: , Attr: 0x20, Size: 17970688 Bytes info: DB drive space available: 3584. ``` before inserting (just after format) ``` info: Entry: , Attr: 0x20, Size: 0 Bytes info: DB drive space available: 132119040. ``` Get space as follows: ``` ULONG freeBytes; status = fx_media_space_available(&flashMedia, &freeBytes); if (status == FX_SUCCESS) { logInfo("DB drive space available: %lu.\r\n", freeBytes); } ``` May be you have any idea what is going on? The DB file size is just 17970688 bytes. BTW integrity check works fine for it: ``` info: INTEGRITY: ok info: MASTER: index | idx_person_name | person | CREATE INDEX idx_person_name ON person(name) info: MASTER: table | cards | cards | CREATE TABLE cards(card_id BLOB(16),active_from INTEGER NOT NULL DEFAULT 0,active_to INTEGER NOT NULL DEFAULT 0,group_id INTEGER NOT NULL DEFAULT 0,is_active INTEGER NOT NULL DEFAULT 0,metadata TEXT default '',user_id INTEGER NOT NULL DEFAULT 0) info: MASTER: table | migrations | migrations | CREATE TABLE migrations (id INTEGER PRIMARY KEY AUTOINCREMENT,version INTEGER NOT NULL,name TEXT,applied_at INTEGER NOT NULL) info: MASTER: table | person | person | CREATE TABLE person (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, created_at INTEGER DEFAULT 0) info: MASTER: table | sqlite_sequence | sqlite_sequence | CREATE TABLE sqlite_sequence(name,seq) info: DB opened successfully! info: DB version: 3.42.0 info: Current DB user_version = 4 info: Migrations completed. Final user_version = 4 info: Execute: 'SELECT group_id FROM cards' info: Read: 1000 info: Read: 2000 info: Read: 3000 info: Read: 4000 info: Read: 5000 info: Read: 6000 info: Read: 7000 info: Read: 8000 info: Read: 9000 info: Read: 10000 ```

dmitryrozhd commented 2025年12月03日 22:52:32 +01:00

First of all, have you implemented the bugfix described in the header of sqlite3_azure.c?

Then I would suggest trying to detect at what moment it goes wrong. Try to write 5000 cards, if it is still bad try 2500 and so on. Then, you could use fx_media_check to see the kind of errors in the file system.

With best regards,
Dmitry

First of all, have you implemented the bugfix described in the header of sqlite3_azure.c? Then I would suggest trying to detect at what moment it goes wrong. Try to write 5000 cards, if it is still bad try 2500 and so on. Then, you could use fx_media_check to see the kind of errors in the file system. With best regards, Dmitry

asamuta commented 2025年12月05日 00:57:47 +01:00

Hi Dmitry, thanks for you time.
Regarding bugfix. As far as I undesrtud it relates cahe in sd card driver. In my case the SD card driver is not used. eMMC read and write implementation is custom, and relies on uncachable memory location dedicated to DMA. It works fine so far.
I decided to conduct the following test. Format, write a 16MB file, write DB in batch print available clusters. Here is a log output:

 info: DB service ready.
 info: [DB service] starting DB engine...
 info: The network cable is connected.
 info: DB drive format success!
debug: DB drive Logical sector size: 512
debug: DB drive Total sectors: 1048576
debug: DB drive Total clusters: 130815
debug: DB drive Bytes per sector: 512
debug: DB drive Sectors per cluster: 8
debug: DB drive FAT size: 1024 sectors
debug: DB drive Available clusters: 130814
 info: IpAddress: 192.168.68.53
 info: NetMask: 255.255.252.0
 info: IpGateway: 192.168.68.1
 info: HTTP WEB Server successfully started.
 info: SNTP: Starting the client...
 info: SNTP: syncing with 0.europe.pool.ntp.org
 info: SNTP: Client has started.
 info: SNTP: update: Dec 4, 2025 23:39:52.75 UTC
 info: Written 512 KB... 130686
 info: Written 1024 KB... 130558
 info: Written 1536 KB... 130430
 info: Written 2048 KB... 130302
 info: Written 2560 KB... 130174
 info: Written 3072 KB... 130046
 info: Written 3584 KB... 129918
 info: Written 4096 KB... 129790
 info: Written 4608 KB... 129662
 info: Written 5120 KB... 129534
 info: Written 5632 KB... 129406
 info: Written 6144 KB... 129278
 info: Written 6656 KB... 129150
 info: Written 7168 KB... 129022
 info: Written 7680 KB... 128894
 info: Written 8192 KB... 128766
 info: Written 8704 KB... 128638
 info: Written 9216 KB... 128510
 info: Written 9728 KB... 128382
 info: Written 10240 KB... 128254
 info: Written 10752 KB... 128126
 info: Written 11264 KB... 127998
 info: Written 11776 KB... 127870
 info: Written 12288 KB... 127742
 info: Written 12800 KB... 127614
 info: Written 13312 KB... 127486
 info: Written 13824 KB... 127358
 info: Written 14336 KB... 127230
 info: Written 14848 KB... 127102
 info: Written 15360 KB... 126974
 info: Written 15872 KB... 126846
 info: Written 16384 KB... 126718
 info: File test.dat written: 32768 chunks (16777216 bytes)
 info: DB drive successfully opened.
 info: DB drive space available: 519036928.
 info: Initializing SQLite3 database...
 info: Header size=168
 info: Header size=192
 info: Entry: , Attr: 0x20, Size: 16777216 Bytes
 info: DB drive space available: 519036928.
 info: Entry: test.dat, Attr: 0x20, Size: 16777216 Bytes
 info: Entry: db, Attr: 0x20, Size: 0 Bytes
 info: DB drive space available: 519036928.
 info: INTEGRITY: ok
 info: DB version: 3.42.0
 info: Current DB user_version = 0
 info: Applying migration 1: CREATE_PERSON_TABLE
 info: Migration 1 applied successfully
 info: Applying migration 2: ADD_TIMESTAMP_TO_PERSON
 info: Migration 2 applied successfully
 info: Applying migration 3: CREATE_INDEX_IDX_PERSON_NAME
 info: Migration 3 applied successfully
 info: Applying migration 4: CREATE_CARDS_TABLE
 info: Migration 4 applied successfully
 info: Migrations completed. Final user_version = 4
 info: Execute: 'SELECT group_id FROM cards'
 info: Progress: 100 cards, time: 165 , clusters available: 126669
 info: Progress: 200 cards, time: 343 , clusters available: 126580
 info: Progress: 300 cards, time: 521 , clusters available: 126447
 info: Progress: 400 cards, time: 702 , clusters available: 126270
 info: Progress: 500 cards, time: 879 , clusters available: 126049
 info: Progress: 600 cards, time: 1067 , clusters available: 125784
 info: Progress: 700 cards, time: 1251 , clusters available: 125476
 info: Progress: 800 cards, time: 1433 , clusters available: 125124
 info: Progress: 900 cards, time: 1616 , clusters available: 124728
 info: Progress: 1000 cards, time: 1804 , clusters available: 124288
 info: Progress: 1100 cards, time: 1991 , clusters available: 123804
 info: Progress: 1200 cards, time: 2184 , clusters available: 123276
 info: Progress: 1300 cards, time: 2372 , clusters available: 122704
 info: Progress: 1400 cards, time: 2562 , clusters available: 122089
 info: Progress: 1500 cards, time: 2754 , clusters available: 121430
 info: Progress: 1600 cards, time: 2948 , clusters available: 120727
 info: Progress: 1700 cards, time: 3142 , clusters available: 119980
 info: Progress: 1800 cards, time: 3345 , clusters available: 119189
 info: Progress: 1900 cards, time: 3545 , clusters available: 118354
 info: Progress: 2000 cards, time: 3746 , clusters available: 117475
 info: Progress: 2100 cards, time: 3952 , clusters available: 116552
 info: Progress: 2200 cards, time: 4174 , clusters available: 115586
 info: Progress: 2300 cards, time: 4385 , clusters available: 114576
 info: Progress: 2400 cards, time: 4594 , clusters available: 113522
 info: Progress: 2500 cards, time: 4807 , clusters available: 112424
 info: Progress: 2600 cards, time: 5022 , clusters available: 111282
 info: Progress: 2700 cards, time: 5243 , clusters available: 110096
 info: Progress: 2800 cards, time: 5464 , clusters available: 108866
 info: Progress: 2900 cards, time: 5689 , clusters available: 107593
 info: Progress: 3000 cards, time: 5922 , clusters available: 106276
 info: Progress: 3100 cards, time: 6150 , clusters available: 104915
 info: Progress: 3200 cards, time: 6396 , clusters available: 103510
 info: Progress: 3300 cards, time: 6642 , clusters available: 102061
 info: Progress: 3400 cards, time: 6881 , clusters available: 100568
 info: Progress: 3500 cards, time: 7124 , clusters available: 99031
 info: Progress: 3600 cards, time: 7371 , clusters available: 97451
 info: Progress: 3700 cards, time: 7619 , clusters available: 95827
 info: Progress: 3800 cards, time: 7881 , clusters available: 94159
 info: Progress: 3900 cards, time: 8150 , clusters available: 92447
 info: Progress: 4000 cards, time: 8409 , clusters available: 90691
 info: Progress: 4100 cards, time: 8671 , clusters available: 88891
 info: Progress: 4200 cards, time: 8937 , clusters available: 87047
 info: Progress: 4300 cards, time: 9208 , clusters available: 85160
 info: Progress: 4400 cards, time: 9482 , clusters available: 83229
 info: Progress: 4500 cards, time: 9760 , clusters available: 81254
 info: Progress: 4600 cards, time: 10041 , clusters available: 79235
 info: Progress: 4700 cards, time: 10329 , clusters available: 77172
 info: Progress: 4800 cards, time: 10619 , clusters available: 75065
 info: Progress: 4900 cards, time: 10917 , clusters available: 72914
 info: Progress: 5000 cards, time: 11217 , clusters available: 70719
 info: Progress: 5100 cards, time: 11533 , clusters available: 68481
 info: Progress: 5200 cards, time: 11858 , clusters available: 66199
 info: Progress: 5300 cards, time: 12192 , clusters available: 63873
 info: Progress: 5400 cards, time: 12522 , clusters available: 61503
 info: Progress: 5500 cards, time: 12846 , clusters available: 59089
 info: Progress: 5600 cards, time: 13183 , clusters available: 56631
 info: Progress: 5700 cards, time: 13517 , clusters available: 54129
 info: Progress: 5800 cards, time: 13857 , clusters available: 51584
 info: Progress: 5900 cards, time: 14201 , clusters available: 48995
 info: Progress: 6000 cards, time: 14553 , clusters available: 46362
 info: Progress: 6100 cards, time: 14905 , clusters available: 43685
 info: Progress: 6200 cards, time: 15271 , clusters available: 40964
 info: Progress: 6300 cards, time: 15637 , clusters available: 38199
 info: Progress: 6400 cards, time: 16008 , clusters available: 35390
 info: Progress: 6500 cards, time: 16384 , clusters available: 32538
 info: Progress: 6600 cards, time: 16767 , clusters available: 29642
 info: Progress: 6700 cards, time: 17180 , clusters available: 26702
 info: Progress: 6800 cards, time: 17577 , clusters available: 23718
 info: Progress: 6900 cards, time: 17992 , clusters available: 20690
 info: Progress: 7000 cards, time: 18406 , clusters available: 17618
 info: Progress: 7100 cards, time: 18826 , clusters available: 14502
 info: Progress: 7200 cards, time: 19267 , clusters available: 11342
 info: Progress: 7300 cards, time: 19715 , clusters available: 8138
 info: Progress: 7400 cards, time: 20152 , clusters available: 4891
 info: Progress: 7500 cards, time: 20591 , clusters available: 1600
 info: Progress: 7600 cards, time: 21021 , clusters available: 83
 info: Progress: 7700 cards, time: 21436 , clusters available: 76
 info: Progress: 7800 cards, time: 21839 , clusters available: 75
 info: Progress: 7900 cards, time: 22243 , clusters available: 74
 info: Progress: 8000 cards, time: 22654 , clusters available: 73
 info: Progress: 8100 cards, time: 23075 , clusters available: 72
 info: Progress: 8200 cards, time: 23481 , clusters available: 71
 info: Progress: 8300 cards, time: 23892 , clusters available: 70
 info: Progress: 8400 cards, time: 24299 , clusters available: 69
 info: Progress: 8500 cards, time: 24707 , clusters available: 68
 info: Progress: 8600 cards, time: 25132 , clusters available: 67
 info: Progress: 8700 cards, time: 25545 , clusters available: 66
 info: Progress: 8800 cards, time: 25952 , clusters available: 65
 info: Progress: 8900 cards, time: 26358 , clusters available: 64
 info: Progress: 9000 cards, time: 26768 , clusters available: 63
 info: Progress: 9100 cards, time: 27180 , clusters available: 62
 info: Progress: 9200 cards, time: 27589 , clusters available: 61
 info: Progress: 9300 cards, time: 27997 , clusters available: 60
 info: Progress: 9400 cards, time: 28414 , clusters available: 59
 info: Progress: 9500 cards, time: 28822 , clusters available: 58
 info: Progress: 9600 cards, time: 29236 , clusters available: 57
 info: Progress: 9700 cards, time: 29662 , clusters available: 56
 info: Progress: 9800 cards, time: 30070 , clusters available: 55
 info: Progress: 9900 cards, time: 30478 , clusters available: 54
 info: Progress: 10000 cards, time: 30889 , clusters available: 53
 info: Took 30896 ms to INSERT 10000 cards
 info: INTEGRITY: ok
 info: MASTER: index | idx_person_name | person | CREATE INDEX idx_person_name ON person(name)
 info: MASTER: table | cards | cards | CREATE TABLE cards(card_id BLOB(16),active_from INTEGER NOT NULL DEFAULT 0,active_to INTEGER NOT NULL DEFAULT 0,group_id INTEGER NOT NULL DEFAULT 0,is_active INTEGER NOT NULL DEFAULT 0,metadata TEXT default '',user_id INTEGER NOT NULL DEFAULT 0)
 info: MASTER: table | migrations | migrations | CREATE TABLE migrations (id INTEGER PRIMARY KEY AUTOINCREMENT,version INTEGER NOT NULL,name TEXT,applied_at INTEGER NOT NULL)
 info: MASTER: table | person | person | CREATE TABLE person (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, created_at INTEGER DEFAULT 0)
 info: MASTER: table | sqlite_sequence | sqlite_sequence | CREATE TABLE sqlite_sequence(name,seq)
 info: Entry: , Attr: 0x20, Size: 16777216 Bytes
 info: Entry: db, Attr: 0x20, Size: 17970688 Bytes
 info: DB drive space available: 217088.

the test code

 bool DBService::write16MBinChunks(const char *fileName) {
 constexpr ULONG CHUNK_SIZE = 512;
 constexpr ULONG TOTAL_SIZE = 16 * 1024 * 1024; // 16 МБ
 constexpr ULONG CHUNKS_COUNT = TOTAL_SIZE / CHUNK_SIZE;
 FX_FILE file;
 UINT status = fx_file_create(&flashMedia, const_cast<char *>(fileName));
 if (status != FX_SUCCESS && status != FX_ALREADY_CREATED) {
 logError("Failed to create file %s: error 0x%02x\r\n", fileName, status);
 return false;
 }
 status = fx_file_open(&flashMedia, &file, const_cast<char*>(fileName), FX_OPEN_FOR_WRITE);
 if (status != FX_SUCCESS) {
 logError("Failed to open file %s for write: error 0x%02x\r\n", fileName, status);
 return false;
 }
 UCHAR buffer[CHUNK_SIZE];
 memset(buffer, 0xAA, CHUNK_SIZE);
 for (ULONG i = 0; i < CHUNKS_COUNT; ++i) {
 status = fx_file_write(&file, buffer, CHUNK_SIZE);
 if (status != FX_SUCCESS) {
 logError("Error writing chunk %lu: error 0x%02x\r\n", i, status);
 fx_file_close(&file);
 return false;
 }
 if ((i + 1) % 1024 == 0)
 logInfo("Written %lu KB... \t\t %lu\r\n", ((i+1) * CHUNK_SIZE) / 1024, flashMedia.fx_media_available_clusters);
 }
 fx_file_close(&file);
 logInfo("File %s written: %lu chunks (%lu bytes)\r\n", fileName, CHUNKS_COUNT, TOTAL_SIZE);
 fx_media_flush(&flashMedia);
 return true;
 }
 bool DBService::format() {
 UINT ret = fx_media_format(
 &flashMedia,
 fx_stm32_custom_driver,
 &this->driverInfo,
 (UCHAR *) fxDataBuffer,
 sizeof(fxDataBuffer),
 const_cast<char *>("DB fs"),
 2, 
 128, // root entries
 0, // hidden sectors 
 1024 * 1024 * 512 / 512, // sectors 
 512, // bytes per sector
 8, // sectors per cluster
 1, // heads
 1 // sectors per track
 );
 if (ret != FX_SUCCESS) {
 logError("DB drive format failed: 0x%02x\r\n", ret);
 return false;
 } else {
 logInfo("DB drive format success!\r\n");
 return true;
 }
 }
 int DBService::insertCardTest() {
 int CARDS_TRANSACTIONS = 100;
 int CARDS_TO_INSERT = 100;
 int startTime = 0, elapsedTime = 0;
 batchReadCards(db);
 execSql("delete from cards;");
 startTime = HAL_GetTick();
 odb::DBCard card;
 for (int t = 0; t < CARDS_TRANSACTIONS; t++) {
 execSql("BEGIN TRANSACTION;");
 for (int i = 0; i < CARDS_TO_INSERT; i++) {
 int d = i + (CARDS_TO_INSERT * t);
 *((int *) card.cardId) = d;
 card.groupId = rand();
 insertCard(card);
 }
 execSql("COMMIT;");
 elapsedTime = HAL_GetTick() - startTime;
 logInfo("Progress: %d cards, time: %d , clusters available: %d\r\n", (t+1)*CARDS_TO_INSERT, elapsedTime, flashMedia.fx_media_available_clusters);
 }
 elapsedTime = HAL_GetTick() - startTime;
 logInfo("Took %d ms to INSERT %d cards\r\n", elapsedTime, CARDS_TO_INSERT*CARDS_TRANSACTIONS);
 return 0;
 }
 void DBService::checkDB() {
 sqlite3_stmt *stmt = NULL;
 char *errmsg = NULL;
 /* integrity_check */
 if (sqlite3_prepare_v2(db, "PRAGMA integrity_check;", -1, &stmt, NULL) == SQLITE_OK) {
 while (sqlite3_step(stmt) == SQLITE_ROW) {
 logInfo("INTEGRITY: %s\r\n", sqlite3_column_text(stmt,0));
 }
 sqlite3_finalize(stmt);
 } else {
 logError("PRAGMA integrity_check prepare failed: %s\r\n", sqlite3_errmsg(db));
 }
 /* dump sqlite_master */
 if (sqlite3_prepare_v2(db, "SELECT type, name, tbl_name, sql FROM sqlite_master ORDER BY type, name;", -1, &stmt, NULL) == SQLITE_OK) {
 while (sqlite3_step(stmt) == SQLITE_ROW) {
 logInfo("MASTER: %s | %s | %s | %s\r\n",
 sqlite3_column_text(stmt,0),
 sqlite3_column_text(stmt,1),
 sqlite3_column_text(stmt,2),
 sqlite3_column_text(stmt,3) ? sqlite3_column_text(stmt,3) : (const unsigned char*)"(NULL)");
 }
 sqlite3_finalize(stmt);
 } else {
 logError("SELECT sqlite_master failed: %s\r\n", sqlite3_errmsg(db));
 }
 }

So, looks like the issue is relates to DB operations or to fileX. (My theory is that FileX does not properly mark sectors as free during database file updates.)
BTW thread stack size for DB thread is 13KB (increasing it from 8KB to 13KB didn't show any difference).
It is strange that integrity check shows that DB is fine.
I would appreciate any ideas. Thanks in advance.

Hi Dmitry, thanks for you time. Regarding bugfix. As far as I undesrtud it relates cahe in sd card driver. In my case the SD card driver is not used. eMMC read and write implementation is custom, and relies on uncachable memory location dedicated to DMA. It works fine so far. I decided to conduct the following test. Format, write a 16MB file, write DB in batch print available clusters. Here is a log output: ``` info: DB service ready. info: [DB service] starting DB engine... info: The network cable is connected. info: DB drive format success! debug: DB drive Logical sector size: 512 debug: DB drive Total sectors: 1048576 debug: DB drive Total clusters: 130815 debug: DB drive Bytes per sector: 512 debug: DB drive Sectors per cluster: 8 debug: DB drive FAT size: 1024 sectors debug: DB drive Available clusters: 130814 info: IpAddress: 192.168.68.53 info: NetMask: 255.255.252.0 info: IpGateway: 192.168.68.1 info: HTTP WEB Server successfully started. info: SNTP: Starting the client... info: SNTP: syncing with 0.europe.pool.ntp.org info: SNTP: Client has started. info: SNTP: update: Dec 4, 2025 23:39:52.75 UTC info: Written 512 KB... 130686 info: Written 1024 KB... 130558 info: Written 1536 KB... 130430 info: Written 2048 KB... 130302 info: Written 2560 KB... 130174 info: Written 3072 KB... 130046 info: Written 3584 KB... 129918 info: Written 4096 KB... 129790 info: Written 4608 KB... 129662 info: Written 5120 KB... 129534 info: Written 5632 KB... 129406 info: Written 6144 KB... 129278 info: Written 6656 KB... 129150 info: Written 7168 KB... 129022 info: Written 7680 KB... 128894 info: Written 8192 KB... 128766 info: Written 8704 KB... 128638 info: Written 9216 KB... 128510 info: Written 9728 KB... 128382 info: Written 10240 KB... 128254 info: Written 10752 KB... 128126 info: Written 11264 KB... 127998 info: Written 11776 KB... 127870 info: Written 12288 KB... 127742 info: Written 12800 KB... 127614 info: Written 13312 KB... 127486 info: Written 13824 KB... 127358 info: Written 14336 KB... 127230 info: Written 14848 KB... 127102 info: Written 15360 KB... 126974 info: Written 15872 KB... 126846 info: Written 16384 KB... 126718 info: File test.dat written: 32768 chunks (16777216 bytes) info: DB drive successfully opened. info: DB drive space available: 519036928. info: Initializing SQLite3 database... info: Header size=168 info: Header size=192 info: Entry: , Attr: 0x20, Size: 16777216 Bytes info: DB drive space available: 519036928. info: Entry: test.dat, Attr: 0x20, Size: 16777216 Bytes info: Entry: db, Attr: 0x20, Size: 0 Bytes info: DB drive space available: 519036928. info: INTEGRITY: ok info: DB version: 3.42.0 info: Current DB user_version = 0 info: Applying migration 1: CREATE_PERSON_TABLE info: Migration 1 applied successfully info: Applying migration 2: ADD_TIMESTAMP_TO_PERSON info: Migration 2 applied successfully info: Applying migration 3: CREATE_INDEX_IDX_PERSON_NAME info: Migration 3 applied successfully info: Applying migration 4: CREATE_CARDS_TABLE info: Migration 4 applied successfully info: Migrations completed. Final user_version = 4 info: Execute: 'SELECT group_id FROM cards' info: Progress: 100 cards, time: 165 , clusters available: 126669 info: Progress: 200 cards, time: 343 , clusters available: 126580 info: Progress: 300 cards, time: 521 , clusters available: 126447 info: Progress: 400 cards, time: 702 , clusters available: 126270 info: Progress: 500 cards, time: 879 , clusters available: 126049 info: Progress: 600 cards, time: 1067 , clusters available: 125784 info: Progress: 700 cards, time: 1251 , clusters available: 125476 info: Progress: 800 cards, time: 1433 , clusters available: 125124 info: Progress: 900 cards, time: 1616 , clusters available: 124728 info: Progress: 1000 cards, time: 1804 , clusters available: 124288 info: Progress: 1100 cards, time: 1991 , clusters available: 123804 info: Progress: 1200 cards, time: 2184 , clusters available: 123276 info: Progress: 1300 cards, time: 2372 , clusters available: 122704 info: Progress: 1400 cards, time: 2562 , clusters available: 122089 info: Progress: 1500 cards, time: 2754 , clusters available: 121430 info: Progress: 1600 cards, time: 2948 , clusters available: 120727 info: Progress: 1700 cards, time: 3142 , clusters available: 119980 info: Progress: 1800 cards, time: 3345 , clusters available: 119189 info: Progress: 1900 cards, time: 3545 , clusters available: 118354 info: Progress: 2000 cards, time: 3746 , clusters available: 117475 info: Progress: 2100 cards, time: 3952 , clusters available: 116552 info: Progress: 2200 cards, time: 4174 , clusters available: 115586 info: Progress: 2300 cards, time: 4385 , clusters available: 114576 info: Progress: 2400 cards, time: 4594 , clusters available: 113522 info: Progress: 2500 cards, time: 4807 , clusters available: 112424 info: Progress: 2600 cards, time: 5022 , clusters available: 111282 info: Progress: 2700 cards, time: 5243 , clusters available: 110096 info: Progress: 2800 cards, time: 5464 , clusters available: 108866 info: Progress: 2900 cards, time: 5689 , clusters available: 107593 info: Progress: 3000 cards, time: 5922 , clusters available: 106276 info: Progress: 3100 cards, time: 6150 , clusters available: 104915 info: Progress: 3200 cards, time: 6396 , clusters available: 103510 info: Progress: 3300 cards, time: 6642 , clusters available: 102061 info: Progress: 3400 cards, time: 6881 , clusters available: 100568 info: Progress: 3500 cards, time: 7124 , clusters available: 99031 info: Progress: 3600 cards, time: 7371 , clusters available: 97451 info: Progress: 3700 cards, time: 7619 , clusters available: 95827 info: Progress: 3800 cards, time: 7881 , clusters available: 94159 info: Progress: 3900 cards, time: 8150 , clusters available: 92447 info: Progress: 4000 cards, time: 8409 , clusters available: 90691 info: Progress: 4100 cards, time: 8671 , clusters available: 88891 info: Progress: 4200 cards, time: 8937 , clusters available: 87047 info: Progress: 4300 cards, time: 9208 , clusters available: 85160 info: Progress: 4400 cards, time: 9482 , clusters available: 83229 info: Progress: 4500 cards, time: 9760 , clusters available: 81254 info: Progress: 4600 cards, time: 10041 , clusters available: 79235 info: Progress: 4700 cards, time: 10329 , clusters available: 77172 info: Progress: 4800 cards, time: 10619 , clusters available: 75065 info: Progress: 4900 cards, time: 10917 , clusters available: 72914 info: Progress: 5000 cards, time: 11217 , clusters available: 70719 info: Progress: 5100 cards, time: 11533 , clusters available: 68481 info: Progress: 5200 cards, time: 11858 , clusters available: 66199 info: Progress: 5300 cards, time: 12192 , clusters available: 63873 info: Progress: 5400 cards, time: 12522 , clusters available: 61503 info: Progress: 5500 cards, time: 12846 , clusters available: 59089 info: Progress: 5600 cards, time: 13183 , clusters available: 56631 info: Progress: 5700 cards, time: 13517 , clusters available: 54129 info: Progress: 5800 cards, time: 13857 , clusters available: 51584 info: Progress: 5900 cards, time: 14201 , clusters available: 48995 info: Progress: 6000 cards, time: 14553 , clusters available: 46362 info: Progress: 6100 cards, time: 14905 , clusters available: 43685 info: Progress: 6200 cards, time: 15271 , clusters available: 40964 info: Progress: 6300 cards, time: 15637 , clusters available: 38199 info: Progress: 6400 cards, time: 16008 , clusters available: 35390 info: Progress: 6500 cards, time: 16384 , clusters available: 32538 info: Progress: 6600 cards, time: 16767 , clusters available: 29642 info: Progress: 6700 cards, time: 17180 , clusters available: 26702 info: Progress: 6800 cards, time: 17577 , clusters available: 23718 info: Progress: 6900 cards, time: 17992 , clusters available: 20690 info: Progress: 7000 cards, time: 18406 , clusters available: 17618 info: Progress: 7100 cards, time: 18826 , clusters available: 14502 info: Progress: 7200 cards, time: 19267 , clusters available: 11342 info: Progress: 7300 cards, time: 19715 , clusters available: 8138 info: Progress: 7400 cards, time: 20152 , clusters available: 4891 info: Progress: 7500 cards, time: 20591 , clusters available: 1600 info: Progress: 7600 cards, time: 21021 , clusters available: 83 info: Progress: 7700 cards, time: 21436 , clusters available: 76 info: Progress: 7800 cards, time: 21839 , clusters available: 75 info: Progress: 7900 cards, time: 22243 , clusters available: 74 info: Progress: 8000 cards, time: 22654 , clusters available: 73 info: Progress: 8100 cards, time: 23075 , clusters available: 72 info: Progress: 8200 cards, time: 23481 , clusters available: 71 info: Progress: 8300 cards, time: 23892 , clusters available: 70 info: Progress: 8400 cards, time: 24299 , clusters available: 69 info: Progress: 8500 cards, time: 24707 , clusters available: 68 info: Progress: 8600 cards, time: 25132 , clusters available: 67 info: Progress: 8700 cards, time: 25545 , clusters available: 66 info: Progress: 8800 cards, time: 25952 , clusters available: 65 info: Progress: 8900 cards, time: 26358 , clusters available: 64 info: Progress: 9000 cards, time: 26768 , clusters available: 63 info: Progress: 9100 cards, time: 27180 , clusters available: 62 info: Progress: 9200 cards, time: 27589 , clusters available: 61 info: Progress: 9300 cards, time: 27997 , clusters available: 60 info: Progress: 9400 cards, time: 28414 , clusters available: 59 info: Progress: 9500 cards, time: 28822 , clusters available: 58 info: Progress: 9600 cards, time: 29236 , clusters available: 57 info: Progress: 9700 cards, time: 29662 , clusters available: 56 info: Progress: 9800 cards, time: 30070 , clusters available: 55 info: Progress: 9900 cards, time: 30478 , clusters available: 54 info: Progress: 10000 cards, time: 30889 , clusters available: 53 info: Took 30896 ms to INSERT 10000 cards info: INTEGRITY: ok info: MASTER: index | idx_person_name | person | CREATE INDEX idx_person_name ON person(name) info: MASTER: table | cards | cards | CREATE TABLE cards(card_id BLOB(16),active_from INTEGER NOT NULL DEFAULT 0,active_to INTEGER NOT NULL DEFAULT 0,group_id INTEGER NOT NULL DEFAULT 0,is_active INTEGER NOT NULL DEFAULT 0,metadata TEXT default '',user_id INTEGER NOT NULL DEFAULT 0) info: MASTER: table | migrations | migrations | CREATE TABLE migrations (id INTEGER PRIMARY KEY AUTOINCREMENT,version INTEGER NOT NULL,name TEXT,applied_at INTEGER NOT NULL) info: MASTER: table | person | person | CREATE TABLE person (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, created_at INTEGER DEFAULT 0) info: MASTER: table | sqlite_sequence | sqlite_sequence | CREATE TABLE sqlite_sequence(name,seq) info: Entry: , Attr: 0x20, Size: 16777216 Bytes info: Entry: db, Attr: 0x20, Size: 17970688 Bytes info: DB drive space available: 217088. ``` the test code ``` bool DBService::write16MBinChunks(const char *fileName) { constexpr ULONG CHUNK_SIZE = 512; constexpr ULONG TOTAL_SIZE = 16 * 1024 * 1024; // 16 МБ constexpr ULONG CHUNKS_COUNT = TOTAL_SIZE / CHUNK_SIZE; FX_FILE file; UINT status = fx_file_create(&flashMedia, const_cast<char *>(fileName)); if (status != FX_SUCCESS && status != FX_ALREADY_CREATED) { logError("Failed to create file %s: error 0x%02x\r\n", fileName, status); return false; } status = fx_file_open(&flashMedia, &file, const_cast<char*>(fileName), FX_OPEN_FOR_WRITE); if (status != FX_SUCCESS) { logError("Failed to open file %s for write: error 0x%02x\r\n", fileName, status); return false; } UCHAR buffer[CHUNK_SIZE]; memset(buffer, 0xAA, CHUNK_SIZE); for (ULONG i = 0; i < CHUNKS_COUNT; ++i) { status = fx_file_write(&file, buffer, CHUNK_SIZE); if (status != FX_SUCCESS) { logError("Error writing chunk %lu: error 0x%02x\r\n", i, status); fx_file_close(&file); return false; } if ((i + 1) % 1024 == 0) logInfo("Written %lu KB... \t\t %lu\r\n", ((i+1) * CHUNK_SIZE) / 1024, flashMedia.fx_media_available_clusters); } fx_file_close(&file); logInfo("File %s written: %lu chunks (%lu bytes)\r\n", fileName, CHUNKS_COUNT, TOTAL_SIZE); fx_media_flush(&flashMedia); return true; } bool DBService::format() { UINT ret = fx_media_format( &flashMedia, fx_stm32_custom_driver, &this->driverInfo, (UCHAR *) fxDataBuffer, sizeof(fxDataBuffer), const_cast<char *>("DB fs"), 2, 128, // root entries 0, // hidden sectors 1024 * 1024 * 512 / 512, // sectors 512, // bytes per sector 8, // sectors per cluster 1, // heads 1 // sectors per track ); if (ret != FX_SUCCESS) { logError("DB drive format failed: 0x%02x\r\n", ret); return false; } else { logInfo("DB drive format success!\r\n"); return true; } } int DBService::insertCardTest() { int CARDS_TRANSACTIONS = 100; int CARDS_TO_INSERT = 100; int startTime = 0, elapsedTime = 0; batchReadCards(db); execSql("delete from cards;"); startTime = HAL_GetTick(); odb::DBCard card; for (int t = 0; t < CARDS_TRANSACTIONS; t++) { execSql("BEGIN TRANSACTION;"); for (int i = 0; i < CARDS_TO_INSERT; i++) { int d = i + (CARDS_TO_INSERT * t); *((int *) card.cardId) = d; card.groupId = rand(); insertCard(card); } execSql("COMMIT;"); elapsedTime = HAL_GetTick() - startTime; logInfo("Progress: %d cards, time: %d , clusters available: %d\r\n", (t+1)*CARDS_TO_INSERT, elapsedTime, flashMedia.fx_media_available_clusters); } elapsedTime = HAL_GetTick() - startTime; logInfo("Took %d ms to INSERT %d cards\r\n", elapsedTime, CARDS_TO_INSERT*CARDS_TRANSACTIONS); return 0; } void DBService::checkDB() { sqlite3_stmt *stmt = NULL; char *errmsg = NULL; /* integrity_check */ if (sqlite3_prepare_v2(db, "PRAGMA integrity_check;", -1, &stmt, NULL) == SQLITE_OK) { while (sqlite3_step(stmt) == SQLITE_ROW) { logInfo("INTEGRITY: %s\r\n", sqlite3_column_text(stmt,0)); } sqlite3_finalize(stmt); } else { logError("PRAGMA integrity_check prepare failed: %s\r\n", sqlite3_errmsg(db)); } /* dump sqlite_master */ if (sqlite3_prepare_v2(db, "SELECT type, name, tbl_name, sql FROM sqlite_master ORDER BY type, name;", -1, &stmt, NULL) == SQLITE_OK) { while (sqlite3_step(stmt) == SQLITE_ROW) { logInfo("MASTER: %s | %s | %s | %s\r\n", sqlite3_column_text(stmt,0), sqlite3_column_text(stmt,1), sqlite3_column_text(stmt,2), sqlite3_column_text(stmt,3) ? sqlite3_column_text(stmt,3) : (const unsigned char*)"(NULL)"); } sqlite3_finalize(stmt); } else { logError("SELECT sqlite_master failed: %s\r\n", sqlite3_errmsg(db)); } } ``` So, looks like the issue is relates to DB operations or to fileX. (My theory is that FileX does not properly mark sectors as free during database file updates.) BTW thread stack size for DB thread is 13KB (increasing it from 8KB to 13KB didn't show any difference). It is strange that integrity check shows that DB is fine. I would appreciate any ideas. Thanks in advance.

dmitryrozhd commented 2025年12月05日 09:18:43 +01:00

Hi asamuta,

what I can see from the first look is that the returned free space may be not right. If you really had only 83 clusters at time 21021 your test could not run further, but it runs. Then it may be a good idea to check the space after the database is vacuumed and closed and its journal truncated or deleted (please make sure it is not in PERSIST mode). If that will not make things clear I would then run fx_media_check.

With best regards,
Dmitry

Hi asamuta, what I can see from the first look is that the returned free space may be not right. If you really had only 83 clusters at time 21021 your test could not run further, but it runs. Then it may be a good idea to check the space after the database is vacuumed and closed and its journal truncated or deleted (please make sure it is not in PERSIST mode). If that will not make things clear I would then run fx_media_check. With best regards, Dmitry

dmitryrozhd commented 2025年12月05日 14:48:18 +01:00

Hi asamuta,

I have probably found a bug. I have pushed the fix, please try.

WIth best regards,
Dmitry

Hi asamuta, I have probably found a bug. I have pushed the fix, please try. WIth best regards, Dmitry

dmitryrozhd commented 2025年12月10日 22:10:49 +01:00

Hi asamuta,

I do not receive your answers so I am closing the issue. In a case the latest release does not solve the problem please do not hesitate to open it again.

With best regards,
Dmitry

Hi asamuta, I do not receive your answers so I am closing the issue. In a case the latest release does not solve the problem please do not hesitate to open it again. With best regards, Dmitry

asamuta commented 2025年12月11日 22:32:46 +01:00

Hi Dmitry,
First of all thanks for the fix, now it works much better!
Still in progress testing it.
Will provide some results later.

Hi Dmitry, First of all thanks for the fix, now it works much better! Still in progress testing it. Will provide some results later.