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.

Update:

Since I moved all my files from local path to NAS, query statement used needs to be updated also:

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

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.

Update:

If the files are located on NAS, you should use query similar to this:

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

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.

Update:

Again if files are located on NAS you could use something like this:

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

And again to delete:

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

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;

Update:

Again if the files are located on NAS you should adapt your queries similar to this:

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

And delete:

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

 

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

10 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.

  2. These clean up sql commands help to maintain clean database entries.
    However when using multiple Kodi machines the library is accessed using Windows shares (SMB) in form of “smb://NAS/Media/Movies/Die Hard/”. The query for paths “SELECT * FROM path
    WHERE strPath NOT LIKE “/%” OR strPath IS NULL;” is returning all my path entries and when I perform the next query nearly all my library content is returned.
    What I can’t figure out is why no one else has had this issue with the query?

    1. At the time when I was writing this tutorial, I had all my files stored locally and the queries are related to that case. Later I moved also to samba share on my NAS. So in this case for example if you want to list all paths that are not on your NAS (I guess you do not need those since all files are there) you can use something like:

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

      Update: I have updated and tested queries in my new setup when NAS is used, so “/%” should be replaced with “smb:/%” if you want to clean your library from anything that is not your NAS links.

  3. Hi erol
    I just used this to fix my database – thank you very much. I have a NFS NAS so changed your queries to;

    SELECT * FROM files WHERE idPath IN ( SELECT idPath FROM path WHERE strPath NOT LIKE ‘nfs:/%’ OR strPath IS NULL );

    DELETE FROM files WHERE idPath IN ( SELECT idPath FROM path WHERE strPath NOT LIKE ‘nfs:/%’ OR strPath IS NULL );

    SELECT * FROM path WHERE strPath NOT LIKE “nfs:/%” OR strPath IS NULL

    DELETE FROM path WHERE strPath NOT LIKE “nfs:/%” OR strPath IS NULL

    Thanks again

  4. I tried this but got an error, seems my database needs to be specified as well. Any pointers?

    # mysql -u root -p
    Enter password:
    Welcome to the MariaDB monitor. Commands end with ; or \g.
    Your MariaDB connection id is 5
    Server version: 10.5.8-MariaDB Arch Linux

    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

    Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

    MariaDB [(none)]> SELECT * FROM WHERE idPath IN ( SELECT idPath FROM path WHERE strPath NOT LIKE ‘nfs:/%’ OR strPath IS NULL );
    ERROR 1046 (3D000): No database selected
    MariaDB [(none)]>

    1. You are right. After connecting to mysql CLI you should use command ‘use KODIDATABASENAME;’ where KODIDATABASENAME is your database name.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.