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
    general_log_file        = /storage/.kodi/userdata/addon_data/service.mariadb/mysql.log
general_log             = 1

    start/stop/restart the mariadb service

    Code
    systemctl stop service.mariadb
systemctl start service.mariadb
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
    mysql -u kodi -p -h <real ip address of the server>

    I assume user kodi@localhost 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.

    • Official Post
    Quote from vpeter

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

    Quote from vpeter

    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.

    • Official Post

    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.
    • Official Post

    Found the culprit so far with MariaDB 10.3.12:

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

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

    Edited once, last by JimmySmith (January 18, 2019 at 7:58 PM).

    Quote from Klojum
    Code
    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.

    • Official Post

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