Deleting from 2 tables in MySQL

labzilla · September 13, 2016 at 3:11 PM

I have some entries in MySQL where the file no longer exists. I used the clean database feature in Kodi, and the entries are still there.

I can select the file using

Quote

SELECT * FROM files RIGHT JOIN path ON path.idPath = files.idPath WHERE files.idPath = 372

I know I can use a select to delete table rows, but I'm getting an error.

When I run this in MyPHPAdmin, I see

Quote

Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available.

I did a quick Google on the error message, and people are talking about a missing index, but both tables have a primary key. Plus I don't want to change table scheme. Any suggestions?

**Klojum** · September 13, 2016 at 7:29 PM Official Post

IDs for entries in files are linked with various other tables in the video database. Deleting only the entries in the 'files' table will get you a corrupted database.

If video entries are still there after a 'Clean Database', then it's probably because the files are somehow still accessible.

labzilla · September 13, 2016 at 10:07 PM

The System->Video->Clean library is not removing all of my deleted files. I have 10 episodes from a TV show that have been deleted, that are still in MySQL.

There is 12 or more files that no longer on my NAS. These files were never part of my movie or tv library, just files I play on kodi. AFAIK these files are only stored in 2 tables, I'm guessing kodi is saving the video resume & played status and nothing else.

I did a fresh build of MySQL database 3 or 4 months ago, not really sure why deleted files are still showing up in the database.