Kodi – database cleanup/cruft

Posted on Author Kodi Community Forum - All Forums

I run kodi off a sql backend, and tonight I was migrating it to another box and noticed a few things.
I’ve been running kodi back when it was called xbmc.. and currently on v17rc3.
On that box I exported a .sql file and went to import it into a new fresh v17rc3 instance (ran once to create the folders/schema) — so yes same versions on both boxes.

First issue, was unable to import .sql:

Code:
SQL Error (1054): Unknown column 'Crop' in 'field list'

Looking at logs to see what kodi created,

Code:
18:52:56.139 T:4476   DEBUG: Mysql execute: CREATE TABLE settings ( idFile integer, Deinterlace bool,ViewMode integer,ZoomAmount float, PixelRatio float, VerticalShift float, AudioStream integer, SubtitleStream integer,SubtitleDelay float, SubtitlesOn bool, Brightness float, Contrast float, Gamma float,VolumeAmplification float, AudioDelay float, OutputToAllSpeakers bool, ResumeTime integer,Sharpness float, NoiseReduction float, NonLinStretch bool, PostProcess bool,ScalingMethod integer, DeinterlaceMode integer, StereoMode integer, StereoInvert bool, VideoStream integer)

Yep no Crop* columns.. so they were removed at some point but no migration/alter of the existing user schema happened…
Tracked it down to: https://github.com/xbmc/xbmc/commit/d30e…ccdb3d2d62

Easy fix, run this to fix old database to conform to existing schema:

Code:
ALTER TABLE `settings` DROP COLUMN `Crop`, DROP COLUMN `CropLeft`, DROP COLUMN `CropRight`, DROP COLUMN `CropTop`, DROP COLUMN `CropBottom`;

Okay, now that I have the data in the database I noticed a lot of old stuff that shouldn’t be in there..

When removing a tvshow from kodi the tables `path`, `tvshow`, and `files` never get their data removed.
Running clean library removes the episodes but the references to files never get cleaned up properly… so you end up with tables pointing to invalid and orphaned information in the db.

There is a trigger when deleting a show.. but it doesnt cleanup everything…
`delete_tvshow` trigger:

Code:
BEGIN DELETE FROM actor_link WHERE media_id=old.idShow AND media_type='tvshow'; DELETE FROM director_link WHERE media_id=old.idShow AND media_type='tvshow'; DELETE FROM studio_link WHERE media_id=old.idShow AND media_type='tvshow'; DELETE FROM tvshowlinkpath WHERE idShow=old.idShow; DELETE FROM genre_link WHERE media_id=old.idShow AND media_type='tvshow'; DELETE FROM movielinktvshow WHERE idShow=old.idShow; DELETE FROM seasons WHERE idShow=old.idShow; DELETE FROM art WHERE media_id=old.idShow AND media_type='tvshow'; DELETE FROM tag_link WHERE media_id=old.idShow AND media_type='tvshow'; DELETE FROM rating WHERE media_id=old.idShow AND media_type='tvshow'; DELETE FROM uniqueid WHERE media_id=old.idShow AND media_type='tvshow'; END

To view stale paths:

Code:
SELECT * FROM `myvideos107`.`path` WHERE (`strHash` IS NULL OR `strHash` = '') AND `idParentPath` = 3 ORDER BY `strHash` ASC;

From this list, plug in the idPath that you get back for one of the shows inplace of ### below:

tvshow:

Code:
SELECT * FROM `myvideos107`.`tvshow` WHERE idShow = (SELECT idShow FROM `myvideos107`.`tvshowlinkpath` WHERE idPath = ###);

now the delete_tvshow trigger *should* have deleted all the actor_link info but you can see some still exists:

actor_link:

Code:
SELECT * FROM `myvideos107`.`actor_link` WHERE media_type = 'tvshow' AND media_id = (SELECT idShow FROM `myvideos107`.`tvshowlinkpath` WHERE idPath = ###);

now, you cant clean up the stale files in the db because the relationship info in the episode table is missing (### is the show id from above):

Code:
SELECT * FROM `myvideos107`.`episode` WHERE `idShow` = ###;

assume it’s gone because of clean library…

there are also blank filenames in the db which looks like a sign of bad cleanup:

Code:
SELECT * FROM `myvideos107`.`files` WHERE `strFilename` = '' ORDER BY `idPath` ASC;