•   
  • 1
  • 19
  • 20
  • 21
  • 22
  • 23(current)
[MYSQL] HOW-TO: 5 User XBMC
(2021-04-08, 19:40)Supay Wrote: 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?

Think I answered this myself as the syntax errors also state the version may be incorrect.  However it has highlighted another issue that may affect others.

After my attempts to bodge a multi user database failed, I decided to just go with multiple databases, one for each user.  I use Trakt to sync watched information as well anyway so can always go back to multi-user system later.  However, as I have used the format for the database name "Kodi_U01_Video_116" I decided to just stick with that for the Kodi v19 setup and in advancedsettings.xml added "Kodi_U01_Video_" as the name.  On rebooting Kodi, it started up fine and in mariadb I could see that a database had been created named "Kodi_U01_Video_119", exactly as expected, and showing the default tables within it.

On trying to scrape my NFS sources, it started running and seemed to read through the NFS share fine but then stopped with nothing showing up.  None of the movie items showed up in Kodi as identified.  I checked the Kodi logs and can see that it is reading the NFS share fine and recognising the individual folders and files.  However, the log had repeated errors regarding Kodi_U01_Video_119 as not existing.  I checked inside my mariadb container and could see the database existed there and the tables within it, and Kodi had only just created this anyway so it obviously had access to do this.  I confirmed permissions and the user had them, tried granting permissions across the board but still the same.  I deleted the database and tried this fresh a number of times, with the same results.

Now, the music NFS share worked perfectly.  That was just named james_music82 and scraped with no issue.  So I tried dropping the "Kodi_U01_Video_119" database again and instead setting advancedsettings.xml to use "james_video_" as the name instead.  In mariadb I saw it create "james_video_119" and on scraping my NFS share the content immediately started adding to the database with no errors this time.  I stopped it and dropped the database again, rebooted, and watched it create again and scrape fine again.  I dropped that database again, changed advancedsettings.xml to "u01_video_" and watched mariadb create "u01_video_119" and this scrape absolutely fine.  Dropped it and tried all over again, worked perfectly again.

I have very little knowledge of mysql/mariadb but something seems to have changed with Kodi v19 that makes it very unhappy with the format "Kodi_U01_Video_119" as a database name.  It can create the database but then seems unable to use it and so all scraping fails.  Hope this helps stop others banging their heads against it as the move to v19/119 goes ahead.
Reply
(2021-04-09, 13:59)Knallex Wrote: 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?

Check my latest post #331

I first wouldn't advise upgrading Kodi to any new version if a database upgrade is not ready for multi-user here as Kodi can't recognise the changed database tables and so cannot upgrade to the new version or read the older database.  So you will either need to downgrade Kodi to v18 and continue on that using your current database until a migration option is ready.  Or create a fresh 119 database as normal without any of this and re-scrape.  That is why I also advise using Trakt so you can port your watched information over to a new setup without worrying about your mysql migration.

However, if my last post is correct and not just me being a plum, then 119 has an issue with the old database name format and will break anyway but we will likely need BigMong to confirm that.
Reply
@Supay 
When the numbers change on the DB it means something has changed Smile
I haven't finished getting the new version working yet only spent like 30 mins looking at it so far. as I have found a problem with the TMDB scrapper for TVShows when it comes to eg. "Family Guy"
Since I use a lot of RPIs with my setup I can only test with one of my PIs as LibreELEC is only has a BETA for RPI4. 2/3 will come later and 1's are EOL (already retired them)

I will try and get it sorted this week depending on RL Big Grin

If you wish to use the new Kodi 19.0 aka Matrix you will need to start fresh without Multi-user for now.
Trakt is awesome for that then you can refresh media info on new import (am looking at doing that and move to TMDB from TVDB) then bring back your watched.

@Knallex 
In your advancedsettings.xml I see you are hosting your Thumbnails on your 2bay Nas, how do you find the Lag/Delay?
and Yes you can convert from Multi-user to single user but you will only keep the data from the first user eg U01.
I haven't done this but it can be done.
Reply
(2021-04-10, 09:26)BigMong Wrote: @Knallex 
In your advancedsettings.xml I see you are hosting your Thumbnails on your 2bay Nas, how do you find the Lag/Delay?
and Yes you can convert from Multi-user to single user but you will only keep the data from the first user eg U01.
I haven't done this but it can be done.

Yes, there is an Lag/Delay. 
But usually it not important for me. Most time i use only the last added items and this works fine. 
I'm hosting the Thumbnails on my qnap, because of several Fire TV Sticks at Home. (so the sticks are not full of many content)
Reply
@BigMong
Okay. Thanks. 
I try to set up a fresh installation with single user. 
Actually i#m not really into mysql script. Could i copy the table and put it back after the new installation?
Reply
(2021-04-10, 09:26)BigMong Wrote: @Supay 
When the numbers change on the DB it means something has changed Smile
I haven't finished getting the new version working yet only spent like 30 mins looking at it so far. as I have found a problem with the TMDB scrapper for TVShows when it comes to eg. "Family Guy"
Since I use a lot of RPIs with my setup I can only test with one of my PIs as LibreELEC is only has a BETA for RPI4. 2/3 will come later and 1's are EOL (already retired them)

I will try and get it sorted this week depending on RL Big Grin

That is fine and I wasn't chasing you. No need to rush for us, I just appreciate you doing this and providing it to us. I use multiple Pi4s with LibreElec, they work very well. I hope the information on the database naming helps. I don't think I did anything wrong, as I only setup Kodi 19 in a standard single database setup but using your naming convention for it, and Kodi 19 just didn't work with that naming format for me.
Reply
Oh, and if you need testing done, then I have 4 LibreElec Pi4s running Kodi 19 and a desktop running Kodi 19 with 6 users. Happy to test anything you need to confirm it works on another person's setup.
Reply
Exclamation 
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.
Reply
(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.

Let me know if you have any problems or questions with this.

I believe I have identified an issue.  I am receiving a syntax error warning when I run the .sql files.  However, I note that you have referenced using MySQL 8.X and I have till now been using MariaDB as that is the commonly available option for docker containers and it is what I run on my Pi Kubernetes cluster.  I don't know much about the differences between them, but I have read that there are changes in MySQL 8 that have potentially diverged the two projects now after a long time of retaining compatibility, and the MariaDB pages also do not show current compatibility with MySQL 8.  I understand this is likely an issue for those choosing to use MariaDB if you build this for MySQL, but it may generate further posts here if there is a compatibility issue now.  You might want to add a clear header to advise if MariaDB is not supported and that people need to use MySQL only now.
Reply
(2021-04-14, 10:34)Supay Wrote:
(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.

Let me know if you have any problems or questions with this.

I believe I have identified an issue.  I am receiving a syntax error warning when I run the .sql files.  However, I note that you have referenced using MySQL 8.X and I have till now been using MariaDB as that is the commonly available option for docker containers and it is what I run on my Pi Kubernetes cluster.  I don't know much about the differences between them, but I have read that there are changes in MySQL 8 that have potentially diverged the two projects now after a long time of retaining compatibility, and the MariaDB pages also do not show current compatibility with MySQL 8.  I understand this is likely an issue for those choosing to use MariaDB if you build this for MySQL, but it may generate further posts here if there is a compatibility issue now.  You might want to add a clear header to advise if MariaDB is not supported and that people need to use MySQL only now.

Oh shoot, didn't even think about that, I will get MariaDB install on my NAS and do some testing.
Reply
(2021-04-14, 13:56)BigMong Wrote:
(2021-04-14, 10:34)Supay Wrote:
(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.

Let me know if you have any problems or questions with this.

I believe I have identified an issue.  I am receiving a syntax error warning when I run the .sql files.  However, I note that you have referenced using MySQL 8.X and I have till now been using MariaDB as that is the commonly available option for docker containers and it is what I run on my Pi Kubernetes cluster.  I don't know much about the differences between them, but I have read that there are changes in MySQL 8 that have potentially diverged the two projects now after a long time of retaining compatibility, and the MariaDB pages also do not show current compatibility with MySQL 8.  I understand this is likely an issue for those choosing to use MariaDB if you build this for MySQL, but it may generate further posts here if there is a compatibility issue now.  You might want to add a clear header to advise if MariaDB is not supported and that people need to use MySQL only now.

Oh shoot, didn't even think about that, I will get MariaDB install on my NAS and do some testing.

Thank you, BigMong! If you have a beer fund donation anywhere, let me know as you deserve it.
Reply
(2021-04-14, 13:59)Supay Wrote:
(2021-04-14, 13:56)BigMong Wrote:
(2021-04-14, 10:34)Supay Wrote: I believe I have identified an issue.  I am receiving a syntax error warning when I run the .sql files.  However, I note that you have referenced using MySQL 8.X and I have till now been using MariaDB as that is the commonly available option for docker containers and it is what I run on my Pi Kubernetes cluster.  I don't know much about the differences between them, but I have read that there are changes in MySQL 8 that have potentially diverged the two projects now after a long time of retaining compatibility, and the MariaDB pages also do not show current compatibility with MySQL 8.  I understand this is likely an issue for those choosing to use MariaDB if you build this for MySQL, but it may generate further posts here if there is a compatibility issue now.  You might want to add a clear header to advise if MariaDB is not supported and that people need to use MySQL only now.

Oh shoot, didn't even think about that, I will get MariaDB install on my NAS and do some testing.

Thank you, BigMong! If you have a beer fund donation anywhere, let me know as you deserve it.

After my last post I was like Ha! bet it's a Char Set stuff Smile
I have update my main post about it as below.

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


Now I didn't ask for a error you was getting but that's the error I got on MariaDB 10 till I update the Char Set and Collate, if your getting something else please let me know

If you like to know a little more about the change take a look at this. It's not some dev blog (but that is linked within the post) so it's easier to understand
https://www.monolune.com/what-is-the-utf...collation/
Reply
(2021-04-14, 19:02)BigMong Wrote:
(2021-04-14, 13:59)Supay Wrote:
(2021-04-14, 13:56)BigMong Wrote: Oh shoot, didn't even think about that, I will get MariaDB install on my NAS and do some testing.

Thank you, BigMong! If you have a beer fund donation anywhere, let me know as you deserve it.

After my last post I was like Ha! bet it's a Char Set stuff Smile
I have update my main post about it as below.

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


Now I didn't ask for a error you was getting but that's the error I got on MariaDB 10 till I update the Char Set and Collate, if your getting something else please let me know

If you like to know a little more about the change take a look at this. It's not some dev blog (but that is linked within the post) so it's easier to understand
https://www.monolune.com/what-is-the-utf...collation/

Thank you for this. I really appreciate this. I wish setups such as yours were possible within Kodi itself so we didn't have to bother you with requests like this but I feel you may be our go to for as long as you are happy to keep sharing. My database setup is in mariadb now and happily scraping. Will confirm the multi user parts are all working once done but looks good so far.
Reply
Dear all,

giving my piece to the mustard...

For now I have a user count of 6 on about 9 or 10 devices, I lost count somewhere ... wating for cryouts Tongue
I use a main Station which has all accounts (PC).

After many tryouts and headaches and scratching I chose to have each user completly separate.
Essentially it takes more space in terms of database space which I find negligable.

To Switch to v19 I used the PC (which has shows by default the login prompt) to create the new DBs.
It took about 10-15min for each (profile change) to be created (much faster than doing it via Raspberry).
(I use Tiny Media Manager for scraping into .nfo, and setup KODI to only use local .nfo as the Scrapers create more trouble with wrong data scraping.
I evaluated this in time spent and remain with the fact that with TMM I scrape what is right than fixing automated wrong data.)

I run on that machine the basic version of Kodi (with only a Weather addon, basic Skin).
Each other device upon install had the most likely profiles created along and a default User Login chosen.

The Master User is not used although I added the DB for it as fallback.
Via SMB I copied the advancedsettings.xml, favorites.xml and sources.xml for each user (after the user creation).

Oddly not all devices (after 1 reboot) accessed/showed the Movies/TV Shows/Music Videos/ Music DB, independent of OS (PC/RPI/Android).

An Edit source with OKing proved unsuccessful either(including reboot), but about 3 Reboots later of each device did the trick !?!?!
(Keeping in mind that on the main station PC used for copying the DBs it worked all the time !?!?!)

Patience young Padawan Wink

So a few reboots later and changing the profiles it all worked fine (unsure if it might be a server issue which I could not detect).

Only now would be the time to add Add-ons for v19, as I found out that the latest version became more sensible to Add-ons.
Checking the forum, I sadly can understand the reasoning for this move, so don't go crazy straight away, keep it simple and what it is supposed to be =).

After these reboots & a few tests I dropped the old DBs.

Although initally I dreamed of the 1 way ... 1 DB for all ... it is not sustainable and seriously disc space, today, does not matter anymore.

First of all for tryouts to not impact other users.
Secondly to add new very old devices with KODI, I found some old androids/iphones which went out of use and might get a second life as music players.
Thirdly if by mistake (as I play around with the DB) I would destroy/make it unusable/etc... I impact my corner only.
Fourthly go v19, the RPI runs smoother, although as mentionned in another post the analog out requires local/ssh intervention.

Lastly ... I see many empty fields in the DBs, hoping that they might be filled ? Plenty of potential remaining for KODI =)
Reply
  •   
  • 1
  • 19
  • 20
  • 21
  • 22
  • 23(current)



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