Row disappearing from table

pwsu

I've setup my .NET application as follows:
- I'm using Microsoft.Data.Sqlite v9.0.6
- My connection string is "Data Source=file:C:...\data.db?node=primary&bind=tcp://0.0.0.0:1201"
- I only have this one node, nothing else is connecting to it currently
- I await "PRAGMA sync_status" for db_is_ready = true before performing any operations on the connection
- I have signed up for the "update_notification" in my application

I have a table with the following schema (I can include missing columns if needed)

CREATE TABLE Node (
Id INTEGER PRIMARY KEY UNIQUE NOT NULL,
Name TEXT COLLATE NOCASE NOT NULL,
...
);

I insert a single row into the table

INSERT INTO Node (
Name,
...)
VALUES (
@Name,
...)
RETURNING id;

Letting it auto generate a key. Later in my app I perform the following update

UPDATE Node SET Name = @Name, ... WHERE Id = @Id

When I perform the update I check the rows in the table inside the transaction and immediately after it is committed (outside of the transaction) and I can see my one row.

My application runs doing other tasks, goes to get the entry it added to the Node table earlier and gets back no result. Checking the table it is empty. During this time I haven't connected other nodes, don't get an "update_notification" event. I don't get this issue when using a non-LiteSync connection string. Do anyone know what possible causes could result in the row going missing and if there are changes that are recommended

Bernardo

I have never seen this happen

It would be very useful if you could create a minimal application that reproduces this behavior

BTW, you do not need to add UNIQUE to the INTEGER PRIMARY KEY as it is already unique. It is also guaranteed to be not null if the engine is setting it.

Bernardo

I was not able to reproduce the problem you are seeing

% ./sqlite3   
SQLite version 3.40.1 2022-12-28 14:03:47
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .log stdout
sqlite> .open "file:test3.db?node=primary&bind=tcp://0.0.0.0:9134"
sqlite> .tab
litesync_config  litesync_local   litesync_logs  
sqlite> CREATE TABLE Node (Id INTEGER PRIMARY KEY UNIQUE NOT NULL, Name TEXT COLLATE NOCASE NOT NULL);
sqlite> .tab
Node             litesync_config  litesync_local   litesync_logs  
sqlite> insert into Node (Name) VALUES ('test');
sqlite> select * from Node;
4294967297|test
sqlite> select * from Node;
4294967297|test
sqlite> UPDATE Node SET Name='Modified' WHERE Id = 4294967297;
sqlite> select * from Node;
4294967297|Modified
sqlite> select * from Node;
4294967297|Modified
sqlite> pragma sync_status;
{
"use_litesync": true,
"node_type": "primary",
"node_id": 1,
"db_is_ready": true,
"sync_down_state": "unknown",
"sync_up_state": "in sync",
"last_tid": -4339114924255909020,
"log_storage": "keep",
"peers": []
}
sqlite> select * from Node;
4294967297|Modified
sqlite> .q

It is probably related to your application