Bugzilla – Bug 15997
Album tracks queries consistently fail from SP clients when using MySQL DB Backend
Last modified: 2011-05-12 14:01:22 UTC
My setup: Version: 7.6.0 - r30469 @ Sat Apr 3 01:03:43 PDT 2010 Hostname: charles-laptop Server IP Address: 192.168.1.32 Server HTTP Port Number: 9000 Operating system: Debian - EN - utf8 Platform Architecture: i686-linux Perl Version: 5.10.1 - i486-linux-gnu-thread-multi Database Version: MySQL 5.0.83-0ubuntu3 Total Players Recognized: 1 Each attempt to browse into an album (i.e., list the tracks) using SqueezePlay (latest SVN trunk) causes the following exception in the SBS log: [10-04-03 19:12:06.4556] Slim::Schema::Storage::throw_exception (119) Error: DBI Exception: DBD::mysql::db selectrow_array failed: Duplicate column name 'name' [for Statement " SELECT COUNT(*) FROM ( SELECT albums.title, contributors.name, genres.name, tracks.coverid, tracks.id, tracks.musicmagic_mixable, tracks.secs, tracks.title, tracks.tracknum FROM tracks JOIN genre_track ON genre_track.track = tracks.id JOIN genres ON genres.id = genre_track.genre JOIN contributor_track ON contributor_track.track = tracks.id JOIN contributors ON contributors.id = contributor_track.contributor JOIN albums ON albums.id = tracks.album WHERE (tracks.content_type != "cpl" AND tracks.content_type != "src" AND tracks.content_type != "ssp" AND tracks.content_type != "dir") AND tracks.album = ? AND contributor_track.role IN (?, ?) GROUP BY tracks.id ORDER BY tracks.disc, tracks.tracknum, tracks.titlesort COLLATE utf8_general_ci ) AS t1 "] [10-04-03 19:12:06.4680] Slim::Schema::Storage::throw_exception (119) Backtrace: frame 0: Slim::Utils::Log::logBacktrace (/usr/share/perl5/Slim/Schema/Storage.pm line 119) frame 1: Slim::Schema::Storage::throw_exception (/usr/share/squeezeboxserver/CPAN/DBIx/Class/Storage/DBI.pm line 1006) frame 2: DBIx::Class::Storage::DBI::__ANON__ (/usr/share/perl5/Slim/Control/Queries.pm line 6372) frame 3: Slim::Control::Queries::_getTagDataForTracks (/usr/share/perl5/Slim/Control/Queries.pm line 4336) frame 4: Slim::Control::Queries::titlesQuery (/usr/share/perl5/Slim/Control/Request.pm line 1931) frame 5: (eval) (/usr/share/perl5/Slim/Control/Request.pm line 1931) frame 6: Slim::Control::Request::execute (/usr/share/perl5/Slim/Web/Cometd.pm line 809) frame 7: Slim::Web::Cometd::handleRequest (/usr/share/perl5/Slim/Web/Cometd.pm line 548) frame 8: Slim::Web::Cometd::handler (/usr/share/perl5/Slim/Web/Cometd.pm line 108) frame 9: Slim::Web::Cometd::webHandler (/usr/share/perl5/Slim/Web/HTTP.pm line 487) frame 10: Slim::Web::HTTP::processHTTP (/usr/share/perl5/Slim/Networking/IO/Select.pm line 139) frame 11: (eval) (/usr/share/perl5/Slim/Networking/IO/Select.pm line 123) frame 12: Slim::Networking::IO::Select::__ANON__ (/usr/share/perl5/Slim/Networking/IO/Select.pm line 183) frame 13: (eval) (/usr/share/perl5/Slim/Networking/IO/Select.pm line 183) frame 14: Slim::Networking::IO::Select::loop (/usr/sbin/squeezeboxserver line 684) frame 15: main::idle (/usr/sbin/squeezeboxserver line 634) frame 16: main::main (/usr/sbin/squeezeboxserver line 1127) This was tried on both ARM and Intel platforms with the same result. I have the very latest SqueezePlay compiled from the SVN 7.6 trunk to use as a client. This happens on 100% of the albums I've tried to track list from SqueezeOS. They list ok from the WebUI, however.
Confirmed with r31564 and latest SqueezePlay nightly. Any developer comment on the matter?
Closer inspection shows that the code introduced in the 7.5/embedded branch in r29896 (by Andy) relies on the sql select statement returning the column names including the table name. This is not standard SQL behaviour, it's an extension that is seemingly supported by SQLite only (full_column_names pragma). It should be possible to make it work by selecting all columns with an alias, so that select a.foo, b.foo ... becomes select a.foo as 'a.foo', b.foo as 'b.foo' ... Not sure what the best way to do that in the current code is, though.
Created attachment 7056 [details] Fix One way of fixing that problem is to always use 'tablename.column' as result column alias - see patch which seems to fix the problem for me.
== Auto-comment from SVN commit #31797 to the slim repo by agrundman == == http://svn.slimdevices.com/slim?view=revision&revision=31797 == Fixed bug 15997, map column names correctly using AS
== Auto-comment from SVN commit #31805 to the slim repo by agrundman == == http://svn.slimdevices.com/slim?view=revision&revision=31805 == Bug 15997, same fix needed in albumsQuery. Remove unneeded sort too
Fixed in build 7.6.0, 32398.