HOW-TO:Share libraries using MySQL: Wiki Edition
(2015-10-24, 00:56)Milhouse Wrote:
(2015-10-23, 23:09)Raytestrak Wrote: Just tried the new 5.7.9 MySQL. All looked well, until I tried to clean my database. It couldn't remove items from my database be doing a cleanup, only manual deletion worked. It was a clean install by the way. Back to 5.6 it is ...

Your debug log (wiki) would have been useful.

I'm experiencing the same problem that Raytestrak.
Database cleaning in MySQL 5.7 fails.

Code:
23:57:41 T:2832   DEBUG: Mysql Start transaction
23:57:41 T:2832   DEBUG: DialogProgress::StartModal called
23:57:41 T:2832   DEBUG: ------ Window Init (DialogProgress.xml) ------
23:57:42 T:2832   DEBUG: SECTION:LoadDLL(special://xbmcbin/system/libnfs.dll)
23:57:42 T:2832   DEBUG: NFS: Context for 127.0.0.1/movies not open - get a new context.
23:57:42 T:2832   DEBUG: NFS: Connected to server 127.0.0.1 and export /movies
23:57:42 T:2832   DEBUG: NFS: chunks: r/w 8192/8192
23:57:54 T:2832   DEBUG: NFS: Context for 127.0.0.1/tvshows not open - get a new context.
23:57:54 T:2832   DEBUG: NFS: Connected to server 127.0.0.1 and export /tvshows
23:57:54 T:2832   DEBUG: NFS: chunks: r/w 8192/8192
23:57:54 T:2832   DEBUG: NFS: Refreshing context for 127.0.0.1/movies, old: 1349041, new: 1361338
23:57:54 T:2832   DEBUG: NFS: Using cached context.
23:57:54 T:2832   DEBUG: NFS: Refreshing context for 127.0.0.1/tvshows, old: 1361267, new: 1361348
23:57:54 T:2832   DEBUG: NFS: Using cached context.
23:57:54 T:2832   DEBUG: NFS: Refreshing context for 127.0.0.1/movies, old: 1361338, new: 1361437
23:57:54 T:2832   DEBUG: NFS: Using cached context.
23:57:54 T:2832   DEBUG: NFS: Refreshing context for 127.0.0.1/tvshows, old: 1361348, new: 1361466
23:57:54 T:2832   DEBUG: NFS: Using cached context.
23:57:54 T:2832   DEBUG: NFS: Refreshing context for 127.0.0.1/movies, old: 1361437, new: 1361520
23:57:54 T:2832   DEBUG: NFS: Using cached context.
23:57:54 T:2832   DEBUG: NFS: Refreshing context for 127.0.0.1/tvshows, old: 1361466, new: 1361521
23:57:54 T:2832   DEBUG: NFS: Using cached context.
23:57:54 T:2832   DEBUG: NFS: Refreshing context for 127.0.0.1/movies, old: 1361520, new: 1361538
23:57:54 T:2832   DEBUG: NFS: Using cached context.
23:57:55 T:2832   DEBUG: NFS: Refreshing context for 127.0.0.1/tvshows, old: 1361521, new: 1362842
23:57:55 T:2832   DEBUG: NFS: Using cached context.
23:57:56 T:2832   DEBUG: NFS: Refreshing context for 127.0.0.1/movies, old: 1361538, new: 1362907
23:57:56 T:2832   DEBUG: NFS: Using cached context.
23:57:56 T:2832   DEBUG: CVideoDatabase::CleanDatabase: Cleaning paths that don't exist and have content set...
23:57:56 T:2832   DEBUG: NFS: Context for 127.0.0.1/artwork not open - get a new context.
23:57:56 T:2832   DEBUG: NFS: Connected to server 127.0.0.1 and export /artwork
23:57:56 T:2832   DEBUG: NFS: chunks: r/w 8192/8192
23:57:56 T:2832   DEBUG: NFS: Refreshing context for 127.0.0.1/movies, old: 1362907, new: 1363156
23:57:56 T:2832   DEBUG: NFS: Using cached context.
23:57:56 T:2832   DEBUG: NFS: Refreshing context for 127.0.0.1/tvshows, old: 1362842, new: 1363305
23:57:56 T:2832   DEBUG: NFS: Using cached context.
23:57:56 T:2832   DEBUG: NFS: Refreshing context for 127.0.0.1/movies, old: 1363156, new: 1363306
23:57:56 T:2832   DEBUG: NFS: Using cached context.
23:57:56 T:2832   DEBUG: NFS: Refreshing context for 127.0.0.1/tvshows, old: 1363305, new: 1363306
23:57:56 T:2832   DEBUG: NFS: Using cached context.
23:57:56 T:2832   DEBUG: NFS: Refreshing context for 127.0.0.1/movies, old: 1363306, new: 1363307
23:57:56 T:2832   DEBUG: NFS: Using cached context.
23:57:56 T:2832   DEBUG: NFS: Refreshing context for 127.0.0.1/tvshows, old: 1363306, new: 1363336
23:57:56 T:2832   DEBUG: NFS: Using cached context.
23:57:56 T:2832   DEBUG: NFS: Refreshing context for 127.0.0.1/movies, old: 1363307, new: 1363337
23:57:56 T:2832   DEBUG: NFS: Using cached context.
23:57:56 T:2832   DEBUG: NFS: Refreshing context for 127.0.0.1/tvshows, old: 1363336, new: 1363337
23:57:56 T:2832   DEBUG: NFS: Using cached context.
23:57:56 T:2832   DEBUG: NFS: Refreshing context for 127.0.0.1/movies, old: 1363337, new: 1363337
23:57:56 T:2832   DEBUG: NFS: Using cached context.
23:57:56 T:2832   DEBUG: CVideoDatabase::CleanDatabase: Cleaning tvshow table
23:57:56 T:2832   DEBUG: CVideoDatabase::CleanDatabase: Cleaning path table
23:57:56 T:2832   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:57:56 T:2832   ERROR: SQL: Undefined MySQL error: Code (1093)
                                            Query: 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:57:56 T:2832   ERROR: CVideoDatabase::CleanDatabase failed
23:57:56 T:2832   DEBUG: Mysql rollback transaction

In MySQL Workbench if I copy and paste the last SQL query, the db throws the following error
Code:
Error Code: 1093. You can't specify target table 'path' for update in FROM clause

I have a bit of experience in SQL Scripting, mostly T-SQL, but it looks like some problem while directly referencing the path table inside the sub-queries that are being joined in the outer tables to obtain the orphaned keys that are going to be deleted in the path table. So I tried to rewrite the query, but I'm not sure if the syntax is correct and it's not going to create inconsistency in the db.
Oh, by the way, in the MySQL 5.7 Reference manual, they say that, I think that is something they recently changed.
Quote:Subqueries

You cannot delete from a table and select from the same table in a subquery.

Anyway, it's just a snipset Smile and I'm glad to help.
Code:
-- SELECT    t1.*
DELETE    t1
FROM    path t1
        LEFT JOIN path t2 ON t1.idPath = t2.idParentPath
        LEFT JOIN files t3 ON t1.idPath = t3.idPath
        LEFT JOIN tvshowlinkpath t4 ON t1.idPath = t4.idPath
        LEFT JOIN movie t5 ON t1.idPath = t5.c23
        LEFT JOIN episode t6 ON t1.idPath = t6.c19
        LEFT JOIN musicvideo t7 ON t1.idPath = t7.c14

WHERE    
        (t1.strContent IS NULL OR t1.strContent = '')
        AND (t1.strSettings IS NULL OR t1.strSettings = '')
        -- AND (t1.strHash IS NULL OR t1.strHash = '')
        AND (t1.exclude IS NULL OR t1.exclude != 1)
        AND t2.idParentPath IS NULL
        AND t3.idPath IS NULL
        AND t4.idPath IS NULL
        AND t5.c23 IS NULL
        AND t6.c19 IS NULL
        AND t7.c14 IS NULL;
Reply


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


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