VACUUM result disk I/O error

[manouhihalid]

Hi,

I try to use "VACUUM" but i get this error disk I/O error.

Other sqlite command work well.

I use ESP32 and SPIFFS.

i use the command

sqlcommand="VACUUM"; "db_exec(db1, sqlcommand);"

thanks for your support

[rezadzikri19]

Did you manage to resolve this? I'm getting the same issue.

[manouhihalid]

Hi,

no. still same issue. i am waiting for response from the author.

[siara-cc]

Hi, not sure what is causing this, but please check this out: https://github.com/siara-cc/esp32_arduino_sqlite3_lib/issues/50#issuecomment-916045739

[PaulDK89]

Hi,

I am experiencing the same issue but through the SPI bound SD card. All other functions are fine, only VACUUM causing this issue.

[savejeff]

I have looked into this and its solvable:

1.) a temporary file is opened. this is not implemented currently -> filename is 0 and the doc says you have to come up with a filename yourself. -> first thing that needs to be fixed in ESP32Open like this:

if( zName==0 ) {
        //return SQLITE_IOERR;
        zName = "/spiffs/.tmp";
}

2.) next the temp file needs to be initialized with some data as when writing out the seek somehow fails as it trys to seek to a position that is outside of the files size. i have not solved this correctly but this helps somehow:

        FILE* fp = fopen(zName, "w+");
        uint8_t buff[128] = { 0 };
        memset(buff, 0, sizeof(buff));
        for(int i = 0; i < 4096 * 2 / 128; i++)
            fwrite(buff, 1, sizeof(buff), fp);
        fclose(fp);

lastly, truncate is not implemented and thus the operation sometimes fails. ESP32 for an unknown reason refuses to implement truncate in there filesystem. you can work around this by (moving the file -> creating a new file with the name of the original file with the correct truncated size -> copy data from the original file into new file)

---

its not a step by step fix but helping hints for somebody how wants to fix this. my code has at this point deviated to far so that i can publish a pullrequest.

also some other problems might also be fixed by implementing the temporary file and trucate like the issue with dropping tables or auto increment primary key

[siara-cc]

@savejeff Thanks for sharing this!! I will check.

[savejeff]

i found out the problem with seek. sqlite expects that when a seek is above the file size and a write is started that the file is extended to the seek position with zeros. so in direct write when the start offset is above the file size, the file must be extended with zeros until file.size == iOfst like this:

ESP32DirectWrite(
[....]

    if(iOfst > p->fp->size()) {
        LogXD(">>> WRITE SEEK OUTSIDE OF FILE");

        // write zeros to file until target offset is reached
        uint8_t buff[128] = { 0 };
        memset(buff, 0, sizeof(buff));
        while(p->fp->size() < iOfst)
            p->fp->write(buff, MIN(128, iOfst - p->fp->size()));

    }
[....]

Here is what chatgpt has to say about it:

When you use fseek() to seek to a position higher than the current file size and subsequently perform a write operation, the behavior depends on the mode in which the file was opened:

If the file was opened in a mode that allows writing (such as "w", "a", or "r+"), the write operation will extend the file to the specified position, filling any gaps with zero bytes if necessary. In other words, the file size will be increased to accommodate the write operation, and any intermediate positions will be filled with zeros.

For example, if the file size is 100 bytes, and you use fseek(file, 1000, SEEK_SET) to move to position 1000, and then perform a write operation, the file size will increase to 1000 bytes, with the bytes between 100 and 999 being filled with zeros.

If the file was opened in a read-only mode (such as "r"), attempting to perform a write operation after seeking to a position beyond the current file size will result in undefined behavior. The write operation may fail, produce an error, or lead to unexpected consequences. In general, it is not valid to write beyond the end of a file that was opened in read-only mode.

It's important to ensure that the file is opened in a mode that allows writing if you intend to extend the file size and perform write operations beyond the current end of the file. Additionally, always check for errors returned by file operations to handle any potential issues that may arise during file manipulation.

Im getting Operation successful with Vacum now

[savejeff]

i think i have now fixed most of the problems that caused the IO Disk errors others reported. @siara-cc i can send you the my implementation. its based on arduinos FS classes and not on the basic fwrite, etc functions but it demonstrates the needed general behaviour

[siara-cc]

Hi, thank you for the detailed explanation. If a PR is not possible, I suggest attaching the zip file here so I can check and modify the library.

[winkelict]

@savejeff ive posted an issue with a fix for esp32open https://github.com/siara-cc/esp32_arduino_sqlite3_lib/issues/81