[sqlite] UPDATE silently failing
On 21 Sep 2015, at 10:44pm, Hugues Bruant wrote: > We've only observed this on OS X so far, with both 3.8.7 and 3.8.11.1. The > new value is always exactly the old value +1 when the statement fails. Can you reproduce this problem with the SQLite shell tool included with Mac OS X ? You should find one already installed on your Mac as '/usr/bin/sqlite3'. Ideally, open the database which exhibits the problem and first execute PRAGMA integrity_check; If this fails to show any problems run your UPDATE command, repeatedly if necessary, and see if you can make the shell tool do anything that suggests it isn't working perfectly. > It is opened in > WAL mode with exclusive locking. Just a quick note that the journal mode is an attribute of the database itself. There is no need to specify the mode each time a connection opens the database. It is saved in the journal file and connections which access the database will automatically understand what mode to use. You can check the journal mode of the database using PRAGMA journal_mode; Simon.
[sqlite] UPDATE silently failing
On Mon, 21 Sep 2015 17:44:13 -0400 Hugues Bruant wrote: > UPDATE cv SET cv_t=? where cv_s=? and cv_o=?; > > Most of the time the row is updated as expected but in some rare cases > we've seen this statement fail silently, as in: > - the row exists > - the row it is not updated > - step returns SQLITE_OK > - changes returns 0 Commit? This behavior is consistent with an uncommitted UPDATE. --jkl
[sqlite] How to know when a sqlite3_stmt has been recompiled ?
On Mon, 21 Sep 2015 11:19:16 +0200 "R.Smith" wrote: > On 2015-09-21 01:53 AM, Nicolas J?ger wrote: > > hi, > > > > > > Scott Robison wrote: > > > >> 3. Some time passes and some external process may alter the schema. > >> > > is it really possible ? if the answer is yes (I thought when a > > process open a communication with the DB, other processes can't > > alter the DB), for me that would mean that the design(s) of the > > program(s) wich communicates with the DB is bad. > > This is the entire point of a database - allowing multiple things to > see the data. A system which only allows one thread to read data is > just a file-handler. Let's understand plainly: It is an isolation failure. All the rows from a SELECT statement are the product of an atomic action. The schema can't change *during* an atomic action. Of course, isolation is an illusion, and other DBMSs take different approaches to it. They either disallow schema changes to tables against which read locks are held, or they snapshot the results and apply the change, or they queue the change as a pending write until the read lock is released. I don't know of another, though, that just throws in the towel. Not to be snarky about it, but depending on what you mean by "file-handler", I can think of a few systems, such as stdio, that provide access by more than one thread to a single file. They don't provide much sense of isolation, though, except in append-mode. --jkl
[sqlite] UPDATE silently failing
On 9/21/15, Hugues Bruant wrote: > On Mon, Sep 21, 2015 at 8:43 PM, Richard Hipp wrote: > >> On 9/21/15, Hugues Bruant wrote: >> > >> > I would be happy to build and test a patched sqlite with extra logging, >> > some sort of vdbe execution tracing >> >> Compile with -DSQLITE_DEBUG and then use "PRAGMA vdbe_debug=ON;" >> before the offending UPDATE and turn if off afterwards. >> > Will do. Does that go through the error/logging callback ( > https://www.sqlite.org/errlog.html) or directly to stdout/stderr? Directly to stdout. If you can capture a malfunctioning trace, and send in the database file and the SQL statement that is running, that should allow us to localize the problem. -- D. Richard Hipp drh at sqlite.org
[sqlite] UPDATE silently failing
On Mon, Sep 21, 2015 at 8:43 PM, Richard Hipp wrote: > On 9/21/15, Hugues Bruant wrote: > > > > I would be happy to build and test a patched sqlite with extra logging, > > some sort of vdbe execution tracing > > Compile with -DSQLITE_DEBUG and then use "PRAGMA vdbe_debug=ON;" > before the offending UPDATE and turn if off afterwards. > Will do. Does that go through the error/logging callback ( https://www.sqlite.org/errlog.html) or directly to stdout/stderr?
[sqlite] UPDATE silently failing
On 9/21/15, Hugues Bruant wrote: > > I would be happy to build and test a patched sqlite with extra logging, > some sort of vdbe execution tracing Compile with -DSQLITE_DEBUG and then use "PRAGMA vdbe_debug=ON;" before the offending UPDATE and turn if off afterwards. -- D. Richard Hipp drh at sqlite.org
[sqlite] UPDATE silently failing
On Mon, Sep 21, 2015 at 8:23 PM, Richard Hipp wrote: > On 9/21/15, Hugues Bruant wrote: > > On Mon, Sep 21, 2015 at 6:49 PM, Richard Hipp wrote: > > > >> On 9/21/15, Hugues Bruant wrote: > >> > Table schema: > >> > > >> > CREATE TABLE cv (cv_s integer not null, cv_o blob not null, cv_t > >> > integer > >> > not null, primary key(cv_s, cv_o)); > >> > > >> > Prepared statement: > >> > > >> > UPDATE cv SET cv_t=? where cv_s=? and cv_o=?; > >> > >> My guess is that the WHERE clause matches no rows. So it isn't > >> silently failing, it is doing exactly what it is suppose to do: > >> Update only those rows you have specified. > >> > > I wish it where that simple. > > > > The where clause should match, as evidenced by the fact that: > > > > SELECT cv_t from cv where cv_s=? and cv_o=?; > > > > returns the correct value both before and after the failing UPDATE. > > > > If that's not convincing enough, I should mention that the code checks > the > > change count and falls back to an INSERT if the UPDATE reports that no > > rows where updated. This results in a SQLITE_CONSTRAINT error, which > > is how we discovered that the update was failing in the first place. > > If you can tell us how to reproduce the problem, we'll work on it for > you. But until then, there isn't much we can do. > I've been trying to isolate the issue but that's unfortunately non-trivial. I would be happy to build and test a patched sqlite with extra logging, some sort of vdbe execution tracing or really anything that would allow more information to be gathered from the full application. If nothing of the sort is possible I'll work on extracting a minimal reproducer but that will probably take a while. Which version of SQLite is this, btw? What OS? Are you compiling > SQLite yourself, or using a library that somebody has built for you? > What programming language are you using? > As mentioned in the first email of the thread: OS: OS X (we use Linux and Windows as well but so far haven't reproduced the issue on these environments) SQLite version: both 3.8.7 and 3.8.11.1 exhibit the issue We build native code ourselves and access the db through the sqlite-jdbc java wrapper ( https://github.com/xerial/sqlite-jdbc ).
[sqlite] UPDATE silently failing
On 9/21/15, Hugues Bruant wrote: > On Mon, Sep 21, 2015 at 6:49 PM, Richard Hipp wrote: > >> On 9/21/15, Hugues Bruant wrote: >> > Table schema: >> > >> > CREATE TABLE cv (cv_s integer not null, cv_o blob not null, cv_t >> > integer >> > not null, primary key(cv_s, cv_o)); >> > >> > Prepared statement: >> > >> > UPDATE cv SET cv_t=? where cv_s=? and cv_o=?; >> >> My guess is that the WHERE clause matches no rows. So it isn't >> silently failing, it is doing exactly what it is suppose to do: >> Update only those rows you have specified. >> > I wish it where that simple. > > The where clause should match, as evidenced by the fact that: > > SELECT cv_t from cv where cv_s=? and cv_o=?; > > returns the correct value both before and after the failing UPDATE. > > If that's not convincing enough, I should mention that the code checks the > change count and falls back to an INSERT if the UPDATE reports that no > rows where updated. This results in a SQLITE_CONSTRAINT error, which > is how we discovered that the update was failing in the first place. If you can tell us how to reproduce the problem, we'll work on it for you. But until then, there isn't much we can do. Which version of SQLite is this, btw? What OS? Are you compiling SQLite yourself, or using a library that somebody has built for you? What programming language are you using? -- D. Richard Hipp drh at sqlite.org
[sqlite] UPDATE silently failing
On Mon, Sep 21, 2015 at 6:49 PM, Richard Hipp wrote: > On 9/21/15, Hugues Bruant wrote: > > Table schema: > > > > CREATE TABLE cv (cv_s integer not null, cv_o blob not null, cv_t integer > > not null, primary key(cv_s, cv_o)); > > > > Prepared statement: > > > > UPDATE cv SET cv_t=? where cv_s=? and cv_o=?; > > My guess is that the WHERE clause matches no rows. So it isn't > silently failing, it is doing exactly what it is suppose to do: > Update only those rows you have specified. > I wish it where that simple. The where clause should match, as evidenced by the fact that: SELECT cv_t from cv where cv_s=? and cv_o=?; returns the correct value both before and after the failing UPDATE. If that's not convincing enough, I should mention that the code checks the change count and falls back to an INSERT if the UPDATE reports that no rows where updated. This results in a SQLITE_CONSTRAINT error, which is how we discovered that the update was failing in the first place.
[sqlite] CSV export CR+LF not working properly on Windows
hi all, I've been hit by this bug: http://sqlite.1065341.n5.nabble.com/new-line-now-r-n-regardless-the-OS-td77371.html I confirm that some basic export writes now to windows 7 with LF only, instead of CR+LF. I'm not against this feature, but I need also the previous behaviour. ==> could it be made available back in a form of another ? (a new csvCRLF option ?) nota: it was still as expected in 3.8.7.2 binary version made available on the main site.
[sqlite] UPDATE silently failing
On 9/21/15, Hugues Bruant wrote: > Table schema: > > CREATE TABLE cv (cv_s integer not null, cv_o blob not null, cv_t integer > not null, primary key(cv_s, cv_o)); > > Prepared statement: > > UPDATE cv SET cv_t=? where cv_s=? and cv_o=?; My guess is that the WHERE clause matches no rows. So it isn't silently failing, it is doing exactly what it is suppose to do: Update only those rows you have specified. > > Most of the time the row is updated as expected but in some rare cases > we've seen this statement fail silently, as in: > - the row exists > - the row it is not updated > - step returns SQLITE_OK > - changes returns 0 > > We've only observed this on OS X so far, with both 3.8.7 and 3.8.11.1. The > new value is always exactly the old value +1 when the statement fails. > > The db is accessed through the sqlite-jdbc java wrapper. It is opened in > WAL mode with exclusive locking. Multiple threads are sharing the > connection but access is serialized by locks both in sqlite-jdbc and the > application itself. > > sqlite was built with clang on OS X Yosemite from the amalgamation and with > the following compiler flags: > -O2 > -fPIC > -mmacosx-version-min=10.6 > -fvisibility=hidden > -DSQLITE_ENABLE_COLUMN_METADATA > -DSQLITE_THREADSAFE=2 > -DSQLITE_CORE > > The issue does not persist across application restart which suggests > something is wrong with the in-memory state but not with the db itself. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org
[sqlite] UPDATE silently failing
> > Ideally, open the database which exhibits the problem and first execute > > PRAGMA integrity_check; > Integrity check does not report any issue. > If this fails to show any problems run your UPDATE command, repeatedly if > necessary, and see if you can make the shell tool do anything that suggests > it isn't working perfectly. > I cannot reproduce the issue in the shell but that doesn't mean much as this differs in many ways from the real scenario: - the shell uses shared cache - the shell is single threaded - the shell doesn't use transactions - I can't accurately reproduce all the other operations that happen to the database (insert, delete, select, ...) prior to the failing update and may contribute to the issue in subtle ways ... I would be more interested in any ways I can get more verbose logging or some sort of execution trace out of sqlite: re-running tests against a patched sqlite is more practical than trying to extract a minimal reproducer at this point.
[sqlite] How to know when a sqlite3_stmt has been recompiled ?
Thank you all for your replies. As suggested, I am going to call sqlite3_column_count each time (after the first sqlite3_step). On Mon, Sep 21, 2015 at 5:57 PM, Scott Robison wrote: > On Mon, Sep 21, 2015 at 5:48 AM, Richard Hipp wrote: > >> On 9/21/15, Dominique Devienne wrote: >> > On Sun, Sep 20, 2015 at 4:10 PM, Igor Tandetnik >> wrote: >> > >> >> On 9/20/2015 9:55 AM, gwenn wrote: >> >> >> >>> If there is no way to know that the statement has been recompiled, I >> >>> guess that the column count should not be cached... >> >>> >> >> >> >> You could use sqlite3_prepare (no _v2), then you'd get an error on >> schema >> >> change. You would then re-prepare the statement and update your caches. >> > >> > >> > Could perhaps also use the change counter >> > https://www.sqlite.org/fileformat2.html#chngctr, if you're not using WAL >> > mode. >> > You'd get false positives I guess, since both DML and DDL changes would >> > increment it, and I'm not sure that's any different from checking the >> > column_count anyway, but just in case it's useful. --DD >> >> PRAGMA schema_version >> (https://www.sqlite.org/pragma.html#pragma_schema_version) does what >> you want. >> >> But here the thing: It is probably far more expensive to run PRAGMA >> schema_version than it is to just rerun sqlite3_column_count(). I >> think this whole conversation is an exercise in premature >> optimization. Has anybody actually *measured* a performance problem >> with sqlite3_column_count()? >> > > I have not, and I don't have the environment to check. The original premise > as I understand it was that a function call was not expensive because the > API function itself was expensive, but transitions through the language > binding in use might be too expensive. I indicated the same thought last > night, that this might be premature optimization, but even if it is not, I > can't imagine any way to get this information (that the schema changed so > discard cached column count) without making *some* api call, and all api > calls will have the same problem: an expensive language binding transition. > > One person did suggest using sqlite3_prepare instead of v2, since it will > return a schema change error code. My gut instinct is that it'll be easier > / at least as performant to just continue to use v2 and not cache column > count after finishing stepping / resetting a statement. But as indicated, I > can't check that. > > The only reason I jumped into this however is that several responses > weren't answering the asked question, assuring the OP that what was being > asked couldn't happen. It can and does by design. In order to cache info > beyond a statement reset, there needs to be a way to invalidate it. > > In any case, I think the knowledge on the list has exhausted its ability to > answer. To summarize: > > 1. Checking the schema version will result in at least one language binding > transition (two if the version changed), whereas checking the column count > will always only be one transition. Might as well just check the column > count. > > 2. Using prepare vs prepare_v2 would return a schema error, at which point > the caller could finalize / re-prepare the statement and update his cached > values. This still involves extra language binding transitions to handle > the schema change that would have been automatically dealt with by > prepare_v2. OP would have to test to see if it benefits his use case, > though it seems unlikely to be any faster than using the easier v2 > interface and calling column count after each first step. > > -- > Scott Robison > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UPDATE silently failing
Table schema: CREATE TABLE cv (cv_s integer not null, cv_o blob not null, cv_t integer not null, primary key(cv_s, cv_o)); Prepared statement: UPDATE cv SET cv_t=? where cv_s=? and cv_o=?; Most of the time the row is updated as expected but in some rare cases we've seen this statement fail silently, as in: - the row exists - the row it is not updated - step returns SQLITE_OK - changes returns 0 We've only observed this on OS X so far, with both 3.8.7 and 3.8.11.1. The new value is always exactly the old value +1 when the statement fails. The db is accessed through the sqlite-jdbc java wrapper. It is opened in WAL mode with exclusive locking. Multiple threads are sharing the connection but access is serialized by locks both in sqlite-jdbc and the application itself. sqlite was built with clang on OS X Yosemite from the amalgamation and with the following compiler flags: -O2 -fPIC -mmacosx-version-min=10.6 -fvisibility=hidden -DSQLITE_ENABLE_COLUMN_METADATA -DSQLITE_THREADSAFE=2 -DSQLITE_CORE The issue does not persist across application restart which suggests something is wrong with the in-memory state but not with the db itself.
[sqlite] Outdated section of docs?
Hi, I was reading this page (http://sqlite.org/lang_datefunc.html), and at the very bottom it says: / / /"Non-Vista Windows platforms only support one set of DST rules. Vista only supports two. Therefore, on these platforms, historical DST calculations will be incorrect. For example, in the US, in 2007 the DST rules changed. Non-Vista Windows platforms apply the new 2007 DST rules to all previous years as well. Vista does somewhat better getting results correct back to 1986, when the rules were also changed."/ I wonder if that's a section that was written years ago, and the bits that apply to Vista also apply to the Windows releases since then? I don't know enough about Windows Timezone things to be able to find out easily but this reads like it was written back in the era of Vista and probably holds for newer releases too. Cheers, Jonathan
[sqlite] How to know when a sqlite3_stmt has been recompiled ?
On 21 September 2015 at 16:36, Simon Slavin wrote: > > On 21 Sep 2015, at 8:29am, Rowan Worth wrote: > > > 1) Statement is prepared > > 2) Statement is stepped > > 3) Statement is reset > > 4) ... time passes ... > > 5) Statement is stepped > > 6) Statement is reset > > 7) ... time passes ... > > etc. etc. > > > > The assertion seems to be that if the return value of > sqlite3_column_count > > is cached at step 2 it will still be valid at step 5 > > Sorry, no. I was saying that if it was cached at step 2 it will still be > valid until the end of step 3. And that if cached at step 5 it will still > be valid until the end of step 6. My understanding is that the database is > locked by the first call to _step() and unlocked at the first of ... > Sorry, I meant gwenn's assertion, not yours :) Although assertion was a poor choice of words - "premise of this thread" would have fit better. It seems reasonable on the surface - I can't think of how to phrase an SQL query such that it returns a different number of columns on a subsequent execution without the schema changing. But then I'm no SQL expert. Another solution is not to use "SELECT *" but instead to specify the values > you want returned. > I like this advice, but it kind of sounds like gwenn is working on a language binding so can't control the SQL itself. -Rowan
[sqlite] Parser Grammar for MAX,MIN,SUM,COUNT,AVG functions
Thanks a lot Clemens ! Thanks a lot :) On Mon, Sep 21, 2015 at 2:58 PM, Clemens Ladisch wrote: > Prakash Premkumar wrote: > > Can you please tell me which grammar rule in parse.y file parses > aggregate > > function ? > > As you already were told, there are rules that parse _all_ functions: > > expr(A) ::= id(X) LP distinct(D) exprlist(Y) RP(E). > expr(A) ::= id(X) LP STAR RP(E). > > Due to the sharing of these rules, the parser allows DISTINCT and * to > be used with non-aggregate functions: > > sqlite> SELECT date(); > 2015-09-21 > sqlite> SELECT date(DISTINCT); > 2015-09-21 > sqlite> SELECT date(*); > 2015-09-21 > > (But it wouldn't be a good idea to rely on this implementation ...) > > > Regards, > Clemens > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] How to know when a sqlite3_stmt has been recompiled ?
gwenn wrote:
> If there is no way to know that the statement has been recompiled, I
> guess that the column count should not be cached...
SQLite already caches it for you (and properly changes it when recompiling):
SQLITE_API int SQLITE_STDCALL sqlite3_column_count(sqlite3_stmt *pStmt){
Vdbe *pVm = (Vdbe *)pStmt;
return pVm ? pVm->nResColumn : 0;
}
Regards,
Clemens
[sqlite] vdbeUnbind return occasionally Error
On Mon, 21 Sep 2015 14:26:54 +0200, Shuhrat Rahimov wrote: > [...] I think I have found the problem. I do the > following: call sqlite3_bind_text() and then if successful I call > sqlite3_step() on the prepared statement and then call sqlite3_reset(). > This sequence is called 20 times as one SQL transaction. I have noticed > that the problem occurs after about 20 transactions since power on. I have > noticed that before sqlite3_bind_text() returns Error 21, the > sqlite3_step() returns NO_MEM error. So, here maybe I am simply running out > of RAM. I have only 160 K of RAM. What could I do here in order to free > memory after sqlite3_step() for further operations. The simplest way to reduce memory footprint is to tune page_size and cache_size with the apropriate PRAGMAs. -- Regards, Kees Nuyt
[sqlite] How to know when a sqlite3_stmt has been recompiled ?
On 21 September 2015 at 14:38, Simon Slavin wrote: > As expected, ALTER TABLE acts like INSERT: it's a change which requires an > exclusive lock. So just as the documentation says, in a normal journal > mode you can't make a change while the database is locked (which it is > during a SELECT), and in WAL mode you can make the change but the > connection doing the SELECT won't see it until it finishes its transaction. > > Which gives the grand conclusion I posted earlier: OP does not have to > worry about this issue. It can never happen. Your schema cannot be > changed on you while you're inside a transaction if you don't intentionally > defeat SQLite's locking mechanism. > This all makes sense, but is it what the OP was asking? I thought gwenn was looking at the normal cached-statement scenario: 1) Statement is prepared 2) Statement is stepped 3) Statement is reset 4) ... time passes ... 5) Statement is stepped 6) Statement is reset 7) ... time passes ... etc. etc. The assertion seems to be that if the return value of sqlite3_column_count is cached at step 2 it will still be valid at step 5 -- unless a schema change happens at step 4 affecting the number of columns that will be returned by a SELECT *. As others have indicated, sqlite3_step may return SQLITE_SCHEMA in this scenario to indicate that the previously prepared statement needs to be recompiled. But a statement prepared by sqlite3_prepare_v2 will transparently recompile itself (up to 50 times by default) instead of returning SQLITE_SCHEMA: https://www.sqlite.org/rescode.html#schema Which I believe has lead to the OP's question "how do I know when this has happened?" One solution suggested by the docs is to rebuild sqlite3 with SQLITE_MAX_SCHEMA_RETRY=0, then handle the SQLITE_SCHEMA error by clearing the cache + manually recompiling the statements. Or maybe using sqlite3_prepare instead of _v2 would do the trick, if no other _v2 features are relied upon. -Rowan
[sqlite] Parser Grammar for MAX,MIN,SUM,COUNT,AVG functions
Thanks a lot for your reply Ambrus. Can you please tell me which grammar rule in parse.y file parses aggregate function ? Thanks a lot for your time Prakash On Mon, Sep 21, 2015 at 2:07 PM, Zsb?n Ambrus wrote: > On Mon, Sep 21, 2015 at 9:11 AM, Prakash Premkumar > wrote: > > I'm reading the sqlite parser grammar. > > > > I could not find the grammar rules which parses aggregate functions like > > MAX,MIN,SUM,COUNT,TOTAL. > > > > Can you please tell me how the aggregate functions are parsed (the > grammar > > rule that parses them) > > A call to an aggregate function is parsed the same way as a call to an > ordinary function. Sqlite then looks up the function name and number > of arguments to determine if this corresponds to an ordinary function > or an aggregate function, and changes the meaning of the query > according to that. > > The page http://sqlite.org/lang_select.html details how the meaning of > a SELECT statement changes if the selected expression contains an > aggregate function: it becomes an aggregate query even if there is no > GROUP BY clause, and it computes one result row from all the input > rows. Aggregate functions can also be used in the expression of > ordinary aggregate queries, containing a GROUP BY clause, or in the > HAVING clause of such a query. > > I probably forgot a few more uses of aggregate functions, but in most > other contexts, an aggregate function found in an expression results > in an error. > > Ambrus > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] vdbeUnbind return occasionally Error
Hello Richard, thanks for your reply. I think I have found the problem. I do the following: call sqlite3_bind_text() and then if successful I call sqlite3_step() on the prepared statement and then call sqlite3_reset(). This sequence is called 20 times as one SQL transaction. I have noticed that the problem occurs after about 20 transactions since power on. I have noticed that before sqlite3_bind_text() returns Error 21, the sqlite3_step() returns NO_MEM error. So, here maybe I am simply running out of RAM. I have only 160 K of RAM. What could I do here in order to free memory after sqlite3_step() for further operations. Regards 2015-09-21 14:08 GMT+02:00 Richard Hipp : > On 9/21/15, Shuhrat Rahimov wrote: > > Hi all, > > I have ported SQLite to an embedded ARM Cortex M4 based processor. a bare > > metal firmware is using SQLite, no OS. It seems to work but occasionaly > > bindText function fails. So, I am doing 20 inserts as a one transaction, > > but occasionally after 400-460 inserts bindText returns Error 21. I have > > seen that it is occuring in vdbeUnbind function, log is: "bind on a busy > > prepared statement". Could you help me to find out what the problem can > be? > > The prepared statement is still in use when you try to call > sqlite3_bind_text(). You can see this by adding "assert( > sqlite3_stmt_busy(pStmt) );" right before you call > sqlite3_bind_text(pStmt,...). This is an application-level error, not > a problem with SQLite or your port. > > You need to invoke sqlite3_reset() on a prepared statement that has > been previously used before you run sqlite3_bind_text() on that > prepared statement. > > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] using mprint as a global variable inside a query
On Sun, Sep 20, 2015 at 6:16 PM, E.Pasma wrote: > Based on that I created function mprint() as limited version of mprintf. > It accepts no format, only a value parameter. Furthermore, if called with > no parameter at all, it returns the last printed value. > The problem with such a function is that it's non-deterministic, i.e. the result is not purely dependent on its arguments, at least for the no-arg overload. And when you go into that territory, all kind of weird things can happen, and the way optimizations play out (or not) is definitely unclear (to me for sure). See all the recent threads about queries/statements involving random() in them (random() the quintessential non-deterministic function!). So the question is more whether your query (2x as fast) is valid for SQL in general, and whether it's guaranteed to return a valid result-set in this and *future* versions of SQLite IMHO. I'm not qualified to answer that one though. --DD
[sqlite] Remove by name from email list
On 21 Sep 2015, at 1:33pm, Robert G Grieger wrote: > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users To have your name removed please see the link attached to the end of every post to this list. Simon.
[sqlite] How to know when a sqlite3_stmt has been recompiled ?
On Sun, Sep 20, 2015 at 4:10 PM, Igor Tandetnik wrote: > On 9/20/2015 9:55 AM, gwenn wrote: > >> If there is no way to know that the statement has been recompiled, I >> guess that the column count should not be cached... >> > > You could use sqlite3_prepare (no _v2), then you'd get an error on schema > change. You would then re-prepare the statement and update your caches. Could perhaps also use the change counter https://www.sqlite.org/fileformat2.html#chngctr, if you're not using WAL mode. You'd get false positives I guess, since both DML and DDL changes would increment it, and I'm not sure that's any different from checking the column_count anyway, but just in case it's useful. --DD
[sqlite] Parser Grammar for MAX,MIN,SUM,COUNT,AVG functions
Hi, I'm reading the sqlite parser grammar. I could not find the grammar rules which parses aggregate functions like MAX,MIN,SUM,COUNT,TOTAL. Can you please tell me how the aggregate functions are parsed (the grammar rule that parses them) Thanks a lot for your time Prakash
[sqlite] FTS5 returns "corrupt" plus trailing zero
My implementation of http://www.sqlite.org/src/artifact/400384798349d658?ln=94-96 returns "corrupt" plus a trailing zero, that is 8 characters in total. Maybe this line http://www.sqlite.org/src/artifact/4fdbc0a321e3a1d7?ln=5364 should be corrected to sqlite3Fts5BufferSet(&rc, &s, 7, (const u8*)"corrupt"); so that the number of characters passed matches the length of "corrupt". Ralf
[sqlite] How to know when a sqlite3_stmt has been recompiled ?
On 2015-09-21 11:18 AM, Rowan Worth wrote: > On 21 September 2015 at 16:36, Simon Slavin wrote: > >> On 21 Sep 2015, at 8:29am, Rowan Worth wrote: >> >>> 1) Statement is prepared >>> 2) Statement is stepped >>> 3) Statement is reset >>> 4) ... time passes ... >>> 5) Statement is stepped >>> 6) Statement is reset >>> 7) ... time passes ... >>> etc. etc. >>> >>> The assertion seems to be that if the return value of >> sqlite3_column_count >>> is cached at step 2 it will still be valid at step 5 >> Sorry, no. I was saying that if it was cached at step 2 it will still be >> valid until the end of step 3. And that if cached at step 5 it will still >> be valid until the end of step 6. My understanding is that the database is >> locked by the first call to _step() and unlocked at the first of ... >> > Sorry, I meant gwenn's assertion, not yours :) Although assertion was a > poor choice of words - "premise of this thread" would have fit better. > It seems reasonable on the surface - I can't think of how to phrase an SQL > query such that it returns a different number of columns on a subsequent > execution without the schema changing. But then I'm no SQL expert. There is no way to phrase SQL to produce different results excepting to use a wildcard or wildcards. One of the very basic-most promises of SQL is that a query will not have a different answer or layout result to what was asked for. I think Simon's explanation and even test of the premise were great answers. I am slightly confused to Gwenn's asking if there is a way to read if the query has changed because of not wanting to make an "expensive" call to see the column count... Problem is the "expensive" call to read the column count will never be any more expensive than whatever call will give the "My schema changed" answer. As I noted earlier, and Simon noted / vetted via example, there is no way to change the layout and/or schema inside the transaction (between 1 & 3, or 4 & 5 above, etc.), so there is only one point at which there exists a need to check - before the first step after a prepare or a reset. At that point, a call to "sqlite3_didmyschemachange()" or whatever would be equally expensive than a "sqlite3_colcount(pstmnt)" (forgive me I don't have the docs in front of me to see the real procedure name). Again, that might just be me misunderstanding Gwenn, but even so I think the solution is not an elusive one.
[sqlite] Parser Grammar for MAX,MIN,SUM,COUNT,AVG functions
Prakash Premkumar wrote: > Can you please tell me which grammar rule in parse.y file parses aggregate > function ? As you already were told, there are rules that parse _all_ functions: expr(A) ::= id(X) LP distinct(D) exprlist(Y) RP(E). expr(A) ::= id(X) LP STAR RP(E). Due to the sharing of these rules, the parser allows DISTINCT and * to be used with non-aggregate functions: sqlite> SELECT date(); 2015-09-21 sqlite> SELECT date(DISTINCT); 2015-09-21 sqlite> SELECT date(*); 2015-09-21 (But it wouldn't be a good idea to rely on this implementation ...) Regards, Clemens
[sqlite] How to know when a sqlite3_stmt has been recompiled ?
On 2015-09-21 01:53 AM, Nicolas J?ger wrote: > hi, > > > Scott Robison wrote: > >> 3. Some time passes and some external process may alter the schema. >> > is it really possible ? if the answer is yes (I thought when a process open a > communication with > the DB, other processes can't alter the DB), for me that would mean that the > design(s) of the > program(s) wich communicates with the DB is bad. This is the entire point of a database - allowing multiple things to see the data. A system which only allows one thread to read data is just a file-handler. > If I would like several process to communicate with a DB, I write first a > `demon` wich receive/send > the querries one by one to the DB and send one-by-one the results to the > processes. So with that > design I consider avoiding any overlapping. This is the magic - SQLite does all this for you so you don't need your programs to have daemons for multiple access. All they have to do is abide by the rules, and check the return values to see if the database is busy or not. There is one caveat here, when you would like to access a database file that is on a network from multiple sources, you need something else to control access, but that is just because network file-systems do not do locking well, and why even on the SQLite site it is suggested to use a client-server based system rather in that case.
[sqlite] Parser Grammar for MAX,MIN,SUM,COUNT,AVG functions
On Mon, Sep 21, 2015 at 9:11 AM, Prakash Premkumar wrote: > I'm reading the sqlite parser grammar. > > I could not find the grammar rules which parses aggregate functions like > MAX,MIN,SUM,COUNT,TOTAL. > > Can you please tell me how the aggregate functions are parsed (the grammar > rule that parses them) A call to an aggregate function is parsed the same way as a call to an ordinary function. Sqlite then looks up the function name and number of arguments to determine if this corresponds to an ordinary function or an aggregate function, and changes the meaning of the query according to that. The page http://sqlite.org/lang_select.html details how the meaning of a SELECT statement changes if the selected expression contains an aggregate function: it becomes an aggregate query even if there is no GROUP BY clause, and it computes one result row from all the input rows. Aggregate functions can also be used in the expression of ordinary aggregate queries, containing a GROUP BY clause, or in the HAVING clause of such a query. I probably forgot a few more uses of aggregate functions, but in most other contexts, an aggregate function found in an expression results in an error. Ambrus
[sqlite] How to know when a sqlite3_stmt has been recompiled ?
On Mon, Sep 21, 2015 at 5:48 AM, Richard Hipp wrote: > On 9/21/15, Dominique Devienne wrote: > > On Sun, Sep 20, 2015 at 4:10 PM, Igor Tandetnik > wrote: > > > >> On 9/20/2015 9:55 AM, gwenn wrote: > >> > >>> If there is no way to know that the statement has been recompiled, I > >>> guess that the column count should not be cached... > >>> > >> > >> You could use sqlite3_prepare (no _v2), then you'd get an error on > schema > >> change. You would then re-prepare the statement and update your caches. > > > > > > Could perhaps also use the change counter > > https://www.sqlite.org/fileformat2.html#chngctr, if you're not using WAL > > mode. > > You'd get false positives I guess, since both DML and DDL changes would > > increment it, and I'm not sure that's any different from checking the > > column_count anyway, but just in case it's useful. --DD > > PRAGMA schema_version > (https://www.sqlite.org/pragma.html#pragma_schema_version) does what > you want. > > But here the thing: It is probably far more expensive to run PRAGMA > schema_version than it is to just rerun sqlite3_column_count(). I > think this whole conversation is an exercise in premature > optimization. Has anybody actually *measured* a performance problem > with sqlite3_column_count()? > I have not, and I don't have the environment to check. The original premise as I understand it was that a function call was not expensive because the API function itself was expensive, but transitions through the language binding in use might be too expensive. I indicated the same thought last night, that this might be premature optimization, but even if it is not, I can't imagine any way to get this information (that the schema changed so discard cached column count) without making *some* api call, and all api calls will have the same problem: an expensive language binding transition. One person did suggest using sqlite3_prepare instead of v2, since it will return a schema change error code. My gut instinct is that it'll be easier / at least as performant to just continue to use v2 and not cache column count after finishing stepping / resetting a statement. But as indicated, I can't check that. The only reason I jumped into this however is that several responses weren't answering the asked question, assuring the OP that what was being asked couldn't happen. It can and does by design. In order to cache info beyond a statement reset, there needs to be a way to invalidate it. In any case, I think the knowledge on the list has exhausted its ability to answer. To summarize: 1. Checking the schema version will result in at least one language binding transition (two if the version changed), whereas checking the column count will always only be one transition. Might as well just check the column count. 2. Using prepare vs prepare_v2 would return a schema error, at which point the caller could finalize / re-prepare the statement and update his cached values. This still involves extra language binding transitions to handle the schema change that would have been automatically dealt with by prepare_v2. OP would have to test to see if it benefits his use case, though it seems unlikely to be any faster than using the easier v2 interface and calling column count after each first step. -- Scott Robison
[sqlite] How to know when a sqlite3_stmt has been recompiled ?
On 21 Sep 2015, at 8:29am, Rowan Worth wrote: > 1) Statement is prepared > 2) Statement is stepped > 3) Statement is reset > 4) ... time passes ... > 5) Statement is stepped > 6) Statement is reset > 7) ... time passes ... > etc. etc. > > The assertion seems to be that if the return value of sqlite3_column_count > is cached at step 2 it will still be valid at step 5 Sorry, no. I was saying that if it was cached at step 2 it will still be valid until the end of step 3. And that if cached at step 5 it will still be valid until the end of step 6. My understanding is that the database is locked by the first call to _step() and unlocked at the first of ... (A) _step() returns SQLITE_DONE (B) _reset() is called (C) _finalize() is called As long as you check the column-count at your first call to _step() then you're fine until one of the above happens. If that worries you, put your SELECT command in a transaction. That means no changes can happen until the transaction finishes no matter how many times _reset() is called. That's what I did in my test. Another solution is not to use "SELECT *" but instead to specify the values you want returned. That way you know how many (and which) columns will be returned because it'll be the number of columns that was requested in your SELECT. It'll work or it will result in an error, but there's no way it can return the wrong number of columns. Simon.
[sqlite] vdbeUnbind return occasionally Error
On 9/21/15, Shuhrat Rahimov wrote: > What could I do here in order to free > memory after sqlite3_step() for further operations. https://www.sqlite.org/c3ref/db_release_memory.html -- D. Richard Hipp drh at sqlite.org
[sqlite] vdbeUnbind return occasionally Error
Hi all, I have ported SQLite to an embedded ARM Cortex M4 based processor. a bare metal firmware is using SQLite, no OS. It seems to work but occasionaly bindText function fails. So, I am doing 20 inserts as a one transaction, but occasionally after 400-460 inserts bindText returns Error 21. I have seen that it is occuring in vdbeUnbind function, log is: "bind on a busy prepared statement". Could you help me to find out what the problem can be? Regards
[sqlite] vdbeUnbind return occasionally Error
On 9/21/15, Shuhrat Rahimov wrote: > Hi all, > I have ported SQLite to an embedded ARM Cortex M4 based processor. a bare > metal firmware is using SQLite, no OS. It seems to work but occasionaly > bindText function fails. So, I am doing 20 inserts as a one transaction, > but occasionally after 400-460 inserts bindText returns Error 21. I have > seen that it is occuring in vdbeUnbind function, log is: "bind on a busy > prepared statement". Could you help me to find out what the problem can be? The prepared statement is still in use when you try to call sqlite3_bind_text(). You can see this by adding "assert( sqlite3_stmt_busy(pStmt) );" right before you call sqlite3_bind_text(pStmt,...). This is an application-level error, not a problem with SQLite or your port. You need to invoke sqlite3_reset() on a prepared statement that has been previously used before you run sqlite3_bind_text() on that prepared statement. -- D. Richard Hipp drh at sqlite.org
[sqlite] How to know when a sqlite3_stmt has been recompiled ?
On 9/21/15, Dominique Devienne wrote: > On Sun, Sep 20, 2015 at 4:10 PM, Igor Tandetnik wrote: > >> On 9/20/2015 9:55 AM, gwenn wrote: >> >>> If there is no way to know that the statement has been recompiled, I >>> guess that the column count should not be cached... >>> >> >> You could use sqlite3_prepare (no _v2), then you'd get an error on schema >> change. You would then re-prepare the statement and update your caches. > > > Could perhaps also use the change counter > https://www.sqlite.org/fileformat2.html#chngctr, if you're not using WAL > mode. > You'd get false positives I guess, since both DML and DDL changes would > increment it, and I'm not sure that's any different from checking the > column_count anyway, but just in case it's useful. --DD PRAGMA schema_version (https://www.sqlite.org/pragma.html#pragma_schema_version) does what you want. But here the thing: It is probably far more expensive to run PRAGMA schema_version than it is to just rerun sqlite3_column_count(). I think this whole conversation is an exercise in premature optimization. Has anybody actually *measured* a performance problem with sqlite3_column_count()? -- D. Richard Hipp drh at sqlite.org
[sqlite] How to know when a sqlite3_stmt has been recompiled ?
I did the experiment. I used two Terminal windows accessing the same database. It's not quite the right experiment because I can't figure out how to make the shell tool stop in the middle of a SELECT. So instead I used a transaction to keep the database locked between two SELECT commands. Here's the setup: SQLite version 3.8.10.2 2015-05-20 18:17:19 Enter ".help" for usage hints. sqlite> CREATE TABLE t(c INT); sqlite> INSERT INTO t (c) VALUES (1),(2),(3),(4),(5); sqlite> SELECT * FROM t; 1 2 3 4 5 sqlite> First, an attempt in default journaling mode: In window A: sqlite> PRAGMA journal_mode; delete sqlite> BEGIN; sqlite> SELECT * FROM t; 1 2 3 4 5 In window B: sqlite> INSERT INTO t (c) VALUES (6); Error: database is locked sqlite> ALTER TABLE t ADD COLUMN y TEXT; Error: database is locked So it won't let you do INSERT or ADD COLUMN because the database is locked, as expected. Now try it in WAL mode. (To my surprise I had to .quit and reopen in window B for it to register that the journal mode had changed. Can anyone tell me why ?) In window A: sqlite> END; sqlite> PRAGMA journal_mode = WAL; wal sqlite> PRAGMA journal_mode; wal sqlite> BEGIN; sqlite> SELECT * FROM t; 1 2 3 4 5 In window B (after .quit and reopen): sqlite> PRAGMA journal_mode; wal sqlite> INSERT INTO t (c) VALUES (6); sqlite> ALTER TABLE t ADD COLUMN y TEXT DEFAULT 'unchanged'; sqlite> SELECT * FROM t; 1|unchanged 2|unchanged 3|unchanged 4|unchanged 5|unchanged 6|unchanged Back to window A: sqlite> SELECT * FROM t; 1 2 3 4 5 sqlite> END; sqlite> SELECT * FROM t; 1|unchanged 2|unchanged 3|unchanged 4|unchanged 5|unchanged 6|unchanged As expected, ALTER TABLE acts like INSERT: it's a change which requires an exclusive lock. So just as the documentation says, in a normal journal mode you can't make a change while the database is locked (which it is during a SELECT), and in WAL mode you can make the change but the connection doing the SELECT won't see it until it finishes its transaction. Which gives the grand conclusion I posted earlier: OP does not have to worry about this issue. It can never happen. Your schema cannot be changed on you while you're inside a transaction if you don't intentionally defeat SQLite's locking mechanism. Which is what I'd thought and hoped, since in any scenario where schema can change while a SELECT is running (e.g. anywhere a graphical front end to SQLite was in use) every programmer would have had to write horrible extra code to handle the possibility. Simon.
[sqlite] Remove by name from email list
[sqlite] How to know when a sqlite3_stmt has been recompiled ?
On 21 Sep 2015, at 12:32am, Scott Robison wrote: > 4. Step through the prepared statement from #1 again after doing any > necessary reset. No finalize / re-prepare step. I believe that if you have done the first _step() then the SELECT will not notice any schema change until you have received SQLITE_DONE or done _reset() or _finalize(). In other words, you don't have to worry about the issue. It might be interesting to check this with and without WAL journalling. I agree that if you do a _reset() then you might have to worry about columns being changed. I believe that _reset() releases locks, just like the other two things I mentioned above. Simon.
[sqlite] disable update's & delete's via triggers?
On 21 Sep 2015, at 12:21am, James Hartley wrote: > I am assuming that dropping the trigger > will re-enable any action which had been disabled by creating the trigger. > > Is this incorrect? Nope. That's one way to do it. Another is to set "PRAGMA recursive_triggers = OFF" then have a TRIGGER make changes to the table. Simon.
[sqlite] disable update's & delete's via triggers?
On 21 Sep 2015, at 12:20am, Scott Robison wrote: > Then just go with something like ((rowid - rowid) <> 0). The basic premise > still holds that it should be easy to come up with a condition that is > always false. Hmm. That tells me what I should have used in the first place: WHERE 0 = 1. Simon.
[sqlite] disable update's & delete's via triggers?
On 20 Sep 2015, at 11:59pm, Keith Medcalf wrote: > The RowID is an integer. It is perfectly possible to have RowID's with a > value less than 0. > > sqlite> create table x(x); > sqlite> insert into x (rowid, x) values (-1, -1); > sqlite> select rowid, x from x; > -1|-1 Yep. But it's unlikely in a world where you allow it to be set by autoincrement. Simon.
