2015-11-27, 07:07
(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