[cat69]

Hello,

syncing the music libray to Yatse fails, while movies and TV-shows work fine.
The issue is a MySQL error:

From Yatse Log:
[646] {"id":19,"jsonrpc":"2.0","method":"AudioLibrary.GetArtists","params":{"albumartistsonly":false,"properties":["style","description","born","died","thumbnail","instrument","genre","fanart","songgenres","isalbumartist"],"limits":{"end":750,"start":0}}}
[646] {"error":{"code":-32603,"message":"Internal error."},"id":19,"jsonrpc":"2.0"}

From Kodi Log:
17:24:50.689 T:14196   ERROR: SQL: [MyMusic72] Undefined MySQL error: Code (1064)
Query: SELECT a1.*, isSong, idSongGenreAlbum, idSongGenreSong, strSongGenreAlbum, strSongGenreSong, art.art_id AS idArt, art.type AS artType, art.url AS artURL FROM (SELECT artist.idArtist, strArtist, strInstruments, strBiography, strGenres, strStyles, strBorn, strDied FROM artist  WHERE ((EXISTS (SELECT 1 FROM song_artist WHERE song_artist.idArtist = artist.idArtist AND song_artist.idRole = 1) OR EXISTS (SELECT 1 FROM album_artist WHERE album_artist.idArtist = artist.idArtist)) AND (artist.strArtist != '')) AND (artist.strArtist <> 'Verschiedene Interpreten') ORDER BY artist.idArtist LIMIT 750) AS a1 JOIN ((SELECT album_artist.idArtist AS id, 0 AS isSong, song_genre.idGenre AS idSongGenreAlbum, genre.strGenre AS strSongGenreAlbum, -1 AS idSongGenreSong, '' AS strSongGenreSong FROM album_artist JOIN song ON song.idAlbum = album_artist.idAlbum LEFT JOIN song_genre ON song_genre.idSong = song.idSong LEFT JOIN genre ON genre.idGenre = song_genre.idGenre GROUP BY album_artist.idArtist, genre.idGenre UNION SELECT song_artist.idArtist AS id, 1 AS isSong, -1 AS idSongGenreAlbum, '' AS strSongGenreAlbum, song_genre.idGenre AS idSongGenreSong, genre.strGenre AS strSongGenreSong FROM song_artist LEFT JOIN song_genre ON song_genre.idSong = song_artist.idSong LEFT JOIN genre ON genre.idGenre = song_genre.idGenre WHERE song_artist.idRole = 1 GROUP BY song_artist.idArtist, genre.idGenre) AS albumSong) ON id = a1.idArtist LEFT JOIN art ON art.media_id = a1.idArtist AND art.media_type = 'artist' GROUP BY a1.idArtist, idSongGenreAlbum, idSongGenreSong, art.art_id ORDER BY a1.idArtist, isSong, idSongGenreAlbum, idSongGenreSong, arttype

Executing the query directly in MySQL Workbench:
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ON id = a1.idArtist LEFT JOIN art ON art.media_id = a1.idArtist AND art.media_' at line 1       

I use the latest develpoment build:
Kodi (18.0-BETA1 Git:20180803-ed7ecfdf15). Platform: Windows NT x86 64-bit
I recently upgraded MySQL to version 8.0.12 coming from 5.7.

Thank you.

[DaveBlake]

Thanks for this report. I believe this issue is related to the version of MySQL you are using. The SQL that Kodi generates works fine with v5.7, but I am yet to test with v8.0.12 of MySQL

[cat69]

I played around a little with the statement:

There is a section from "AS a1 JOIN ((SELECT album_artist.idArtist AS id" until "genre.idGenre) AS albumSong ) ON id = a1.idArtist"
When removing the outer brackets to "AS a1 JOIN (SELECT album_artist.idArtist AS id" and "genre.idGenre) AS albumSong ON id = a1.idArtist" the statement is at least executed without any syntax error.

Though what I don't know is if the result is still what is intended. I'm not so familiar with SQL and that are definitely too much JOIN statements for me ;-)

Here's the statement that works:
 SELECT a1.*, isSong, idSongGenreAlbum, idSongGenreSong, strSongGenreAlbum, strSongGenreSong, art.art_id AS idArt, art.type AS artType, art.url AS artURL
    FROM ( SELECT artist.idArtist, strArtist, strInstruments, strBiography, strGenres, strStyles, strBorn, strDied FROM artist  
            WHERE ( ( EXISTS ( SELECT 1 FROM song_artist WHERE song_artist.idArtist = artist.idArtist AND song_artist.idRole = 1
                                        ) OR
                            EXISTS ( SELECT 1 FROM album_artist WHERE album_artist.idArtist = artist.idArtist
                                        )
                          ) AND ( artist.strArtist != '')
                       ) AND ( artist.strArtist <> 'Verschiedene Interpreten' )
            ORDER BY artist.idArtist LIMIT 750
       ) AS a1
              JOIN ( SELECT album_artist.idArtist AS id, 0 AS isSong, song_genre.idGenre AS idSongGenreAlbum, genre.strGenre AS strSongGenreAlbum, -1 AS idSongGenreSong, '' AS strSongGenreSong FROM album_artist
                      JOIN song ON song.idAlbum = album_artist.idAlbum
                        LEFT JOIN song_genre ON song_genre.idSong = song.idSong
                         LEFT JOIN genre ON genre.idGenre = song_genre.idGenre GROUP BY album_artist.idArtist, genre.idGenre
                          UNION SELECT song_artist.idArtist AS id, 1 AS isSong, -1 AS idSongGenreAlbum, '' AS strSongGenreAlbum, song_genre.idGenre AS idSongGenreSong, genre.strGenre AS strSongGenreSong FROM song_artist
                           LEFT JOIN song_genre ON song_genre.idSong = song_artist.idSong
                             LEFT JOIN genre ON genre.idGenre = song_genre.idGenre WHERE song_artist.idRole = 1 GROUP BY song_artist.idArtist, genre.idGenre) AS albumSong ON id = a1.idArtist  
                             
                LEFT JOIN art ON art.media_id = a1.idArtist AND art.media_type = 'artist' GROUP BY a1.idArtist, idSongGenreAlbum, idSongGenreSong, art.art_id ORDER BY a1.idArtist, isSong, idSongGenreAlbum, idSongGenreSong, arttype

Regards

[DaveBlake]

Thanks, those brackets are the issue.
I had been pulling the query apart in all kinds of clever ways to no avial but not tried those brackets. They can be removed without changing the results of the query.  I will raise a fix later today, and generate a test build. If you could be so kind to test it out that would be great, I'll post a link to the build here.

The fact that SQLite and MySQL 5.7 is totally happy with the original syntax, but that 8.0.12 isn't does make me worry what other unexpected changes could be lurking. But I know what kinds of thing to look for now.

[DaveBlake]

Here you go @cat69 http://mirrors.kodi.tv/test-builds/windo...ge-x64.exe
a test build for Windows with this MySQL 8.0 issued fixed.

[cat69]

Thanks a lot, works perfectly now.

[DaveBlake]

Excellent, thanks for the very quick feedback. I will merge so it is in from the next nightly