[VelvetKevorkian]

https://github.com/xbmc/xbmc/issues/1975...-868984954

As advised by Mr. Blake over on GitHub, i've opened this thread.
It seems like there is an issue with my music db, it cannot be cleaned anymore.

Code:

2021-06-26 12:32:37.310 T:8756 INFO : Cleanup: Starting musicdatabase cleanup .. 2021-06-26 12:32:37.317 T:8756 ERROR : SQL: [MyMusic82.db] SQLite error SQLITE_ERROR (no such trigger: tgrDeleteSongArtist) Query: DROP TRIGGER tgrDeleteSongArtist (no such trigger: tgrDeleteSongArtist) 2021-06-26 12:32:37.343 T:8756 ERROR : Process error processing job MusicLibraryCleaningJob]

KODI is running on an Nvidia Shield Pro 2019, the music db was created from scratch a few days ago.
The Shield tends to close down KODI if a different app is focused, so maybe there was an interrupted db update.

Any help is greatly appreciated.

[Klojum]

Either the trigger was never created properly, or it has been malformed for whatever reason in the database file.

Personally I'd suggest a library export, so a new music database can be created, metadata imported and built up from the start.

[DaveBlake]

As mentioned on Github, the log snippet shows that that there problem with your your music database schema (and that you are using local SQLite databases). The database trigger tgrDeleteSongArtist is missing (and I suspect that tgrDeleteAlbumArtist may be lost too). I can only guess that something interupted either database migration from a previous version, or perhaps the cleaning process previously and caused this trigger to be lost.

The missing db trigger can be created with some simple SQL, however you will need to run some SQLite browser tool (such as DB Browser) to be able to do that. I'm not sure if you will be able to do that on the Nvidia Shield itself, you probably need to copy the MyMusic82.db file onto another computer, create the trigger, and then copy it back. I can get you additional help if you are unsure how to do that or the location of the database file.

Using DB Browser

First to check if there are any other triggers missing run

Code:

SELECT name FROM sqlite_master WHERE type = 'trigger';

The result should be 15 triggers:
"tgrDeleteAlbum", "tgrDeleteArtist", "tgrDeleteSong", "tgrDeleteSource", "tgrInsertSong", "tgrUpdateSong", "tgrInsertAlbum", "tgrUpdateAlbum", "tgrInsertArtist", "tgrUpdateArtist", "tgrInsertGenre", "tgrInsertSongArtist", "tgrInsertAlbumArtist", "tgrDeleteSongArtist", "tgrDeleteAlbumArtist".
I suspect it is just the last two that are missing, but please report back if others are missing too.

Then the SQL to recreate this missing trigger(s) is:

Code:

CREATE TRIGGER tgrDeleteSongArtist AFTER DELETE ON song_artist FOR EACH ROW BEGIN
INSERT INTO removed_link (idArtist, idMedia, idRole)
VALUES(OLD.idArtist, OLD.idSong, OLD.idRole);
END;

CREATE TRIGGER tgrDeleteAlbumArtist AFTER DELETE ON album_artist FOR EACH ROW BEGIN
INSERT INTO removed_link (idArtist, idMedia, idRole)
VALUES(OLD.idArtist, OLD.idAlbum, -1);
END;

Save the database once SQL has run succesfully, and copy the db back onto the shield, you don't even need to stop Kodi to do this (as long it isn't trying to write anything to the music library at the time e.g. increment a playcount or doing a scan).

However if all this talk of SQL and manually doing this to your databse sounds too much for you, then say and I will try to come up with another approach.

[DaveBlake]

Personally I'd suggest a library export, so a new music database can be created, metadata imported and built up from the start.

Remember music not video, so to repopulate a new music database means rescanning all the music files. Export/import can of course be used to maintain additional album and artists information and artwork (and avoid rescraping this from remote sites), and more essentially can be used to keep song playback history (playcount, lastplayed and user rating), but export does not backup the library.

That is a fallback approach to repairing the a broken schema, but if you have the skills and confidence to use  SQL then it is far quicker just to check and recreate any missing triggers.

[DaveBlake]

From Github, discussion is better continued here on the forum.

 Inconsistencies in the DB schema only manifest themselves in the logs and are not reported to the user.

The log is generally where all errors in Kodi are reported, this is by design. Up to the user to look at it, or not. The other possibility is the event log (if the user has enabled it) but this is destroyed on shutdown and the user still has to look at it. I assume what you want is a dilog box saying "clean-up failed!!!!".

But generally corruption of db schema is rare - my family have used Kodi daily for years and NEVER had any kind of db issue.
 

The way suggested in the forum to fix the error (export, rebuild, etc.) could surely offer the software itself?

My suggestion for the simple quick fix is that you use SQL to repair the trigger. Since db corruption is both rare and when it does happen problems are very diverse, there is no obvious self-repair facility that could be created.

[Klojum]

Auto/self-fixing software... That'll be the day

[VelvetKevorkian]

thx, i'll use SQLiteDatabaseBrowserPortable to add the missing triggers

[arad85]

As mentioned on Github, the log snippet shows that that there problem with your your music database schema (and that you are using local SQLite databases). The database trigger tgrDeleteSongArtist is missing (and I suspect that tgrDeleteAlbumArtist may be lost too). I can only guess that something interupted either database migration from a previous version, or perhaps the cleaning process previously and caused this trigger to be lost.

The missing db trigger can be created with some simple SQL, however you will need to run some SQLite browser tool (such as DB Browser) to be able to do that. I'm not sure if you will be able to do that on the Nvidia Shield itself, you probably need to copy the MyMusic82.db file onto another computer, create the trigger, and then copy it back. I can get you additional help if you are unsure how to do that or the location of the database file.

Using DB Browser

First to check if there are any other triggers missing run
 

Code:

SELECT name FROM sqlite_master WHERE type = 'trigger';

The result should be 15 triggers:
"tgrDeleteAlbum", "tgrDeleteArtist", "tgrDeleteSong", "tgrDeleteSource", "tgrInsertSong", "tgrUpdateSong", "tgrInsertAlbum", "tgrUpdateAlbum", "tgrInsertArtist", "tgrUpdateArtist", "tgrInsertGenre", "tgrInsertSongArtist", "tgrInsertAlbumArtist", "tgrDeleteSongArtist", "tgrDeleteAlbumArtist".
I suspect it is just the last two that are missing, but please report back if others are missing too.

Then the SQL to recreate this missing trigger(s) is:
 

Code:

CREATE TRIGGER tgrDeleteSongArtist AFTER DELETE ON song_artist FOR EACH ROW BEGIN
INSERT INTO removed_link (idArtist, idMedia, idRole)
VALUES(OLD.idArtist, OLD.idSong, OLD.idRole);
END;

CREATE TRIGGER tgrDeleteAlbumArtist AFTER DELETE ON album_artist FOR EACH ROW BEGIN
INSERT INTO removed_link (idArtist, idMedia, idRole)
VALUES(OLD.idArtist, OLD.idAlbum, -1);
END;

Save the database once SQL has run succesfully, and copy the db back onto the shield, you don't even need to stop Kodi to do this (as long it isn't trying to write anything to the music library at the time e.g. increment a playcount or doing a scan).

However if all this talk of SQL and manually doing this to your databse sounds too much for you, then say and I will try to come up with another approach.

Just in case anyone stumbles across this and is using MariaDB as the SQL backend, this error also occurs there. To fix it, you need to change the delimiter as shown here: https://mariadb.com/kb/en/trigger-keeps-creating-error/

So the SQL becomes:

sql:


DELIMITER |

CREATE TRIGGER tgrDeleteSongArtist AFTER DELETE ON song_artist FOR EACH ROW BEGIN

INSERT INTO removed_link (idArtist, idMedia, idRole)

VALUES(OLD.idArtist, OLD.idSong, OLD.idRole);

END |



CREATE TRIGGER tgrDeleteAlbumArtist AFTER DELETE ON album_artist FOR EACH ROW BEGIN

INSERT INTO removed_link (idArtist, idMedia, idRole)

VALUES(OLD.idArtist, OLD.idAlbum, -1);

END |

DELIMITER ;

[Klojum]

Thread marked solved.

[buddiemac]

David Blake - Thanks so much - you were correct the two delete triggers were missing. I used a GUI for SQL, SQLCipher Browser. This GUI provide tools to modify Database pragmas thus I added your script and executed it and the Triggers were added!. Then import modified database back into Kodi using the Program Addon "backup" and then ran clean on the library and now I could see the trace bar showing progress.

Thought adding triggers would be difficult but you made it so easy that in 15 minutes it was completed. Losing these triggers are common in Matirx?

I did notice the syntax for these two triggers are different than the others:

CREATE TRIGGER tgrDeleteArtist AFTER delete ON artist FOR EACH ROW BEGIN  DELETE FROM album_artist WHERE album_artist.idArtist = old.idArtist;  DELETE FROM song_artist WHERE song_artist.idArtist = old.idArtist;  DELETE FROM discography WHERE discography.idArtist = old.idArtist;  DELETE FROM art WHERE media_id=old.idArtist AND media_type='artist'; END

CREATE TRIGGER tgrDeleteAlbumArtist AFTER DELETE ON album_artist FOR EACH ROW BEGIN
INSERT INTO removed_link (idArtist, idMedia, idRole)
VALUES(OLD.idArtist, OLD.idAlbum, -1);
END

Just a FYI

[Klojum]

Thread marked solved (again).

[Shredder_guitar]

excellent solution. This helped me. Thank you for posting.

[Shredder_guitar]

CREATE TRIGGER tgrDeleteAlbumArtist AFTER DELETE ON album_artist FOR EACH ROW BEGIN
INSERT INTO removed_link (idArtist, idMedia, idRole)
VALUES(OLD.idArtist, OLD.idAlbum, -1);
END;

This has happened to me twice now and both times this query entry has fixed it. Thank you very much!

[VelvetKevorkian]

Hi,


i'm having the same issue again:
 

Code:

2023-07-12 22:20:34.358 T:29964    info <general>: CMusicDatabase::Cleanup: Starting musicdatabase cleanup ..
2023-07-12 22:20:34.435 T:29964   error <general>: SQL: [NAS_Music82] Undefined MySQL error: Code (1360)
                                                   Query: DROP TRIGGER tgrDeleteSongArtist
                                                   
2023-07-12 22:20:34.437 T:29964   error <general>: CJobWorker::Process error processing job MusicLibraryCleaningJob

This time i know what caused it:
I used Kore on my phone, Library maintenance -> Clean audio library.
While it was doing that task, i accidentally hit Clean video library which seems to have canceled the audio db process, damaged the db/caused the issue.


But now i use PMA on my NAS with KODI and cannot access the .db files with an SQL editor.
I presume i have to adjust the commands, since e.g. the sqlite_master table isn't there:

[VelvetKevorkian]

nvm, the maria db solution posted by arad85 worked after a bit of trial and error