[peter0123]

Hi,

i test V16 and found one problem.
Windows, Android, Mysql & Shared User-Data

Code:

21:02:06 T:4236   ERROR: SQL: Undefined MySQL error: Code (1242)
                                            Query: SELECT type,url FROM art WHERE media_id=(SELECT idArtist from album_artist WHERE idalbum=-1 AND iOrder=0) AND media_type='artist'
21:02:06 T:4236   ERROR: CMusicDatabase::GetArtistArtForItem(-1) failed

Master => xbmc/xbmc/music/MusicDatabase.cpp

Code:

Z: 5433

NOW:   => std::string query = PrepareSQL("SELECT url FROM art WHERE media_id=(SELECT idArtist from %s_artist WHERE id%s=%i AND iOrder=0) AND media_type='artist' AND type='%s'", mediaType.c_str(), mediaType.c_str(), mediaId, artType.c_str());
  
FIXED: => std::string query = PrepareSQL("SELECT url FROM art WHERE media_id IN (SELECT idArtist from %s_artist WHERE id%s=%i AND iOrder=0) AND media_type='artist' AND type='%s'", mediaType.c_str(), mediaType.c_str(), mediaId, artType.c_str());
  
Z: 5514

NOW:   => std::string sql = PrepareSQL("SELECT type,url FROM art WHERE media_id=(SELECT idArtist from %s_artist WHERE id%s=%i AND iOrder=0) AND media_type='artist'", mediaType.c_str(), mediaType.c_str(), mediaId);

FIXED: => std::string sql = PrepareSQL("SELECT type,url FROM art WHERE media_id IN (SELECT idArtist from %s_artist WHERE id%s=%i AND iOrder=0) AND media_type='artist'", mediaType.c_str(), mediaType.c_str(), mediaId);

Log File: http://pastebin.com/m5Cv3mYG

Any one the same problem?

[Razze]

Well that code seems to be there since 2012/2014? Not sure why it shows up with 12 for you, might be due to your data.
https://github.com/xbmc/xbmc/commit/50bf...cb1844f5b0
https://github.com/xbmc/xbmc/commit/0148...3467777712

I don't think we should use a IN for the first one, as the result should be one value, it should be limited to 1.

PHP Code:

std::string query = PrepareSQL("SELECT url FROM art WHERE media_id=(SELECT idArtist from %s_artist WHERE id%s=%i AND iOrder=0 LIMIT 1) AND media_type='artist' AND type='%s'", mediaType.c_str(), mediaType.c_str(), mediaId, artType.c_str()); 


Needs testing in mysql.

Edit:
On another node, if this fails due to your data that means you have multiple duplicate ids on one of the *_artist tables. Which shouldn't be possible in the first place. So it's probably not it, but you might want to check.
These tables should have a unique key index, please check if it is there on your mysql, just to be sure.

[peter0123]

Hi!

We can check this:

0

Code:

SELECT `idArtist` FROM artist GROUP BY `idArtist` HAVING count(*) > 1

no primary key: 1525 (Tabelle: album_artist)

SELECT `idArtist` FROM album_artist GROUP BY `idArtist` HAVING count(*) > 1

no primary key: 4955

Code:

SELECT `idArtist` FROM song_artist GROUP BY `idArtist` HAVING count(*) > 1

Other: 0

Code:

SELECT `idAlbum` FROM album GROUP BY `idAlbum` HAVING count(*) > 1

same with 52 & 53 database version.
but the mysql error are only in V16 in no isengard machine..


i become more then one value with the modified sql:

Code:

SELECT type,url FROM art WHERE media_id IN (SELECT idArtist from album_artist WHERE idalbum=-1 AND iOrder=0) AND media_type='artist'

0 - 5 (6 insgesamt, Die Abfrage dauerte 0.0443 Sekunden.)
Die aktuelle Markierung enthält keine eindeutige ("unique") Spalte

as is the design what's wrong, it should never be a duplicate ID can be entered!
look at the values: this are the "problems" or not...

http://pastebin.com/5LYnmu06
http://pastebin.com/absKcaz1

___

with limit..

Code:

SELECT type,url FROM art WHERE media_id=(SELECT idArtist from album_artist WHERE idalbum=-1 AND iOrder=0 Limit 1) AND media_type='artist'

the script can work, no error in logs, or scrapper stops, but the db values

http://pastebin.com/YFUfBtq9


the problem is the db, and the code
After all, why it came to the incident?
and why only until V16?

Code:

SELECT type,url FROM art WHERE media_id IN (SELECT idArtist from album_artist WHERE idalbum=-1 AND iOrder=0) AND media_type='artist'

Edit: when you build test-version, please send me links. thx!

[peter0123]

Now i become troubles with the migrated Video DB Kodi hangs after i scrap manual a movie, i check the logs..
V15 Client run normal with old DB.
i have test the migrations process (db) 3 times before i start with other testing. I checked all changes.
important step is, to change sql user to "kodi" if other in use.
and the video+music db, need the normal name before you start the upgrade. "MyMusic" "MyVideos"
my last db names "kodi-music52" with advancedsettings.xml fails at upgrade. i copy and renamed each DB with PhpMyAdmin (500Movies, 25k Episodes & 100k A-Files) in 20 seconds !!!! best solutions, better then commands/tools in command line.

and the skin settings now are in the addons, and not at the global user share with the upgrade.
I test each possible error step.

i start with new tests and new db's.

Code:

Server: Localhost via UNIX socket
Server-Typ: MariaDB
Server Version: 5.5.43-MariaDB-log - Source distribution
Protokoll-Version: 10
Benutzer: root@localhost
Server Zeichensatz: UTF-8 Unicode (utf8)
Apache
Datenbank-Client Version: libmysql - mysqlnd 5.0.11-dev - 20120503 - $Id: 15d5c781cfcad91193dceae1d2cdd127674ddb3e $
PHP-Erweiterung: mysqli Dokumentation
phpmyadmin: Versionsinformationen: 4.3.0

[Milhouse]

i have test the migrations process (db) 3 times before i start with other testing. I checked all changes.
important step is, to change sql user to "kodi" if other in use.

Post a debug log for one of these database migrations - it's possibly failing during the migration.

[peter0123]

i can give you db backups when you want.
i have no debug log, from the last migration saved need to copy from backup all data and start a migration process again for this.

[peter0123]

i look again..

this i my problem:

Code:

Query: SELECT type,url FROM art WHERE media_id=(SELECT idArtist from album_artist WHERE idalbum=-1 AND iOrder=0) AND media_type='artist'

the mysql server make at this point an error, then the scrapper and kodi stuck

.
i run a test sql, and become 6 values. with the sql statement from kodi, mysql break at this point.



table art, art_id is primary and auto increment


table album_artist


idartist needs to be duplicated


we see here that ids


=> 2


=> 2


=> 2


=> 0



The Problem for Kodi is, that the Mysql Server make with the SQL Statement an Error.
I see no duplicated entrys in tables with autoincrement or primary key..


Kodi make no problem, only the mysql server with this design sql statement.
can anyone test this on older an newer mysql version?

Edit:
- http://forum.kodi.tv/showthread.php?tid=148073
- http://trac.kodi.tv/ticket/13713


the solution in the past (x years!) for the developer in the ticket: "AND iOrder=0"
now we know, the iOrder is the problem..
My highest number is "12".... most "0"... => 94949 entrys..

i see here a database design problem. and i think, older mysql version make no troubles,. please help me to verify.
correct sql statement with limit and you fix this fast.
thx

[peter0123]

ok, new start:

Start to take music folder to library, settings before:
=> Download additional info during updates" OFF
=> Override song tags with online information" OFF

Start to scrap artist, settings before:
=> Override song tags with online information" ON
=> Download additional info during updates" ON

Start to scrap albums, settings before:
=> Override song tags with online information" ON
=> Download additional info during updates" ON

SELECT type,url FROM art WHERE media_id=(SELECT idArtist from album_artist WHERE idalbum=-1 AND iOrder=0) AND media_type='artist'

=> OKAY, look good at first look.. old music-database problem.. no migration problem.
but we see, the old fix to help when more then 1 value returns, is nothing.
better the team integreat anything better, and a litte repair function for users before the upgrading from V15 to V16.
V15 is no problem, 4 kodis run with this database... only 2 with the new one now.
i think many people have this problem, v16 upgrade will show us this in the future. no on faults, i think thats a scrapper problem in the past, each Os freeze, shutdowns from user..or anything else

thx a lot for helping ! sry, i can't give you Reputations..

@Milhouse
@Razze

[Razze]

I'm not sure if I can follow you.

I've just set up an mysql instance and scraped some artists with the newest kodi version. No error so far.
It's happening when you try to scrap them correct?
That would mean it's something in your data or it was already fixed in a newer build.

[peter0123]

i think the problem was in the past, my testing which scrapper is the best

No problem with migration from VDB 93.94 or MDB 52.53.