[Dennisreneholm]

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?

[Milhouse]

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.

[Dennisreneholm]

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

[Milhouse]

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

[Dennisreneholm]

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?

[Milhouse]

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

[Dilligaf]

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

[Dennisreneholm]

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

___________________________________________________________

[Milhouse]

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

[Dennisreneholm]

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'

[Dilligaf]

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

[Dilligaf]

"super" is in global>administration

[Dennisreneholm]

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

[Dilligaf]

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

[Dennisreneholm]

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

"super" is in global>administration

I don't see a menu that says global, or administration.

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.