[sam_1421]

Hi everyone,

I have a small problem with my library : all my movies are duplicated.

What happened is that at first I was using the hostname in my sources, but I recently changed it to 192.168.10.160 (my server's IP) so it's easier to setup across different operating systems (Android, MacOSX. Ubuntu, Windows). I'm using a MySQL server to store my data so the path must be identical across all my devices.

The problem is that I now have two entries for every movie. For example, I will have both smb://192.168.10.160/Movies/Inception.mkv AND smb://MEDIA_CENTER/Movies/Inception.mkv. It is the same file on the same server, but with a different path. Cleaning the library doesn't work because it searches to see if the file exists, but in both cases it does exist.

How would you do that?

Thanks!

[ChrisZ.]

Hi,

you can delete the "wrong" entries manually, but depending on the amount of movies, this might take a while..

Additionaly, you should remove the "wrong" sources (smb://MEDIA_CENTER/Movies/) from your library, by removing the according sources from your video-library.

Alternatively, you might try the following workaround:

-> Export your Video-Database.
-> Remove all entries in your Video-Database,
or remove the according video-sources and run a clean-up -> in the result, your database should be empty.

-> Run an according import. (If you also removed the video-sources, you just add them, movies should be imported automatically).
-> Your movies should be in.

Of course, there are other workarounds like doing according clean-ups in the SQL-Database directly.

Hope this information helps.

[sam_1421]

Thanks for your answer! Would it be OK to remove the entries in the movies database with a simple query like DELETE FROM movies WHERE path REGEXP 'MEDIA_CENTER' ? If it's OK it wouldn't take long to do I think!

[mkortstiege]

Don't forget to clean the old path table entries.

[ChrisZ.]

Yes, removing them in the database directly with according query, will remove them in the library.

As mkorstiege mentioned, you need do the same for path entries, and you should do the same for file entries.

And it´s very important to remove the "wrong" source from you library, otherwise it will be reimportet/rescraped next time, you click on according update-button

[sam_1421]

Thanks guys, that worked like a charm. For those wanting to do it, here are the commands I used to do it:

PHP Code:

DELETE FROM movie WHERE c22 REGEXP 'smb://HOSTNAME_I_WANT_TO_DELETE';
DELETE FROM path WHERE strPath REGEXP 'smb://HOSTNAME_I_WANT_TO_DELETE'
DELETE FROM files WHERE idPath NOT IN (SELECT idPath FROM path); 


I already tried the method of removing the source and cleaning the library before, but it didn't work. I assumed it was because the path was still valid even though it was not in my sources, so the cleanup daemon didn't consider it invalid.

[ChrisZ.]

Good news!

This way it´s also completely removed in the database

Regards,
Chris

[sincze]

Whoehoe those commands saved my day. Tnx.

[rrick]

Hi sam_1421,

If I understand your question correctly, you are running Kodi with a remote (or centralised) MySQL database as your Movie/TV Episode datastore.  You now have some duplicate entries in your wither your movies, TV Episodes, or both and you want to clean up your duplicate entries in your Mysql database. 

Firstly you want to identify the name of your Kodi database

[rrick]

Hi sam_1421,

If I understand your question correctly, you are running Kodi with a remote (or centralised) MySQL database as your Movie/TV Episode datastore.  You have some duplicate entries in your movies, TV Episodes, or both and you want to clean up your duplicate entries in your MySQL database. 

Firstly you need to identify the name of your Kodi database:

$ mysqlshow -u root -p
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| MyMusic72          |
| MyMusic82          |
| MyVideos116        |
| MyVideos119        |
| mysql              |
| performance_schema |
| test               |
+--------------------+
$

In the above example I have two Kodi sets of data stores, MyVideos116 and MyVideos119 so I need to reference the table with higher number (MyVideos119).

Secondly, do a backup of the database (my kody database is MyVideos119): 

$ mysqldump -u root -p MyVideos119 | gzip -9 -c > myvideos119-`date +%Y%m%d-%H:%M`.sql.gz

Now we need to process the cleanup of the database and in regard with the "Movies" and to do this you need to log into MySQL and then execute the following SQL statement (Remember my database is MyVideos119, replace that with your database name):

$ mysql -u root -p
mysql> DELETE t1 FROM movie t1 INNER JOIN movie t2 WHERE t1.idMovie < t2.idMovie AND t1.c00 = t2.c00;
Query OK, 1277 rows affected (53.67 sec)

and for the duplicate TV Episodes you need to execute the following (remembering to replace the database with your database name):

mysql> DELETE t1 FROM episode t1 INNER JOIN episode t2 WHERE t1.idEpisode < t2.idEpisode AND t1.c00 = t2.c00;
Query OK, 7466 rows affected (31 min 12.61 sec)

mysql> \q
$

And that should be it.

When you now startup your Kodi device your duplicate movies and episodes should all be removed and you should still have all of your wanted programming in the database.

Best of luck, let me know if you have problems.

Rick

[Klojum]

Hi sam_1421,

You do know that you are reacting to a post that is 7 yrs old by now?
sam_1421 also hasn't visited this forum since that day.

The t1 and t2 references in your two queries are not entirely useful.

[rrick]

Yes Sam, I am aware.  The point of most forums is to act as an information repository.  Now, I had no success in trying to resolve this issue for my koi network and once I worked out the solution I made it available for the whole community (should they need it).

Maybe I am a lot older than you, but this is how the "internet community" used to work, if it doesn't any longer then that explains why I could not find the answers when I needed them.

[rrick]

...[deleted]
and for the duplicate TV Episodes you need to execute the following (remembering to replace the database with your database name):

mysql> DELETE t1 FROM episode t1 INNER JOIN episode t2 WHERE t1.idEpisode < t2.idEpisode AND t1.c00 = t2.c00;
Query OK, 7466 rows affected (31 min 12.61 sec)

Anyone who wants to use the above command to remove duplicate TV episodes please be aware that if you use the naming standard of...

/TVshow/S1/S01E01 - Episode_name.*

Then you may delete episodes that you may not have intended to delete, so be aware.  Another approach would be to delete your TV shows and rescan the lot. I could fix the SQL and I will look at it further when I get a chance.

Cheers, Rick 
(10 node Kodi home network - 80TB NAS data storage - my complete VCD, DVD & BluRay library.)

[rafi1]

My feedback: https://github.com/xbmc/xbmc/issues/25053