Not detecting changed directories

[micahmo]

Hi @cjmanca!

First of all I want to say that this is an amazing tool! Thanks for all your work on this!

I believe I've discovered a bit of a bug related to change detection. After the initial processing, I have been adding media, but plex-credits-detect has not been detecting it. It only detects after a restart of the application. I did some troubleshooting and I found that the Plex database query was returning incorrectly.

I opened the db open in DB Browser, and you can see there is a changed directory at 9:16.

However, in the code, it's returning no rows after 8:55.

I even tweaked the code a bit to return all rows from the directories table, and result.Get<DateTime>("updated_at") still showed the old date.

I'm not sure if this is related to the fact that Plex seems to be using SQLite in WAL mode. But I tried creating the connection with sb.JournalMode = SQLiteJournalModeEnum.Wal and that didn't help.

The only thing that fixed this issue for me was closing and reopening the connection at the beginning of GetRecentlyModifiedDirectories. I suppose it's not the worst solution in the world since it happens at most 1/minute, but it doesn't seem right. If you have more experience with SQLite, may you'll have some idea why the queries seem to be returning stale data!

[cjmanca]

Seems to be working fine right now on my current working version. I just tested as follows: 1) Launched PCD 2) Renamed a video 3) "Scan Library Files" in Plex 4) Waited for ~60 seconds until PCD did it's next check

PCD correctly saw the changed directory and scanned it.

That said - I've been doing some fairly extensive database changes on my current working version, so it's possible I already unintentionally fixed a bug that may have been causing it. You could grab the "in-progress" branch if you'd like to see if it's helped before the next release.

Make sure you make a copy of your current PCD database though in case you want to revert. There's a DB migration required for the new version.

[micahmo]

Thanks for the quick response and for testing the issue! I'm glad to hear it's working on your system with the current changes. If it won't be too long until the next release, I'll probably wait. I've deployed my own local changes to get around the issue in the meantime.

BTW, I want to stress that (as far as I could see) the SQLite query itself was returning bad data. I queried in DB Browser and saw one thing, then I stepped through the code and saw another. I have a hard time believing it's an application bug on your part, so maybe there's something related to how the the SQLite db is being accessed? If you don't mind my asking, do you also have the associated shm and wal files with your Plex db? And do you see that the modified times of those files are much newer than the db itself?

Finally I should mention that I'm running this in Docker on Linux.

[cjmanca]

Yes, my Plex DB also has the shm/wal files. The Plex DB is configured in "write ahead log" mode (WAL), which I believe is a persistent database setting, and should be detected by connecting applications. That said - I'll change my connection string to explicitly use the WAL journal mode to be safe.

I suspect what may have solved it was properly closing query result connections. In researching some of the database locking issues (Database BUSY errors some people have been getting), I found that apparently result connections must be explicitly closed, which I didn't realize before. It's possible that the unclosed previous results were interfering with subsequent queries.

EDIT: Yes, WAL mode is a persistent setting: https://sqlite.org/wal.html#:~:text=3.3.-,persistence%20of%20wal%20mode,-Unlike%20the%20other

[cjmanca]

Fixed by #34