HOW-TO:Share libraries using MySQL: Wiki Edition
(2015-11-26, 11:23)Milhouse Wrote: @pnkiller78: I don't have a 5.7.x installation, so can you try the following query:

Code:
DELETE FROM path
WHERE (strContent IS NULL OR strContent = '') AND
      (strSettings IS NULL OR strSettings = '') AND
      (strHash IS NULL OR strHash = '') AND
      (exclude IS NULL OR exclude != 1) AND
      (idParentPath IS NULL OR NOT EXISTS (SELECT 1 FROM (SELECT idPath FROM path) as parentPath
                                                    WHERE parentPath.idPath = path.idParentPath)) AND
      NOT EXISTS (SELECT 1 FROM files
                  WHERE files.idPath = path.idPath) AND
      NOT EXISTS (SELECT 1 FROM tvshowlinkpath
                  WHERE tvshowlinkpath.idPath = path.idPath) AND
      NOT EXISTS (SELECT 1 FROM movie
                  WHERE movie.c23 = path.idPath) AND
      NOT EXISTS (SELECT 1 FROM episode
                  WHERE episode.c19 = path.idPath) AND
      NOT EXISTS (SELECT 1 FROM musicvideo
                  WHERE musicvideo.c14 = path.idPath)

You should get the 1093 error.

Now try:
Code:
SET optimizer_switch = 'derived_merge=off';

and repeat the first query - does it work or continue to fail?

Modifying the optimizer_switch to restore pre-5.7.6 compatibility on a per-session basis might be the easiest solution...

Edit: Or could you try modifying the query to use "SELECT DISTINCT" as it mentions in the linked query optimizer article (just make sure you restore the default optimize_switch!)
Quote:(Other workarounds include using SELECT DISTINCT or LIMIT in the subquery, although these are not as explicit in their effect on materialization.)

However we don't know what other queries will also need changing, in which case restoring pre-5.7.6 compatibility might still be the best option.

The thing is that MySQL isn't a long term strategic solution, so spending time rewriting and testing existing code that works perfectly well with our strategic solution (SQLite) is becoming less of an option. Quick and dirty might have to suffice in order to keep MySQL limping along, or alternatively users will just have to stick with pre-5.7.x servers.

Using the optimizer_switch it runs without errors.
In fact, I added the option in my.ini file and now the whole transaction ends without problems.

Code:
23:24:33 T:408   DEBUG: ------ Window Init (DialogYesNo.xml) ------
23:24:33 T:408    INFO: Loading skin file: DialogYesNo.xml, load type: KEEP_IN_MEMORY
23:24:33 T:408   DEBUG: Keyboard: scancode: 0x4b, sym: 0x0114, unicode: 0x0000, modifier: 0x0
23:24:33 T:408   DEBUG: CInputManager::OnKey: left (0xf082) pressed, action is Left
23:24:33 T:408   DEBUG: Keyboard: scancode: 0x1c, sym: 0x000d, unicode: 0x000d, modifier: 0x0
23:24:33 T:408   DEBUG: CInputManager::OnKey: return (0xf00d) pressed, action is Select
23:24:33 T:408   DEBUG: ------ Window Deinit (DialogYesNo.xml) ------
23:24:33 T:408  NOTICE: CVideoDatabase::CleanDatabase: Starting videodatabase cleanup ..
23:24:33 T:408   DEBUG: CAnnouncementManager - Announcement: OnCleanStarted from xbmc
23:24:33 T:408   DEBUG: GOT ANNOUNCEMENT, type: 16, from xbmc, message OnCleanStarted
23:24:33 T:408   DEBUG: Mysql Start transaction
23:24:33 T:408   DEBUG: DialogProgress::StartModal called
23:24:33 T:408   DEBUG: ------ Window Init (DialogProgress.xml) ------
23:24:33 T:408    INFO: Loading skin file: DialogProgress.xml, load type: KEEP_IN_MEMORY
23:24:34 T:408   DEBUG: SECTION:LoadDLL(special://xbmcbin/system/libnfs.dll)
23:24:34 T:408   DEBUG: NFS: Context for 127.0.0.1/movies not open - get a new context.
23:24:34 T:408   DEBUG: NFS: Connected to server 127.0.0.1 and export /movies
23:24:34 T:408   DEBUG: NFS: chunks: r/w 8192/8192
23:24:36 T:3176   ERROR: CCurlFile::FillBuffer - Failed: Timeout was reached(28)
23:24:36 T:3176   DEBUG: CCurlFile::Open - effective URL: <http://mirror.umd.edu/xbmc/addons/gotham/addons.xml>
23:24:36 T:3632  NOTICE: Thread FileCache start, auto delete: false
23:24:36 T:3632    INFO: CFileCache::Process - Hit eof.
23:24:36 T:3632   DEBUG: Thread FileCache 3632 terminating
23:24:36 T:3176   ERROR: Failed to read directory listing http://mirrors.kodi.tv/addons/gotham/addons.xml for repository repository.xbmc.org.
23:24:36 T:3176   DEBUG: ADDON: cpluff: 'Not all directories were successfully scanned.'
23:24:43 T:408   DEBUG: NFS: Context for 127.0.0.1/tvshows not open - get a new context.
23:24:43 T:408   DEBUG: NFS: Connected to server 127.0.0.1 and export /tvshows
23:24:43 T:408   DEBUG: NFS: chunks: r/w 8192/8192
23:24:43 T:408   DEBUG: NFS: Refreshing context for 127.0.0.1/movies, old: 12846, new: 22312
23:24:43 T:408   DEBUG: NFS: Using cached context.
23:24:43 T:408   DEBUG: NFS: Refreshing context for 127.0.0.1/tvshows, old: 22232, new: 22320
23:24:43 T:408   DEBUG: NFS: Using cached context.
23:24:43 T:408   DEBUG: NFS: Refreshing context for 127.0.0.1/movies, old: 22312, new: 22443
23:24:43 T:408   DEBUG: NFS: Using cached context.
23:24:43 T:408   DEBUG: NFS: Refreshing context for 127.0.0.1/tvshows, old: 22320, new: 22472
23:24:43 T:408   DEBUG: NFS: Using cached context.
23:24:43 T:408   DEBUG: NFS: Refreshing context for 127.0.0.1/movies, old: 22443, new: 22547
23:24:43 T:408   DEBUG: NFS: Using cached context.
23:24:43 T:408   DEBUG: NFS: Refreshing context for 127.0.0.1/tvshows, old: 22472, new: 22571
23:24:43 T:408   DEBUG: NFS: Using cached context.
23:24:43 T:408   DEBUG: NFS: Refreshing context for 127.0.0.1/movies, old: 22547, new: 22573
23:24:43 T:408   DEBUG: NFS: Using cached context.
23:24:45 T:408   DEBUG: NFS: Refreshing context for 127.0.0.1/tvshows, old: 22571, new: 24217
23:24:45 T:408   DEBUG: NFS: Using cached context.
23:24:45 T:408   DEBUG: NFS: Refreshing context for 127.0.0.1/movies, old: 22573, new: 24311
23:24:45 T:408   DEBUG: NFS: Using cached context.
23:24:46 T:408   DEBUG: CVideoDatabase::CleanDatabase: Cleaning paths that don't exist and have content set...
23:24:46 T:408   DEBUG: NFS: Context for 127.0.0.1/artwork not open - get a new context.
23:24:46 T:408   DEBUG: NFS: Connected to server 127.0.0.1 and export /artwork
23:24:46 T:408   DEBUG: NFS: chunks: r/w 8192/8192
23:24:46 T:408   DEBUG: NFS: Refreshing context for 127.0.0.1/movies, old: 24311, new: 24981
23:24:46 T:408   DEBUG: NFS: Using cached context.
23:24:46 T:408   DEBUG: NFS: Refreshing context for 127.0.0.1/tvshows, old: 24217, new: 25078
23:24:46 T:408   DEBUG: NFS: Using cached context.
23:24:46 T:408   DEBUG: NFS: Refreshing context for 127.0.0.1/movies, old: 24981, new: 25079
23:24:46 T:408   DEBUG: NFS: Using cached context.
23:24:46 T:408   DEBUG: NFS: Refreshing context for 127.0.0.1/tvshows, old: 25078, new: 25079
23:24:46 T:408   DEBUG: NFS: Using cached context.
23:24:46 T:408   DEBUG: NFS: Refreshing context for 127.0.0.1/movies, old: 25079, new: 25080
23:24:46 T:408   DEBUG: NFS: Using cached context.
23:24:46 T:408   DEBUG: NFS: Refreshing context for 127.0.0.1/tvshows, old: 25079, new: 25081
23:24:46 T:408   DEBUG: NFS: Using cached context.
23:24:46 T:408   DEBUG: NFS: Refreshing context for 127.0.0.1/movies, old: 25080, new: 25081
23:24:46 T:408   DEBUG: NFS: Using cached context.
23:24:46 T:408   DEBUG: NFS: Refreshing context for 127.0.0.1/tvshows, old: 25081, new: 25082
23:24:46 T:408   DEBUG: NFS: Using cached context.
23:24:46 T:408   DEBUG: NFS: Refreshing context for 127.0.0.1/movies, old: 25081, new: 25082
23:24:46 T:408   DEBUG: NFS: Using cached context.
23:24:46 T:408   DEBUG: CVideoDatabase::CleanDatabase: Cleaning tvshow table
23:24:46 T:408   DEBUG: CVideoDatabase::CleanDatabase: Cleaning path table
23:24:46 T:408   DEBUG: Mysql execute: DELETE FROM path WHERE (strContent IS NULL OR strContent = '') AND (strSettings IS NULL OR strSettings = '') AND (strHash IS NULL OR strHash = '') AND (exclude IS NULL OR exclude != 1) AND (idParentPath IS NULL OR NOT EXISTS (SELECT 1 FROM (SELECT idPath FROM path) as parentPath WHERE parentPath.idPath = path.idParentPath)) AND NOT EXISTS (SELECT 1 FROM files WHERE files.idPath = path.idPath) AND NOT EXISTS (SELECT 1 FROM tvshowlinkpath WHERE tvshowlinkpath.idPath = path.idPath) AND NOT EXISTS (SELECT 1 FROM movie WHERE movie.c23 = path.idPath) AND NOT EXISTS (SELECT 1 FROM episode WHERE episode.c19 = path.idPath) AND NOT EXISTS (SELECT 1 FROM musicvideo WHERE musicvideo.c14 = path.idPath)
23:24:46 T:408   DEBUG: CVideoDatabase::CleanDatabase: Cleaning genre table
23:24:46 T:408   DEBUG: Mysql execute: DELETE FROM genre WHERE NOT EXISTS (SELECT 1 FROM genre_link WHERE genre_link.genre_id = genre.genre_id)
23:24:46 T:408   DEBUG: CVideoDatabase::CleanDatabase: Cleaning country table
23:24:46 T:408   DEBUG: Mysql execute: DELETE FROM country WHERE NOT EXISTS (SELECT 1 FROM country_link WHERE country_link.country_id = country.country_id)
23:24:46 T:408   DEBUG: CVideoDatabase::CleanDatabase: Cleaning actor table of actors, directors and writers
23:24:46 T:408   DEBUG: Mysql execute: DELETE FROM actor WHERE NOT EXISTS (SELECT 1 FROM actor_link WHERE actor_link.actor_id = actor.actor_id) AND NOT EXISTS (SELECT 1 FROM director_link WHERE director_link.actor_id = actor.actor_id) AND NOT EXISTS (SELECT 1 FROM writer_link WHERE writer_link.actor_id = actor.actor_id)
23:24:46 T:408   DEBUG: CVideoDatabase::CleanDatabase: Cleaning studio table
23:24:46 T:408   DEBUG: Mysql execute: DELETE FROM studio WHERE NOT EXISTS (SELECT 1 FROM studio_link WHERE studio_link.studio_id = studio.studio_id)
23:24:46 T:408   DEBUG: CVideoDatabase::CleanDatabase: Cleaning set table
23:24:46 T:408   DEBUG: Mysql execute: DELETE FROM sets WHERE NOT EXISTS (SELECT 1 FROM movie WHERE movie.idSet = sets.idSet)
23:24:46 T:408   DEBUG: Mysql commit transaction
23:24:46 T:408  NOTICE: CVideoDatabase::CleanDatabase: Cleaning videodatabase done. Operation took 00:12
Reply


Messages In This Thread
RE: HOW-TO:Share libraries using MySQL: Wiki Edition - by pnkiller78 - 2015-11-27, 07:07


Logout Mark Read Team Forum Stats Members Help
HOW-TO:Share libraries using MySQL: Wiki Edition2
This forum uses Lukasz Tkacz MyBB addons.