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.