[drivera]

Hi!!

I'm no stranger to Kodi, having run it in a shared DB setup for quite some time already.  This is the first time the automatic upgrade chokes on me. The DB server is a MySQL 8.0.11 instance.

This is the upgrade error:

14:37:47.169 T:139711353796352  NOTICE: Old database found - updating from version 60 to 72
14:38:31.698 T:139711353796352  NOTICE: Attempting to update the database kodi_music72 from version 60 to 72
14:41:39.772 T:139711353796352   ERROR: SQL: [kodi_music72] Undefined MySQL error: Code (1419)
                                            Query: CREATE TRIGGER tgrDeleteAlbum AFTER delete ON album FOR EACH ROW BEGIN  DELETE FROM song WHERE song.idAlbum = old.idAlbum;  DELETE FROM album_artist WHERE album_artist.idAlbum = old.idAlbum;  DELETE FROM album_source WHERE album_source.idAlbum = old.idAlbum;  DELETE FROM art WHERE media_id=old.idAlbum AND media_type='album'; END
14:41:39.772 T:139711353796352   ERROR: Exception updating database kodi_music72 from version 60 to 72
14:41:39.772 T:139711353796352   ERROR: Error updating database kodi_music72 from version 60 to 72

For the Video DB I get this other error:

14:41:42.881 T:139711353796352  NOTICE: Old database found - updating from version 107 to 116
14:42:41.870 T:139711353796352  NOTICE: Attempting to update the database kodi_videos116 from version 107 to 116
14:44:10.983 T:139711353796352   ERROR: SQL: [kodi_videos116] Undefined MySQL error: Code (1419)
                                            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
14:44:10.984 T:139711353796352   ERROR: Exception updating database kodi_videos116 from version 107 to 116
14:44:10.984 T:139711353796352   ERROR: Error updating database kodi_videos116 from version 107 to 116

I've tried to obliterate the new DB versions and let Kodi try and re-create them a few times, to no avail (as expected, really...it was just worth a shot ). I suspect this is a DB syntax issue, but obviously it's not something I can fix.

Any ideas?

Thanks!

[drivera]

Manually running these modified syntaxes worked:

DELIMITER $$
CREATE TRIGGER tgrDeleteAlbum AFTER delete ON album FOR EACH ROW
BEGIN
    DELETE FROM song WHERE song.idAlbum = old.idAlbum;
    DELETE FROM album_artist WHERE album_artist.idAlbum = old.idAlbum;
    DELETE FROM album_source WHERE album_source.idAlbum = old.idAlbum;
    DELETE FROM art WHERE media_id=old.idAlbum AND media_type='album';
END$$
DELIMITER ;

And...

DELIMITER $$
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$$
DELIMITER ;

But I don't know what else is missing from the upgrade process...

Cheers!

[drivera]

Nevermind... turns out at some point the Kodi user's SUPER privilege got revoked, fixed my problem as per http://wiki.ispirer.com/sqlways/troubles...ry-logging

In particular, I executed

 mysql> SET GLOBAL log_bin_trust_function_creators = 1;

As root and that cleared up the problem.

Kudos to me for not RTFM earlier

Cheers!