Near limit syntax error sqlite

SQLite Forum

(1.1) By Olivier Mascia (lvrmsc) on 2020-09-10 06:23:59 edited from 1.0 [link] [source]

Hello, With 3.33 which are the right instructions to use SQLITE_ENABLE_UPDATE_DELETE_LIMIT? Is recompiling from sources still required or can this now be used through amalgamation and more specifically through the SEE supplied amalgamation replacements?

(2) By Keith Medcalf (kmedcalf) on 2020-09-10 06:34:28 in reply to 1.0 [link] [source]

A patch was added which allows the amalgamation to be build in such a way as it will work with or without SQLITE_ENABLE_UPDATE_DELETE_LIMIT when the amalgamation is compiled.

This would have first appeared in release code 3.33.0.

Previously SQLITE_ENABLE_UPDATE_DELETE_LIMIT be specified BOTH when the amalgamation is generated AND when it is compiled in order for it to work; or, specified in neither case in order for the additional ORDER BY/LIMIT clauses to not be recognized. Inconsistency resulted in inconsistent results.

I have no knowledge with respect to SEE .

(3.1) By Keith Medcalf (kmedcalf) on 2020-09-10 07:06:05 edited from 3.0 in reply to 2 [link] [source]

If you define either SQLITE_ENABLE_UPDATE_DELETE_LIMIT or SQLITE_UDL_CAPABLE_PARSER when generating the amalgamation, you must define one or the other of those two symbols when compiling the amalgamation.

Inconsistency results in a missing function (updateDeleteLimitError) declaration and failure to compile the amalgamation.

(5) By Olivier Mascia (lvrmsc) on 2020-09-10 07:18:05 in reply to 3.1 [link] [source]

Spotted that too in the code, though unless SQLITE_UDL_CAPABLE_PARSER gets defined through some other mean, I don’t see myself defining that. I’ll dig deeper.

(4) By Olivier Mascia (lvrmsc) on 2020-09-10 07:07:48 in reply to 2 [link] [source]

Knew that, been there. Was working nicely up to version 3.31. To produce the SEE amalgamation, one had to append the SEE code to the standard amalgamation. It was easy to compile the amalgamation with the option properly enabled, then append SEE code.

With 3.32 that broke because the SEE code distribution was simplified, distributing pre-built amalgamations for the various flavours of cyphers sets supported.

Devs kindly and very quickly helped me by supplying me with the right SEE-amalgamation prebuilt to my needs.

I spotted the change for 3.33 and adjusted my build in order to use the SEE amalgamation as is along with compiling it with SQLITE_ENABLE_UPDATE_DELETE_LIMIT. It looked fine and I failed to properly test immediately though. 🙁

As the pages on the website clearly are a bit outdated about the way to activate this option, in regards to the checkin you referred to, I opened this thread here to trigger verification / clarification. Maybe I misunderstood a detail in how to properly use that feature now.

(6) By Richard Hipp (drh) on 2020-09-10 12:10:31 in reply to 1.1 [link] [source]

Thanks for the report. The 3.33 release of SEE has been updated with support for -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT. I will be taking additional steps to help ensure that I don’t leave this out again in the future.

(7) By Olivier Mascia (lvrmsc) on 2020-09-10 12:57:50 in reply to 6 [link] [source]

Does the fix I’m seeing in the timeline address all codec variants (not limited to see-aes128-ccm)? Or should I better wait some days before updating?

We recently switched from using only sqlite3-see-aes128-ofb.c to using sqlite3-see.c itself, so as to support both aes128 and aes256 for some time, thanks to the key material prefixes (progressive migration to aes256). We will later settle on sqlite3-see-aes256-openssl.c and sqlite3-see-aes256-cryptoapi.c depending on the platform.

(8) By Richard Hipp (drh) on 2020-09-10 13:01:33 in reply to 7 [link] [source]

The fix in the SEE repository should apply to all of the SEE variants. But is is not in the «sqlite3.c» file in the SEE repository. Yet.

I have a fix on the public SQLite trunk (check-in 5b905d7d7714d1d2) that should provide a more general solution moving forward. With that latest trunk change, any amalgamation built using the -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT flag should be buildable with or without that flag. I will also add release checklist items to verify this prior to release.

(9) By TripeHound on 2020-09-10 13:42:45 in reply to 8 [link] [source]

The above check-in ensures the necessary define ( SQLITE_UDL_CAPABLE_PARSER ) is «baked-in» to the amalgamation if SQLITE_ENABLE_UPDATE_DELETE_LIMIT is defined when creating the amalgamation.

. on a slightly related matter, there was a recent thread where someone was trying to compile the amalgamation with some of the several SQLITE_OMIT_xxx options that only work when building from the canonical sources. Given this crops up every now and again (and despite it being documented at Options To Omit Features), would it be sensible/possible to include in amalgamations assembled without such options something like:

In light of (my emphasis):

Important Note: The SQLITE_OMIT_* options may not work with the amalgamation. SQLITE_OMIT_* compile-time options usually work correctly only when SQLite is built from canonical source files.

from the above page, you may only want to do this for «omit» options that are known not to work properly with the amalgamation, or – if it’s (justifiably) felt trying to keep track of which ones won’t work / might work / will work is too much trouble, you could decide to «ban» all the «omit» options unless applied to the canonical sources.

(If something like this has already been considered and rejected, then please ignore this post!)

(10) By anonymous on 2020-09-10 15:01:33 in reply to 8 [link] [source]

Thanks again Richard. Using code from SEE timeline Check-in [87401772a1], I get:

from running the cli. Obviously the .c file has:

while the .h (obtained in the SEE ZIP package out of Check-In [87401772a1) has:

May I safely patch the SQLITE_SOURCE_ID of the .h file or should I better wait for some other Check-In, in case some other detail (than the SOURCE_ID) would not be right?

(11) By Richard Hipp (drh) on 2020-09-10 15:12:52 in reply to 10 [link] [source]

That should be safe. But you can also update to the latest SEE check-in.

Источник

UPDATE . ORDER BY . LIMIT . does not work #306

According to sqlite documentation at http://www.sqlite.org/lang_update.html, I should be able to use order by and limit in an update, but node-sqlite3 does not let me, telling me that I have a syntax error near order .

Running the same query on the command line sqlite3 (version 3.7.17) results in one row being updated.

I’m sure I’m missing something, but I’m not sure what it is.

Example of problem below:

The text was updated successfully, but these errors were encountered:

If anyone is waiting on this bug being fixed and is not aware of the fix below, you can do an update from a subselect in the meantime:

That will not help you if you’re trying to use the simpler form of UPDATE . LIMIT in an ORM, though.

The documentation you linked says:

If SQLite is built with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option then the syntax of the UPDATE statement is extended with optional ORDER BY and LIMIT clauses

It looks like you are running a version of SQLite that was compiled without that support.

I noticed «SQLITE_ENABLE_UPDATE_DELETE_LIMIT» is off too. It’d be nice if there was somthing we could put in our package.json maybe to enable it when doing a rebuild.

Actually, our compile-time options are not in package.json , they’re in /deps/sqlite3.gyp .

Based on @Mithgol ‘s tip, I took a naive stab at a pull request ( #699 )

But it looks like it’s not working, which makes sense based on the following note in the documentation: If this option is defined, then it must also be defined when using the ‘lemon’ tool to generate a parse.c file. Because of this, this option may only be used when the library is built from source, not from the amalgamation or from the collection of pre-packaged C files provided for non-Unix like platforms on the website.

Источник

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-StablesystemEmbyServer.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-Stablesystem
	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)