•   
  • 1
  • 21
  • 22
  • 23
  • 24
  • 25(current)
[MYSQL] HOW-TO: 5 User XBMC
(2021-04-13, 21:15)BigMong Wrote: Database Version 119 [aka Matrix]

This has been tested with MySQL Server Version 8.X I don't know if this will (it should) work with lower versions.

Please Note:
Lower Versions or MariaDB 10 you will need to change the Character Set and Collate.
Within each file do a find and replace in this order;
utf8mb4_0900_ai_ci  ->  utf8_general_ci
utf8mb4  ->  utf8


What does this do?
This will allow you to use the main user "Kodi_U01_Video_119" to add shows and movies to your collection and let the other users see this data as its added. (only need to sync once for everyone to get the new items)
Also this keeps the play counts, last played, and ratings separate for each user

Some things to start off with.
  • The MySQL user i'm using for Matrix is "KODI_19" (I change this with each major version) and this user I don't give access to anything (as I do other things with this server)
    CREATE USER 'KODI_19'@'%' IDENTIFIED BY 'password';
    Change password to what you want to use.
  • I have 9 users profiles on this set up, you can remove the unneeded, leave them or even add more.
    Naming for my Databases:
    Kodi_U01_Video_119
    Kodi_U02_Video_119
    ...
    Kodi_U09_Video_119
  • You should update the advancedsettings.xml file and reboot Kodi after the steps below are done.
    My advancedsettings.xml as a reference
    <advancedsettings>
      <samba>
        <clienttimeout>30</clienttimeout>  <!-- timeout (in seconds) -->
      </samba>
      <videolibrary>
        <recentlyaddeditems>50</recentlyaddeditems> <!-- number of recently added items. Defaults to 25 -->
      </videolibrary>
      <videodatabase>
        <type>mysql</type>
        <host>X.X.X.X</host> <!-- Use IP here as its faster then DNS -->
        <port>3306</port>
        <user>KODI_19</user>
        <pass>some_password</pass>
        <name>Kodi_U01_Video_</name> <!-- Change the U01 to the one you want -->
      </videodatabase>
    </advancedsettings>
    I also set within Libre-ELEC to wait for network on boot
  • Added the sql files to Github.
  1. Create the default tables
    If you have already got a Multi-User setup then start here as Kodi will fail to update the database
    This SQL will create all the tables and views that Kodi does.
    It also has a small fix in the "tvshow" table column "C06" as I have found scraping shows with lots of fan art fails on the field size eg. Family Guy
    Kodi V119 - 01 Default Tables.sql
  2. Updating the base tables
    This will change the tables and make views so you can have more users.
    Kodi V119 - 02 Update Base Tables.sql
  3. Creating the users
    Run this for each user with doing a find and replace for _U02 to _U03 etc, this is set to use the main Database of Kodi_U01_Video_119.
    Kodi V119 - 03 Creating the Users.sql
  4. Finalizing
    This is optional but I do recommend doing the first part of it as it cleans the bookmarks when you remove files.
    Kodi V119 - 04 Finalizing.sql
  5. Current watched status
    You should be using https://trakt.tv/ but if you have python on your Kodi box you can use the tool from https://github.com/MilhouseVH/texturecache.py
    I used this the other day to move Kodi 14 to Kodi 19 as trakt didn't support that version anymore.
    Backup:
    Code:
    ./texturecache.py watched movies backup movies.dat
    ./texturecache.py watched tvshows backup tvshows.dat
    Restore:
    Code:
    ./texturecache.py watched movies restore movies.dat
    ./texturecache.py watched tvshows restore tvshows.dat
    the .dat files can be open in a text editor if you like to see what it exports.
Let me know if you have any problems or questions with this.
Hi BigMong
I followed this pretty much to the letter I'm sure I did. I'm running MariaDB on my NAS so I made those changes to the scripts first. Everything seems to be working for my 2 profiles. Adult for me with 2 sources (mature films & kids movies) and a kids profile with just 1 source (kids movies). Same for TV. Adult and kids tv sources. The only thing I can spot that's not working right is recently watched TV shows on the kids profile. It works fine in the adult profile. Oh and recently watched movies works fine even in the kids profile. It's not a deal breaker but I wonder why just that one thing is not working as it should. 
Many thanks for this great bit of work. I must admit it seemed a bit daunting at first but once I read it couple of times and took my time it all worked out. You must let me buy you a beer or a coffee
Nvidia Shield (tube) | Kodi 19.3 | Sony STR-DN1080 amp in 7.2 | Asustor NAS 11TB raid 5 | Epson EH-TW7000 4k projector | 92" retractable screen | Canton CX 80 speakers
Reply
@BigMong Sorry to bump this post but I realised I didn't actually tag you in my last post. Was wondering if you have any input on this?
Nvidia Shield (tube) | Kodi 19.3 | Sony STR-DN1080 amp in 7.2 | Asustor NAS 11TB raid 5 | Epson EH-TW7000 4k projector | 92" retractable screen | Canton CX 80 speakers
Reply
I am building a new machine. What would be the easiest way of setting this up brand new using 19.4. I'd rather start from anew versus transferring my current setup to the new machine. Windows 10. I don't need a step by step guide, just a quick rundown of catching up to your current post for Matrix without having to upgrade for each version. I have my full library of movies and tv shows scraped with EMM, and do not use trakt for watched status as all viewing is done on the local network and not streamed via the internet.
Reply
(2022-07-17, 07:51)valerinism Wrote: I am building a new machine. What would be the easiest way of setting this up brand new using 19.4. I'd rather start from anew versus transferring my current setup to the new machine. Windows 10. I don't need a step by step guide, just a quick rundown of catching up to your current post for Matrix without having to upgrade for each version. I have my full library of movies and tv shows scraped with EMM, and do not use trakt for watched status as all viewing is done on the local network and not streamed via the internet.

You just need to follow that post I copied in my reply a couple of posts above this one.
Nvidia Shield (tube) | Kodi 19.3 | Sony STR-DN1080 amp in 7.2 | Asustor NAS 11TB raid 5 | Epson EH-TW7000 4k projector | 92" retractable screen | Canton CX 80 speakers
Reply
I have been using kodi (osmc) for many years now and just recently started playing with using mysql instead of individual databases on each.  Then I happened upon this beauty of work which made it even better with separate watched status for other family members.  Thanks to all who have contributed.  

The one issue so far that has been a bit of a pain, was that other users who share the database could hose the library if their pi was to lose a mount and attempt cleanup (they selected the default 'delete' option when the prompt showed).

Not sure if this has been mentioned in the previous posts, but the way I got around this was to create a read-only mysql user for those users' pis, and grant only SELECT access to all tables in their db, except granting ALL access to both the bookmark and files tables.  Been using this for a few weeks now without issues for the most part.  They can still have their own watched status and get regular updates when i update from my end.  One small issue is if a read-only user attempts to refresh certain items, OSMC will die with a sadface, but thats minimal and I just tell them to stop trying to update.

Has anyone else been plagued with other users hosing their library like this?  If so, just wondering how they approached preventing it.
Reply
  •   
  • 1
  • 21
  • 22
  • 23
  • 24
  • 25(current)



Logout Mark Read Team Forum Stats Members Help
[MYSQL] HOW-TO: 5 User XBMC5
This forum uses Lukasz Tkacz MyBB addons.