SQLite 2.3.0 - DELETE FROM [tablename] with many records results in I/O Error

[genotix]

I have been attempting to TRUNCATE a table in SQLite on an ESP32-WROVER-B using the SPIFFS library.

Since TRUNCATE doesn't exist (u
The execution fails with an I/O error on the filesystem.
I have tried to do this with:
PRAGMA journal_mode= TRUNCATE / NONE / WAL
To make sure the system is not overloaded with audit logs.
This didn't do the trick.

Am I running into a bug?
I have the ESP32 version of the SQLite3 stack.

[mprync]

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

My HEAP and SPIFFS size are all fine, not sure if it's an ESP32 issue or a library level issue?

[mr-wiggle]

this is a SPIFFS error. Change your filesystem to LITTLEFS and you should be good to go.

[vinsaurral]

Could you solve the problem? I am testing the query DELETE FROM idHist WHERE id > 0 AND IsReady = 0 ORDER BY id DESC LIMIT 10; but it does not recognize the ORDER.
How do I delete 10 rows?

[genotix]

Could you solve the problem? I am testing the query DELETE FROM idHist WHERE id > 0 AND IsReady = 0 ORDER BY id DESC LIMIT 10; but it does not recognize the ORDER. How do I delete 10 rows?

Deleting 10 rows isn't done by using a limit this way so your syntax is incorrect.
I'm not sure if ANSI SQL allows you to use LIMIT in a DELETE.
Also an ORDER in a DELETE statement is not possible.
SQL allows you to use limit using SELECT FIRST or using LIMIT (depending on the SQL implementation).

You should be able to execute the SELECT limit, put the ID's in an array and walk through the array to delete the records or pass them as a static string in the statement so something like
DELETE FROM idHist WHERE id IN ( 1,2,3,4,5,6,7,8,9,10 ) AND IsReady = 0;

It's best to check the SQL manual a bit and get a good understand of the statements.

You could also use this method.

[winkelict]

i think the solution in this issue might fix this problem #81

[siara-cc]

@genotix @mprync @mr-wiggle Please check with today's commit. This seems working now after implementing the code changes from @winkelict and @savejeff
Thanks to all of you for the support!

[niveus]

I'm still getting a disk i/o error when I run DELETE FROM <table>;. Inserts and selects seem to work fine. I'm using the latest d0b26f5 on SPIFFS

[siara-cc]

Seems I/O error is prevalent with SPIFFS. Can you try on LittleFS?

[niveus]

I didn't realize arduino esp32 shipped with LittleFS. It was easy to get it going and it seems to work now. Thanks!

[davi-domo]

hi, a small problem that I also encountered which was causing me confusion
databases must be built without ROWID
to overcome auto-increment I record the largest id at startup which I save as a variable
by doing that I considerably increased the speed and no more watchdog problems
if it can help you

[siara-cc]

@davi-domo It makes sense because Sqlite maintains auto increment values in a table and it occupies couple of additional b-tree pages depending on the page size. Tables with ROWID are ok unless you create an index which poses the same issue. So it is better to create a table without ROWID and therefore merge the table and index.