Clarification of "Clean library..." & help to clean DB

Clarification of "Clean library..." & help to clean DB - Printable Version

+- Kodi Community Forum (https://forum.kodi.tv)
+-- Forum: Support (https://forum.kodi.tv/forumdisplay.php?fid=33)
+--- Forum: General Support (https://forum.kodi.tv/forumdisplay.php?fid=111)
+---- Forum: OS independent / Other (https://forum.kodi.tv/forumdisplay.php?fid=228)
+---- Thread: Clarification of "Clean library..." & help to clean DB (/showthread.php?tid=294011)

Pages: 1 2 3 4

RE: Clarification of "Clean library..." & help to clean DB - scott967 - 2018-04-14

(2018-04-14, 13:19)fr1day Wrote:
(2018-04-14, 00:41)scott967 Wrote: I'm not sure what the requirement is. I think many things such as streamed files never have entries in the library. I always thought "cleaning" was about library items, not file table items.

scott s.
.
If that's the case, then maybe the clean function needs to be expanded to include the cleaning of all items that are no longer valid. What possible benefit is there to a database keeping information on files that no longer exist?

I don't know the history of "files", but it seems to serve as the entry point for storing playcount / last played, stream details, resume point, bookmarks, etc. I assume this was done to allow for keeping info on streamed video across Kodi sessions. I suppose an alternative is to create m3u or strm files for streamed content locally and add these to the library, but I can see advantages to not using the library for this category of media.

scott s.
.

RE: Clarification of "Clean library..." & help to clean DB - fr1day - 2018-04-15

That sounds like a slightly impractical way of keeping track of information. So in the scenario where you change an SD movie file for 720p, or 1080p, and then later on change it again to 4k. Which one is used as the entry point? The first, latest, all of them? When all but one file is deleted why keep information on the ones that no longer exist?

Regardless, if a movie or TV show has all files removed from the flesystem, and the library is then cleaned, any information that's retained in the database becomes bloat.

RE: Clarification of "Clean library..." & help to clean DB - mdh99 - 2018-04-16

(2018-04-15, 13:09)fr1day Wrote: So in the scenario where you change an SD movie file for 720p, or 1080p, and then later on change it again to 4k. Which one is used as the entry point?

I've done this many times; --> if <-- the file name and extension is the same it just updates the existing entry with the new codec info first time you play the file.

RE: Clarification of "Clean library..." & help to clean DB - Karellen - 2018-04-30

Came across this by chance. It might explain why the library is not cleaned of streamed video... https://trac.kodi.tv/ticket/15950

RE: Clarification of "Clean library..." & help to clean DB - sparky3387 - 2019-04-28

https://gist.github.com/sparky3387/53eb486576f6ff91959c3aab9a5093ea

This annoyed me enough to create a python script to delete the hanging data, it worked successfully for me, but I wont make any promises, and I have only configured it for SMB connections, run a dummy run with the following:

./clean-kodidb.py --mysql-host "192.168.1.1" --mysql-user "kodi" --mysql-pass "*****" --kodi-dbname "MyVideos116"

To DELETE DATA run the following command
./clean-kodidb.py --mysql-host "192.168.1.1" --mysql-user "kodi" --mysql-pass "*****" --kodi-dbname "MyVideos116" --dummy no

RE: Clarification of "Clean library..." & help to clean DB - HeresJohnny - 2019-05-02

(2018-04-30, 08:00)Karellen Wrote: Came across this by chance. It might explain why the library is not cleaned of streamed video... https://trac.kodi.tv/ticket/15950

I had a quick look through the ticket and the only reason I can think of to keep Web links in the database is the watched status. However, the WatchedList add-on does a marvellous job of keeping track even of watched web links like YouTube videos. So maybe it is time to expand the cleaning feature to http/https (and preferably add WatchedList to Kodi core).

Ok, I've struck my statement from the record which was probably wrong. The watched status of Youtube videos is probably retained across installations just BECAUSE they are kept in the SQL database where other installations find it.

RE: Clarification of "Clean library..." & help to clean DB - sjwright - 2020-05-18

For people who want to delete orphan file entries from their MySQL/MariaDB database, this is fairly easy to do by running a DELETE query directly on the database. Standard disclaimer—this worked for me, today, on the current release of Kodi, running on my system. It is possible that it will cause problems for you. It is also highly probable that this will not work unmodified at some point in the in future. Running this is a very stupid idea if you are not comfortable with SQL queries. And always perform a backup of the database before performing any manual operations because it's trivial to screw something up.

This query lets you see what will be deleted. It's a fairly straightforward operation. It deletes all files from the table unless it has a relationship to content:

sql:


SELECT *

FROM MyVideos116.files

WHERE idfile not in (select idfile from MyVideos116.episode)

and idfile not in (select idfile from MyVideos116.movie)

and idfile not in (select idfile from MyVideos116.musicvideo)

ORDER BY `idFile` DESC

Replace the first line (SELECT *) with DELETE to actually delete all rows:

sql:


DELETE

FROM MyVideos116.files

WHERE idfile not in (select idfile from MyVideos116.episode)

and idfile not in (select idfile from MyVideos116.movie)

and idfile not in (select idfile from MyVideos116.musicvideo)

ORDER BY `idFile` DESC

Optionally, you can also tidy up child records for these tables:

sql:
DELETE FROM MyVideos116.bookmark

WHERE idfile not in (select idfile from MyVideos116.files);

DELETE FROM MyVideos116.settings

WHERE idfile not in (select idfile from MyVideos116.files);

DELETE FROM MyVideos116.stacktimes

WHERE idfile not in (select idfile from MyVideos116.files);

DELETE FROM MyVideos116.streamdetails

WHERE idfile not in (select idfile from MyVideos116.files);

RE: Clarification of "Clean library..." & help to clean DB - sjwright - 2020-05-18

After performing the above, I recommend immediately performing an Update Library operation. In my case that first run took a lot longer than a regular update, and it caused numerous files to be rediscovered and correctly matched.

RE: Clarification of "Clean library..." & help to clean DB - Karellen - 2020-05-18

Thanks @sjwright

I probably need to refresh my library as it has almost 1200 orphan entries.

The other tables that are not cleaned by a Clean Library are Bookmark, streamdetails and path.

Also, your statement did not work for me until I removed the "MyVideos116." reference

RE: Clarification of "Clean library..." & help to clean DB - sjwright - 2020-05-18

(2020-05-18, 12:29)Karellen Wrote: The other tables that are not cleaned by a Clean Library are Bookmark, streamdetails and path.

I did provide queries for removing orphans from bookmark, streamdetails, settings and stacktimes.

I chose to ignore the path table because removing files doesn't technically orphan paths. It won't fix any problems or speed anything up. It's also non-trivial to construct the appropriate DELETE query because it would require a recursive join to correctly traverse its internal hierarchical relationship.

RE: Clarification of "Clean library..." & help to clean DB - scott967 - 2020-05-18

Didn't try it, but on paper it makes sense. Users who prefer to play out of the files node and not add to their library probably don't want to run this query.

scott s.
.

RE: Clarification of "Clean library..." & help to clean DB - DappereDodo - 2020-12-26

I noticed the query has a lot of youtube videos in it. So basically every video you watched outside Kodi's library will be removed here and that includes videos played through the youtube addon. Just a word of warning Smile

@Karellen 1200? Pfff, 15456 Cool

RE: Clarification of "Clean library..." & help to clean DB - graysky - 2021-01-31

(2020-05-18, 12:08)sjwright Wrote: For people who want to delete orphan file entries from their MySQL/MariaDB database, this is fairly easy to do by running a DELETE query directly on the database. Standard disclaimer—this worked for me, today, on the current release of Kodi, running on my system.

EDIT: I played around a bit with it and the following worked. Thanks for the post!

sql:


USE MyVideos119;

SELECT * FROM files WHERE idfile not in (select idfile from episode) and idfile not in (select idfile from movie) and idfile not in (select idfile from musicvideo) ORDER BY `idFile` DESC;

sql:


USE MyVideos119;

DELETE FROM files WHERE idfile not in (select idfile from episode) and idfile not in (select idfile from movie) and idfile not in (select idfile from musicvideo) ORDER BY `idFile` DESC;

RE: Clarification of "Clean library..." & help to clean DB - graysky - 2021-02-01

Added to wiki: https://kodi.wiki/index.php?title=MySQL%2FAdvanced_notes&type=revision&diff=223775&oldid=155407

Thanks @sjwright for the inspiration.

RE: Clarification of "Clean library..." & help to clean DB - Klojum - 2021-02-01

(2021-01-31, 16:09)graysky Wrote: EDIT: I played around a bit with it and the following worked. Thanks for the post!

Some video database records in Kodi are currently being deleted via triggers, but that doesn't properly clean all the affected database records. For example, properly removing a complete TV show manually affects upto 18 tables in the video database. And that still leaves out cleaning of the local textures database regarding fanart/thumbs, as well as the thumb files themselves. For now, there isn't the man power available to implement that large clean functionality overhaul.

So yeah, the video database will pile up some dead records here and there, but Kodi functionality or speed isn't really hindered by this. Database engines output their queries in milliseconds. An annual spring-cleaning of exporting and rescraping your media collection in Kodi will be enough to refreshen the databases. After the recent TMDB internal cleaning and shake-up of their content, where lots of old/dead fanart links were removed and new ones added, a rescrape is recommended anyway.

For a glance on TV show cleaning, see my PHP scripting post here.