SQLite Exception - Error: near ")": syntax error

[PenkethBoy 2063]

Not sure when this started but was having a Query failure via API - from a previously working query in a script - to 4.1.0.6/7

 

../emby/Artists?Limit=99&Recursive=true&Fields=Itemcounts&IncludeItemTypes=MusicAlbum

 

and the failure i get is - "Exception of type 'SQLitePCL.pretty.SQLiteException' was thrown."

 

More detail from the log

2019-02-09 12:51:41.342 Info HttpServer: HTTP GET https://192.168.1.81:8920/emby/Artists?Limit=99&Recursive=true&Fields=Itemcounts. UserAgent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36
2019-02-09 12:51:41.421 Info HttpServer: HTTP Response 200 to 192.168.1.56. Time: 80ms. https://192.168.1.81:8920/emby/Artists?Limit=99&Recursive=true&Fields=Itemcounts
2019-02-09 12:54:41.138 Info HttpServer: HTTP GET https://192.168.1.81:8920/emby/Artists?Limit=99&Recursive=true&Fields=Itemcounts&IncludeItemTypes=MusicAlbum. UserAgent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36
2019-02-09 12:54:41.146 Info HttpServer: HTTP GET https://192.168.1.81:8920/emby/Users/77b5353a3f534740b1fa4a2e02c9b4c6. UserAgent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36
2019-02-09 12:54:41.147 Error HttpServer: Error processing request
	*** Error Report ***
	Version: 4.1.0.7
	Command line: D:\Emby-Stable\system\EmbyServer.dll
	Operating system: Microsoft Windows NT 6.2.9200.0
	64-Bit OS: True
	64-Bit Process: True
	User Interactive: True
	Processor count: 8
	Program data path: D:\Emby-Stable
	Application directory: D:\Emby-Stable\system
	SQLitePCL.pretty.SQLiteException: Error: near ")": syntax error
	SQLitePCL.pretty.SQLiteException: Exception of type 'SQLitePCL.pretty.SQLiteException' was thrown.
	   at SQLitePCL.pretty.SQLiteException.CheckOk(sqlite3 db, Int32 rc)
	   at SQLitePCL.pretty.SQLiteDatabaseConnection.PrepareStatement(String sql, String& tail)
	   at SQLitePCL.pretty.DatabaseConnection.PrepareStatement(IDatabaseConnection This, String sql)
	   at System.Linq.Enumerable.SelectListIterator`2.ToArray()
	   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
	   at Emby.Sqlite.BaseSqliteRepository.PrepareAllSafe(IDatabaseConnection connection, List`1 sql)
	   at Emby.Server.Implementations.Data.SqliteItemRepository.<>c__DisplayClass184_0.<GetItemLinks>b__2(IDatabaseConnection db)
	   at SQLitePCL.pretty.DatabaseConnection.RunInTransaction[T](IDatabaseConnection This, Func`2 f, TransactionMode mode)
	   at Emby.Sqlite.ManagedConnection.RunInTransaction[T](Func`2 action, TransactionMode mode)
	   at Emby.Server.Implementations.Data.SqliteItemRepository.GetItemLinks(InternalItemsQuery query, Int32[] itemValueTypes, Type returnType)
	   at Emby.Server.Implementations.Data.SqliteItemRepository.GetArtists(InternalItemsQuery query)
	   at Emby.Server.Implementations.Library.LibraryManager.GetArtists(InternalItemsQuery query)
	   at MediaBrowser.Api.UserLibrary.ArtistsService.GetItems(GetItemsByName request, InternalItemsQuery query)
	   at MediaBrowser.Api.UserLibrary.BaseItemsByNameService`1.GetResultSlim(GetItemsByName request)
	   at MediaBrowser.Api.UserLibrary.ArtistsService.Get(GetArtists request)
	   at Emby.Server.Implementations.Services.ServiceExecGeneral.Execute(Type serviceType, IRequest request, Object instance, Object requestDto, String requestName)
	   at Emby.Server.Implementations.Services.ServiceController.Execute(HttpListenerHost appHost, Object requestDto, IRequest req)
	   at Emby.Server.Implementations.Services.ServiceHandler.ProcessRequestAsync(HttpListenerHost appHost, IRequest httpReq, IResponse httpRes, ILogger logger, String operationName, CancellationToken cancellationToken)
	   at Emby.Server.Implementations.HttpServer.HttpListenerHost.RequestHandler(IRequest httpReq, String urlString, String host, String localPath, CancellationToken cancellationToken)
	Source: SQLitePCL.pretty
	TargetSite: Void CheckOk(SQLitePCL.sqlite3, Int32)
	
2019-02-09 12:54:41.147 Info HttpServer: HTTP Response 500 to 192.168.1.56. Time: 9ms. https://192.168.1.81:8920/emby/Artists?Limit=99&Recursive=true&Fields=Itemcounts&IncludeItemTypes=MusicAlbum

What appears to be happening is that - depending on the combination of optional parameters the passing of the url is creating a sql request that appears to be malformed.

 

some combination of IncludeItemTypes, Fields=Itemcounts and Limits= - is causing the Error Near ")" error - but not always - as you can see if i remove IncludeItemTypes the query runs fine (see top two lines of log extract above)

 

[PenkethBoy 2063]

For Info

 

I get the same issue with /Artist/AlbumArtists end point - possibly using the same code?

 

This is trying to get itemcounts (Albums and Song) counts for an AlbumArtist

 

works fine without a Limit=10 applied

 

Example from Swagger

[Luke 37022]

What are you using this for?

[PenkethBoy 2063]

Just swagger in the examples above 

 

but it does not work in powershell either - but thats not the problem - its internal to Emby - the sql query is failing

[Luke 37022]

I understand, but i essentially did this development in the beta just for you  because i was under the impression you were building something that was going to be publicized.

[PenkethBoy 2063]

It is but these sort of issues - introduced "recently" - since i last tested it (middle of 3.6 beta cycle) which worked fine at that time - keep setting it back

 

My intention is a script to do all the Reporting and more that the Report-plugin does - its been done for months - been waiting on the long beta cycle to finish and any final tweaks to Stable to finish - which is fine - before doing final testing - before release - to save doing all the testing several times

 

This issue came up as i needed an updated report of Artists and it failed - as above - so i investigated why and reported it and found that AlbumArtists also failed with the same error etc

 

Was not aware it was just for me - thanks for that