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 - Raytestrak - 2015-10-26

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

It wasn't a request for help, I was just posting my experience. My logs have already been cleared, so I can't post any extra info. Sorry.


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - misterhek - 2015-10-29

Just a heads up for anyone using mariadb 10.1.8, there seems to be an issue with the way totalCount is calculated in the tvshowcounts table of the mysql database. For some reason, when using 10.1.8, the count is always doubled-1. So if you have 50 episodes of a show, the totalCount will erroneously be 99 (50*2 -1).

This only happens with mariadb 10.1.8. Downgrading to 10.0.21 solves the problem. Not sure if this should be reported as a kodi bug or mariadb bug, but I'll be sticking to 10.0.21 until it's ironed out. Unfortunately, arch doesn't have 10.0.22 in their repositories since that version fixes the crash from 10.0.21 when playing songs with Kodi through a mysql database.


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - bounguine - 2015-11-24

Nasty problem after reinstalling Ubuntu on the server with MySQL DB.
On backups raised Kodi-clients (with advancedsettings.xml unchanged) are unable to read MyMovies52 and MyVideos93 as there're no views.
All privileges are granted to XBMC-user.

http://pastebin.com/345xzsdg

I dropped the schemas but Kody is unable to update DB using old MyMovies and MyVideos:

http://pastebin.com/fM4fAyVf


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - marantz - 2015-11-24

I just reported the mariadb 10.1 problem
https://mariadb.atlassian.net/browse/MDEV-9181?filter=-2


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - pnkiller78 - 2015-11-26

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



RE: HOW-TO:Share libraries using MySQL: Wiki Edition - Milhouse - 2015-11-26

Thanks. Unfortunately that's a fairly common MySQL "gotcha" and I'd be surprised if it's new in 5.7.x... that query will need to be adapted, most likely with a temporary table, or iterate over a select and perform individual delete queries.. Sad


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - Milhouse - 2015-11-26

Thought this had broken before... PR5007.

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

This restriction existed in v5.5, so not sure what has changed in 5.7 that makes it even more restrictive...

Edit: Here we go, query optimizer changed in 5.7.6... http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-6.html#mysqld-5-7-6-optimizer


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - Milhouse - 2015-11-26

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


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - pnkiller78 - 2015-11-27

(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



RE: HOW-TO:Share libraries using MySQL: Wiki Edition - Milhouse - 2015-11-27

Ok good, thanks (although please don't post logs to the forum in future!) There will hopefully be a fix in Kodi 16 beta 3 (see PR8393) that means sever configuration is not required.


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - Milhouse - 2015-11-28

MySQL 5.7.x support should be fixed in nightly builds after 27 November (and in Kodi 16 beta 3, once that is released).


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - Rhys15 - 2015-12-02

Quick question, i'm running Kodi 15.2 and will be setting up a MySQL database for my library. What is the recommended version of MySQL to install? The Wiki says 5.5.42 but that particularly version is not available on the website?

Also my library currently uses local paths to my media. I understand for other devices to work with MySQL, it has to be full SMB names. Is there a way to convert my paths or would it be easier to just rescan my media into the library?

Edit: Also seen someone mention in a different thread that Kodi may be dumping support for MySQL databases in the future... is this true? I assume there would be an alternative if this was the case?


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - Milhouse - 2015-12-02

Use whatever MySQL version is available - 5.7.x should work with latest nightles, or stick to 5.5.x/5.6.x if using a release version.

If your library is based on local paths, too bad - dump it and start again from scratch with network paths. You'll waste too much time trying to fix things, and probably never get it right. If you want to backup/restore your watched states the simplest option is (IMHO) the script in my sig.

The intended replacement for MySQL is UPnP, but that's still some way of yet - probably Kodi 18 at the earliest, so MySQL still has some life in it yet.


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - flhthemi - 2015-12-02

If you want 5.5.46 for Windows it can be found here: http://dev.mysql.com/downloads/file/?id=459073

Down at the bottom left corner of the page click "No thanks, just start my download".


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - Rhys15 - 2015-12-02

(2015-12-02, 14:46)Milhouse Wrote: Use whatever MySQL version is available - 5.7.x should work with latest nightles, or stock to 5.6.x if using a release version.

If your library is based on local paths, too bad - dump it and start again from scratch with network paths. You'll waste too much time trying to fix things, and probably never get it right. If you want to backup/restore your watched states the simplest option is (IMHO) the script in my sig.

The intended replacement for MySQL is UPnP, but that's still some way of yet - probably Kodi 18 at the earliest, so MySQL still has some life in it yet.

I'm using 15.2 Release so I guess 5.6.x works OK? The library isn't an issue, it's not huge and shouldn't take too long so i'll rebuild it in MySQL.

Good to hear MySQL has some life yet, just hope moving over to UPnP if that comes next won't be too much of a painful process.

(2015-12-02, 15:54)flhthemi Wrote: If you want 5.5.46 for Windows it can be found here: http://dev.mysql.com/downloads/file/?id=459073

Down at the bottom left corner of the page click "No thanks, just start my download".

I found 5.5.46 on there but would rather use the most up to date version of MySQL that is completely stable with Kodi. Going by Milhouse' post above, 5.6.x is the current stable version?


This forum uses Lukasz Tkacz MyBB addons.