[Milhouse]

Am I right in understanding that Kodi should play nicely with MariaDB and that using it might be a possible workaround? I'd had problems with it before: https://forum.kodi.tv/showthread.php?tid=318705

Kodi should in theory play nicely with MariaDB, but there's no absolute guarantees as for the time being the only two database backends that are "certified" are SQLite and MySQL. Kodi may work with MariaDB, but only so long as MariaDB remains compatible with MySQL.

I'm aware of your thread, but so far the behaviour you have observed isn't reproducible. We have done internal testing against MySQL and MariaDB and with/without derived_merge=off both backends return results in a similar time 0.001s (ie. basically immeasurable).

By all means try MariaDB instead. Kodi may gain official MariaDB support if PR13318 is merged.

I would also suggest testing with LibreELEC from a USB thumb drive to test your backend server, just in case there's a problem with your docker container.

[ventolin]

I've downgraded to mysql 5.6 and everything is behaving itself again, so I'm happy for the time being. 

In your tests, what size does the MyMusic db have? I would suggest filling it with an awful lot of data in order to simulate this behaviour. For example:

Code:


mysql> select count(*) from song;
+----------+
| count(*) |
+----------+
|   103294 |
+----------+
1 row in set (0.02 sec)


[Milhouse]

I've downgraded to mysql 5.6 and everything is behaving itself again, so I'm happy for the time being. 

In your tests, what size does the MyMusic db have? I would suggest filling it with an awful lot of data in order to simulate this behaviour. For example:

I don't recall but probably not that many, I guess the important thing is that there was no obvious difference in performance.

It's entirely possible there is a regression/bug in the MySQL 5.7.19 server you were testing - I think we did internal testing with MySQL 5.7.20/MariaDB 10.1.26. This is really the biggest problem with server-based relational databases - every version can behave a little differently, whether intentionally or not.

[ventolin]

I've downgraded to mysql 5.6 and everything is behaving itself again, so I'm happy for the time being. 

In your tests, what size does the MyMusic db have? I would suggest filling it with an awful lot of data in order to simulate this behaviour. For example:

I don't recall but probably not that many, I guess the important thing is that there was no obvious difference in performance.

It's entirely possible there is a regression/bug in the MySQL 5.7.19 server you were testing - I think we did internal testing with MySQL 5.7.20/MariaDB 10.1.26. This is really the biggest problem with server-based relational databases - every version can behave a little differently, whether intentionally or not. 

 It has nothing to do with the minor version. I wasted much time testing it on several 5.7 versions, inside and outside of docker. 

Since what Kodi is doing is essentially turning off database optimizations, then the important thing is to test with a realistic set of fixtures in the db. Put more data in the db and you *will* see a very obvious difference in performance.

[Milhouse]

It has nothing to do with the minor version. I wasted much time testing it on several 5.7 versions, inside and outside of docker. 

OK, if not the minor version, then you yourself admit 5.6.x is fine while 5.7.x is not. Is this a Kodi issue or a MySQL issue? I honestly don't know.

Without further evidence it's difficult to know what to do - assuming it's a MySQL bug, we can't code around every MySQL server bug, the best option in such a situation is to just avoid the specific MySQL Server version - we've had this situation with MariaDB too, when they released a buggy server version and the issue was resolved in the next minor update of MariaDB Server.

To the best of my knowledge MySQL performance hasn't been an issue since we introduced the derived_merge=off change over 2 years ago, so I'm really not sure why it would be a problem now unless something has changed in MySQL 5.7.x, or it's the type of connector you are using (testing with LibireELEC would have ruled out such variables).

Since what Kodi is doing is essentially turning off database optimizations

Not so much turning off an optimisation, rather we are instructing MySQL to support the types of queries it has historically supported (prior to 5.7.6, I believe) which means we don't have to refactor the entire MySQL support (which would probably have meant a swifter end to MySQL support if I'm honest).

then the important thing is to test with a realistic set of fixtures in the db. Put more data in the db and you *will* see a very obvious difference in performance.

What's realistic for you isn't necessarily realistic for me, particularly without knowing the spec of the server hosting the database. But sure - do you want to upload a backup of your Music database, maybe someone will be willing to load/test it, or we can use it in future. Personally my Music db is quite small, 7800 songs/351 albums, and my backend MySQL Server is only 5.5.21 running on a low-powered NAS (HP N36L with 8GB RAM), so in some sense it's all relative - a small db on low powered server, large db on higher powered server, what is realistic here, at what point does it become unreasonable to host a large database on a low powered server?

[Pico1965]

Good morning!!

I don't have a NAS supporting mySQL.
I can't powered on 1 my PC h/24 for mySQL purpose.

Can I store my libraries in db4free.net?

If yes... how?

Thanks for your attention.

[Milhouse]

Can I store my libraries in db4free.net?

If yes... how?

Accessing this data across the internet may not be a great user experience (depending on where you are - db4free.net seems to be in Austria) but I guess it will work. Request an account and configure it, presumably you'll get an ip address and port.

Note that there are limitations - 200MB data limit (after which your data will be regularly wiped), no guarantee in terms of service availability or data retention etc. In short, it's a testing service, and not intended for "production" use so don't be surprised if it works only some of the time, or your data disappears without warning.

My advice would be a Raspberry Pi with Raspbian (other SBCs are available) running MySQL (or MariaDB) - low power, always on, no data limits or retention issues.

[Pico1965]

Thanks for your attention.

when register you must create:
A database with name --> KODI
a user --> kodi
a password  --> kodi
In wiki:<advancedsettings>
<videodatabase>
<type>mysql</type>
<host>mysql8.db4free.net</host>
<port>3307</port> [must be 3307]
<user>kodi</user>
<pass>kodi</pass>
</videodatabase> [...]

But not work. The Kodi' db are not created.

I understand the TOS.
My LOCAL databases are  84Mb.

I will try... if possible.

[nickr]

To receive meaningful assistance you will need to provide a full debug log.

The instructions are here... debug log (wiki)

If you are using the Basic Method, then ensure the following is applied...
1.Enable debugging in Settings>System Settings>Logging,
2.Restart Kodi
3.Replicate the problem.

If you are using the Advanced Method ensure you have correctly created and applied the advancedsettings.xml file.

In both instances, you should see the word DEBUG throughout the log.

Note: Full logs only. No partial or redacted logs
Do NOT post your logs directly into the forum. Always use a paste site like pastebin.com. Post the link to your pasted log in the forum

[flhthemi]

Just a reminder, don't forget to open port 3307 on your router! That always seems to be my mistake. I'm old and have CRS Syndrome (Can't Remember Squat)

[AMoo-Miki]

@Milhouse, about a year ago I posted about the problems I faced with Kodi caching results of slow queries on https://forum.kodi.tv/showthread.php?tid=313580, where Kodi didn't invalidate caches correctly. On that thread, @Koying tried to help but unfortunately misunderstood the problem, but since you seem to be interested in the MySQL stuff, I am tagging you here to get some traction.

The short version:
Prior to Kodi 17, even though the code existed to cache the results of slow queries, for some reason, no cache was ever created and as a result no one noticed this bug in the older versions.
When an episode is watched, the episode list cache gets invalidated, but caches for the season list and TV shows list don't get invalidated and as a result show stale and incorrect data for a long time. Similarly if a movie is explicitly refreshed, Kodi removes the old row of data in the database and creates a new one, and because it doesn't invalidate the cache, the wrong information is displayed.

I tried to work on a fix and raise a PR but sadly setting up the dev environment is a herculean task which I haven't been able to successfully do. Hope the info I gave makes enough sense for you to look into it.

Refs:
https://trac.kodi.tv/ticket/17336 <-- TV Shows watching
https://trac.kodi.tv/ticket/17536 <-- Movie refreshing

[Milhouse]

Try Kodi 18 as you might find the problem is fixed now that File Item caching is mostly broken in Estuary.

I don't have any solution that will correctly invalidate the cache, I suspect that might require a chunk of work for someone (a sledgehammer solution might be to invalidate all File Item caches whenever any database update occurs).

Disabling the File Item cache entirely might be a faster solution as not only does the File Item cache have the problem you noted, but also in Kodi 18 the caches are created/written, but never loaded/read (as the wrong name is used).

The following patch should disable the File Item cache in Kodi 18: http://nmacleod.com/public/oebuild/patch...tirely.txt

[AMoo-Miki]

Thanks for looking into this. I am afraid the cache of skin directives is different from the library queries, but I will surely give 18 a shot. I hope the caching of library queries is broken too

[garynitely]

hi, i was wondering if anybody could help me out.

i have tried to set up sharing between kodi running on a windows 10 minipc and a raspberry pi following the instructions here:
https://www.howtogeek.com/75535/how-to-s...with-xbmc/

i have installed MySQL onto the windows minipc, which has an external hard drive plugged into it containing my media collection (which is then shared over the network to the Raspberry Pi).

However, after creating the advancedsettings.xml file in step 4, i am not able to import my library. i exported it as a single file, as i wish to keep my folder a little tidier and not have about 4-5 files per movie.

if i delete the advancedsettings.xml file and reboot kodi, i am able to import the library, however each time i create it and reboot kodi again, no importing.

any guidance would be hugely appreciated as i am really tearing my hair out with this!

thanks!

[flhthemi]

I'm not a MySql expert by any means but -

You should not need to import your library when advancedsettings.xml is being used. If I remember right I shut down all devices and started Kodi on the mysql server using advancedsettings.xml (your mini PC) with NO libraries established I then setup the library sources, as one would normally do, in Kodi on your mini PC. If you have installed mysql properly it should then create a mysql databse on the server and magicaly add the info as Kodi's library function scans for and obtains it. Then you need to have advancedsettings on the pi you have and start Kodi on it. If the mysql server is installed properly the pi should now have the same data available. There should be no library sources available on it as it's now pulling the info from mysql. It is very important that you have port 3306 open on your router to the mysql server (the mini PC).

I THINK I have also had a new install of mysql running and installed advancedsettings.xml on a Kodi device that already has a library and it just made the databases and all was well!

If you use mysql command SHOW DATABASES; it should display your mysql databases myvideos107 (Kodi 17) and if there is music mymusic60 (Kodi 17) similar to this: