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 [email protected]% |
    +-----------------------------------------------------------------------------------------------------+
    | 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 [email protected]% |
    +--------------------------------------------------------------------------------------------------------------------------------+
    | 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?