-
Notifications
You must be signed in to change notification settings - Fork 74
solution to SQL error: disk I/O / SQLITE_IOERR (10): temp file support #81
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Comments
there are quite some reasons sqlite uses temp files: https://www.sqlite.org/tempfiles.html SQLite currently uses nine distinct types of temporary files: Rollback journals in my case, add a sudden point, using a large query, without any database changes sqlite decided to make a temp file to speed up the query (i assume). It might have tried to make a transient index for this. Because there are so many reasons sqlite can request a temp file it might be important to add the above function to esp32.cpp |
Hi, i've worked quite a bit on the sqlite file abstraction for my project. One problem with the current implementation in this repo is that the temp/journal file is in a 8K Buffer, not an actual file. sqlite3 does not always directly writes or reads from the actual file if the query is to short. so a BEGIN TRANSACTION does not immediately create a journal file. only after some inserts it actually writes to it. another problem is with the truncate operation that is needed by the sqlite3 lib when for example the journal file is truncated to 0 bytes after a COMMIT command or if VACUUM is used as the database file shrinks. i have gotten all these running reliably |
hi, yes, i use platformio, i would be very interested to see your implementation! |
did you ever make a PR or send a zip to sierra-cc? |
i gave sierra a zip with adjusted but not directly ready to use code (it was already a little bit too far gone from the base code) but i don't know if he integrated it. how should i send you the code? it will take me a little to prepare it so that i don't leak too much code |
here the repo with running code for ESP32, RP2040 and STM32. Teensy 4.1 should also work. i recommend you extract the code you need |
thanks man! looks good, it evolved quite far from this original sqlite3 library right? will take me a while to figure out |
Yeah I have redonr basically the complete file interaction by switching to my personal jFS library that is a wrapper for each MCUs own SD library. If you want you can exchange jFS with SdFat pretty easily as they basically have the same interface. If you gave questions just open up an issue on the jSQlite repo |
@savejeff Yes you had shared the changes that you had made for this issue. I started working on the info you have given but unfortunately have not been able to spend time to complete it. |
Hi @winkelict I have tested your dropin code suggestion and committed to this repo. Seems working. Thanks a lot!! |
in esp32.cpp the comment for this function specifies this:
however, this is not implemented in esp32:
In some issues this was already mentioned, but i did not find a complete implementation/fix.
#55
#41
ive made a drop in replacement for esp32open that will generate a temporary file name and open that in w+ mode.
also, to overcome the problem that different filesystems have differents roots (like /sd) ive added parsing out the root dir when a .db file is opened. This way the temp files will always be in the same folder as the .db file. Also, the temp file is unlinked right away so does not have to be cleaned up / deleted on file closing as the sqlite library specifies. This is the function:
Or should i open a pull request?
The text was updated successfully, but these errors were encountered: