Bugzilla – Bug 7035
Sorting broken for Genres list and Album tracks
Last modified: 2009-09-08 09:20:23 UTC
Genres under Home->Music Library->Genres aren't sorted. For example, I have Electronica, Dance, Comedy, Ambient, Jazz... It's probably not relevant, but I tag tracks with multiple genres, using a separator character e.g. "Electronica;Ambient".
I haven't seen this. QA: can you please work with Max to reproduce?
They sort fine for me too. Max, are you sure you have latest jive and sc software?
I am running the latest SC and Jive - https://svn.slimdevices.com/repos/slim/branches/7.0/server R17367; Version 7.0 r1853. The genre list is also not sorted when displayed in the web interface. Looking at the genres table in the database, the list is being returned in the order I get if I do a 'select name from genres' with no sort specified. I haven't rescanned in a while, in case that makes any difference.
QA to reproduce making sure to use tracks with multiple genres separated by a delimiter. Max, Andy requests that you turn on SQL debugging so we can see what the query is.
[22:42:57.6191] Slim::Schema::ResultSet::Base::generateConditionsFromFilters (142) levelMap: { album => "track", contributor => "album", genre => "contributor" } [22:42:57.6205] Slim::Schema::ResultSet::Base::generateConditionsFromFilters (143) filters : {} [22:42:57.6219] Slim::Schema::ResultSet::Base::generateConditionsFromFilters (189) find: {} [22:42:57.6230] Slim::Schema::ResultSet::Base::descend (202) $self->result_class: [Slim::Schema::Genre] [22:42:57.6238] Slim::Schema::ResultSet::Base::descend (225) Working on level: [Genre] [22:42:57.6249] Slim::Schema::ResultSet::Base::descend (227) $self->result_source->schema->source($level)->result_class: [Slim::Schema::Genre] [22:42:57.6260] Slim::Schema::ResultSet::Base::descend (235) Calling method: [browse] [22:42:57.6293] Slim::Schema::Debug::query_start (26) SELECT COUNT( DISTINCT( me.id ) ) FROM genres me: [22:42:57.6337] Slim::Schema::Debug::query_start (26) SELECT LEFT(me.namesort, 1), COUNT( DISTINCT(me.id) ) FROM genres me GROUP BY LEFT(me.namesort, 1) ORDER BY me.namesort: [22:42:57.6445] Slim::Schema::Debug::query_start (26) SELECT me.id, me.name, me.namesort, me.namesearch, me.musicmagic_mixable FROM genres me GROUP BY me.id ORDER BY me.namesort LIMIT 37: [22:42:57.7051] Slim::Schema::Debug::query_start (26) SELECT me.name, me.value FROM metainformation me WHERE ( name = ? ): 'isScanning' [22:42:57.7119] Slim::Schema::Debug::query_start (26) SELECT COUNT( DISTINCT( me.id ) ) FROM tracks me WHERE ( me.audio = ? ): '1' [22:42:57.7167] Slim::Schema::Debug::query_start (26) SELECT COUNT( DISTINCT( me.id ) ) FROM albums me: [22:42:57.7231] Slim::Schema::Debug::query_start (26) SELECT COUNT( DISTINCT( me.id ) ) FROM contributors me LEFT JOIN contributor_album contributorAlbums ON ( contributorAlbums.contributor = me.id ) JOIN albums album ON ( album.id = contributorAlbums.album ) WHERE ( ( ( album.compilation IS NULL ) OR ( album.compilation = ? ) ) AND contributorAlbums.role IN ( ?, ?, ?, ? ) ): '0', '1', '2', '4', '5' [22:42:57.7263] Slim::Web::Pages::addLibraryStats (234) (Level: genre, previousLevel: track) Found 6170 songs, 398 albums & 193 artists [22:43:06.0232] Slim::Schema::Debug::query_start (26) SELECT me.name, me.value FROM metainformation me WHERE ( name = ? ): 'isScanning' [22:43:06.0281] Slim::Schema::Debug::query_start (26) SELECT COUNT( * ) FROM albums me: [22:43:06.0350] Slim::Schema::Debug::query_start (26) SELECT COUNT( DISTINCT( me.id ) ) FROM contributors me LEFT JOIN contributor_album contributorAlbums ON ( contributorAlbums.contributor = me.id ) JOIN albums album ON ( album.id = contributorAlbums.album ) WHERE ( ( ( album.compilation IS NULL ) OR ( album.compilation = ? ) ) AND contributorAlbums.role IN ( ?, ?, ?, ? ) ): '0', '1', '2', '4', '5' [22:43:06.0397] Slim::Schema::Debug::query_start (26) SELECT COUNT( * ) FROM genres me: [22:43:06.0451] Slim::Schema::Debug::query_start (26) SELECT COUNT( DISTINCT( me.id ) ) FROM tracks me WHERE ( me.audio = ? ): '1' The relevant bit is "SELECT me.id, me.name, me.namesort, me.namesearch, me.musicmagic_mixable FROM genres me GROUP BY me.id ORDER BY me.namesort LIMIT 37". It's your GROUP BY that's messing things up.
Output from that query (run manually) mysql> SELECT me.id, me.name, me.namesort, me.namesearch, me.musicmagic_mixable FROM genres me GROUP BY me.id ORDER BY me.namesort\G *************************** 1. row *************************** id: 1 name: Electronica namesort: ELECTRONICA namesearch: ELECTRONICA musicmagic_mixable: NULL *************************** 2. row *************************** id: 2 name: Dance namesort: DANCE namesearch: DANCE musicmagic_mixable: NULL *************************** 3. row *************************** id: 3 name: Comedy namesort: COMEDY namesearch: COMEDY musicmagic_mixable: NULL *************************** 4. row *************************** id: 4 name: Ambient namesort: AMBIENT namesearch: AMBIENT musicmagic_mixable: NULL *************************** 5. row *************************** id: 5 name: Jazz namesort: JAZZ namesearch: JAZZ musicmagic_mixable: NULL *************************** 6. row *************************** id: 6 name: Rock namesort: ROCK namesearch: ROCK musicmagic_mixable: NULL *************************** 7. row *************************** id: 7 name: World namesort: WORLD namesearch: WORLD musicmagic_mixable: NULL *************************** 8. row *************************** id: 8 name: Folk namesort: FOLK namesearch: FOLK musicmagic_mixable: NULL *************************** 9. row *************************** id: 9 name: Blues namesort: BLUES namesearch: BLUES musicmagic_mixable: NULL *************************** 10. row *************************** id: 10 name: Musicals namesort: MUSICALS namesearch: MUSICALS musicmagic_mixable: NULL *************************** 11. row *************************** id: 11 name: Rock/Rock namesort: ROCK ROCK namesearch: ROCK ROCK musicmagic_mixable: NULL *************************** 12. row *************************** id: 12 name: Classical namesort: CLASSICAL namesearch: CLASSICAL musicmagic_mixable: NULL *************************** 13. row *************************** id: 13 name: Baroque namesort: BAROQUE namesearch: BAROQUE musicmagic_mixable: NULL *************************** 14. row *************************** id: 14 name: Christmas namesort: CHRISTMAS namesearch: CHRISTMAS musicmagic_mixable: NULL *************************** 15. row *************************** id: 15 name: Classical/Classical namesort: CLASSICAL CLASSICAL namesearch: CLASSICAL CLASSICAL musicmagic_mixable: NULL *************************** 16. row *************************** id: 16 name: Rock/Mellow namesort: ROCK MELLOW namesearch: ROCK MELLOW musicmagic_mixable: NULL *************************** 17. row *************************** id: 17 name: Classical/Modern namesort: CLASSICAL MODERN namesearch: CLASSICAL MODERN musicmagic_mixable: NULL *************************** 18. row *************************** id: 18 name: Romantic namesort: ROMANTIC namesearch: ROMANTIC musicmagic_mixable: NULL *************************** 19. row *************************** id: 19 name: Jazz/Modern namesort: JAZZ MODERN namesearch: JAZZ MODERN musicmagic_mixable: NULL *************************** 20. row *************************** id: 20 name: Spoken Comedy namesort: SPOKEN COMEDY namesearch: SPOKEN COMEDY musicmagic_mixable: NULL *************************** 21. row *************************** id: 21 name: Audio Books namesort: AUDIO BOOKS namesearch: AUDIO BOOKS musicmagic_mixable: NULL *************************** 22. row *************************** id: 22 name: Early Music namesort: EARLY MUSIC namesearch: EARLY MUSIC musicmagic_mixable: NULL *************************** 23. row *************************** id: 23 name: Soundtrack namesort: SOUNDTRACK namesearch: SOUNDTRACK musicmagic_mixable: NULL *************************** 24. row *************************** id: 24 name: Musical Comedy namesort: MUSICAL COMEDY namesearch: MUSICAL COMEDY musicmagic_mixable: NULL *************************** 25. row *************************** id: 25 name: Funk namesort: FUNK namesearch: FUNK musicmagic_mixable: NULL *************************** 26. row *************************** id: 26 name: Tango namesort: TANGO namesearch: TANGO musicmagic_mixable: NULL *************************** 27. row *************************** id: 27 name: Misc namesort: MISC namesearch: MISC musicmagic_mixable: NULL *************************** 28. row *************************** id: 28 name: Jazz/Classical namesort: JAZZ CLASSICAL namesearch: JAZZ CLASSICAL musicmagic_mixable: NULL *************************** 29. row *************************** id: 29 name: Soul namesort: SOUL namesearch: SOUL musicmagic_mixable: NULL *************************** 30. row *************************** id: 30 name: Acid Jazz namesort: ACID JAZZ namesearch: ACID JAZZ musicmagic_mixable: NULL *************************** 31. row *************************** id: 31 name: Folk/Rock namesort: FOLK ROCK namesearch: FOLK ROCK musicmagic_mixable: NULL *************************** 32. row *************************** id: 32 name: New Age namesort: NEW AGE namesearch: NEW AGE musicmagic_mixable: NULL *************************** 33. row *************************** id: 33 name: Symphony namesort: SYMPHONY namesearch: SYMPHONY musicmagic_mixable: NULL *************************** 34. row *************************** id: 34 name: R&B namesort: R B namesearch: R B musicmagic_mixable: NULL *************************** 35. row *************************** id: 35 name: Rap namesort: RAP namesearch: RAP musicmagic_mixable: NULL *************************** 36. row *************************** id: 36 name: No Genre namesort: NO GENRE namesearch: NO GENRE musicmagic_mixable: NULL *************************** 37. row *************************** id: 37 name: Alternative namesort: ALTERNATIVE namesearch: ALTERNATIVE musicmagic_mixable: NULL 37 rows in set (0.00 sec)
looks more like your mysql server is mixed up.
This fellow in the forums had pretty much the same problem. http://forums.slimdevices.com/showthread.php?t=43008 Could it be due to something like corrupted table indexes, maybe from a scanner crash?
For info, I've just dropped and recreated the database. It's had no effect on the ordering.
Don't know what's going on here - I can't reproduce the issue by creating my own tables and firing similar queries at them. However, what's the point of the group by in that query? id is a primary key, so must be unique. The group by is therefore meaningless, especially as there's no aggregate functions. I wouldn't be suprised to find that there's some weird undefined behaviour going on due to the odd use of the group by. If there were duplicate values, what would be the expected behaviour? The column selected by group by would be undefined wouldn't it? Either way, I'd recommend removing the group by. Doing so fixes the problem for me.
This is now hitting me for browsing album tracks - the tracks are being returned unsorted, meaning that I now can't play an album in the right order. The query getting the tracks is as follows: SELECT me.id, me.url, me.content_type, me.title, me.titlesort, me.title search, me.album, me.tracknum, me.timestamp, me.filesize, me.disc, me.remote, me.audio, me.audio_size, me.audio_offset, me.year, me.secs, me.cover, me.vbr_scale, me.bitrate, me.samplerate, me.samplesize, me.channels, me.block_alignment, me.endian, me.bpm, me.tagversion, me.drm, me.musicmagic_mixable, me.musicbrainz_id, me.playcount, me.lastplayed, me.lossless, me.lyrics, me.rating, me.replay_gain, me.replay_peak FROM tracks me WHERE ( me.album = 397 AND ( ( me.content_type != 'cpl' ) AND ( me.content_type != 'src' ) AND ( me.content_type != 'ssp' ) AND ( me.content_type != 'dir' ) ) AND me.remote != 1 ) GROUP BY me.id ORDER BY me.disc, me.tracknum, concat('0', me.titlesort) LIMIT 13 Once again, it contains an extraneous GROUP BY. The query contains no aggregrate clauses and me.id is a primary key so must be unique. Removing the group by makes the query return tracks in the expected order. I should have mentioned previously that I am running against an external mysql database, version 5.0.51-3. It's running on the same host as SqueezeCenter and is simply the Debian lenny installed version. Max
Post your MySQL configuration file. I have no idea where it may be on your system, but maybe something in the settings aren't meshing with the way SqueezeCenter would like.
Conf to follow as an attachment, but I've not changed it from Debian's defaults. I still think the solution to this problem is to remove the unnecessary group bys from those queries. Unless I'm much mistaken, they don't do a think and are basically invalid, which could be triggering undefined behaviour. Removing them and manually running the queries certainly fixes the problem, but I haven't yet been able to work out what's introducing them in the Slim source.
Created attachment 2896 [details] MySQL config file conf.d is empty and debian.cnf only contains the standard host/socket/user/basedir details.
Slim/Schema/ResultSet/Base.pm 261L: sub distinct { my $self = shift; # XXX - this will work when mst fixes ResultSet.pm # $self->search(undef, { 'distinct' => 1 }); $self->search(undef, { 'group_by' => map { $self->{'attrs'}->{'alias'}.'.'.$_ } $self->result_source->primary_columns }); } Commenting out the group_by line fixes my track listing problems, but not genre sorting. However, this obviously isn't a fix. Unfortunately, DBIx::Class::ResultSet implements "'distinct' => 1" by grouping by all columns, so changing it to that doesn't help either. There are now other reports of people having problems with this on the forums. Debian's MySQL seems to be a common factor so far.
Suspect this is a MySQL bug - http://bugs.mysql.com/bug.php?id=32202. I think versions 5.0.50 and 5.0.51 are affected.
I'm getting some strange orderings in album browse. Mysql version is: MySQL version: 5.0.45-Debian_1ubuntu3.1 B.
I'll join the chorus... My album sort is very strange: http://forums.slimdevices.com/showthread.php?t=43571 Also using Debian/Lenny: SlimServer Version: 6.5.4 - 12568 - Debian - EN - iso-8859-1 Perl Version: 5.8.8 i486-linux-gnu-thread-multi MySQL Version: 5.0.51-3
Andy: I'm concerned that this is a big problem for 7.0. How common do we expect this to be?
(In reply to comment #17) > I'm getting some strange orderings in album browse. Mysql version is: > > MySQL version: 5.0.45-Debian_1ubuntu3.1 > > B. > Sorry about the sparse system info, here is more: Ubuntu 7.10 SqueezeCenter version: 7.0.1 - 17600 - Debian - DA - utf8 IP adresse for server: 192.168.1.5 Perl version: 5.8.8 i486-linux-gnu-thread-multi MySQL version: 5.0.45-Debian_1ubuntu3.1 Platformarkitektur: i686-linux
Brandon: what do you think? If it's that MySQL bug I am not too concerned since we don't ship a version with the bug and they already have a patch for it. However, the "group by id" in this case is pointless, maybe DBIC can be improved (or already has been?) to optimize that out?
There do seem to be points where SC is specifying a group-by = *.id. If there really is no point to it, should be something we can consider removing.
I had been running MySQL 5.0.45 on Windows without seeing this. So I installed 5.0.51a on my Windows server and the sorting problem is there in both SlimServer 6.5 and in SC7. Installed a later version of MySQL and it's fixed. Source and binaries for the GPL'd MySQL Enterprise 5.0.52 and 5.0.54 can be found at http://mirror.provenscaling.com/mysql/enterprise/ .
OK, everyone seeing the problem please update to 5.0.52 or higher and let us know if it's fixed.
(In reply to comment #21) > Brandon: what do you think? If it's that MySQL bug I am not too concerned > since we don't ship a version with the bug and they already have a patch for > it. Just for the record, I think the deb is now shipped without mysql due to other problems. See the post by 'Diranged' in http://forums.slimdevices.com/showthread.php?t=42876&page=9
(In reply to comment #24) > OK, everyone seeing the problem please update to 5.0.52 or higher and let us > know if it's fixed. > Hmm, I'm no expert but I think backporting mysql-server on Ubuntu (and probably other Linux-distros) without breaking all kinds of stuff is non-trivial. I'd say the local mysql binary needs to go back into the deb (as per my previous post).
Hey, I'm the millia as above. I'm running Deb Test, hence mysql 5.0.51. I'm at SqueezeServer testing, now too. I don't have the problem with track sorting- they're fine. But, all my files are very newly ripped and the tags are accurate as far as track numbering goes. Does the original poster have issues sorting albums, too? Happy to dig up any tech logs y'all need...
Since that version of MySQL is not in Debian stable I don't imagine it will be that hard for them to update it to a newer version.
It's probably worth filing a bug with Debian about this one (although I don't know what their etiquette for upstream bugs is). I'm writing this in the hope that someone else will do it (sorry!).
Debian testing just got a new version of mysql-server-50 that appears to have fixed this problem. However, I had to do a clear database rescan for the fix to take effect (probably due to table index black magic within MySQL).
(In reply to comment #30) > Debian testing just got a new version of mysql-server-50 that appears to have > fixed this problem. However, I had to do a clear database rescan for the fix > to take effect (probably due to table index black magic within MySQL). > Since this appears to be a Debian MySQL issue, I suggest we close this bug. Tech Support should be aware of this issue, adding it to the Forum / FAQ if appropriate.
Now that Debian have a fixed version of MySQL in testing and unstable, this probably isn't even an FAQ issue. No stable version of Debian contains a broken version of MySQL and anyone running testing would hopefully keep it up to date anyway.
Actually, it didn't seem to get fixed for me. I've got the latest mysql from testing, and all other dependencies. Did you uninstall/reinstall anything, Max, or just apt-get and the problem magically went away?
oh, and i did indeed do a complete clear scan- it does it every night actually. No luck there...
Please confirm what version of MySQL you have installed and what distribution/version you are running on. I simply had to do an apt-get dist-upgrade, and a full clear db rescan.
I'm on: SqueezeCenter Version: 7.0 - 17943 - Debian - EN - utf8 Perl Version: 5.8.8 i486-linux-gnu-thread-multi MySQL Version: 5.0.51a-3 It's Debian testing, and squeezecenter testing. And, after a full clear db rescan, bug persists.
I have the same problems as David Millians: The sorting order shown in genre and album listings is by the order the db was created instead of the normal alfabetic order. My tags are good. I have also everything. Removed SC7 + SOODS and reinstall everything. Nothing helps. I am using sb3 in combination with nas-server synology 107+: SqueezeCenter-versie: 7.0 - 17793 - Linux - NL - utf8 Perl-versie: 5.8.8 armv5tejl-linux-thread-multi MySQL-versie: 5.0.51 Platformarchitectuur: armv5tejl-linux Is there someone who can help me with this irritating bug?
This should be fixed as of MySQL 5.0.52, but that's not yet available in Debian/Lenny. *** This bug has been marked as a duplicate of bug 6748 ***