mariadb and databases not being created properly

    i am trying to use mariadb as the backend database for my rpi4/libreelec/kodi install. i have created the advancedsettings.xml and pointed it to my mariadb instance. i know the connections work because the databases are created (MyMusic82 and MyVideos119), but the databases are incomplete. the tables and indexes are created, but there are no triggers and no views created.

    i had this working at one point in the past, and i did a mysqldump of the database tables, triggers, indexes, etc, but no data as i figured i could always repopulate it. i have that sql output and i can see the parts of the databases that are missing. i dropped the databases and rebooted the rpi4 to force the recreation of the databases, and they seem to be incomplete. i cannot get my libraries to be loaded into mariadb.

    some examples from /storage/.kodi/temp/kodi.log

    the creation of the database fails, and all subsequent work is for naught because not all of the database is present. what is going on with the database creation process? i have mariadb running 10.4.19 and LibreELEC (official): 10.0.2 (RPi4.arm).

    yes, the kodi user has all necessary access to the databases.

    Code
    MariaDB [(none)]> show grants for kodi;
+-----------------------------------------------------------------------------------------------------+
| Grants for kodi@% |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `kodi`@`%` IDENTIFIED BY PASSWORD 'PassHashRedacted' |
| GRANT ALL PRIVILEGES ON `MyMusic82`.* TO `kodi`@`%` WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `MyVideos119`.* TO `kodi`@`%` WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------+
3 rows in set (0.002 sec)

    the tables are created as well as an index or more, but the creation of the first trigger fails and the whole process bombs out. lines 22 onward, from my first post, show the error and failure.

    i didnt see the error code 1419 before, but looked it up. because i am running galera n-way multi-primary replication, the log_bin_trust_function_creators option needs to be enabled and/or the kodi user needs GLOBAL SUPER privs. i am not sure if one or the other is required, but i set both, dropped both library databases and rebooted my rpi4. will see what happens...

    i am running mariadb 10.4.19 on fedora 32. not the most recent, but not ancient.

    i found that giving the kodi user SUPER privs, and setting the log_bin_trust_function_creators global variable to 1 allowed the databases to be properly created.

    interestingly, i also see some failed queries indicate missing views...

    Code
    2022-09-20 09:37:09.454 T:834 ERROR <general>: SQL: [MyVideos119] The table does not exist
Query: select * from movie_view ORDER BY dateAdded desc, idMovie desc LIMIT 10


2022-09-20 09:37:09.455 T:834 ERROR <general>: GetMoviesByWhere failed
2022-09-20 09:37:09.477 T:834 ERROR <general>: SQL: [MyVideos119] The table does not exist
Query: select * from musicvideo_view ORDER BY dateAdded desc, idMVideo desc LIMIT 10


2022-09-20 09:37:09.477 T:834 ERROR <general>: GetMusicVideosByWhere failed

    i think the queries fail because the database(s) are empty. i still need to add the sources and media. i think the error messages are misleading if not wrong. the query is for data in a view which does exist but has not data, not a table.

    i am not sure i like what i had to do, to get libraries added to mariadb. i had to grant global rights to the kodi user, which does not sit well with me.

    Code
    MariaDB [(none)]> show grants for kodi;
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for kodi@% |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `kodi`@`%` IDENTIFIED BY PASSWORD 'PassHashRedacted' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.002 sec)

    i might be able to pare down the access to something like global "super" and then grant database specific access to the kodi user, but there isnt much in the way of clear and concise detail i can find. does anyone have good info around what global privs the kodi user needs, in order to keep to a "minimum access necessary" config?

    "interestingly, i also see some failed queries indicate missing views..."

    I've upgraded today from Kodi 19.5. to 20. But mariadb migration is obviously failing. The migration from video119 to 121 caught all tables, but none of the views and none of the triggers - so nothing is shown in the kodi libraries. The Music82 tables seem untouched and are working well.

    I found sveral similar entries here an elsewhere, but I couldn't get my mariadb running correctly.

    I (re-)tried the migration several times (librelec or kodi-windows clients), but all the same result.

    The kodi Log shows the following error:

    info <general>: Attempting to update the database MyVideos121 from version 119 to 121

    2023-03-10 17:51:20.204 T:998

    error <general>: SQL: Can't determine list of routines to drop.

    The often mentioned solution "mysql_upgrade" gives me:

    ERROR 1064 (42000): 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 'mysql_upgrade' at line 1

    But here I couldn't find a hint that helped me....

    MariaDB Version is 10.11.2 - I think it's uptodate. The root and the kodi user got all neccesary rights granted as I read in several forums - but who knows? Any help is apriciated! :)

    If needed, I'll try to provide a full debug error.

    I'm migrating kodi now the third time and I had no problem with the databasemigration before.

    Thanks for support!

    Edited once, last by MacNoname (March 11, 2023 at 9:14 PM).