[jbinkley60]

Hi Jeff,

thanks for tackling this! I'm not sure there's a use case for local MySQL but the docker users might prove me wrong. In any case, the advancedsettings should be similar for local or LAN/remote. In my case it looks like

xml:
<advancedsettings>

<videodatabase>

<type>mysql</type>

<host>192.168.1.185</host>

<port>3306</port>

<user>root</user>

<pass>XXX</pass>

<compression>true</compression>

</videodatabase>

<musicdatabase>

<type>mysql</type>

<host>192.168.1.185</host>

<port>3306</port>

<user>root</user>

<pass>XXX</pass>

<compression>true</compression>

</musicdatabase>

</advancedsettings>

Parsing the settings from advancedsettings would certainly be elegant but I'm not sure if that is desirable by admins (data protection issue). I'm leaning towards a dedicated config setting, like the WatchedList add-on requires.

Thanks.  It certainly can be done both ways, parsing the advancedsettings.xml file or dedicated addon settings.  One downside of the settings approach is that the MySQL userid and password will now be in two places (or more in the case of the WatchedList addon)  in Kodi in unencrypted text.  Looking at the WatchedList addon, it seems to store them in plain text:

python:
<setting id="mysql_user" type="text" label="30053" default="xbmc" enable="eq(-10,1)" />

<setting id="mysql_pass" type="text" label="30054" default="xbmc" enable="eq(-11,1)" />

I suspect the reality though is that most folks use pretty basic userids / password with their MySQL setup for Kodi.  I'll leverage some of the stuff in the WatchedList addon to speed up my work. 


Thanks,

Jeff

[Klojum]

Some people also want to use the <name>MyOwnBlahblahDatabase</name> option to opt for alternate names to the video and/or music databases, for whatever situation.
But, Kodi will add the versioning number to that resulting in for example: MyOwnVideoDatabase121 or MyOwnMusicDatabase82.

[jbinkley60]

ome people also want to use the <name>MyOwnBlahblahDatabase</name> option to opt for alternate names to the video and/or music databases, for whatever situation.
But, Kodi will add the versioning number to that resulting in for example: MyOwnVideoDatabase121 or MyOwnMusicDatabase82.

Yep.  I saw that in the MySQL setup documentation.  If I parse the advancedsettings.xml file I will parse that tag.  That name tag is only available for MySQL and not local SQlite databases, correct ?


Thanks,

jeff

[Klojum]

That name tag is only available for MySQL and not local SQlite databases, correct ?

Indeed.

[HeresJohnny]

Some people also want to use the <name>MyOwnBlahblahDatabase</name> option to opt for alternate names to the video and/or music databases, for whatever situation.
But, Kodi will add the versioning number to that resulting in for example: MyOwnVideoDatabase121 or MyOwnMusicDatabase82.

I wish that could be deprecated if only for the sake of having less support headaches for you guys :-)

[Klojum]

I'm not sure why it was ever implemented. My guess it would only be for avoiding already existing databases on 'big' servers for other applications, or just for creating test databases. Seeing the SQLite workings, I don't see a reason to keep this option. But it's not getting into anyone's way either. So, it's just there.

[jbinkley60]

So I have been spending some more time looking at the Kodi Selective Cleaner code, reading up more on how Kodi works with MySQL and thinking about a few answers I have gotten here.  From what I can see each Kodi instance is a peer client to the main MySQL server and has the ability to read, write and update the MySQL database.  The database name in the MySQL database is based upon whether the name tag is used (for the base name) and then the appropriate version added to align to the Kodi version (i.e. append 121 for Nexus video database).  This also explains the difference between the MySQL webpage which says all clients must be on the same version of Kodi,  otherwise a Kodi 19 client might be on the database which ends in 119 and a Kodi 20 client would be on one which ends in 121.  They both would work with MySQL but not share data.    So in this scenario if you loaded the Kodi Selective Cleaner on both clients you could get different results because it would be hitting two different databases in MySQL.  Not a problem just not something I had considered since I've never really looked closely at how this all works.  

This brings me to a couple of questions for how folks run MySQL Kodi environments.  First, is in an upgrade scenario like I described how does the MySQL instance of the old database get deleted once all clients get upgraded (i.e. what deletes MyVideos119 once the last Matrix client upgrades) ?   Second, how do folks handle database updates / metadata scraping ?  One scenario is only to use one specific Kodi client as a scraper to control database updates, allow all clients to scrape / update or do something different.  I am curious how you typically protect against a rogue client (or client user) from making a bunch of unwanted changes to the MySQL database that then impacts all clients ?

Sorry for all the questions but realize my frame of reference is a Mezzmo environment which is a bit different so I want to understand the differences better to make sure I am not making any assumptions for the Kodi Selective Cleaner MySQL updates.  In the Mezzmo environment only the Mezzmo server talks to the back-end database, does the metadata scraping and the Kodi clients all use HTTP / SOAP calls to get data from Mezzmo.  The Mezzmo Kodi addon provides an abstraction layer to the Kodi database and handles all updates.  So you can run any mix of client versions at any time and always share the same data.  Database updates and such are manged centrally.  Clients can only do things like update playcounts, resume pointers and other things related to media playback.   So it definitely seems a bit different.

I think I have a path forward now for MySQL in the cleaner code.  I'll start doing some coding over the next few days and post some test code to ensure MySQL database connectivity is working and such before I determine if I am going to have to write a second set of SQL queries for My SQL vs. SQLite.  I am hoping not.


Thanks,

Jeff

[HeresJohnny]

I'll provide some input from my perspective.

1. Database update
When doing a major database upgrade, i.e. augmenting the version, the previous database is not removed automatically, nor would I recommend to do it manually. The old database serves as a backup and failsafe. If anything goes wrong with the database import (think buggy code or an existing database error), repair is as easy as removing the new database and starting the import all over. Also, sometimes I tinker with old hardware, for example Android tablets, which may be version locked due to hardware (chipset) or software (Android version) constraints, but that is just me.

2. Scraping
When I started with a centralized database I actually scraped from different end devices which in turn were a mix of different OS. I had Android tablets, a Windows notebook, a Streambox running LibreELEC or CoreELEC. Sources were a mix of SMB paths and NFS path. I had to find out the hard way that different OS may construct network paths with subtle differences so scraping from different devices sometimes resulted in subtle problems with paths and once even in major problems due to different time stamp formatting.

Nowadays I use a single Kodi instance to scrape and it is located on the server where the database server runs and where the sources are physically connected. I call it the server installation although no dedicated client-server software exists for Kodi (still wating for headless Kodi ) This is simply the fastest way and the best method to avoid aforementioned problems. Import and scraping of new tv shows is fully automated here, no user intervention is necessary to add new shows, start scraping or download artwork. Scraping of music files on the other hand I only start manually because new files are rarely added and I only refine tagging of existing files once in a while in foobar2000 to make it as compatible as possible between softwares. Unfortunately, Kodi uses a policy of read-only for tags in music files at this moment which stops me from making it my main go-to music player (music files should be as portable as possible IMHO so I want tags like rating to be written to file and not a database).

3. Users
"Rogue" user is a funny term for me as a non-English speaker as it seems to imply malicious intent. However, in a multi-user environment the cause of errors is mostly ignorance, hell that is sometimes even true in my own case as a long time user and admin. So to make Kodi fool-proof (never possible 100% as the saying goes "If someone invents something fool-proof, someone else invents a better fool"). What I do is to simply add no sources on installations that are used for dedicated playback. This makes no difference for the presentation of objects in the skin as all content is pushed from the database. In addition, I disable all the potentially dangerous options under SETTINGS - MEDIA - GENERAL - FILES.


As an afterthought, I'm not sure how thoroughly the music database is cleaned. There are some threads in the music support sub-forum that hint at problems. Maybe @black_eagle or @DaveBlake could formulate some wishes.

As a side-thought, Kodi would also benefit from unifying its file cleaning processes. When a tv show is removed through file view Kodi optionally deletes all files including artwork and even the folder. When a movie is removed through file view, Kodi only deletes the video file(s), leaving any orphaned artwork and the folder in place. This is probably connected to the scraping mechanism which assumes file and folder names should be identical (a setting which personally I don't find beneficial but which I leave actived because I don't understand all the repercussions).

[jbinkley60]

Thanks for the detailed answers and it aligns to some of my thinking on how folks operate these types of environments.  I have heard similar things about the music database not fully cleaning up after deletions.   It will be interesting when I get to coding that and see what we find.  I'll focus on the MySQL interface for the cleaner which supports the Kodi video database and then we can discuss the music database.  I am making some progress.  I have the new setting added and detection of whether the advancedsettings.xml file exists or not.  I am working on the XML parsing of your example.  Slow but steady progress.


Jeff

[jbinkley60]

Hi Jeff,

thanks for tackling this! I'm not sure there's a use case for local MySQL but the docker users might prove me wrong. In any case, the advancedsettings should be similar for local or LAN/remote. In my case it looks like

xml:
<advancedsettings>

<videodatabase>

<type>mysql</type>

<host>192.168.1.185</host>

<port>3306</port>

<user>root</user>

<pass>XXX</pass>

<compression>true</compression>

</videodatabase>

<musicdatabase>

<type>mysql</type>

<host>192.168.1.185</host>

<port>3306</port>

<user>root</user>

<pass>XXX</pass>

<compression>true</compression>

</musicdatabase>

</advancedsettings>

Parsing the settings from advancedsettings would certainly be elegant but I'm not sure if that is desirable by admins (data protection issue). I'm leaning towards a dedicated config setting, like the WatchedList add-on requires.

I was coding the XML parser against your configuration file and you have a tag called compression which doesn't appear on the MySQL Wiki page.   Are they actually used for anything or just extra ?


Thanks,

Jeff

[HeresJohnny]

Some time ago there was a problem with the c connector in kodi when compression was turned on. The problem has been resolved, not sure if that setting has been deprecated now.

[jbinkley60]

Some time ago there was a problem with the c connector in kodi when compression was turned on. The problem has been resolved, not sure if that setting has been deprecated now.

Ok.  Thanks.  I've gotten the advancedsettings.xml parser working now and can pull out all of the connection setting information.  I need to gt my test instance of MySQL setup so I can test the MySQL connector in the addon.  Hopefully I'll have something to test in the next couple of days.


Jeff

[jbinkley60]

Some time ago there was a problem with the c connector in kodi when compression was turned on. The problem has been resolved, not sure if that setting has been deprecated now.

I've made some good (but slower than desired) progress.  I am now able to query data from MySQL into Kodi Selective Cleaner.  The good news is that most existing queries look like they run as is.  However, MySQL executes them differently so I will have a bit of coding to do, especially on the analyzer module,  Due to this, it is going to take a bit longer than expected.  I will try to have something out for testing in the next couple of days which will populate the main screen with all of the counts from the various tables.  That will confirm proper interoperability.  From that I can work on the remaining query execution updates.


Thanks,

Jeff

[jbinkley60]

Some time ago there was a problem with the c connector in kodi when compression was turned on. The problem has been resolved, not sure if that setting has been deprecated now.

Ok, here's the first MySQL test release of the Kodi Selective Cleaner. If you want to try it.  You will need to go into the settings and change from Kodi Local to MySQL.  Note that it doesn't support split running (i.e. video in MySQL and music ls Kodi local) of the databases.  The functionality is very limited right now for MySQL.  It should mainly just popup the main screen with counts from each of the MySQL databases (i.e. movies, TV Shows, music videos and albums).  If this works Ok it sets the structure to begin completing the rest of the addon support for MySQL.

Thanks,

Jeff

[HeresJohnny]

I've tested it on a portable installation of Nexus and a nightly of Omega. On Nexus it connects to the databases and returns counts of objects, on Omega I get error messages of "Database not found". Also, the add-on installs to plugin.program.kcsleaner which I'm guessing should be plugin.program.kscleaner :-)