2 macOS computers, both running the same OS version
Syncthing 1.24 on both
1 sync folder, 98k files ~ 17GB
1 hour rescan time
TrashCan file versioning
Everything works really well.
But there’s this 1 pesky sqlite db file that gets written to maybe 10-15 times per day that consistently generates a sync-conflict copy. I’m trying to figure out why this would be. My assumption is that it’s because the 2 machines happen to write to the db at close to the same time.
Nothing special about this db, it’s very small (10kb) and has just a single table with 4 columns.
Is there a suggested way to track this down? Any way to change the polling settings or frequency for a single file? Or any other tips?
Everything else is working really well so I think maybe with some additional debug logging I could track down what’s going on here. Thanks for any advice!
sqlite uses memory mapped files, which do not update mtime as they are written, so we don’t think it’s changed until we end up reading it as we try to sync it.
Simply put, don’t be insane, and don’t expect to sync databases, that is just a crazy thing to do, you will corrupt them and lose all your data.
I understand what you’re saying about trying to sync databases, but…
The purpose of this small db is to share a simple key/value store between these 2 computers. Nothing confidential.
What method do you think would work better than a sqlite db?
I’m trying to avoid using plaintext files or csv because so I didn’t have to think about escaping quotes, tabs or other nonsense, and not have the complexity of using grep/sed/awk or whatever to manipulate them.
“Insane” or not, is there a method to force ST to recognize that this db file has been updated? Would a simple touch /path/to/db after a commit be enough?
Assuming Syncthing did its thing perfectly with whatever workaround you put in place using touch or whatnot, SQLite doesn’t support someone else (Syncthing) just replacing the database file while it’s open. The only way this can work is if you can guarantee your database-using program is open in at most one location at a time – and then sync should work out of the box as well since the modification time will be set on close.
If you want your program running in two locations at once with a shared database you need to either use an external database or implement some sort of sync protocol in the program itself.
Thanks @calmh. Hmm, this is turning into an interesting puzzle. I do see the problem with yanking the db out from under sqlite when it might be in the middle of an operation. So that makes sense. I just set up a test with a simple while :; do loop on both computers, making writes to the db at random intervals, and was quickly able to reproduce the conflicts.
I’m back to wondering if anyone knows of a way to sync a small flat file key-value store between 2 computers for non-critical data.