MariaDB mysql server addon

  • I tried with this in /storage/.kodi/userdata/addon_data/service.mariadb/my.cnf and I got log file with bunch of lines from Kodi.

    Code
    1. general_log_file = /storage/.kodi/userdata/addon_data/service.mariadb/mysql.log
    2. general_log = 1


    start/stop/restart the mariadb service

    Code
    1. systemctl stop service.mariadb
    2. systemctl start service.mariadb
    3. systemctl restart service.mariadb


    Kodi user can't connect on localhost but only on real ip address. That's why you need to use -h parameter

    Code
    1. mysql -u kodi -p -h <real ip address of the server>


    I assume user [email protected] doesn't even exist according to what is created: LibreELEC.tv/mariadb.start at master · LibreELEC/LibreELEC.tv · GitHub


    I'm not mysql user so something can also be wrongly implemented.

    Done giving support here because of last events.

  • /storage/.kodi/userdata/addon_data/service.mariadb/mysql.log

    This is indeed created now, so probably addons have no write permissions outside their own addon-data folder?

    The "service." prefix also did indeed do the trick.


    Kodi user can't connect on localhost but only on real ip address. That's why you need to use -h parameter

    I don't recall having to do that with MySQL. It's possibly a feature on later releases, that also got into MariaDB.

    It's just something I stumbled onto when mistyping my passwords. But, back to the real work now.

  • I've stopped the testing of the MariaDB add-on, as my SSD is not as cooperative as I had hoped. It's possibly suffering from drive errors which results in unexpected behavior. Not something you want with a server application.


    So for now it's back to Ubuntu + MySQL.

  • vpeter FYI, I just installed the MariaDB server add-on a Generic PC setup, and all is well for one thing again: the query for the "Recently added episodes" takes some 20 seconds to display their icon thumbs on Kodi's main screen. :-|


    Is it possible to update the server MariaDB add-on to v10.3.12? Who knows it'll fix it. Right now, Team Kodi is not in the happiest of moods of keeping up SQL db support.


    EDIT: CvH just started compiling the updated version.

  • Klojum, did you start with the fresh/clean content and db/schema? I am using now maria 10.1.37 and didnt notice anything wrong..Thumbnails shows just fine for TV Shows / Movies.


    If you talking about Recent episodes thumbnails on the main screen,

    • I am using service skin widgets and improved recent function on Confluence (Estuary is bad and downgrade for non-tablet/mobile devices) on Kodi though (which removes watched from recents), but I doubt it has any impact on SQL queries.
    • I have scheduled job for texturecache.py c which does caching thumbnails.

    6x Odroid C2 + CoreELEC 9.0.1; 6x RPi2 + LibreELEC 9.0.1; 3x Win7 Kodi 18.1 + vPeter's mariaDB plugin as Library DB

  • did you start with the fresh/clean content and db/schema?

    Fresh? Of course not (yet)... haha. I'm in IT, therefore I'm lazy by default. ;-)


    I am using a SQL dump of v116 MySQL 5.5.x into MariaDB now.

    If all else fails, I can do a restore/rescraping of the video collection.

  • You can try it with clean, scrape some tvshow and you will see (as I did when move from MySQL to MariaDB). I personally think that it has nothing to do with MariaDB version :)

    6x Odroid C2 + CoreELEC 9.0.1; 6x RPi2 + LibreELEC 9.0.1; 3x Win7 Kodi 18.1 + vPeter's mariaDB plugin as Library DB

  • Found the culprit so far with MariaDB 10.3.12:

    Code
    1. 12:11:59.127 T:140109139670784 DEBUG: RunQuery took 39317 ms for 6538 items query: select * from episode_view WHERE ((episode_view.dateAdded > '2012-01-01')) AND ((episode_view.playCount IS NULL OR episode_view.playCount < 1))

    If this is to blame on MySQL Workbench for doing the export/import MariaDB, we'll find out after rescraping into a fresh/clean database. I have to do a updated video library export first.

  • Is it possible to update the server MariaDB add-on to v10.3.12? Who knows it'll fix it. Right now, Team Kodi is not in the happiest of moods of keeping up SQL db support.

    Will do with few extra small changes.

    Done giving support here because of last events.

  • vpeter: FYI, I've listed the server settings for MySQL and MariaDB. Perhaps you notice something odd? Normally when a larger query runs slow, there may be a too small caching problem. But that's about the only thing that I can think about.


    MySQL: hastebin

    MariaDB: hastebin

  • I checked mysql vs mariadb Peter's configs before (when mariadb plugin was created) and they looked fine to me (I mean, thigns defined on Mysql and working years for me was present also on mariadb config).. Also I gladly noted that binary loggin is disabled by default..

    6x Odroid C2 + CoreELEC 9.0.1; 6x RPi2 + LibreELEC 9.0.1; 3x Win7 Kodi 18.1 + vPeter's mariaDB plugin as Library DB

    Edited once, last by JimmySmith ().

  • I just recreated the MyVideos116 on MariaDB 10.3.12 server, and have Kodi 18 rc5.2 (LibreELEC beta3) rescrape the video collection, but the query on the episodes_view remains slow: some 30 seconds... :cry:

  • Code
    1. 12:11:59.127 T:140109139670784 DEBUG: RunQuery took 39317 ms for 6538 items query: select * from episode_view WHERE ((episode_view.dateAdded > '2012-01-01')) AND ((episode_view.playCount IS NULL OR episode_view.playCount < 1))

    Can you run this from mysql itself? mysql -u root -p


    I don't have any database that's why I copy/paste entries on exported library to get more episodes. And from mysql I get

    Quote

    use MyVideos116;

    select * from episode_view WHERE ((episode_view.dateAdded > '2012-01-01')) AND ((episode_view.playCount IS NULL OR episode_view.playCount < 1));



    14816 rows in set (0.193 sec)

    Listing them on terminal takes around 7 seconds. using LibreELEC (Milhouse): devel-20190118210257-#0118-ge23e672 (Generic.x86_64) and MariaDB addon 10.3.11.

    Done giving support here because of last events.

  • not much data in my case

    Code
    1. 33 row(s) returned
    2. Execution Time : 0.008 sec
    3. Transfer Time : 0.002 sec
    4. Total Time : 0.011 sec

    6x Odroid C2 + CoreELEC 9.0.1; 6x RPi2 + LibreELEC 9.0.1; 3x Win7 Kodi 18.1 + vPeter's mariaDB plugin as Library DB

  • I think I've found it. I installed MariaDB on a Ubuntu 18.04.1 machine, and doubled the cache settings.

    Updated the /etc/mysql/mariadb.conf.d/50-server.cnf file with:


    # * Query Cache Configuration

    query_cache_limit = 2M (was 1)

    query_cache_size = 32M (was 16)


    The episode_view query now raps it up in 0,223 secs for 9320 records from a remote MySQL client.


    I'm gonna try to change settings on LibreELEC's MariaDB server now. :)

  • Today is slow to me to. Strange.


    Update: And now I can't even import db :(

    Done giving support here because of last events.

  • Importing went okay here via MySQL Workbench. I noticed that the LE MariaDB serve has a initial value for query_cache_limit of 128K ? Perhaps try 256K next. Although the MariaDB server default setting in Ubuntu 18.04.1 for query_cache_limit is 1MB.

  • I got system back and still long time for even simple select * from episode_view. No idea why. It should not be working like that.

    Done giving support here because of last events.