Help with MySql external databse

  • I have used LibreElec for a number of years now and it generally works wonderfully. My biggest complaint is that with 4 boxes, the watched history is not shared between them. I'm not a fan of cloud tools and did not want to use something like trakt. I tried a number of self-hosted tracker options but could never get any of them to work. My latest attempt is to have my kodi boxes use an external mysql database. This seems like it should be simpler and easier to manage. I've tried MariaDB, mysql and am now using an LXC turnkey MySql container. The database seems fine, its listening on its network port on the standard port 3306 and other applications seem to be able to reach it just fine.

    I follow the instructions for configuring kodi. I have existing config in the advancedsettings.xml file and they seem to work fine. I added in the <videodatabase> and <musicdatabase> options in the xml file and rebooted. Username, password, port all correct yet when I scan my video source (or rather attempt to scan), nothing happens. No scanner up in the corner, nothing displaying on the Movies/TVShows menus. If I restore the advancedsettings.xml file to my old config, the scanning of my video directory works just fine. I feel like I am missing a step in the config but every instructional article/video I can find seems to indicate this is all I need to do. Can anyone suggest a good how-to on configuring libreelec kodi to use an external mysql database? As far as I can tell, my kodi instance isn't even trying to reach out to the database. Furthermore, if I manually navigate to one of my videos, the machine crashes and reboots immediately. If I go back to my old advancedsettings.xml, everything is fine again, I just, obviously have data stored in the local database.

    I'm willing to deploy a new mqsql database but I would prefer it in a Proxmox container if possible.

  • That's my advancedsettings.xml file ^ and I've never had to do more than that to have things work. MariaDB is running on a NAS box in the network. Try telnet'ing to the IP/port for the database to ensure it connects. Networking issues are not uncommon once VMs and containers are being used. Also have a look in logs for any issues; enable debugging and issues are normally quite clear.

  • Here is my specific advancedsettings.xml file:

    telnet is not installed on the container I am running MariaDB on (so that is refused), but I can ssh to it without any issue from libreelec. Its also listening on port 3306 and other applications seem to be using it fine. I turned on debugging on libreelec but I don't see any mention of mysql, my MariaDB IP or port 3306 in the logs other than the parsing of the advancedsettings.xml file. Are there other keywords to look for?

    How is one supposed to use this feature? Is the UX of libreelec supposed to be the same as it was before, just the data is stored on this external database rather than internally? When I try to "Update Library" without this external mysql setup, I see a spinner on the top right corner and it appears to walk through all my video and update all the local info (as expected). If I try to do the same thing with this mysql data present, when I click on "Update Library", as far as I can tell, nothing happens. I don't see a spinner, I don't see anything in the logs. I can view the flat file structure of my NAS video folder, but if I try to play a video, kodi crashes and re-starts. This does not reboot the entire OS, just kodi itself it appears.

    I'm not sure if it is related to my mysql problem or not but maybe this crash file will shed some light, I will attach it.

    I am using libreelec 11.0.6 on generic Intel. I noticed there is a 12.x stream started, should I try a nightly? I noticed in the 11.0.6 UI if I select the 12.x repository, it tells me 11.95.1 is the latest version. I guessing none of this is relevant and this feature has been working for a while. I feel like I am missing something very basic here but I keep finding different instructional websites and youtube videos telling me to set things up this way and it doesn't work for me.

    I am installing libreelec from scratch here on new hardware. Did not want to upset my working systems while trying to get this working. Do I need to parse my video directory into the local database before turning the external one on? Or does that not matter? I'm kind of grasping at straws here.

  • Code
    2024-03-20 20:43:28.744 T:880     error <general>: Unable to open database: MyMusic62 [1045](Access denied for user 'kodi'@'' (using password: YES))

    ^ looks fairly self-explanatory. I'd guess that you created the user and granted access from localhost but not the local subnet.

  • Is this not sufficient? As far as I can tell "ALL" grants the user all privileges other than granting other users privileges. Ideally I would grant the subnet privileges but, for now, I just want to start with seeing something work.

    Thank you for your help btw.

    MariaDB [mysql]> SHOW GRANTS FOR 'kodi'@'';
    | Grants for [email protected]                                                                                            |
    1 row in set (0.000 sec)
    MariaDB [mysql]> 

    I thought maybe there was some syntax formatting on my password that wasn't matching, however, I can log into the mariadb using the kodi user and the password I generated. I have to assume that means I configured the user correctly. I am thinking I am going to feel very foolish when I finally figure out what I am doing wrong here.

    Edited once, last by staufl: Merged a post created by staufl into this post. (March 21, 2024 at 4:21 PM).

  • Are there password requirements? I found some people saying they needed to escape certain password characters to make it work. I tried deleting every user and re-creating a user with a password of 'abc'.

    IF I create the localhost version of the new user, I can log into mysql just fine and have permissions that I set, but I try to sync from kodi and I get these failure:

    2024-03-22 20:51:19.230 T:840     error <general>: Unable to open database: MyMusic82 [1045](Access denied for user 'kodi'@'' (using password: YES))

    Not sure if it is related, but I see this warning farther up in the logs:

    2024-03-22 20:51:17.673 T:824   warning <general>: VIDEO database configuration is experimental.

    Even though this feels like a mis-config in the mariaDB, I am not sure how to go about making this any simpler. I can use this mariaDB with other applications but kodi always tells me Access is denied. Is there a way to dump the actual password into the logs for testing?

  • I finally figured out what was wrong. I have not seen this "issue" reported anywhere else so I thought I would report it here in case anyone else hits it. I was spending all my time concentrating on the mysql side of things. Turns out, the issue was on the libreelec side of things. I needed to reboot. In some instances, I needed to delete and re-create my definition of where my videos were stored. Maybe there is a simpler method? Clear Library or something? Not sure, all I know is that after I rebooted libreelec, everything was working fine for me.