PDA

View Full Version : SQL Playlist / Dynamic Playlist problem



Philip Meyer
2014-08-07, 01:27
I tried to play a dynamic playlist, that is provided via SQL Playlist, but it failed to play the mix.

Home > Dynamic Playlists > Recently added > Not rated > Not rated songs recently added

[I imagine a similar fault exists in other SQL Playlists]

Looking at the log:

[09:22:22.0977] Slim::Schema::Storage::throw_exception (122) Error: DBI Exception: DBD::SQLite::db prepare failed: near "DAY": syntax error [for Statement "select tracks.url from tracks where tracks.audio=1 and exists (select 1 from track_statistics t1 where tracks.url=t1.url and t1.added>UNIX_TIMESTAMP(DATE_ADD(NOW(),INTERVAL -30 DAY))) and not exists (select 1 from track_statistics t2 where tracks.url=t2.url and t2.rating>0) and not exists (select 1 from dynamicplaylist_history d where tracks.id=d.id and d.client='00:04:20:1e:00:c6') group by tracks.id order by rand() limit 10;"]
[09:22:22.0988] Slim::Schema::Storage::throw_exception (122) Backtrace:

frame 0: Slim::Utils::Log::logBacktrace (C:/Squeezebox/LMS/Beta/server/Slim/Schema/Storage.pm line 122)
frame 1: Slim::Schema::Storage::throw_exception (C:\Squeezebox\LMS\Beta\server\CPAN/DBIx/Class/Storage/DBI.pm line 1006)
frame 2: DBIx::Class::Storage::DBI::__ANON__ (C:\Squeezebox\LMS\Beta\Cache\InstalledPlugins/Plugins/SQLPlayList/Plugin.pm line 1057)
frame 3: (eval) (C:\Squeezebox\LMS\Beta\Cache\InstalledPlugins/Plugins/SQLPlayList/Plugin.pm line 1056)
frame 4: Plugins::SQLPlayList::Plugin::executeSQLForPlaylis t (C:\Squeezebox\LMS\Beta\Cache\InstalledPlugins/Plugins/SQLPlayList/Plugin.pm line 964)
frame 5: Plugins::SQLPlayList::Plugin::getTracksForPlaylist (C:\Squeezebox\LMS\Beta\Cache\InstalledPlugins/Plugins/SQLPlayList/Plugin.pm line 1261)
frame 6: Plugins::SQLPlayList::Plugin::getNextDynamicPlayLi stTracks (C:\Squeezebox\LMS\Beta\Cache\InstalledPlugins/Plugins/DynamicPlayList/Plugin.pm line 3319)
frame 7: (eval) (C:\Squeezebox\LMS\Beta\Cache\InstalledPlugins/Plugins/DynamicPlayList/Plugin.pm line 3319)
frame 8: Plugins::DynamicPlayList::Plugin::getTracksForPlay list (C:\Squeezebox\LMS\Beta\Cache\InstalledPlugins/Plugins/DynamicPlayList/Plugin.pm line 345)
frame 9: Plugins::DynamicPlayList::Plugin::findAndAdd (C:\Squeezebox\LMS\Beta\Cache\InstalledPlugins/Plugins/DynamicPlayList/Plugin.pm line 525)
frame 10: Plugins::DynamicPlayList::Plugin::playRandom (C:\Squeezebox\LMS\Beta\Cache\InstalledPlugins/Plugins/DynamicPlayList/Plugin.pm line 3848)
frame 11: Plugins::DynamicPlayList::Plugin::cliPlayPlaylist (C:/Squeezebox/LMS/Beta/server/Slim/Control/Request.pm line 1885)
frame 12: (eval) (C:/Squeezebox/LMS/Beta/server/Slim/Control/Request.pm line 1885)
frame 13: Slim::Control::Request::execute (C:/Squeezebox/LMS/Beta/server/Slim/Control/Request.pm line 882)
frame 14: Slim::Control::Request::executeRequest (C:/Squeezebox/LMS/Beta/server/Slim/Web/HTTP.pm line 915)
frame 15: Slim::Web::HTTP::processURL (C:/Squeezebox/LMS/Beta/server/Slim/Web/HTTP.pm line 723)
frame 16: Slim::Web::HTTP::processHTTP (C:/Squeezebox/LMS/Beta/server/Slim/Networking/IO/Select.pm line 123)
frame 17: (eval) (C:/Squeezebox/LMS/Beta/server/Slim/Networking/IO/Select.pm line 119)
frame 18: Slim::Networking::IO::Select::__ANON__ (C:/Squeezebox/LMS/Beta/server/Slim/Networking/IO/Select.pm line 168)
frame 19: (eval) (C:/Squeezebox/LMS/Beta/server/Slim/Networking/IO/Select.pm line 168)
frame 20: Slim::Networking::IO::Select::loop (C:\Squeezebox\LMS\Beta\server\slimserver.pl line 715)
frame 21: main::idle (C:\Squeezebox\LMS\Beta\server\slimserver.pl line 665)
frame 22: main::main (C:\Squeezebox\LMS\Beta\server\slimserver.pl line 1200)

Phil

erland
2014-08-11, 22:42
Thanks for reporting, it looks like I've missed to update a few playlist template parameters for SQLite, the current query only works with MySQL.

I'll try to release a corrected version in the near future, feel free to remind me if you don't see a new release where this is fixed within the next week or two.

Philip Meyer
2014-08-22, 15:24
>I'll try to release a corrected version in the near future, feel free to
>remind me if you don't see a new release where this is fixed within the
>next week or two.
>
Has there been any progress?

erland
2014-08-25, 21:55
Thanks for the reminder.
I've been a bit busy during the last weeks but I'll try to get some time to go through through all the templates and fix this sometime later this week.

erland
2014-09-03, 21:50
Hmm, is this a playlist you have created yourself with SQL Playlist plugin ?
Do you also see it under Extras/SQL Playlists menu in web interface ? (This means that it's a playlist you have created yourself)

If you see it under Extras/SQL Playlists menu, do you get the raw SQL when you click on it or do you get the parameter based dialog that's more user friendly. If you get the raw SQL it means that it's a playlist you have created yourself, probably by customising the SQL of one of the existing ones, and then the problem is that you are using MySQL syntax.

In this case you will need to change the part:

UNIX_TIMESTAMP(DATE_ADD(NOW(),INTERVAL -30 DAY))
To something like:

STRFTIME("%s",DATE('NOW','-30 DAY'))

Philip Meyer
2014-09-06, 09:51
>Hmm, is this a playlist you have created yourself with SQL Playlist
>plugin ?

I can't remember / I am a bit confused to be honest!

In Extras > SQL Playlists, I have a "Recently Added Not Rated Songs", which i guess is one I created from a SQL Playlist template. However, it doesn't have any playlist groups configured, so I wouldn't expect to find it under "Dynamic Playlists > Recently added > Not rated > Not rated songs recently added"?

If I click into Dynamic Playlists > Recently added > Not rated > Not rated songs recently added >, I get a trackstat view containing the option to play as a dynamic playlist. I assumed this was therefore provided by TrackStat, and assumed that TrackStat was not providing the correct SQL for this choice.

Anyway, I made the change to the SQL as you suggested, and also pressed the "Download last version of existing playlists". It seems to be working now.

Phil