[jeffski10]

+1 for fixing this. I did actually get so fed up of the errors in the log I manually cleaned the library removing all the entries with dateAdded Null as above. Removed a lot of the errors but obviously not ideal to do manually.

[pr0xZen]

To me it appears so, according to the logfile. Not sure why they're so quick, but its "checking" every one of them in some manner, just 1000+ in a few seconds (in my instance). Local network ones though, there's a 5 second timeout - causing a swift job taking half an hour.

Is this really happening? It just seems so absurd.

I wonder if this explains the odd "Clean Library" effect I get? When I hit the "Clean Library" button it starts and almost instantly gives up after a few seconds and the progress bar has hardly moved. I press it a second time and now it gets right through a scan (well, the progress bar moves a lot further)

I've not been experiencing this. It does freeze alot with no graphical progession, presumably on a "string" of successive timeouts, but no involuntary aborts.

[BatterPudding]

To me it appears so, according to the logfile. Not sure why they're so quick, but its "checking" every one of them in some manner, just 1000+ in a few seconds (in my instance). Local network ones though, there's a 5 second timeout - causing a swift job taking half an hour.

Is this really happening? It just seems so absurd.

I wonder if this explains the odd "Clean Library" effect I get? When I hit the "Clean Library" button it starts and almost instantly gives up after a few seconds and the progress bar has hardly moved. I press it a second time and now it gets right through a scan (well, the progress bar moves a lot further)

I've not been experiencing this. It does freeze alot with no graphical progession, presumably on a "string" of successive timeouts, but no involuntary aborts.

I have never bothered looking at the logs, but there is a clear abort on the first attempt for me. It barely moves the progress bar before the dialog box just disappears without error. When I try it today I find it actually freezes my GUI for 20-30 seconds after the box vanishes. (GUI freeze includes the mouse pointer and debug overlay) Second attempt it always succeeds and clearly counts up to 100% in little steps... noticeable pauses but it completes.

When I look at the literal description of "Clean Library..." it says "Remove items from your library that can't be found (either renamed, deleted, or on removable storage that is currently unplugged)". That sounds very much to me like it should be removing these duff items that were added by File Mode.

I'm off to investigate my clean library freeze... see if there is anything related in the logs...

Update: Okay... reading that log didn't give me any clue as to the freeze I am getting as the log thinks all is well even though it is getting floods of warnings from the "unsupported protocol" of plugin, rtmp, rtmpe and the other streams. It even complains about not being able to open many of those File Mode files.... but leaves it all in the database instead of cleaning it out.

I get a feeling I could use those errors and warnings from the log file to locate the majority of my dodgy links in the database.


What about this as a different test. If a file is listed in the FILES table and not referenced anywhere else in the SQL Database then surely it can be deleted? Then once those files have been removed, the same rule can be run over the PATHS table - if path not referenced elsewhere then it should be removed.

[BatterPudding]

+1 for fixing this. I did actually get so fed up of the errors in the log I manually cleaned the library removing all the entries with dateAdded Null as above. Removed a lot of the errors but obviously not ideal to do manually.

When you cleaned by hand what did you actually do? Did you delete the records from the database, or just replace values with NULL data?

[jeffski10]

When you cleaned by hand what did you actually do? Did you delete the records from the database, or just replace values with NULL data?

I run a couple of sql statements:

Clean up all stream files - selects to look for records and deletes to get rid.

select * FROM files as t1 inner join path as t2 on t1.idpath = t2.idpath where strpath like 'http://%'

delete t1 FROM files as t1 inner join path as t2 on t1.idpath = t2.idpath where strpath like 'http://%'


Then to clean up redundant paths :

SELECT * FROM `path` as t1 left join files as t2 on t1.idpath = t2.idpath where t2.idpath is null and t1.strContent is null

delete t1 FROM `path` as t1 left join files as t2 on t1.idpath = t2.idpath where t2.idpath is null and t1.strContent is null

[BatterPudding]

@jeffski10: Thanks. Very handy. I'll now screw my old SQL head on to make sure I fully understand that code. See if I can get it to catch everything I need.

I can see other patterns I need to add to grab the odd disks I have put into the BluRay drive. Or the USB sticks I have plugged in. Or plain simple "Any path that isn't listed as a current source" would be good for me.

I'll go and puzzle a bit... but probably tomorrow... I want to read some of wiki to see if there is any hint as to why these files have been added in the first place.

[BatterPudding]

More research.... this has to be a bug. Surely not a feature....

In the Database wiki page http://kodi.wiki/view/Databases it says the following:

In addition to indexing media files when activated by user-selected Content settings, Kodi also puts a video in its database if you change any OSD setting while watching it. Resume points are stored in this database as well. These entries are added to the database whether the affected video is part of the Video Library or not.

So is one of those features mis-firing and now *always* adding the file to the database?

[zerocool_ie]

SELECT * FROM `path` as t1 left join files as t2 on t1.idpath = t2.idpath where t2.idpath is null and t1.strContent is null

delete t1 FROM `path` as t1 left join files as t2 on t1.idpath = t2.idpath where t2.idpath is null and t1.strContent is null

Interestingly, updating my library after running this query caused Kodi to recreate a row for every 1st sub-folder of my TV sources...

Code:

smb://SERVER/TV/TV-Continuing/Show 1
smb://SERVER/TV/TV-Continuing/Show 2
smb://SERVER/TV/TV-Ended/Show 1
smb://SERVER/TV/TV-Ended/Show 2

Haven't seen any negative effects yet.

Edit: Looks like the artwork for all of my TV shows and casts is rebuilding...

[BatterPudding]

We should be shouting BACKUP YOUR VIDEOS99.db before running this.

a\ in case something goes wrong and
b\ in case we can use this to create a real patch to the "Clean Database" function as messy databases will be needed for testing...

[zerocool_ie]

We should be shouting BACKUP YOUR VIDEOS99.db before running this.

+1

I have daily backups scheduled on the MySQL server

[nickr]

I guess this is how kodi keeps track of watched status, and the other features mentioned in post 22. How else are you going to keep track.

As for privacy concerns, who is going to see?

[scott967]

It does seem like there could be an option to clean files / paths for entries that are not added to the database.

scott s.
.

[Ned Scott]

Yes, Kodi logs every single video played back to store watched status, video settings, etc. Like with old cached images, this is one of those areas where Kodi really needs some "garbage collection". It's normally not a big deal, but it does kind of bother me. That's why I will never watch porn in Kodi. Never doubt your significant other's skill with an SQLite editor.

[jeffski10]

Interestingly, updating my library after running this query caused Kodi to recreate a row for every 1st sub-folder of my TV sources...

I checked my DB and I don't see that happening but I did find I am still missing entries as I have some streams in with filenames e.g a row like:

idFile idPath strFilename playCount lastPlayed dateAdded
3121 873 3.0.3 2 2016-04-17 00:40:01 NULL

as filename isn't Null.

As nickr mentions there is a play count against these so it will be for Kodi retaining the playcount on streams. Not sure for streams I am too interested in retaining those as I tend to watch them once but I can see for others they may want to have the history retained. That might be solved by a setting for Clean Db with a toggle as to what to clean up. I can also see not just http streams but also pvr: entries which don't appear to be going.

[BatterPudding]

Yes, Kodi logs every single video played back to store watched status, video settings, etc. Like with old cached images, this is one of those areas where Kodi really needs some "garbage collection". It's normally not a big deal, but it does kind of bother me. That's why I will never watch porn in Kodi. Never doubt your significant other's skill with an SQLite editor.

I don't get why it needs to log *everything* yet has no way of then cleaning that out. I feed in temporary media from all kinds of angles, but I didn't expect KODI to remember I had plugged in a memory stick with a few videos on two years ago.

And I am not just thinking privacy here - it is more about the efficiency. The database is getting filled up with "one off" data that it isn't going to need again. And it is leading to issues in places like the Clean Library scans which loads up all the old streams, but doesn't seem to know what to do with them as it just spits out warnings about them. My debug logs are quite a mess on Clean Library scans - and I think it is leading to that issue mentioned above. Could there be other hidden problems being caused for people? What other KODI functions will rattle through that Files table assuming these are videos that live in your collection?

It does seem like there could be an option to clean files / paths for entries that are not added to the database.

scott s.
.

This is the key to me - a "Deeper Clean" option. The way I had understood "Clean Library" was that it would remove items from the database that were in the library but not found on the hard disk any more. Basically I had plain misunderstood what was in the database. I thought database == library and nothing more. It would be good if that Clean Library function could be optionally extended to include "Files not in the database". Or "Files that don't exist". Why keep a watched status for something you can't actually watch again?

It is the streams that are especially messy here - watch something on YouTube and it is stashed in teh database. Listen to the radio and it is stashed in the database. The "Watched" flag seems a bit odd for these as it is not used on screen anywhere. Especially as it is usually tricky to have "watched" a whole radio channel


In in the community spirit I am going to experiment a bit more with making a bit of SQLrubber Script. I want to try and make a wider catch net. Trouble is it is sunny out there so I don't think I'll get much done today