mariadb and databases not being created properly

brendan_kearney · September 20, 2022 at 12:08 AM

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

Code

2022-09-19 18:43:06.686 T:831      INFO <general>: creating version table                                                                                     
2022-09-19 18:43:06.887 T:831      INFO <general>: create artist table                                                                                        
2022-09-19 18:43:07.177 T:831      INFO <general>: create album table                                                                                         
2022-09-19 18:43:07.622 T:831      INFO <general>: create audiobook table                                                                                     
2022-09-19 18:43:07.865 T:831      INFO <general>: create album_artist table                                                                                  
2022-09-19 18:43:08.076 T:831      INFO <general>: create album_source table                                                                                  
2022-09-19 18:43:08.344 T:831      INFO <general>: create genre table                                                                                         
2022-09-19 18:43:08.791 T:831      INFO <general>: create path table                                                                                          
2022-09-19 18:43:09.129 T:831      INFO <general>: create source table                                                                                        
2022-09-19 18:43:09.486 T:831      INFO <general>: create source_path table                                                                                   
2022-09-19 18:43:09.671 T:831      INFO <general>: create song table                                                                                          
2022-09-19 18:43:09.870 T:831      INFO <general>: create song_artist table                                                                                   
2022-09-19 18:43:10.224 T:831      INFO <general>: create song_genre table                                                                                    
2022-09-19 18:43:10.610 T:831      INFO <general>: create role table                                                                                          
2022-09-19 18:43:11.017 T:831      INFO <general>: create infosetting table                                                                                   
2022-09-19 18:43:11.254 T:831      INFO <general>: create discography table                                                                                   
2022-09-19 18:43:11.516 T:831      INFO <general>: create art table                                                                                           
2022-09-19 18:43:11.816 T:831      INFO <general>: create versiontagscan table                                                                                
2022-09-19 18:43:12.056 T:831      INFO <general>: create removed_link table                                                                                  
2022-09-19 18:43:12.483 T:831      INFO <general>: CreateAnalytics - creating indices                                                                         
2022-09-19 18:43:20.177 T:831      INFO <general>: create triggers                                                                                            
2022-09-19 18:43:20.182 T:831     ERROR <general>: SQL: [MyMusic82] Undefined MySQL error: Code (1419)                                                        
                                                   Query: CREATE TRIGGER tgrDeleteAlbum AFTER delete ON album FOR EACH ROW BEGIN  DELETE FROM song WHERE song.
                                                                                                                                                              
2022-09-19 18:43:20.182 T:831     ERROR <general>: CreateDatabase unable to create database:11

Display More

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).

squirtbrnr · September 20, 2022 at 3:32 AM

Did you grant the required permissions for the database to the user which Kodi uses to connect with?

brendan_kearney · September 20, 2022 at 1:51 PM

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.

brendan_kearney · September 20, 2022 at 2:39 PM

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...

**chewitt** · September 22, 2022 at 5:29 AM

What version of MariaDB? .. it needs to be something recent.

brendan_kearney · September 22, 2022 at 1:47 PM

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.

Code

2022-09-20 09:36:35.813 T:819 INFO <general>: creating version table
2022-09-20 09:36:35.990 T:819 INFO <general>: create artist table
2022-09-20 09:36:36.350 T:819 INFO <general>: create album table
2022-09-20 09:36:36.689 T:819 INFO <general>: create audiobook table
2022-09-20 09:36:37.094 T:819 INFO <general>: create album_artist table
2022-09-20 09:36:37.324 T:819 INFO <general>: create album_source table
2022-09-20 09:36:37.506 T:819 INFO <general>: create genre table
2022-09-20 09:36:37.697 T:819 INFO <general>: create path table
2022-09-20 09:36:37.913 T:819 INFO <general>: create source table
2022-09-20 09:36:38.134 T:819 INFO <general>: create source_path table
2022-09-20 09:36:38.325 T:819 INFO <general>: create song table
2022-09-20 09:36:38.521 T:819 INFO <general>: create song_artist table
2022-09-20 09:36:38.704 T:819 INFO <general>: create song_genre table
2022-09-20 09:36:38.897 T:819 INFO <general>: create role table
2022-09-20 09:36:39.111 T:819 INFO <general>: create infosetting table
2022-09-20 09:36:39.352 T:819 INFO <general>: create discography table
2022-09-20 09:36:39.571 T:819 INFO <general>: create art table
2022-09-20 09:36:39.800 T:819 INFO <general>: create versiontagscan table
2022-09-20 09:36:40.009 T:819 INFO <general>: create removed_link table
2022-09-20 09:36:40.245 T:819 INFO <general>: CreateAnalytics - creating indices
2022-09-20 09:36:45.742 T:819 INFO <general>: create triggers
2022-09-20 09:36:46.965 T:819 INFO <general>: Create native MySQL/MariaDB functions
2022-09-20 09:36:47.346 T:819 INFO <general>: create song view
2022-09-20 09:36:47.515 T:819 INFO <general>: create album view
2022-09-20 09:36:47.804 T:819 INFO <general>: create artist view
2022-09-20 09:36:48.144 T:819 INFO <general>: create albumartist view
2022-09-20 09:36:48.294 T:819 INFO <general>: create songartist view
...
2022-09-20 09:36:49.795 T:819 INFO <general>: creating version table
2022-09-20 09:36:49.978 T:819 INFO <general>: create bookmark table
2022-09-20 09:36:50.213 T:819 INFO <general>: create settings table
2022-09-20 09:36:50.455 T:819 INFO <general>: create stacktimes table
2022-09-20 09:36:50.655 T:819 INFO <general>: create genre table
2022-09-20 09:36:51.043 T:819 INFO <general>: create country table
2022-09-20 09:36:51.398 T:819 INFO <general>: create movie table
2022-09-20 09:36:51.569 T:819 INFO <general>: create actor table
2022-09-20 09:36:52.755 T:819 INFO <general>: create path table
2022-09-20 09:36:52.927 T:819 INFO <general>: create files table
2022-09-20 09:36:53.148 T:819 INFO <general>: create tvshow table
2022-09-20 09:36:53.513 T:819 INFO <general>: create episode table
2022-09-20 09:36:53.761 T:819 INFO <general>: create tvshowlinkpath table
2022-09-20 09:36:53.974 T:819 INFO <general>: create movielinktvshow table
2022-09-20 09:36:54.177 T:819 INFO <general>: create studio table
2022-09-20 09:36:54.550 T:819 INFO <general>: create musicvideo table
2022-09-20 09:36:54.724 T:819 INFO <general>: create streaminfo table
2022-09-20 09:36:54.946 T:819 INFO <general>: create sets table
2022-09-20 09:36:55.124 T:819 INFO <general>: create seasons table
2022-09-20 09:36:55.317 T:819 INFO <general>: create art table
2022-09-20 09:36:55.530 T:819 INFO <general>: create tag table
2022-09-20 09:36:55.934 T:819 INFO <general>: create rating table
2022-09-20 09:36:56.107 T:819 INFO <general>: create uniqueid table
2022-09-20 09:36:56.298 T:819 INFO <general>: CreateAnalytics - creating indices
2022-09-20 09:37:06.928 T:819 INFO <general>: CreateAnalytics - creating triggers
2022-09-20 09:37:07.697 T:819 INFO <general>: create episode_view
2022-09-20 09:37:07.761 T:819 INFO <general>: create tvshowcounts
2022-09-20 09:37:07.840 T:819 INFO <general>: create tvshowlinkpath_minview
2022-09-20 09:37:07.915 T:819 INFO <general>: create tvshow_view
2022-09-20 09:37:07.996 T:819 INFO <general>: create season_view
2022-09-20 09:37:08.076 T:819 INFO <general>: create musicvideo_view
2022-09-20 09:37:08.207 T:819 INFO <general>: create movie_view

Display More

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.

brendan_kearney · September 23, 2022 at 6:10 PM

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?

MacNoname · March 10, 2023 at 10:37 PM

"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!