[jbinkley60]

I've gone ahead and published a test release of Kodi Selective Cleaner which has support to fix music database triggers. 

With regards to my questions, I added a setting which allows you to disabled the missing trigger checking (default), silently repair and log (On) it notify you and ask if you want it to be fixed (notify).  The current version works with locally connected libraries.  For MySQL it will detect the missing triggers but not repair them, if you are using the standard mymusicxx MySQLdatabase naming.  

Feedback is welcome.


Thanks,

Jeff

[black_eagle]

Fixed in core.  The fix will be in Nexus v20.3 and nightlies going forwards.

Relevant PR's - master and Nexus

[jbinkley60]

Fixed in core.  The fix will be in Nexus v20.3 and nightlies going forwards.

Relevant PR's - master and Nexus

Great news. Glad it's resolved in Kodi.


Jeff

[HeresJohnny]

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;

The KSC add-on found two missing triggers in mymusic83, the exact ones as in the old post above. All triggers are present in mymusic82, no idea why those two would be missing now - the db would have been converted from a previous version. However, on running the SQL code to create the two missing triggers in DBeaver (flattened):

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;

I'm getting an error:

Code:

SQL Error [1064] [42000]: (conn=874) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'END' at line 1

I've had Dbeaver copy the code from mymusic82 to recreate the syntax so I'm pretty sure there's no typos. I also checked that all columns exist in the tables and have the same data types. Any idea what could be going wrong?

[DaveBlake]

The KSC add-on found two missing triggers in mymusic83, the exact ones as in the old post above. All triggers are present in mymusic82, no idea why those two would be missing now - the db would have been converted from a previous version.

The triggers will be missing if cleaning the music database has been interrupted either by a power outage shutting down Kodi without using UI, or a break in your network connection.

The SQL error in your manual repair is probably because the ";" as end of block is being interperted as the delimiter in the tool you are using. See https://mariadb.com/kb/en/delimiters/

[HeresJohnny]

(Post updated to make it clearer)
Thanks and sorry, I read a few posts further along the old problem and found https://forum.kodi.tv/showthread.php?tid...pid3070920

Re-posting the solution:

Code:

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 ;

[black_eagle]

UPDATE: Yes, it does, too. A simple database refresh wasn't enough to show the changes, had to disconnect and reconnect, strangely.

Not entirely clear what you are reporting here.  Does it work or not ?

[jbinkley60]

The KSC add-on found two missing triggers in mymusic83, the exact ones as in the old post above. All triggers are present in mymusic82, no idea why those two would be missing now - the db would have been converted from a previous version. However, on running the SQL code to create the two missing triggers in DBeaver (flattened):

Glad the new KS Cleaner feature helped.


Jeff