How to additionally clean-up Kodi MySQL database from unwanted entries

If you are like me using Kodi (XBMC) library to manage all your multimedia files you are probably used to updating and cleaning your library after adding or deleting new files.

My library at the moment consists of 15121 files total, but after examining it in PHPMyAdmin I have noticed that files table contains also some entries that point to non-existing idPath from path table and other files that are pointing to a path that is non-local (HTTP or plugin instead of regular system path).

I decided to clean up my library and to delete all files from files table and paths from paths table that point to non-local file system.

Before you begin make sure to backup your database.

Check to see if you have entries in your files table that have non existing idPath reference to path table.

SELECT * 
FROM files AS f
WHERE f.idPath NOT IN
 (
 SELECT idPath
 FROM path
 );

If you have such entries you can easily delete them with small change to this SQL statement:

DELETE 
FROM files
WHERE idPath NOT IN
(
SELECT idPath
FROM path
);

Check path entries that point to something else than local file system:

SELECT * FROM path 
WHERE strPath NOT LIKE "/%" OR strPath IS NULL;

You will probably find some path entries that refer to some plugins or HTTP addresses.

Select all files from files table which refer to non-local path or local path is empty:

SELECT * FROM files AS f
LEFT JOIN
 path AS p
ON p.idPath = f.idPath
WHERE p.strPath NOT LIKE "/%" OR p.strPath IS NULL;

It is easier to analyze if you do a join to see results in parallel. In my case result was showing 193 files in total.

Show just results from files table without join:

SELECT * FROM files WHERE idPath IN
 (
 SELECT idPath
 FROM path
 WHERE strPath NOT LIKE "/%" OR strPath IS NULL
 );

Again you should see 193 rows and you can delete them with following statement:

DELETE FROM files WHERE idPath IN
 (
 SELECT idPath
 FROM path
 WHERE strPath NOT LIKE "/%" OR strPath IS NULL
 );

Result should be 193 rows deleted.

Now you can go to clean up your paths from path table where path is not local or empty.

SELECT * FROM path 
WHERE strPath NOT LIKE "/%" OR strPath IS NULL;

In my case I found 125 paths total. And to delete them you can use:

DELETE FROM path 
WHERE strPath NOT LIKE "/%" OR strPath IS NULL;

I will probably update this article later with details how to automate this task via cron job.

4 Replies to “How to additionally clean-up Kodi MySQL database from unwanted entries”

  1. What is the purpose of doing this? I have an issue with library cleaning taking a very long time and it appears this may correct that issue by looking at the log after. A lot of remote sources with error and warning.

    1. I have automatic video database clean and scan operations running daily. After a while I have noticed that there are leftover data in the database which gets reported every time in the log when cron is running clean and scan. At least manually cleaning it up with these commands will keep your database free of these entries and also your logs will not be filled with unneeded error reports.

Leave a Reply

Your email address will not be published. Required fields are marked *