Hi there,
I’m running Kodi on Linux (Gentoo) and I’m trying to get the MySQL database backend running. Normally Kodi creates all necessary databases on startup if they don’t exist. For some reason on my machine some of the tables or views aren’t created and give me a lot of errors in the log.
20:12:44.436 T:139746642728704 ERROR: GetRecentlyAddedAlbumSongs failed
20:12:44.438 T:139746642728704 ERROR: SQL: [kodi_music60] The table does not exist
Query: SELECT albumview.*, albumartistview.* FROM (SELECT idAlbum FROM album WHERE strAlbum != '' ORDER BY idAlbum DESC LIMIT 10) AS recentalbums JOIN albumview ON albumview.idAlbum = recentalbums.idAlbum JOIN albumartistview ON albumview.idAlbum = albumartistview.idAlbum ORDER BY albumview.idAlbum desc, albumartistview.iOrder
20:12:44.438 T:139746642728704 ERROR: GetRecentlyAddedAlbums failedTo investigate that problem I extracted the table creation queries from the source code (MusicDatabase.cpp, Line 242). Importing the SQL code works until the stuff with the triggers. I’m quite a beginner in MySQL and haven’t had anything to do with triggers so far. The code:
CREATE TRIGGER tgrDeleteAlbum AFTER delete ON album FOR EACH ROW BEGIN
DELETE FROM song WHERE song.idAlbum = old.idAlbum;
DELETE FROM album_artist WHERE album_artist.idAlbum = old.idAlbum;
DELETE FROM album_genre WHERE album_genre.idAlbum = old.idAlbum;
DELETE FROM albuminfosong WHERE albuminfosong.idAlbumInfo=old.idAlbum;
DELETE FROM art WHERE media_id=old.idAlbum AND media_type='album';
ENDThe output from PHPMyAdmin:
Error
SQL query:
CREATE TRIGGER tgrDeleteAlbum AFTER delete ON album FOR EACH ROW BEGIN
DELETE FROM song WHERE song.idAlbum = old.idAlbum
MySQL said: Documentation
#1064 - 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 '' at line 2
I’m without any ideas what could be wrong. Any ideas?
(btw just for info the whole musicdb sql part)