(778) SQLITE_IOERR_WRITE when adding a table using UNIQUE or PRIMARY KEY on spiffs

[mr-wiggle]

I am having an issue where I cannot add tables using UNIQUE or PRIMARY KEY. This produces an disk i/o error. my DB file is on SPIFFS. I do not have an SD card, so can't test if this happens on SD.

To reproduce the issue:

1. load example code sqlite3_spiffs (I can compile and run fine here, no issues) https://github.com/siara-cc/esp32_arduino_sqlite3_lib/tree/master/examples/sqlite3_spiffs
2. Change the SQL command on line 102 to have either PRIMARY KEY or UNIQUE for any column
   rc = db_exec(db1, "CREATE TABLE test1 (id INTEGER UNIQUE, content);");
3. compile and run. Now the i/o error shows up.

load:0x3fff0018,len:4
load:0x3fff001c,len:1216
ho 0 tail 12 room 4
load:0x40078000,len:9720
ho 0 tail 12 room 4
load:0x40080400,len:6352
entry 0x400806b8
FILE: /test1.db SIZE: 0
FILE: /test2.db SIZE: 0
FILE: /test1.db-journal SIZE: 1024
Opened database successfully
Opened database successfully
CREATE TABLE test1 (id INTEGER UNIQUE, content);
SQL error: disk I/O error
Time taken:17245

From https://www.sqlite.org/rescode.html#ioerr_write:

(778) SQLITE_IOERR_WRITE
The SQLITE_IOERR_WRITE error code is an extended error code for SQLITE_IOERR indicating an I/O error in the VFS layer while trying to write into a file on disk. This error might result from a hardware malfunction or because a filesystem came unmounted while the file was open. This error should not occur if the filesystem is full as there is a separate error code (SQLITE_FULL) for that purpose.

And here's some info on the VFS layer: https://www.sqlite.org/vfs.html

I can't figure out why adding UNIQUE to the sql would cause a disk error. Even with debugging set to verbose, I'm not getting any more information. Using the console example, I was able to get the 778 error code when entering the same CREATE TABLE sql command, but I'm stuck there. I can't seem to get any more info.

Based on the nature of the 778 error being related to the VFS layer, it seems like the issue might be in the library.

Any thoughts?

[siara-cc]

Not sure whats wrong with SPIFFS. Since I had the ESP32 SDMMC breakout board handy, I tried it out with the Micro SD and it works fine:

Thanks for providing detailed info.

[siara-cc]

This library relies on the VFS layer of ESP32 for SPIFFS, SD_MMC and SD_SPI. I have not checked the code though to say that the problem is definitely not in this library.

[mr-wiggle]

Wow, thanks for the super fast response!

I'm unfortunately not very well read when it comes to VFS layers and how they operate. The literature suggests that you can choose which VFS you'd like to use with SQLite. https://sqlite.org/c3ref/vfs_find.html Are you aware of any others that I might be able to try in place of the default one?

The thing that really doesn't make send to me is: If the SQLite is just manipulating a single .db file, why would it matter what is being written to the VFS layer. ie, wouldn't creating a database with or without constraints be the same as far as the file system is concerned? It's just writing bits to a file.

[mr-wiggle]

Not sure if this is a clue or completely unrelated: I can't get the library to compile in Sloeber (basically Eclipse but uses the Arduino ESP32 core). Just including the library causes some errors:

shox96_0_2.cpp - line 274: ol = append_bits(out, ol, c_95[c_in], l_95[c_in], state); is giving me error:

C:\Users\dan\Documents\Arduino\libraries\Sqlite3Esp32\src\shox96_0_2.cpp:274:44: error: array subscript has type 'char' [-Werror=char-subscripts]

[siara-cc]

C:\Users\dan\Documents\Arduino\libraries\Sqlite3Esp32\src\shox96_0_2.cpp:274:44: error: array subscript has type 'char' [-Werror=char-subscripts]

You could overcome by changing the line to:
ol = append_bits(out, ol, c_95[(int)c_in], l_95[(int)c_in], state);
or wait for me to change it.
and it is unrelated to the issue with SPIFFS.

It will take me some time to look into why it does not work with SPIFFS.
Not sure what your use case is, but have you looked into my other library that overcomes the memory issues faced with this library?
https://github.com/siara-cc/sqlite_micro_logger_arduino
The unique constraint can be implemented by looking for the ID in your code using binary search, assuming it will be recorded in ascending order (see the doc).

[jason7sc]

@mr-wiggle I can confirm this happens for me as well. I'm not sure if the SPIFFS partition has anything to do with it or not, but I've located where the error occurs. I'm using the IDF version of this, which has a slightly different esp32.c file vs the esp32.cpp file included with the arduino version, but the underlying operation that causes the error is the same.

I traced it down to this:

Arduino esp32.cpp in function ESP32DirectWrite lines 106-110

ofst = fseek(p->fp, iOfst, SEEK_SET); //lseek(p->fd, iOfst, SEEK_SET);
if( ofst != 0 ){
    //Serial.println("Seek error");
    return SQLITE_IOERR_WRITE;
}

ESP-IDF esp32.c in function esp32_Write lines 448-451

ofst = fseek(file->fd, iofst, SEEK_SET);
if (ofst != 0) {
	return SQLITE_IOERR_SEEK;
}

If I first create the table without any constraints (primary_key, unique, auto-increment), then later add those constraints, it will work fine. If I try and create a new table with any of those constraints, then fseek returns -1.

@siara-cc Any ideas on what might be causing this?

[siara-cc]

@mr-wiggle @jason7sc
Please try now by cloning this repo.. I changed the config to just bare minimum and let SQLite used the defaults for most things. Also I have upgraded to latest version of SQLite. Now I don't see the error when.I make it CREATE TABLE test1 (id INTEGER UNIQUE, content);.

[jason7sc]

@siara-cc thanks for responding.

I'm using ESP-IDF, so I can't verify the changes for Arduino, but here are my results using the SPIFFS example from the esp-idf examples repo:

I updated the sqlite library files and the config file, but I'm unable to create/open the database now. Using the old config, I can still open/create the database and perform the same operations as before, so the new sqlite library files don't appear to be the issue.

I tried adding/modifying some config parameters I thought would be relevant, but still couldn't get it to work. Are there other config parameters that should be set for ESP-IDF?

Here is the debug output for the disk I/O operations. It looks like fseek is failing at esp32_Read: 2r -1 != 24 FAIL (fseek returns -1):

esp32_FullPathname: /storage/test.sqlite
esp32_Open: 0o /storage/test.sqlite r
esp32_Access: /storage/test.sqlite 0 -1 0
esp32_Open: 1o /storage/test.sqlite w+
esp32_Open: 2o /storage/test.sqlite OK
esp32_DeviceCharacteristics:
esp32_DeviceCharacteristics:
esp32_SectorSize:
esp32_DeviceCharacteristics:
esp32_SectorSize:
esp32_Read: 1r /storage/test.sqlite 100 0[0] 
esp32_Read: 3r /storage/test.sqlite 0 100 FAIL
esp32_FileControl:
esp32_FileControl:
esp32_Lock:Not locked
esp32_Access: /storage/test.sqlite-journal 0 -1 0
esp32_Access: /storage/test.sqlite-wal 0 -1 0
esp32_FileSize: /storage/test.sqlite:  0[0]
esp32_DeviceCharacteristics:
esp32_Unlock:
esp32_Lock:Not locked
esp32_Access: /storage/test.sqlite-journal 0 -1 0
esp32_Read: 1r /storage/test.sqlite 16 24[24] 
esp32_Read: 2r -1 != 24 FAIL
esp32_Access: /storage/test.sqlite-wal 0 -1 0
esp32_FileSize: /storage/test.sqlite:  0[0]
esp32_DeviceCharacteristics:
esp32_Unlock:
esp32_Lock:Not locked
esp32_Access: /storage/test.sqlite-journal 0 -1 0
esp32_Read: 1r /storage/test.sqlite 16 24[24] 
esp32_Read: 2r -1 != 24 FAIL
esp32_Access: /storage/test.sqlite-wal 0 -1 0
esp32_FileSize: /storage/test.sqlite:  0[0]
esp32_Lock:Not locked
esp32_Open: 0o /storage/test.sqlite-journal r
esp32_Access: /storage/test.sqlite-journal 0 -1 0
esp32_Open: 1o /storage/test.sqlite-journal w+
esp32_Open: 2o /storage/test.sqlite-journal MEM OK
esp32_DeviceCharacteristics:
esp32_Randomness
esp32mem_Write: /storage/test.sqlite-journal [0] [4096] OK
esp32_Lock:Not locked
esp32_DeviceCharacteristics:
esp32mem_Read: /storage/test.sqlite-journal [4096] [8] OK
esp32mem_Sync: /storage/test.sqlite-journal OK
esp32mem_Write: /storage/test.sqlite-journal [0] [12] OK
esp32mem_Sync: /storage/test.sqlite-journal OK
esp32_FileControl:
esp32_Write: 1w /storage/test.sqlite 4096 0[0] 
esp32_Write: 3w /storage/test.sqlite OK
esp32_Write: 1w /storage/test.sqlite 4096 4096[4096] 
esp32_Write: 3w /storage/test.sqlite OK
esp32_FileControl:
esp32_Sync( /storage/test.sqlite: ): 0 
esp32mem_Close: /storage/test.sqlite-journal OK
esp32_Unlock:
esp32_DeviceCharacteristics:
esp32_Unlock:
esp32_FileControl:
esp32_DeviceCharacteristics:
esp32_Unlock:
esp32_Close: /storage/test.sqlite 0
Error: disk I/O error: disk I/O error

[mr-wiggle]

I too am having issues with the updated library.

I updated the Sqlite3Esp32 Library to 2.1.0 in Arduino IDE. I still get the i/o error (code 10) when creating a table using CREATE TABLE test1 (id INTEGER UNIQUE, content); This is in an isolated sketch with nothing else going on.

Version 2.1.0 is also creating memory issues in my project (which is in Sloeber IDE). It appears to be interfering with the WiFi ssl_client. I haven't had a chance to dig into it yet, but I'm getting error code 7 in sqlite (malloc failied) and this from the ssl client [E][ssl_client.cpp:33] _handle_error(): [start_ssl_client():199]: (-16) BIGNUM - Memory allocation failed. With no changes other than switching from V2.0 and V2.1, this issue goes away on 2.0 and returns when I update to V2.1 again.

@siara-cc happy to do any testing you may need, just let me know!

@jason7sc thanks for the sleuthing!

[siara-cc]

@mr-wiggle Thanks. I am not sure why UNIQUE wouldn't work for you. Also I created a release 2.2 which does not seem to show up on the Library Manager.
I have now restored previous config settings. Hopefully memory issues are not caused by the new Sqlite version. Please try again with version 2.3.

[mr-wiggle]

I tested using V2.3 and have the same issues (both with the Unique constraint causing the disk I/O error and with the memory issues ( malloc failed in Sqlite and memory allocation errors in the ssl_client ). Reverting back to V2.0 solved the memory issues.

My test with V2.3 consisted of loading the example sketch "sqlite3_spiffs" and running it. It runs ok with all queries succeeding as loaded. Then added UNIQUE to create table test1 statement and got the SQL error: disk I/O error

I'm using:

• Arduino Esp32 Core V1.0.4
• Arduino IDE 1.8.10
• ESP32 Chip is ESP32D0WDQ5 (revision 1)

[siara-cc]

@mr-wiggle I will check what is going wrong.

[illosan]

I've the same issue.

I'm using an esp32 and the error come when i try to create a table with AUTOINCREMENT, no problem if i don't use this feature.

[siara-cc]

This solution may solve your problems but I have not tried it:
#21 (comment)

[illosan]

Same error with AUTOINCREMENT. I hadn't written it before but occasionally (always using spiffs) there is an I / O error

[ASH247iT]

Hi - I have the same error with AUTOINCREMENT using spiffs - it gives me an I / O error
If I remove AUTOINCREMENT from this statement it works
"CREATE TABLE IF NOT EXISTS test1 (id INTEGER PRIMARY KEY, content TEXT);"
I havent tried updating the table and adding AUTOINCREMENT later yet

[illosan]

Updating the table don't work...

[ASH247iT]

@illosan Thanks for the info
I found an interesting article about Autoincrement in SQLite that might help as a workaround for anyone interested - it basically says you dont need it - it will do it for you
Read this from SQLiteTutorial : https://www.sqlitetutorial.net/sqlite-autoincrement/

I tried it out using the spiffs example (modified) and it does work - I tried a restart before the end and you can see it continues to increment

␛[0;32mI (89501) sp: SQL : INSERT INTO test1(content) VALUES ('hello from ESP32 : 0 ');
␛[0;32mI (89961) sp: Operation successful␛[0m
␛[0;32mI (89961) sp: SQL insert suceessful␛[0m
␛[0;32mI (89961) sp: SQL : SELECT * FROM test1;
␛[0;32mI (89981) sp: Callback function called: ␛[0m
␛[0;32mI (89981) sp: id = 1
␛[0;32mI (89981) sp: content = hello from ESP32 : 0
␛[0;32mI (89981) sp: Callback function called: ␛[0m
␛[0;32mI (89991) sp: id = 2
␛[0;32mI (89991) sp: content = hello from ESP32 : 1
␛[0;32mI (89991) sp: Callback function called: ␛[0m
␛[0;32mI (90001) sp: id = 3
␛[0;32mI (90001) sp: content = hello from ESP32 : 2
␛[0;32mI (90011) sp: Callback function called: ␛[0m
␛[0;32mI (90011) sp: id = 4
␛[0;32mI (90021) sp: content = hello from ESP32 : 3
␛[0;32mI (90021) sp: Callback function called: ␛[0m
␛[0;32mI (90031) sp: id = 5
␛[0;32mI (90031) sp: content = hello from ESP32 : 4
␛[0;32mI (90041) sp: Callback function called: ␛[0m
␛[0;32mI (90041) sp: id = 6
␛[0;32mI (90041) sp: content = hello from ESP32 : 0

[illosan]

The problem is if you delete a line. With Autoincrement the id of the line is lost, without the next line you write will use the deleted id.

[mr-wiggle]

I have a bit of info that I've found which might be helpful.

I recently switched over to LittleFS from SPIFFS and I've found that I can successfully use things like UNIQUE now that I'm on the new file system.

For those who aren't familiar LittleFS is a drop in replacement for SPIFFS and can be mounted on a spiffs partition on the ESP32.

[ankit-thealchemist]

Please follow the solution here
siara-cc/esp32-idf-sqlite3#13