•   
  • 1
  • 19
  • 20
  • 21
  • 22(current)
  • 23
  •   
[MYSQL] HOW-TO: 5 User XBMC
Is there any full setup guide that cover it all from a fully set MySQL database (already working) and Kodi 18? I mean, something beginner friendly that explain the process... I got kinda lost on the Kodi Leia "update" post.

Cheers!
Reply
(2017-02-03, 09:48)BigMong Wrote: Update: Database Version 116 [aka Leia]
this helped so much. thank you!!

for now we share 1 set of stats - content is segregated by tags: wife, kid

in our primary DB - we add/remove the kids tag using context.manageTags ( shameless plug Tongue

the 2nd-db is now safe for a 4yr old 

ex: 
CREATE ALGORITHM=MERGE DEFINER=`xbmc`@`%` SQL SECURITY DEFINER VIEW `episode` AS
  SELECT x.*
  FROM `xbmcvid116`.`episode` x
  LEFT JOIN `xbmcvid116`.`tag_link` tl
  ON ( x.idShow = tl.media_id AND tl.media_type = "tvshow" )
  WHERE tl.tag_id=2 ;
rPi 2&3 | android phones | fireHD8 | linux | win10 + NFS NAS w/ mySQL + props to...
libreElecyatse, titan, AELflexGet, context.manageTags (a zosky original)
Reply
Currently having an issue where I changed scrapper from thetvdb the themoviedb because the former was no longer scrapping (and in the past i've been told by people on this forum to move to themoviedb so decided to finally make the move). 

Everything worked fine except for two shows, Family Guy and Doctor Who, I found the error is that the c06 column is too small in the post here: https://forum.kodi.tv/showthread.php?tid=340933

I tried to run the command suggested on my tables

'ALTER TABLE Kodi_U01_Video_116.tvshow MODIFY COLUMN c06 mediumtext DEFAULT NULL NULL'
 

but it's throwing an error 

#1347 - 'Kodi_U01_Video_116.tvshow' is not of type 'BASE TABLE'

I'm guessing that because of the nature of the multiple tables setup, something isn't quite right, and I need a different command?
Reply
Apologies, ignore me, I didn't understand what a 'view' was, I ran 'ALTER TABLE Kodi_U01_Video_116.global_tvshow MODIFY COLUMN c06 mediumtext DEFAULT NULL NULL' and everything seems to be working fine Big Grin
Reply
(2020-01-25, 15:52)LikeTearsInTheRain Wrote: Apologies, ignore me, I didn't understand what a 'view' was, I ran 'ALTER TABLE Kodi_U01_Video_116.global_tvshow MODIFY COLUMN c06 mediumtext DEFAULT NULL NULL' and everything seems to be working fine Big Grin

Ahh awesome I was about to say you need to do it to the table not the view Smile

The column type that are set in this edit are the defaults.
When I get to moving to the TheMovieDB for my TV Show info I will look into this but I'm not happy with the data that come from TheMovieDB for TV Shows,
and I will need to rename a huge amount of my collection.

But this will be helpful for later thank you.
Reply
I wanted to ask, I noticed 18.7 stable has been released, is it safe to update to this client? I've disabled auto updating incase any updates might corrupt my database.
Reply
(2020-05-22, 13:21)LikeTearsInTheRain Wrote: I wanted to ask, I noticed 18.7 stable has been released, is it safe to update to this client? I've disabled auto updating incase any updates might corrupt my database.

Looking at Databases (wiki) the database is still 116 so it should be fine, I havn't updated but will when LibreELEC update.
The version of the database table don't normally change for minor updates only on major 16.x, 17.x, 18.x, etc.

I wont look at v19 - Matrix till it's in an RC build
Reply
Gotcha, thanks dude!
Reply
Wink 
(2019-02-07, 07:01)BigMong Wrote: Database Version 116 [aka Leia]
This is based on the work started by N4TH4N

Updates:
- Fixed some typos in the SQL
- New Trigger for Ratings

Todo:
 - Play with the idea to sync the Thumbnails from the main device to the rest

What does this do?
This will allow you to use the main user "Kodi_U01_Video_116" 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 Leia is "KODI_18" (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_18'@'%' 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_116
    Kodi_U02_Video_116
    ...
    Kodi_U09_Video_116
  • 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_18</user>
        <pass>some_password</pass>
        <name>Kodi_U01_Video_</name>
      </videodatabase>
    </advancedsettings>
    I also set within Libre-ELEC to wait for network on boot
  • Added some of the sql as files as I was over the 65,500 character limit.
 
  1. Create the default tables
    *** ONLY DO THIS IF UPGRADING from my last Multi-User. If doing a new setup go to Step 2 ***
    Because the tables have been changed from the defaults with views and altered columns the Upgrade ran by Kodi will fail.
    This will create all the base tables that Kodi does and Grant All Privileges to this Database.
    Kodi V116 Default Tables.sql
  2. Updating the base tables
    This will update the tables and views for more users
    Kodi V116 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_116
    Kodi V116 Creating the users.sql
  4. Updating the delete_file Trigger
    This does housekeeping if you use the clean database from Kodi or remove files.
    /*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
    /*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
    /*!40101 SET @[email protected]@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8mb4 */;

      -- TRIGGER FIX FOR MULT USERS --
      DROP TRIGGER IF EXISTS `Kodi_U01_Video_116`.`delete_file`;
      DELIMITER $$
      CREATE DEFINER=`KODI_18`@`%` TRIGGER `Kodi_U01_Video_116`.`delete_file` AFTER DELETE
      ON `Kodi_U01_Video_116`.`global_files`
      FOR EACH ROW
      BEGIN
        DELETE FROM `Kodi_U01_Video_116`.bookmark WHERE idfile = old.idfile;
        DELETE FROM `Kodi_U02_Video_116`.bookmark WHERE idfile = old.idfile;
        DELETE FROM `Kodi_U03_Video_116`.bookmark WHERE idfile = old.idfile;
        DELETE FROM `Kodi_U04_Video_116`.bookmark WHERE idfile = old.idfile;
        DELETE FROM `Kodi_U05_Video_116`.bookmark WHERE idfile = old.idfile;
        DELETE FROM `Kodi_U06_Video_116`.bookmark WHERE idfile = old.idfile;
        DELETE FROM `Kodi_U07_Video_116`.bookmark WHERE idfile = old.idfile;
        DELETE FROM `Kodi_U08_Video_116`.bookmark WHERE idfile = old.idfile;
        DELETE FROM `Kodi_U09_Video_116`.bookmark WHERE idfile = old.idfile;
        DELETE FROM `Kodi_U01_Video_116`.settings WHERE idfile = old.idfile;
        DELETE FROM `Kodi_U01_Video_116`.stacktimes WHERE idfile = old.idfile;
        DELETE FROM `Kodi_U01_Video_116`.streamdetails WHERE idfile = old.idfile;
      END$$
      DELIMITER ;

    /*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
    /*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
    /*!40101 SET [email protected]_COLLATION_CONNECTION */;
  5. Copying over the old watched status (If Step 1 was used or the Database naming format has changed)
    Would recommend using a service like https://trakt.tv/ for keeping this information in sync
    Update the _U01 with the user you wish to copy over.
    This may timeout so you can run more then once and it wont update the same information more then once.
    SET SQL_SAFE_UPDATES = 0;
    UPDATE Kodi_U01_Video_116.files AS NEWTBL
      INNER JOIN Kodi_U01_Video_107.files AS OLDTBL ON NEWTBL.strFilename = OLDTBL.strFilename 
    SET 
      NEWTBL.lastPlayed = OLDTBL.lastPlayed,
      NEWTBL.playCount = OLDTBL.playCount
    WHERE
      NEWTBL.strFilename = OLDTBL.strFilename AND OLDTBL.lastPlayed IS NOT NULL AND NEWTBL.playCount IS NULL;
    SET SQL_SAFE_UPDATES = 1;
  6. Show/Season average rating
    I'm the only one that likes to rate things in my setup so this is build for just me.
    Can add more user by duplicating the If Statement and updating the _U01 keep the updates point to the main U01 table tho.
    ** I have always felt rating a Show/Season is always wrong as it should be an average of the episodes, that is what is done here **
      DROP TRIGGER IF EXISTS `Kodi_U01_Video_116`.`update_rating`;
      DELIMITER $$
      CREATE DEFINER=`KODI_18`@`%` TRIGGER `Kodi_U01_Video_116`.`update_rating` AFTER UPDATE
      ON `Kodi_U01_Video_116`.`global_episode`
      FOR EACH ROW
      BEGIN
        IF (new.userrating_U01 <> old.userrating_U01) THEN
          UPDATE `Kodi_U01_Video_116`.global_tvshow SET userrating_U01 = (SELECT ROUND(AVG(userrating_U01)) FROM global_episode WHERE userrating_U01 IS NOT NULL AND idShow=old.idShow) WHERE idShow=old.idShow;
          UPDATE `Kodi_U01_Video_116`.global_seasons SET userrating_U01 = (SELECT ROUND(AVG(userrating_U01)) FROM global_episode WHERE userrating_U01 IS NOT NULL AND idShow=old.idShow AND idSeason=old.idSeason) WHERE idShow=old.idShow AND idSeason=old.idSeason;
        END IF;
      END$$
      DELIMITER ;
The amount of times i have referred back to this post is crazy. I should have broken my installs so much.  Confused

Thanks as always Mong
Reply
This post (and the previous posts) are a life saver!
Poking through a bunch of pages and replies I'm still not totally sure/or clear if its possible to accomplish this but I thought I would post first / ask for forgiveness later Rolleyes

For my home use this is absolutely perfect since all adults, everyone wants all the content stored locally, but my parents house where it gets a bit tricky. I love the scrape once & set artwork once & everyone is updated and each user has their own watch status. But what if certain users only want to see specific content? I have multiple shares that break my media up (movies, tv, anime-movies, anime-tv, kids, doc-movies, doc-tv...) Now my dad is very picky about what he wants to watch so he only wants (movies, tv), my mother loves my documentary collection so (movies, tv, doc-movies, doc-tv), and for my nieces and nephews who come over (kids, movies, tv, anime-movies, anime-tv) and for this I'm thinking a simple smart playlist that restricts content based on rating will get that job done for the kids profile.

Right now my thought is at least 2 MySQL docker containers and I will have to scrape the content twice. Which isn't a huge killer except for the fact that I go through and set a TON of custom artwork and fan art which can get very tedious. Any insight would be hugely appreciated.
Reply
(2021-01-27, 23:15)justenoughlinux Wrote: This post (and the previous posts) are a life saver!
Poking through a bunch of pages and replies I'm still not totally sure/or clear if its possible to accomplish this but I thought I would post first / ask for forgiveness later Rolleyes

For my home use this is absolutely perfect since all adults, everyone wants all the content stored locally, but my parents house where it gets a bit tricky. I love the scrape once & set artwork once & everyone is updated and each user has their own watch status. But what if certain users only want to see specific content? I have multiple shares that break my media up (movies, tv, anime-movies, anime-tv, kids, doc-movies, doc-tv...) Now my dad is very picky about what he wants to watch so he only wants (movies, tv), my mother loves my documentary collection so (movies, tv, doc-movies, doc-tv), and for my nieces and nephews who come over (kids, movies, tv, anime-movies, anime-tv) and for this I'm thinking a simple smart playlist that restricts content based on rating will get that job done for the kids profile.

Right now my thought is at least 2 MySQL docker containers and I will have to scrape the content twice. Which isn't a huge killer except for the fact that I go through and set a TON of custom artwork and fan art which can get very tedious. Any insight would be hugely appreciated.

What I do is just use the sources.xml in the profile to give each one of my users access to what they need.  I have a very granular directory structure though, so that's easiest for me.
Reply
(2021-01-28, 00:20)OtheA Wrote:
(2021-01-27, 23:15)justenoughlinux Wrote: This post (and the previous posts) are a life saver!
Poking through a bunch of pages and replies I'm still not totally sure/or clear if its possible to accomplish this but I thought I would post first / ask for forgiveness later Rolleyes

For my home use this is absolutely perfect since all adults, everyone wants all the content stored locally, but my parents house where it gets a bit tricky. I love the scrape once & set artwork once & everyone is updated and each user has their own watch status. But what if certain users only want to see specific content? I have multiple shares that break my media up (movies, tv, anime-movies, anime-tv, kids, doc-movies, doc-tv...) Now my dad is very picky about what he wants to watch so he only wants (movies, tv), my mother loves my documentary collection so (movies, tv, doc-movies, doc-tv), and for my nieces and nephews who come over (kids, movies, tv, anime-movies, anime-tv) and for this I'm thinking a simple smart playlist that restricts content based on rating will get that job done for the kids profile.

Right now my thought is at least 2 MySQL docker containers and I will have to scrape the content twice. Which isn't a huge killer except for the fact that I go through and set a TON of custom artwork and fan art which can get very tedious. Any insight would be hugely appreciated.

What I do is just use the sources.xml in the profile to give each one of my users access to what they need.  I have a very granular directory structure though, so that's easiest for me.

Hmmm did some testing and that doesn't work.

If you setup a multi-user database following this guide, every user "shares" the view created by your master profile.

1. create user & login to generate directory structure
2. push advancedsettings.xml that has your MySQL credentials as well as the
<name>Kodi_U03_Video_</name> 
tag .
3. login as user shows the identical content that I have created from the master profile just with separate watched statuses.
4. push the "sources.xml" file to the users directory, log out/in and I actually get new behaviour here. An empty structure that requires me to scrape all the media again.

Was this what you were getting at?
Reply
(2021-01-29, 15:55)justenoughlinux Wrote:
(2021-01-28, 00:20)OtheA Wrote:
(2021-01-27, 23:15)justenoughlinux Wrote: This post (and the previous posts) are a life saver!
Poking through a bunch of pages and replies I'm still not totally sure/or clear if its possible to accomplish this but I thought I would post first / ask for forgiveness later Rolleyes

For my home use this is absolutely perfect since all adults, everyone wants all the content stored locally, but my parents house where it gets a bit tricky. I love the scrape once & set artwork once & everyone is updated and each user has their own watch status. But what if certain users only want to see specific content? I have multiple shares that break my media up (movies, tv, anime-movies, anime-tv, kids, doc-movies, doc-tv...) Now my dad is very picky about what he wants to watch so he only wants (movies, tv), my mother loves my documentary collection so (movies, tv, doc-movies, doc-tv), and for my nieces and nephews who come over (kids, movies, tv, anime-movies, anime-tv) and for this I'm thinking a simple smart playlist that restricts content based on rating will get that job done for the kids profile.

Right now my thought is at least 2 MySQL docker containers and I will have to scrape the content twice. Which isn't a huge killer except for the fact that I go through and set a TON of custom artwork and fan art which can get very tedious. Any insight would be hugely appreciated.

What I do is just use the sources.xml in the profile to give each one of my users access to what they need.  I have a very granular directory structure though, so that's easiest for me.

Hmmm did some testing and that doesn't work.

If you setup a multi-user database following this guide, every user "shares" the view created by your master profile.

1. create user & login to generate directory structure
2. push advancedsettings.xml that has your MySQL credentials as well as the
<name>Kodi_U03_Video_</name> 
tag .
3. login as user shows the identical content that I have created from the master profile just with separate watched statuses.
4. push the "sources.xml" file to the users directory, log out/in and I actually get new behaviour here. An empty structure that requires me to scrape all the media again.

Was this what you were getting at?
I can't recall.  I set it up a while ago.  That's just how I limited my kids from watching rated "R" movies, etc.
Reply
Do I take it that simply changing 116 for 119 and KODI_18 for KODI_19 in the .sql files, with an appropriately named user and database in mariadb won't do the job?  I got a lot of SQL syntax errors in the output when i tried this, and so assume there have been too many changes between 116 and 119 for this to work?
Reply
My Problems seems nearly the same.

After Upgrade it's not possible to connect to my database.
And every changing at the folder typ to "Movies" is not saving. 

Is it possible to get back? Form Multiuser to Single-User?

--------------------------------------------------------------------------

Hello,

after Upgrade to Matrix i'm unable to connect to my database. 

- advancedsettings.xml / SQL Database / SMB Setting

Is there any change? 
I can't change the settings for the path to "movie", although the libary won't update.
(When i kill the file it works)

Notice
i use multiuser


<advancedsettings>
    <samba>
        <clienttimeout>30</clienttimeout>
    </samba>
    <videolibrary>
        <recentlyaddeditems>50</recentlyaddeditems>
    </videolibrary>
    <videodatabase>
        <type>mysql</type>
        <host>QNAP-2Bay</host>
        <port>3306</port>
        <user>KODI_18</user>
        <pass>XXXXXXXXX</pass>
        <name>Kodi_U01_Video_</name>
    </videodatabase>
    <pathsubstitution>
        <substitute>
            <from>special://masterprofile/Thumbnails</from>
            <to>smb://KODI_18:[email protected]/Filme/_Thumbnails</to>
        </substitute>
    </pathsubstitution>
</advancedsettings>
Reply
  •   
  • 1
  • 19
  • 20
  • 21
  • 22(current)
  • 23
  •   



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