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 - Dennisreneholm - 2017-02-12

I got some problems upgrading from 16.1 to 17.0.
I'm using a shared MySQL database, running on WAMP.
My MySQL is version 5.0.51b.

I have closed all instances of Kodi, so nothing should be using the database.
I open Kodi on my Windows 10 PC, and i see the upgrading messages on the start screen.
After a while, it goes to the Kodi menu, but it says i have no media.

If i check my databases with phpmyadmin, i can see that myvideos99 has 34 tables, but myvideos107 only has 30.

I have tried dropping the 107 database and restarting the process, but it's the same result.

What's going wrong, and how do i correct it?


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

(2017-02-12, 15:52)Dennisreneholm Wrote: What's going wrong, and how do i correct it?

Nobody knows unless you post a debug log (wiki).

1. Enable debug logging in Kodi
2. Stop Kodi
3. Drop your MyVideos107 database
4. Start Kodi and allow the database migration to finish
5. If you still have no media, post your complete kodi.log file on pastebin.com, then paste the link in this thread.


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - Dennisreneholm - 2017-02-12

Log is bigger than pastebin allows me to upload.
Dropbox link the the log file here:
https://www.dropbox.com/s/drnrd75f4v48dwn/kodi.log?


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

(2017-02-12, 17:05)Dennisreneholm Wrote: Log is bigger than pastebin allows me to upload.
Dropbox link the the log file here:
https://www.dropbox.com/s/drnrd75f4v48dwn/kodi.log?

Code:
16:00:28.178 T:4512    INFO: CVideoDatabase::CreateAnalytics - creating triggers
16:00:28.178 T:4512   DEBUG: Mysql execute: CREATE TRIGGER delete_movie AFTER DELETE ON movie FOR EACH ROW BEGIN DELETE FROM genre_link WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM actor_link WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM director_link WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM studio_link WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM country_link WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM writer_link WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM movielinktvshow WHERE idMovie=old.idMovie; DELETE FROM art WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM tag_link WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM rating WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM uniqueid WHERE media_id=old.idMovie AND media_type='movie'; END
16:00:28.179 T:4512   ERROR: SQL: [MyVideos107] Undefined MySQL error: Code (1227)
                                            Query: CREATE TRIGGER delete_movie AFTER DELETE ON movie FOR EACH ROW BEGIN DELETE FROM genre_link WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM actor_link WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM director_link WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM studio_link WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM country_link WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM writer_link WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM movielinktvshow WHERE idMovie=old.idMovie; DELETE FROM art WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM tag_link WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM rating WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM uniqueid WHERE media_id=old.idMovie AND media_type='movie'; END
16:00:28.180 T:4512   ERROR: Exception updating database MyVideos107 from version 99 to 107
16:00:28.180 T:4512   ERROR: Error updating database MyVideos107 from version 99 to 107
16:00:28.180 T:4512   DEBUG: Mysql rollback transaction

Error 1227 is a permission problem (your MySQL user doesn't have the required privilege to create a trigger).

Did you grant all privileges to your "m3dia" user?

This thread might help: http://forum.kodi.tv/showthread.php?tid=163035


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - Dennisreneholm - 2017-02-13

Ahh, that sounds right!

I did switch from a xbmc user to m3dia last year.

Hmm, is it possible to get this working without granting ALL permissions to the MySQL server?
What is specifically needed for the user?


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - Milhouse - 2017-02-13

Not sure, but suspect you'll need to grant most privileges as your user will be creating new databases, views, indexes, triggers.


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - Dilligaf - 2017-02-13

(2017-02-13, 09:15)Dennisreneholm Wrote: Ahh, that sounds right!

I did switch from a xbmc user to m3dia last year.

Hmm, is it possible to get this working without granting ALL permissions to the MySQL server?
What is specifically needed for the user?
Is xbmc still a valid MySql user? Maybe swith to user xbmc to let the library upgrade then switch back to your m3dia user? Otherwise create a second user with full control to use for the upgrade process then later delete/retire it


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - Dennisreneholm - 2017-02-13

For some reason, if i delete the user xbmc, nothing works :/ Never got that working quite the way i wanted.

I want to use m3dia, and only that user.

I don't understand why it's not working with the current privileges though.

As I see it, the user m3dia has full access for every database named myvideosXXX, since the user has full access to myvideos%.
Image
___________________________________________________________
Image


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - Milhouse - 2017-02-13

Try adding the SUPER privilege (which is apparently required in order to create triggers): http://stackoverflow.com/a/11946043


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - Dennisreneholm - 2017-02-13

My phpmyadmin is not the same as the one in the link, i don't have that option for SUPER.
Should i run this query then?
GRANT SUPER ON `myvideos%`.* TO 'm3dia'


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - Dilligaf - 2017-02-13

I think you need "create" under global privileges as well as you will be creating a new myvideos database


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - Dilligaf - 2017-02-13

"super" is in global>administration


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - Dennisreneholm - 2017-02-13

(2017-02-13, 15:27)Dennisreneholm Wrote: My phpmyadmin is not the same as the one in the link, i don't have that option for SUPER.
Should i run this query then?
GRANT SUPER ON `myvideos%`.* TO 'm3dia'

Guess not
#1221 - Incorrect usage of DB GRANT and GLOBAL PRIVILEGES


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - Dilligaf - 2017-02-13

Easiest would be "check all" in global, save, start Kodi and let it update the database, once updated and workin go back in and remove the global privileges


RE: HOW-TO:Share libraries using MySQL: Wiki Edition - Dennisreneholm - 2017-02-13

(2017-02-13, 15:27)Dilligaf Wrote: I think you need "create" under global privileges as well as you will be creating a new myvideos database
myvideos107 does get created, so i don't that that's the issue

(2017-02-13, 15:27)Dilligaf Wrote: "super" is in global>administration
I don't see a menu that says global, or administration.

(2017-02-13, 15:27)Dilligaf Wrote: Easiest would be "check all" in global, save, start Kodi and let it update the database, once updated and workin go back in and remove the global privileges
Might be the easiest now, but that still means i have to do it manuel "everytime" there is an update, which i would rather not.


This forum uses Lukasz Tkacz MyBB addons.