Remove/Deleted distinction in MySQL Database

Remove/Deleted distinction in MySQL Database - 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: Remove/Deleted distinction in MySQL Database (/showthread.php?tid=344954)

Remove/Deleted distinction in MySQL Database - sumnone - 2019-06-27

I'm trying to sync my Movie folders directory with the Kodi MySQL db... My current schema (db) is MyVideos116. It should be one of the latest Kodi versions.

I have removed the movies from Kodi and it no longer shows them as expected, even on subsequent library scans (even though the directories still exist).

However, when I start going through the tables to figure out the distinction between what is "removed" and what is not removed, I can't figure it out.

I've looked in the movie table, movie_view view, path table and the "removed" movie's records still exist there. So, how does Kodi know not to show them in the UI?

RE: Remove/Deleted distinction in MySQL Database - Karellen - 2019-06-27

There are 4 tables in the database that retain information for various reasons. They are...
Bookmark
files
path
streamdetails

(2019-06-27, 04:36)sumnone Wrote: I have removed the movies from Kodi and it no longer shows them as expected

It is unusual that you still have entries in Movies table. Are you sure this is the case? How are you removing them?

RE: Remove/Deleted distinction in MySQL Database - sumnone - 2019-06-27

(2019-06-27, 06:54)Karellen Wrote: Are you sure this is the case? How are you removing them?

Yeah, I'm positive.

In this case, I'm removing them through the UI (Manage... > Remove from Library > Are you sure > Yes). Then they disappear never to come back, yet exist in the database still. I was hoping to find what you describe so I can do a simple select statement based on the directory structure... but find references to the movies I removed in all tables and views. I'm assuming something is telling the UI to not show those records, either a deleted=true field or something to that effect? But, I'm not finding it.

RE: Remove/Deleted distinction in MySQL Database - Karellen - 2019-06-27

(2019-06-27, 09:11)sumnone Wrote: I'm assuming something is telling the UI to not show those records, either a deleted=true field or something to that effect?

Nope. Removing from library should delete the entry from the database, except for those 4 tables.

What happens if you run a Clean Library? https://kodi.wiki/view/Settings/Media/Library#Video_Library

Are you sure you are looking at the correct database? Maybe you are using Profiles and are looking at the wrong profile?

RE: Remove/Deleted distinction in MySQL Database - Klojum - 2019-06-27

Unfortunately, Kodi's "garbage collection" isn't as sophisticated as it could/should be, certain tables and files are overlooked when doing a delete of a movie or tv series. The same thing happens with doing a video library clean operation. Thumnbails of deleted videos may still remain in the cache for whatever reason. Some things of those things are brushed under the rug.

This all perhaps is a souvenir from the past when XBMC started using databases, but IMO it can be better. The clean operation may take a bit longer but, it'll be more accurate for sure. Deleting everything related to a single movie or a tv show doesn't take that much time, unless you're on a RPi 0/1 perhaps.

RE: Remove/Deleted distinction in MySQL Database - sumnone - 2019-06-27

(2019-06-27, 09:35)Karellen Wrote: Are you sure you are looking at the correct database? Maybe you are using Profiles and are looking at the wrong profile?

Yeah, I checked the all the dbs that have been left around from previous upgrades and they exist there too.

I'm not using profiles as far as I know. I also don't want to do a clean db at this point, but may ultimately be the solution.

Here's some more insight... My MySQL database exists on my NAS. I'm using my W10 PC and and android box to access the Kodi db. I almost never use the W10 PC for Kodi. In my original post, I'm referring to my android box as that's how I watch most Kodi stuff. When I open Kodi on my pc, I can still see the "removed" movies, even after a library update. So, the movies being "removed" in the UI appears to be unique to the android tv box only. So, I'm inclined to think that if I run a clean db from the android box, it will maybe solve the problem? However, running clean db from the pc will give a completely different result (as far as cleaning the db goes)? Seems undesirable behavior if that's the case.

RE: Remove/Deleted distinction in MySQL Database - Karellen - 2019-06-27

Sorry, with my tiny iphone screen, I missed the MySQL mention in your first post.

klojum's your man for MySQL.

RE: Remove/Deleted distinction in MySQL Database - sumnone - 2019-06-27

(2019-06-27, 09:39)Klojum Wrote: The clean operation may take a bit longer but, it'll be more accurate for sure. Deleting everything related to a single movie or a tv show doesn't take that much time, unless you're on a RPi 0/1 perhaps.

I did a clean db from the android box and the "removed" movies still exist in the MySQL db, yet not in the android tv box Kodi UI...

I think it will remove the records in the db if the actual files are gone (?), which is my whole issue in the first place. Seems like a catch 22?

Unless you allow Kodi to delete files, the folder/files are not touched. Even if you allow it to delete files, it still leaves the folder and srt files lying around. I allow the latter and then delete folders under a few MBs. However, I had a backup replication mishap which put the folders back as they were to begin with from backup (long story short, Kodi looks at my backup directory on the NAS, rather than the source on my PC). So now I have a bunch of orphaned movie directories and don't know how to manage that. If I can't determine what's been "removed" from the db, I don't see a solution?

RE: Remove/Deleted distinction in MySQL Database - sumnone - 2019-06-27

(2019-06-27, 11:43)sumnone Wrote: If I can't determine what's been "removed" from the db, I don't see a solution?

It seemed like if I do an "Export Library" it would add xml files (or similar) to the actual directory structure? I could then see which folders don't have those xml files and delete them?

RE: Remove/Deleted distinction in MySQL Database - Klojum - 2019-06-27

(2019-06-27, 11:43)sumnone Wrote: I think it will remove the records in the db if the actual files are gone (?), which is my whole issue in the first place. Seems like a catch 22?

There are two options for deleting movies and their files (same as for tv episodes), the first with assuming you have Renaming and Deletion of files enabled in your General Media Settings:
1- Use the delete option in the Kodi GUI, there will be a "Are you sure" message, and then a subsequent question if you want to delete the video file from your video source as well.
2- Deleting the video file from your source device outside of Kodi, and doing a subsequent "Clean Video Library" action.

(2019-06-27, 11:43)sumnone Wrote: Even if you allow it to delete files, it still leaves the folder and srt files lying around

Yes, the big problem there I think is that some people think it is a brilliant idea to place several different movies (even their entire collection) in one big folder. Not deleting the folder will ensure Kodi doesn't delete their entire movie collection. Helas, not everyone is as well-behaved in file management as they should. Kodi could remove all related files to the deleted movie (subtitles, nfo, art files), but the folder is another matter.

(2019-06-27, 11:43)sumnone Wrote: So now I have a bunch of orphaned movie directories and don't know how to manage that.

There are external tools, for example for deleting empty directories, or perhaps also delete folders that have a content of less then, say 10 MB. It's a crude way of a cleanup, unfortunately Kodi is not perfect (yet).

RE: Remove/Deleted distinction in MySQL Database - sumnone - 2019-06-27

(2019-06-27, 12:05)Klojum Wrote: ...

Thanks for the reply guys. Please just disregard, it's behaving as it should be...

The one movie I was so fixated on as my test bed, has some different issue as to why it's not showing up in the UI that is probably a mental error on my part. I'm not going to troubleshoot that as it's getting deleted anyway.

When I went through the whole process again of "removing" an individual movie and then checking the MySQL db, etc.... everything is working as expected (i.e. the records for it no longer exist in the movie table, movie_view view, etc). Now I can just write my script to deal the backup replication mishap exactly as I had originally intended.

Many thanks again for the help and sorry to waste everyone's time.