Kodi Community Forum
Database Cleanup don't really clean all up - 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: Database Cleanup don't really clean all up (/showthread.php?tid=375610)

Database Cleanup don't really clean all up - BiTeMeNoW - 2024-01-01

Dear all,

Using MySql I noticed that the "uniqueid" table contains a lot of obsolete records.
It seems that when I replace a file the tables episode / movie / files / path / seasons are cleaned up correctly, but uiniqueid will be leaved back and wasting storage (and performance) for ever.
Is this supposed to be, or is it a glitch / bug?
As i do not use local database since more than a decade I'm curious if this also happen to SQLite.

Best regards

RE: Database Cleanup don't really clean all up - HeresJohnny - 2024-01-01

Maybe this will help you:

https://forum.kodi.tv/showthread.php?tid=369984

RE: Database Cleanup don't really clean all up - BiTeMeNoW - 2024-01-01

Thanks @HeresJohnny, this is not the expected answear Smile, but I can assume that it should be and KODI do have problems with orphaned records. Right?
Thank you very much for your fast help.

RE: Database Cleanup don't really clean all up - jbinkley60 - 2024-01-01

(2024-01-01, 15:53)HeresJohnny Wrote: Maybe this will help you:

https://forum.kodi.tv/showthread.php?tid=369984

I just checked the code and the Wiki page and I am not checking the uniqueid table for orphans with Kodi Selective Cleaner.  I'll look to add this in the next release.  Not sure how I missed this originally. 


Thanks,

Jeff

RE: Database Cleanup don't really clean all up - izprtxqkft - 2024-01-01

(2024-01-01, 20:17)BiTeMeNoW Wrote: but I can assume that it should be and KODI do have problems with orphaned records. Right?

correct

outside of database triggers (in the database itself) i do not believe kodi makes any effort to clean the database, it's the same for the "path" table, after long term use you will notice a lot of bits

if comfortable and confident enough i recommend adding custom triggers to aid keeping these entries cleaned up

RE: Database Cleanup don't really clean all up - jbinkley60 - 2024-01-01

(2024-01-01, 22:02)izprtxqkft Wrote:
(2024-01-01, 20:17)BiTeMeNoW Wrote: but I can assume that it should be and KODI do have problems with orphaned records. Right?

correct

outside of database triggers (in the database itself) i do not believe kodi makes any effort to clean the database, it's the same for the "path" table, after long term use you will notice a lot of bits

if comfortable and confident enough i recommend adding custom triggers to aid keeping these entries cleaned up

This thread got me curious.  I looked at an SQLite MyVideos122 and a MySQL myvideos122 database and there are 4 triggers which clean the uniqueid table when either an episode, movie, musicvideo  or tvshow is removed. 

When I add the uniqueid table into the analyzer it will be interesting to see the results.  Triggers work fine as long as things operate properly.  However, things sometimes go wrong or  go in the wrong sequence.  It could also be that there are enough remnants in the database that the trigger hasn't fired and just running KS Cleaner to clean out the other stuff may cause the triggers to fire. 

I'll try to get the uniqueid table added before I head out on vacation in just over 2 weeks.


Thanks,

Jeff

RE: Database Cleanup don't really clean all up - BiTeMeNoW - 2024-01-03

(2024-01-01, 23:07)jbinkley60 Wrote: This thread got me curious.  I looked at an SQLite MyVideos122 and a MySQL myvideos122 database and there are 4 triggers which clean the uniqueid table when either an episode, movie, musicvideo  or tvshow is removed. 

When I add the uniqueid table into the analyzer it will be interesting to see the results.  Triggers work fine as long as things operate properly.  However, things sometimes go wrong or  go in the wrong sequence.  It could also be that there are enough remnants in the database that the trigger hasn't fired and just running KS Cleaner to clean out the other stuff may cause the triggers to fire. 

I'll try to get the uniqueid table added before I head out on vacation in just over 2 weeks.


Thanks,

Jeff

Thank you Jeff. This cleaner is really appreciated. I was lookin to do it manually as my library is quite old (over a decade now) and there are a lot of orphaned entries in many tables  Laugh
Best regards

RE: Database Cleanup don't really clean all up - therealjoeblow - 2024-01-10

I used the cleaner tool last fall on my +10 year old database and reduced it from ~230gb to ~110gb.  It works really well.

Cheers
TRJB