Kodi Community Forum - Music db creation failing on MariaDB at triggers

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.

Code:
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 failed

To 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:

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';

END

The output from PHPMyAdmin:

Code:
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)

(2017-04-26, 21:10)musv Wrote: [ -> ]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.

Were you installing a new Kodi version on a server that had and older music db to migrate to the the new version? Interupted migration is the only reason I can think of for a new db not being correctly created.

However this occurred, the best way forwards is to drop the corrupted db, ensuring it is completely removed from your MySQL server, and then run Kodi again. This should recreate the music db again on startup. Post (a link to) the whole debug log if this still results in an issue.

Quote:To 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....

Elsewhere in the code modifies the creation statements to allow for MySQL or SQLite syntax differences, so what you extracted may not be correct. To be honest trying to do this manually, copying SQL from the code, is not going to sort out a corrupted db.

(2017-04-27, 16:05)DaveBlake Wrote: [ -> ]Were you installing a new Kodi version on a server that had and older music db to migrate to the the new version?

No, I dropped the databases completely.

(2017-04-27, 16:05)DaveBlake Wrote: [ -> ]Elsewhere in the code modifies the creation statements to allow for MySQL or SQLite syntax differences, so what you extracted may not be correct. To be honest trying to do this manually, copying SQL from the code, is not going to sort out a corrupted db.

Yes, but it was point to start for me, when I ran out of ideas.

For testing I dropped the database kodi_music60 and started kodi. Here's the logfile:

https://paste.pound-python.org/show/umSn...OyXnUYrub/

The relevant parts are:

Code:
10:09:35.860 T:139787748837120   ERROR: SQL: [kodi_music60] Undefined MySQL error: Code (1419)

                                            Query: 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'; END

10:09:35.860 T:139787748837120   ERROR: CreateDatabase unable to create database:0

And that's exactly the problem I figured out in my first posting. Mysql isn't able to create the triggers.

After the startup process the views are missing. I find the following tables:

Code:
use kodi_music60

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [kodi_music60]> show tables;

+------------------------+

| Tables_in_kodi_music60 |

+------------------------+

| album                  |

| album_artist           |

| album_genre            |

| albuminfosong          |

| art                    |

| artist                 |

| content                |

| cue                    |

| discography            |

| genre                  |

| path                   |

| role                   |

| song                   |

| song_artist            |

| song_genre             |

| version                |

+------------------------+

16 rows in set (0.00 sec)

MariaDB [kodi_music60]> SHOW FULL TABLES IN kodi_music60 WHERE TABLE_TYPE LIKE 'VIEW';

Empty set (0.00 sec)

Used MariaDB version: 10.1.22.

Nevertheless I'll play around today to get this trigger stuff somehow imported into the database.

The creation order for a new db is tables, indexes, triggers and then views, so failing during trigger creation will result in a db with tables but no views as you have.

I think you have a privileges issue in MariaDB that is preventing the triggers from being created.

MariaDB says: ERROR 1419 is ER_BINLOG_CREATE_ROUTINE_NEED_SUPER: "You do not have the SUPER privilege and binary logging is enabled"

From MySQL manual (http://dev.mysql.com/doc/refman/5.1/en/p...vided.html):
The TRIGGER privilege enables you to create and drop triggers. You must have this privilege for a table to create or drop triggers for that table. This privilege was added in MySQL 5.1.6. (Prior to MySQL 5.1.6, trigger operations required the SUPER privilege.)

This post gives a solution (I think) http://forum.kodi.tv/showthread.php?tid=...pid1958252 although reading the thread may help.

Change MariaDB server settings, drop corrupted music db, and start Kodi one more time.

EDIT: Oh and I have renamed the thread to make it clearer for anyone else with a similar issue.

Thanks, this was the hint I needed. According to http://forum.kodi.tv/showthread.php?tid=...pid1958252

Code:
SET GLOBAL log_bin_trust_function_creators = 1;

did the trick.

Maybe this setting should be included into the Kodi Wiki.