•   
  • 1
  • 18
  • 19
  • 20
  • 21
  • 22(current)
[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
  •   
  • 1
  • 18
  • 19
  • 20
  • 21
  • 22(current)



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