2017-12-10, 16:38
just fyi - Just tested using mysql-server-5.7 installed directly on ubuntu (no dockerization), same issue persists
mysqld, Version: 5.7.20 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
2018-01-03T19:21:35.796959Z 20 Connect [email protected] on using SSL/TLS
2018-01-03T19:21:35.797607Z 20 Query SET NAMES utf8
2018-01-03T19:21:35.798267Z 20 Query SET SESSION sql_mode = (SELECT REPLACE(@@SESSION.sql_mode,'ONLY_FULL_GROUP_BY',''))
2018-01-03T19:21:35.798901Z 20 Query SELECT @@SESSION.optimizer_switch
2018-01-03T19:21:35.799614Z 20 Query SET SESSION optimizer_switch = 'derived_merge=off'
2018-01-03T19:21:35.800626Z 20 Query show databases like 'MyMusic60'
2018-01-03T19:21:35.802001Z 20 Init DB MyMusic60
2018-01-03T19:21:35.802847Z 20 Query show databases like 'MyMusic60'
2018-01-03T19:21:35.803500Z 20 Query show tables
2018-01-03T19:21:35.830449Z 21 Connect [email protected] on using SSL/TLS
2018-01-03T19:21:35.831148Z 21 Query SET NAMES utf8
2018-01-03T19:21:35.831816Z 21 Query SET SESSION sql_mode = (SELECT REPLACE(@@SESSION.sql_mode,'ONLY_FULL_GROUP_BY',''))
2018-01-03T19:21:35.832471Z 21 Query SELECT @@SESSION.optimizer_switch
2018-01-03T19:21:35.833139Z 21 Query SET SESSION optimizer_switch = 'derived_merge=off'
2018-01-03T19:21:35.834396Z 21 Query show databases like 'MyMusic60'
2018-01-03T19:21:35.835821Z 21 Init DB MyMusic60
2018-01-03T19:21:35.836721Z 21 Query show databases like 'MyMusic60'
2018-01-03T19:21:35.837417Z 21 Query show tables
2018-01-03T19:21:35.838299Z 21 Query SELECT COUNT(1) FROM songview WHERE songview.idAlbum = 33661
2018-01-03T19:21:37.124151Z 21 Query SELECT songview.*, songartistview.* FROM songview JOIN songartistview ON songartistview.idsong = songview.idsong WHERE songview.idAlbum = 33661 ORDER BY songartistview.idsong, songartistview.idRole, songartistview.iOrder
2018-01-03T19:21:40.479931Z 21 Quit
mysql> use MyMusic60;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SET SESSION optimizer_switch = 'derived_merge=off';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT sql_no_cache songview.*, songartistview.* FROM songview JOIN songartistview ON songartistview.idsong = songview.idsong WHERE songview.idAlbum = 33091 ORDER BY songartistview.idsong, songart
istview.idRole, songartistview.iOrder;
[.... results removed for brevity ....]
4 rows in set, 1 warning (3.37 sec)
mysql> quit;
(2018-01-03, 21:54)ventolin Wrote: If there are people responsible for the kodi-wide DB handling (not just music db), could you ping them with this message?There are no such "responsible" people, but there are a few team members that use client/server setups themselves and that is a near as we get to it. I will certianly raise it with them. Historically MySQL and its variants have thrown up a variety of issues over the years. These are often fixed by Millhouse, one of the active MySQL users on the team, but I doubt if he would want to be considered responsible.
(2018-01-03, 21:54)ventolin Wrote: Looking through the code and the github issues, it seems that this was the rationale behind it: https://github.com/xbmc/xbmc/pull/8393#i...-159857118 This is done to have the really weird DELETE queries (example: https://forum.kodi.tv/showthread.php?tid...pid2170957 ) not break. However, it's being applied to *every* session - not just sessions opened in order to perform these crazy delete queries.
Suggested fix: only set this in sessions when these weird deletes with subselects are performed.
Quote:I have now been looking into trying to get 5.6 working on 16.04 but apparently that's not particularly straightforward, not to mention out of scope for this discussion.
Kind of annoying and I'm wondering whether or not to put the time and effort into hacking about my MySQL install, or just wait for Kodi 18 to drop which might potentially fix the issue from the other side anyway?
(2018-03-20, 23:44)marantz Wrote: just tested this on mariadb 10.2.13 and SET SESSION optimizer_switch = 'derived_merge=off'; had no effect on performanceGood to know. I had bad (and similar) experiences when I switched to MariaDB and was met with very hostile answers along the lines of "mysql db backend is barely supported, mariadb definitely not" in #xbmc on freenode when I asked about it.
(2018-03-21, 08:48)ventolin Wrote:Yes this is a running theme within the team as MYSQL support was added, when really the team wanted a full client to client approach in the long term. Nobody wrote that yet so, we are kind of stuck with the old style central server approach which nobody really supports.(2018-03-20, 23:44)marantz Wrote: just tested this on mariadb 10.2.13 and SET SESSION optimizer_switch = 'derived_merge=off'; had no effect on performanceGood to know. I had bad (and similar) experiences when I switched to MariaDB and was met with very hostile answers along the lines of "mysql db backend is barely supported, mariadb definitely not" in #xbmc on freenode when I asked about it.
I might give it another go this weekend just to see if things have improved with mariadb 10.1. Thanks!