search

vlcn-io / cr-sqlite

Convergent, Replicated SQLite. Multi-writer and CRDT support for SQLite
https://vlcn.io
MIT License
2.61k stars 69 forks source link

`DROP TABLE` leads to corruption? #433

Open louwers opened 1 month ago

louwers commented 1 month ago

Once a table has been turned into a CRR with crsql_as_crr, dropping it seems to result in irrecoverable corruption since the crsql_changes will be in an invalid state. Deleting changes is not possible (only inserts are allowed).

SELECTing from crsql_changes now leads to query aborted. If the table is dropped and re-created on two sites, trying to sync will fail whenINSERTing old changes that are lingering (could not find row to merge with for tbl ....

I think when dropping a table, all related entries in crsql_changes should be deleted.

louwers commented 1 month ago 
SELECT crsql_begin_alter('mytable');
DROP TABLE mytable
SELECT crsql_commit_alter('mytable');

Results in:

Error: failed compacting tables post alteration

louwers commented 1 month ago

OK deleting mytable__crsql_pks and mytable__crsql_clock seems to resolve the issues.

But since these are not part of the API of cr-sqlite, I think these tables should be dropped along with the source table automatically. But I think this is a known limitation right now:

https://github.com/vlcn-io/cr-sqlite/blob/0d62b52b4662ee1a762c9fd9264d48a91ab8df83/py/correctness/spec.md?plain=1#L29

tantaman commented 1 month ago

Thanks. I'll update begin_alter to account for drop table