HOW-TO:Share libraries using MySQL: Wiki Edition

  Thread Rating:
  • 2 Vote(s) - 5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Post Reply
FLJamie Offline
Junior Member
Posts: 1
Joined: Jun 2017
Reputation: 0
Post: #466
Trying to setup MySQL with Raspberry Pi3, OSMC (May 2017 update) running Kodi 17. I had MySQL (version 5.5 as I was told 5.6 or 5.7 were not compatible) DB working fine with previous build, but after doing a rebuild and wiping out my MySQL database to start from scratch, i cannot seem to get the databases to create and populate on my MySQL server. Am i missing some step here? I have the media connections to my library on my NAS and i can see all the files. Also my TV Shows and Movies Menu items seem to disappear from the main OSMC menu. Separate issue but still weird.
find quote
BuFf0k Offline
Junior Member
Posts: 1
Joined: Jul 2017
Reputation: 0
Post: #467
Hi all, so I have a rather interesting problem that I am hoping that you guys may be able to help with. I set up my Primary System (A Beefy Quad Core PC with lots of storage) in the living room using the wiki guide (Which was excellent). It runs Windows 10 with SMB (With advanced security disabled, so all my devices can browse without passwords). The tricky bit is this; when I fire up Kodi on my laptop, everything works beautifully without any problems, however when I try watching things from my LibrELEC Raspberry Pi 3B or my Samsung Galaxy S8+ (Haven't tried on the wife's iPhone or iPad yet) most things work like they should, however some TV Shows (Archer being one example) and movies (Again, not all of them , a lot of them work perfectly), when I select them from either of these devices tell me that the Media is Missing and asks if I should remove from the library...

This is extremely frustrating as trying to watch those files on either platform via direct ad (If I delete the settings xml) they work perfectly so it is not the platform but something with the sharing. Each of the three SMB Shared Folders (Series, Series 2 and Movies) were added to the primary server via IP (SMB:\\172.17.42.2\Series\). If it was a sharing issue, though, then the specific series' that don't work would be all series in a specific share, which is not the case. Any help will be greatly appreciated.
find quote
jingai Offline
Skilled Skinner
Posts: 2,207
Joined: Mar 2010
Reputation: 37
Post: #468
Queuing songs is very slow with MySQL 5.7.18 (and possibly lower):

Code:
# Time: 2017-07-25T02:46:21.336164Z
# [email protected]: xbmc[xbmc] @ xxx.xxx.xxx [192.168.1.2]  Id:   191
# Query_time: 0.197212  Lock_time: 0.000169 Rows_sent: 1  Rows_examined: 130900
SET timestamp=1500950781;
SELECT songview.*,songartistview.* FROM songview  JOIN songartistview ON songview.idSong = songartistview.idSong  WHERE songview.idSong = 25345  ORDER BY songartistview.idRole, songartistview.iOrder;

This wasn't the case with MySQL 5.6.

When I try that query manually, it executes instantly. So I started logging all queries to see what's going on and find Kodi does:

Code:
2017-07-25T02:31:09.800543Z    15 Connect   [email protected] on  using TCP/IP
2017-07-25T02:31:09.800581Z    15 Query SET NAMES utf8
2017-07-25T02:31:09.800621Z    15 Query SET SESSION sql_mode = (SELECT REPLACE(@@SESSION.sql_mode,'ONLY_FULL_GROUP_BY',''))
2017-07-25T02:31:09.800663Z    15 Query SELECT @@SESSION.optimizer_switch
2017-07-25T02:31:09.800712Z    15 Query SET SESSION optimizer_switch = 'derived_merge=off'
2017-07-25T02:31:09.800760Z    15 Query show databases like 'xbmc_music60'
2017-07-25T02:31:09.800869Z    15 Init DB   xbmc_music60
2017-07-25T02:31:09.800912Z    15 Query show databases like 'xbmc_music60'
2017-07-25T02:31:09.800993Z    15 Query show tables
2017-07-25T02:31:09.801116Z    15 Query SELECT songview.*,songartistview.* FROM songview  JOIN songartistview ON songview.idSong = songartistview.idSong  WHERE songview.idSong = 8627  ORDER BY songartistview.idRole, songartistview.iOrder

If I do all of the above manually, it takes just as long as it does from Kodi (~0.2 seconds). I found the culprit is:

Code:
2017-07-25T02:31:09.800712Z    15 Query SET SESSION optimizer_switch = 'derived_merge=off'

If I omit this, it's fine again.

Here's the output from EXPLAIN, if it helps:

Code:
mysql> EXPLAIN SELECT songview.*,songartistview.* FROM songview  JOIN songartistview ON songview.idSong = songartistview.idSong  WHERE songview.idSong = 8627  ORDER BY songartistview.idRole, songartistview.iOrder;
+----+-------------+-------------+------------+--------+---------------------------------+-----------------+---------+-----------------------------------+-------+----------+-----------------------------+
| id | select_type | table       | partitions | type   | possible_keys                   | key             | key_len | ref                               | rows  | filtered | Extra                       |
+----+-------------+-------------+------------+--------+---------------------------------+-----------------+---------+-----------------------------------+-------+----------+-----------------------------+
|  1 | PRIMARY     | <derived3>  | NULL       | ref    | <auto_key0>                     | <auto_key0>     | 5       | const                             |    10 |   100.00 | Using where; Using filesort |
|  1 | PRIMARY     | <derived2>  | NULL       | ref    | <auto_key0>                     | <auto_key0>     | 4       | const                             |    10 |   100.00 | NULL                        |
|  3 | DERIVED     | role        | NULL       | ALL    | PRIMARY                         | NULL            | NULL    | NULL                              |     6 |   100.00 | NULL                        |
|  3 | DERIVED     | song_artist | NULL       | ref    | idxSongArtist_3,idxSongArtist_4 | idxSongArtist_4 | 5       | xbmc_music60.role.idRole          |  4350 |   100.00 | Using where                 |
|  3 | DERIVED     | artist      | NULL       | eq_ref | PRIMARY                         | PRIMARY         | 4       | xbmc_music60.song_artist.idArtist |     1 |   100.00 | NULL                        |
|  2 | DERIVED     | song        | NULL       | ALL    | idxSong7,idxSong3,idxSong6      | NULL            | NULL    | NULL                              | 25620 |   100.00 | Using where                 |
|  2 | DERIVED     | album       | NULL       | eq_ref | PRIMARY                         | PRIMARY         | 4       | xbmc_music60.song.idAlbum         |     1 |   100.00 | NULL                        |
|  2 | DERIVED     | path        | NULL       | eq_ref | PRIMARY                         | PRIMARY         | 4       | xbmc_music60.song.idPath          |     1 |   100.00 | NULL                        |
+----+-------------+-------------+------------+--------+---------------------------------+-----------------+---------+-----------------------------------+-------+----------+-----------------------------+

~0.2 doesn't seem like a long time, but I help with an Alexa skill to control Kodi via JSON-RPC and we have a few commands that add songs in bulk, and this extra delay causes the skill to time out.
find quote
Milhouse Offline
Team-Kodi Member
Posts: 12,710
Joined: Jan 2011
Reputation: 627
Post: #469
Here's the reason for adding "SET SESSION optimizer_switch = 'derived_merge=off'": https://github.com/xbmc/xbmc/pull/8393#i...-159857118

Unless something has now changed in MySQL 5.7.x, you can either have slow-ish (but working) queries, or queries that are unable to delete from tables in a subquery.

We opted for the working queries. I assume the delete from subquery restriction still exists in MySQL 5.7.18?

Is going back to MySQL 5.6.x an option? Or try MariaDB, maybe it doesn't have a query optimiser from the 1990s.

Texture Cache Maintenance Utility: Preload your texture cache for optimal UI performance. Remotely manage media libraries. Purge unused artwork to free up space. Find missing media. Configurable QA check to highlight metadata issues. Aid in diagnosis of library and cache related problems.
find quote
jingai Offline
Skilled Skinner
Posts: 2,207
Joined: Mar 2010
Reputation: 37
Post: #470
Aye, I understood the reason for the addition. The query doesn't fail without it on MySQL 5.7.18, though. It might be worth not setting the option if >= 5.7.18 (or whatever version 'fixed' this).

Unfortunately for us, .2 second queries per song means most requests that add songs like this are going to time out. Amazon gives us a flat 10 seconds to return or Alexa complains about the request.

Going back to 5.6 for me is an option, but we have other users complaining about it too.
(This post was last modified: 2017-07-25 07:19 by jingai.)
find quote
Milhouse Offline
Team-Kodi Member
Posts: 12,710
Joined: Jan 2011
Reputation: 627
Post: #471
(2017-07-25 07:17)jingai Wrote:  The query doesn't fail without it on MySQL 5.7.18, though. It might be worth not setting the option if >= 5.7.18 (or whatever version 'fixed' this).

Your SELECT query won't fail due to having derived_merge=on, but queries that DELETE from a table which is used in a subquery will fail with MySQL 5.7.6+ when derived_merge is ON (for example when cleaning the database, see the DELETE query in this post).

If the delete-from-a-table-in-a-subquery restriction is no longer an issue in MySQL 5.7.18 then maybe derived_merge=off could be applied conditionally, but I think it unlikely that anything will have changed for the better in MySQL.

Rewriting all the affected queries to avoid deleting from tables used in a subquery is the only other option, and that just isn't going to happen IMHO.

Texture Cache Maintenance Utility: Preload your texture cache for optimal UI performance. Remotely manage media libraries. Purge unused artwork to free up space. Find missing media. Configurable QA check to highlight metadata issues. Aid in diagnosis of library and cache related problems.
find quote
jingai Offline
Skilled Skinner
Posts: 2,207
Joined: Mar 2010
Reputation: 37
Post: #472
Do you think it'd be possible to set derived_merge=off only for queries that DELETE from a table used in a subquery rather than setting it unconditionally for 5.7.6+?
find quote
Milhouse Offline
Team-Kodi Member
Posts: 12,710
Joined: Jan 2011
Reputation: 627
Post: #473
(2017-07-25 08:32)jingai Wrote:  Do you think it'd be possible to set derived_merge=off only for queries that DELETE from a table used in a subquery rather than setting it unconditionally for 5.7.6+?

I'm sure it would be possible to analyze the query in the MySQL database wrapper in order to detect DELETE queries and apply the setting before executing such a query (then unset it afterwards) but... that's just a bigger hack than the one we have already. Obviously for a connection that doesn't need the setting (pre 5.7.6) none of this would be required so any "analyzing" could be skipped entirely. Can't be done higher up because SQLite doesn't care about this. Then everything would need to be thoroughly re-tested.

Texture Cache Maintenance Utility: Preload your texture cache for optimal UI performance. Remotely manage media libraries. Purge unused artwork to free up space. Find missing media. Configurable QA check to highlight metadata issues. Aid in diagnosis of library and cache related problems.
(This post was last modified: 2017-07-25 09:08 by Milhouse.)
find quote
jingai Offline
Skilled Skinner
Posts: 2,207
Joined: Mar 2010
Reputation: 37
Post: #474
Yeah, I understand the ramifications. I'll recommend to our users to switch to MariaDB -- just made the switch here and it's working fine now Smile
find quote
Post Reply