Disk I/O Error when creating database table inline with AUTOINCREMENT

[davidtheITguy]

Hi,

I'm getting an error "Disk I/O" from sqlite3_exec after attempting a CREATE TABLE statement that has extended attributes, namely trying to define a primary key with auto increment inline as a single statement.

As an example, the following statement works:

CREATE TABLE tblThisWorks (TimeStampUTC TEXT, Condition TEXT);

This statement never works for me:

CREATE TABLE tblThisDoesntWork (ID INTEGER, TimeStampUTC TEXT, Condition TEXT. PRIMARY KEY(ID AUTOINCREMENT));

I'm running this on an ESP32-S2-DevKit using SPIFFS. Everything works except trying to create the table with an extended attribute such as a primary key. Equally strange is the fact that the API returns "Disk I/O Error".

The actual code statement which throws the error looks like this:

rc = sqlite3_exec(db, " tblThisDoesntWork (ID INTEGER, TimeStampUTC TEXT, Condition TEXT. PRIMARY KEY(ID AUTOINCREMENT));", 0, 0, &err_msg);

[siara-cc]

Hi, You might try using LITTLEFS as mentioned here:
#50 (comment)

but it could be because how SQLite handles it internally:

So when you create a table with a primary key index, there are 2 b-tree indices created:

• 1 for the table and 1 for the primary key

When you include autoincrement, 1 more b-tree index is created

• 1 for the table, 1 for the primary key and 1 for the b-tree index

So there are 3 sets of b-tree pages to deal with, although we see it as a single table.

In the example you have shown, the PRIMARY KEY AUTOINCREMENT is really redundant because thats what SQLite does anyway behind the scenes although the ID is hidden. It can be accessed as rowid like this:

SELECT rowid, condition from tblThisWorks;

If you still need the PRIMARY KEY AUTOINCREMENT, you could try adding WITHOUT ROWID as in here:
https://www.sqlite.org/withoutrowid.html

at the end of create statement, which reduces it to 2 b-tree pages.

You could also try using 512 page size (please create the db after cloning the latest of this repo).

[davidtheITguy]

First, thank you for your response! Much appreciated.

I dug around a bit more and I think this issue has an even better explanation of the problem I am seeing.

I will certainly look into LITTLEFS. Reviewing some of the other issues reported (e.g. above), I think I better understand why Disk I/O errors are thrown when there are locking, schema, etc errors. But I (respectfully) think that the file system shouldn't be the layer to report application faults if that makes sense. If the data definition or schema or parsers fail then I think the API should report as such. This is moot I'm sure as the original SQLITE API is a port I believe.

I am also aware of the ROWID option and yes that does work.

FWIW, the trick I am using here is to create a VIEW that selects ROWID and the parent table columns together which has the same result of using the AUTOINCREMENT. Example:

CREATE VIEW myView AS SELECT ROWID, cola, colb, colc FROM myTable

The above works on the ESP32 without issue.

Thanks again for the help!

[savejeff]

@siara-cc i think it would be good if this was added to limitations in the readme page as PRIMARY KEY AUTOINCREMENT will be used by many users first. when it says Disk I/O error, it adds to the confusion even more
fixing the bug itself of course would be even better.

otherwise very well written lib!

[siara-cc]

@savejeff I will add this issue to the README if you know many people use it. But I am not sure if it is a limitation since I have not tried it out.

I think it should be rather an Info note as to how Sqlite works and how it is an overkill to use PRIMARY KEY AUTOINCREMENT since Sqlite already has the default ROWID for all tables anyway.

[savejeff]

i think thats a good idea. when you are already familiar with SQL but not sqlite in particular, the first table you will create will have an id primary autoincrement ^^ i was not awear of the rowid and for example chatgpt uses PRIMARY KEY AUTOINCREMENT even when you ask for sqlite3 SQL

[siara-cc]

You are right.. PRIMARY KEY AUTOINCREMENT is an overkill only for esp32 and it does not matter much for desktop applications. ROWID is an internal sort of less documented thing. ChatGPT probably gave the most common solution.

[winkelict]

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

[siara-cc]

@davidtheITguy This is working now after implementing the code changes from @winkelict and @savejeff
Thanks to all of you for the support!