Kodi Community Forum
HOW-TO:Share libraries using MySQL: Wiki Edition - Printable Version

Kodi Community Forum (https://forum.kodi.tv)
+-- Forum: Support (https://forum.kodi.tv/forumdisplay.php?fid=33)
+--- Forum: Tips, tricks, and step by step guides (https://forum.kodi.tv/forumdisplay.php?fid=110)
--- Thread: HOW-TO:Share libraries using MySQL: Wiki Edition (/showthread.php?tid=157572)

Pages: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - FLJamie - 2017-06-10

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.


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - BuFf0k - 2017-07-21

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.


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - jingai - 2017-07-25

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.


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - Milhouse - 2017-07-25

Here's the reason for adding "SET SESSION optimizer_switch = 'derived_merge=off'": https://github.com/xbmc/xbmc/pull/8393#issuecomment-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.


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - jingai - 2017-07-25

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.


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - Milhouse - 2017-07-25

(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.


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - jingai - 2017-07-25

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+?


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - Milhouse - 2017-07-25

(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.


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - jingai - 2017-07-25

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


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - boscorelly - 2017-08-11

hi,

which version of mariadb ?

will it works with a master on w10 and a slave under raspbian ?


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - jingai - 2017-08-11

I used the latest version and it's working fine for me.


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - ikkeenjij36 - 2017-11-01

Hi all trying to connect and building a db on my syno nas but i can't get it working,i have set up all by the tutorials but it just keep using the local db,any hel please?


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - mika91 - 2017-11-05

Hello,

Just a simple question: what are the database exported to mysql?
In particular, I need to know if the "settings" database is exported, because on my shield TV I have to change deinterlace and upsacaling method for all my videos, but these options will not be supported by my other players (rpi and odroid)
Thanks


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - Klojum - 2017-11-05

Not everything is exported from the MySQL databases with the Video Library Export, if that's what you mean. Only certain metadata of videos stored, and not the contents of the 'settings' table, afaik. If you want to keep those values, the only database export/backup method is to use 'mysqldump' for a full backup of the whole Kodi video database.

The fact that some video players do not support certain functions is not the problem of making a backup. Right now, the shared database in Kodi does not support multiple/different video players and their settings per video. I doubt it ever will.


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - ventolin - 2018-01-07

(2017-07-25, 07:07)Milhouse Wrote: Here's the reason for adding "SET SESSION optimizer_switch = 'derived_merge=off'": https://github.com/xbmc/xbmc/pull/8393#issuecomment-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.
 Hi Milhouse,

Wish I'd seen Jingai's posts in this thread earlier. I've been struggling with this same problem (e.g. listing the tracks on an album takes upwards of 6 seconds) and had created a thread here - https://forum.kodi.tv/showthread.php?tid=323247&action=newpost - only to have found the diverged_merge culprit recently. 

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


This forum uses Lukasz Tkacz MyBB addons.