Re: [sqlite] Found it

On 1/26/12 9:36 AM, "Stephan Beal" wrote: >On Thu, Jan 26, 2012 at 2:53 PM, Mohit Sindhwani wrote: > >> Absolutely! I come home from work and tune in to this thread, gripped >>:) >> > >+1 to Mohit and the others who's written similar responses. i rarely >follow >threads which don't directly af

Re: [sqlite] Found it

On Thu, Jan 26, 2012 at 12:36 PM, Stephan Beal wrote: > On Thu, Jan 26, 2012 at 2:53 PM, Mohit Sindhwani wrote: > > > Absolutely! I come home from work and tune in to this thread, gripped :) > > > > +1 to Mohit and the others who's written similar responses. i rarely follow > threads which don't

Re: [sqlite] Found it

On Thu, Jan 26, 2012 at 2:53 PM, Mohit Sindhwani wrote: > Absolutely! I come home from work and tune in to this thread, gripped :) > +1 to Mohit and the others who's written similar responses. i rarely follow threads which don't directly affect me, but John Elrick's determination and stamina ha

Re: [sqlite] Found it

On Thu, Jan 26, 2012 at 3:29 AM, Jean-Denis MUYS wrote: > > On 25 janv. 2012, at 22:30, John Elrick wrote: > > > > > I have to say this has been a fascinating learning experience. It has > been > > enlightening reading the SQLite code and beginning to understand its > inner > > workings. > > John

Re: [sqlite] Found it

On 26/1/2012 5:21 AM, Nico Williams wrote: On Wed, Jan 25, 2012 at 3:07 PM, Richard Hipp wrote: The SQLite byte-code engine was being too conservative and was reparsing the schema in places where it was not strictly necessary. The fix was to restrict the places where the schema was reparsed to

Re: [sqlite] Found it

On 25 janv. 2012, at 22:30, John Elrick wrote: > > I have to say this has been a fascinating learning experience. It has been > enlightening reading the SQLite code and beginning to understand its inner > workings. > John > ___ What a thriller! I was holding my breath. Did you thi

Re: [sqlite] Found it

On Wed, Jan 25, 2012 at 4:21 PM, Nico Williams wrote: > On Wed, Jan 25, 2012 at 3:07 PM, Richard Hipp wrote: > > The SQLite byte-code engine was being too conservative and was reparsing > > the schema in places where it was not strictly necessary. The fix was to > > restrict the places where the

Re: [sqlite] Found it

On Wed, Jan 25, 2012 at 4:07 PM, Richard Hipp wrote: > On Wed, Jan 25, 2012 at 4:01 PM, John Elrick >wrote: > > > Much better. I wasn't sure which version to incorporate the changes into > > (the link is to the source), so I put them into 3.7.9. > > > > The total mallocs have dropped to 1.5 mil

Re: [sqlite] Found it

On Wed, Jan 25, 2012 at 3:07 PM, Richard Hipp wrote: > The SQLite byte-code engine was being too conservative and was reparsing > the schema in places where it was not strictly necessary.  The fix was to > restrict the places where the schema was reparsed to situations that really > needed it. Th

Re: [sqlite] Found it

Thanks. On Wed, Jan 25, 2012 at 4:11 PM, Petite Abeille wrote: > > On Jan 25, 2012, at 10:09 PM, John Elrick wrote: > > > DML? > > http://en.wikipedia.org/wiki/Data_Manipulation_Language > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org >

Re: [sqlite] Found it

On Jan 25, 2012, at 10:09 PM, John Elrick wrote: > DML? http://en.wikipedia.org/wiki/Data_Manipulation_Language ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Found it

On Wed, Jan 25, 2012 at 4:07 PM, Richard Hipp wrote: > On Wed, Jan 25, 2012 at 4:01 PM, John Elrick >wrote: > > > Much better. I wasn't sure which version to incorporate the changes into > > (the link is to the source), so I put them into 3.7.9. > > > > The total mallocs have dropped to 1.5 mil

Re: [sqlite] Found it

On Wed, Jan 25, 2012 at 4:01 PM, John Elrick wrote: > Much better. I wasn't sure which version to incorporate the changes into > (the link is to the source), so I put them into 3.7.9. > > The total mallocs have dropped to 1.5 million -- twice as high as 3.7.5 but > in line. I'll have to run some

Re: [sqlite] Found it

Much better. I wasn't sure which version to incorporate the changes into (the link is to the source), so I put them into 3.7.9. The total mallocs have dropped to 1.5 million -- twice as high as 3.7.5 but in line. I'll have to run some additional performance testing but visually the system appear

Re: [sqlite] Found it

On Wed, Jan 25, 2012 at 3:39 PM, Richard Hipp wrote: > I'm testing a candidate fix for your problem now. I'll send you a link > once the tests finish (assuming they all work). Please try http://www.sqlite.org/src/info/11f68d997d and let me know if it solves your problem. Thanks. -- D. Richa

Re: [sqlite] Found it

I'm testing a candidate fix for your problem now. I'll send you a link once the tests finish (assuming they all work). On Wed, Jan 25, 2012 at 3:35 PM, John Elrick wrote: > Richard, > > I've found a problem that I don't understand. Looking at the case > OP_VerifyCookie, I attempted to output pO

Re: [sqlite] Found it

Richard, I've found a problem that I don't understand. Looking at the case OP_VerifyCookie, I attempted to output pOp->opcode, which I expected to equal OP_VerifyCookie. However, it doesn't: Schema has changed current state = 'db=0025, pOp->opCode=66785672, db->aDb[pOp->p1].pSchema=02110AB

Re: [sqlite] Found it

Richard, Is it reasonable to say that, assuming: 1. The schema has not changed 2. There is only one connection to the database that the value of iGeneration should always equal pOp->p3 in OP_VerifyCookie? ___ sqlite-users mailing list sqlite-users@sq

Re: [sqlite] Found it

On Wed, Jan 25, 2012 at 1:13 PM, Richard Hipp wrote: > On Wed, Jan 25, 2012 at 10:18 AM, John Elrick >wrote: > > > > > I do have some more information for you. I tracking everything which > could > > call reset and discovered that all of these are stemming from > > > > SQLITE_PRIVATE int sqlite

Re: [sqlite] Found it

On Wed, Jan 25, 2012 at 10:18 AM, John Elrick wrote: > > I do have some more information for you. I tracking everything which could > call reset and discovered that all of these are stemming from > > SQLITE_PRIVATE int sqlite3VdbeHalt(Vdbe *p){ > >/* Rollback or commit any schema changes

Re: [sqlite] Found it

Addendum On Wed, Jan 25, 2012 at 1:12 PM, John Elrick wrote: > On Wed, Jan 25, 2012 at 9:50 AM, Richard Hipp wrote: > >> On Wed, Jan 25, 2012 at 9:02 AM, John Elrick > >wrote: >> >> > >> > current state = 'db=03FB1188, db->aDb[pOp->p1].pSchema=02110AB0, >> > db->aDb[pOp->p1].pSchema->schema_cook

Re: [sqlite] Found it

On Wed, Jan 25, 2012 at 9:50 AM, Richard Hipp wrote: > On Wed, Jan 25, 2012 at 9:02 AM, John Elrick >wrote: > > > > > current state = 'db=03FB1188, db->aDb[pOp->p1].pSchema=02110AB0, > > db->aDb[pOp->p1].pSchema->schema_cookie=27, > > db->aDb[pOp->p1].pSchema->iGeneration=63, u.av.iMeta=27, u.av

Re: [sqlite] Found it

Ok. Possible scenario. I can't prove it, but somebody tell me if this is what could be going on: prepare query1 prepare query2 create something -> schema change query1.step - schema invalid so recreate - recreation of schema changes the generation query2.step - schema invalid so recreate

Re: [sqlite] Found it

On Wed, Jan 25, 2012 at 10:32 AM, Simon Slavin wrote: > > On 25 Jan 2012, at 3:18pm, John Elrick wrote: > > > So, it appears that something is making SQLite think the schema has > changed. > > Can you read the following: > > > > If you thi

Re: [sqlite] Found it

On 25 Jan 2012, at 3:18pm, John Elrick wrote: > So, it appears that something is making SQLite think the schema has changed. Can you read the following: If you think this corresponds to what you are seeing in the source code, can you l

Re: [sqlite] Found it

On Wed, Jan 25, 2012 at 9:50 AM, Richard Hipp wrote: > On Wed, Jan 25, 2012 at 9:02 AM, John Elrick >wrote: > > > > > current state = 'db=03FB1188, db->aDb[pOp->p1].pSchema=02110AB0, > > db->aDb[pOp->p1].pSchema->schema_cookie=27, > > db->aDb[pOp->p1].pSchema->iGeneration=63, u.av.iMeta=27, u.av

Re: [sqlite] Found it

On Wed, Jan 25, 2012 at 9:02 AM, John Elrick wrote: > > current state = 'db=03FB1188, db->aDb[pOp->p1].pSchema=02110AB0, > db->aDb[pOp->p1].pSchema->schema_cookie=27, > db->aDb[pOp->p1].pSchema->iGeneration=63, u.av.iMeta=27, u.av.iGen=63, > pOp->p1=1, pOp->p2=27, pOp->p3=0' > > sql = 'update aler

Re: [sqlite] Found it

One more bit of interesting information. if( db->aDb[pOp->p1].pSchema->schema_cookie!=u.av.iMeta ){ fenestra_sqlite3_dump_stack_trace("OP_VerifyCookie"); sqlite3ResetInternalSchema(db, pOp->p1); } The call to fenestra_sqlite3_dump_stack_trace is never invoked. Whatever is resetting

Re: [sqlite] Found it

On Tue, Jan 24, 2012 at 7:46 PM, Richard Hipp wrote: > On Tue, Jan 24, 2012 at 7:37 PM, John Elrick >wrote: > > > > > It appears that the value is set here: > > > > u.av.pBt = db->aDb[pOp->p1].pBt; > > if( u.av.pBt ){ > >sqlite3BtreeGetMeta(u.av.pBt, BTREE_SCHEMA_VERSION, (u32 > *)&u.av.iM

Re: [sqlite] Found it

On Wed, Jan 25, 2012 at 8:02 AM, Richard Hipp wrote: > On Wed, Jan 25, 2012 at 7:53 AM, John Elrick >wrote: > > > I started with the place where iGeneration was being changed and found > > nothing out of the ordinary. I started an examination and noticed the > > following. My "huh?" surrounds

Re: [sqlite] Found it

On Wed, Jan 25, 2012 at 7:53 AM, John Elrick wrote: > I started with the place where iGeneration was being changed and found > nothing out of the ordinary. I started an examination and noticed the > following. My "huh?" surrounds the question, why is sqlite3SchemaClear > being called if pBt is a

Re: [sqlite] Found it

...especially given that the results of sqlite3SchemaClear are being passed as a parameter and from the declaration, sqlite3SchemaClear doesn't return anything. On Wed, Jan 25, 2012 at 7:53 AM, John Elrick wrote: > I started with the place where iGeneration was being changed and found > nothing o

Re: [sqlite] Found it

I started with the place where iGeneration was being changed and found nothing out of the ordinary. I started an examination and noticed the following. My "huh?" surrounds the question, why is sqlite3SchemaClear being called if pBt is assigned? SQLITE_PRIVATE Schema *sqlite3SchemaGet(sqlite3 *db

Re: [sqlite] Found it

On Tue, Jan 24, 2012 at 7:37 PM, John Elrick wrote: > > It appears that the value is set here: > > u.av.pBt = db->aDb[pOp->p1].pBt; > if( u.av.pBt ){ >sqlite3BtreeGetMeta(u.av.pBt, BTREE_SCHEMA_VERSION, (u32 *)&u.av.iMeta); >u.av.iGen = db->aDb[pOp->p1].pSchema->iGeneration; > }else{ >

Re: [sqlite] Found it

On Tue, Jan 24, 2012 at 7:26 PM, Richard Hipp wrote: > On Tue, Jan 24, 2012 at 7:18 PM, John Elrick >wrote: > SNIP > So the problem was introduced by > > http://www.sqlite.org/src/info/36c04dd1695f08 > > Which is the fix for this bug: > > http://www.sqlite.org/src/info/f7b4edece25c99

Re: [sqlite] Found it

On Tue, Jan 24, 2012 at 7:18 PM, John Elrick wrote: > I really found it this time. > > While I was looking over the code changes, I noticed a section of code > dealing with case OP_VerifyCookie: > > On a hunch, I replaced this line: > > if( u.av.iMeta!=pOp->p2 || u.av.iGen!=pOp->p3 ){ > > with th

Re: [sqlite] Found it

I really found it this time. While I was looking over the code changes, I noticed a section of code dealing with case OP_VerifyCookie: Inside that section is the following in 3.7.6 if( u.av.iMeta!=pOp->p2 || u.av.iGen!=pOp->p3 ){ sqlite3DbFree(db, p->zErrMsg); p->zErrMsg = sqlite3DbStr

Re: [sqlite] Found it

On Tue, Jan 24, 2012 at 5:58 PM, John Elrick wrote: > Let me rephrase that... > > > > http://www.sqlite.org/fenstra/sqlite-201102040051.zip > > 8,191 calls to malloc (consistent with 3.7.5 for this dataset) > >> > > http://www.sqlite.org/fenstra/sqlite-201102112254.zip > > > 14,726 calls to malloc

Re: [sqlite] Found it

Let me rephrase that... I ran this test with a small case. See results below. On Tue, Jan 24, 2012 at 5:55 PM, John Elrick wrote: SNIP > > http://www.sqlite.org/fenstra/sqlite-201102040051.zip >> > > Low malloc count > 8,191 calls to malloc (consistent with 3.7.5 for this dataset) > > >> >>

Re: [sqlite] Found it

On Tue, Jan 24, 2012 at 5:36 PM, Richard Hipp wrote: > On Tue, Jan 24, 2012 at 5:21 PM, John Elrick >wrote: > > > On Tue, Jan 24, 2012 at 3:05 PM, Richard Hipp wrote: > > > > > > > > OK. Please try these: > > > > > > http://www.sqlite.org/fenstra/sqlite-201102040051.zip > Low malloc count >

Re: [sqlite] Found it

On Tue, Jan 24, 2012 at 5:21 PM, John Elrick wrote: > On Tue, Jan 24, 2012 at 3:05 PM, Richard Hipp wrote: > > > > > > OK. Please try these: > > > > http://www.sqlite.org/fenstra/sqlite-201102040051.zip > > http://www.sqlite.org/fenstra/sqlite-201102112254.zip > > > > > Both of these are low mal

Re: [sqlite] Found it

On Tue, Jan 24, 2012 at 3:05 PM, Richard Hipp wrote: > On Tue, Jan 24, 2012 at 1:28 PM, John Elrick >wrote: > > > > > > > > http://www.sqlite.org/fenstra/sqlite-201103081639.zip > > > > > > This one gives the high number of mallocs just as does 3.7.6. > > > > OK. Please try these: > > http://ww

Re: [sqlite] Found it

Nope and nope. They are happening apparently out of the blue. On Jan 24, 2012 2:40 PM, "Nico Williams" wrote: > Maybe a view is getting materialized, or a some other temp table's > creation under the hood is triggering this? > ___ > sqlite-users mailin

Re: [sqlite] Found it

On Tue, Jan 24, 2012 at 1:28 PM, John Elrick wrote: > > > > http://www.sqlite.org/fenstra/sqlite-201103081639.zip > > > This one gives the high number of mallocs just as does 3.7.6. > > OK. Please try these: http://www.sqlite.org/fenstra/sqlite-201102040051.zip http://www.sqlite.org/fenstra/sqli

Re: [sqlite] Found it

Maybe a view is getting materialized, or a some other temp table's creation under the hood is triggering this? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Found it

On Tue, Jan 24, 2012 at 12:53 PM, Richard Hipp wrote: > On Tue, Jan 24, 2012 at 12:15 PM, John Elrick >wrote: > > > > > > > Can you put a breakpoint on sqlite3ResetInternalSchema() and figure out > > > from whence it is being called so often? > > > > > > > > > > Just did. Most of the calls are

Re: [sqlite] Found it

On 24 January 2012 19:32, John Elrick wrote: > On Tue, Jan 24, 2012 at 1:29 PM, Krystian Bigaj >wrote: > > You can compile sqlite3.dll with debug information, and then you can in > VS > > attach to your running .exe - you should be able to see sqlite > > stacks/breakpoints/step-in/out/etc. shoul

Re: [sqlite] Found it

On Tue, Jan 24, 2012 at 1:28 PM, John Elrick wrote: SNIP http://www.sqlite.org/fenstra/sqlite-201103241737.zip > > > This one is failing to compile. It needs _msize. That sounds like the > 3.7.10 issue reported recently. > > I opened the sqlite3.c file and it is version 3.7.10.

Re: [sqlite] Found it

On Tue, Jan 24, 2012 at 1:29 PM, Krystian Bigaj wrote: > You can compile sqlite3.dll with debug information, and then you can in VS > attach to your running .exe - you should be able to see sqlite > stacks/breakpoints/step-in/out/etc. should also work. > > Thanks very much for the suggestion. We

Re: [sqlite] Found it

You can compile sqlite3.dll with debug information, and then you can in VS attach to your running .exe - you should be able to see sqlite stacks/breakpoints/step-in/out/etc. should also work. Best regards, Krystian Bigaj On 24 January 2012 19:25, John Elrick wrote: > On Tue, Jan 24, 2012 at 1:

Re: [sqlite] Found it

On Tue, Jan 24, 2012 at 12:53 PM, Richard Hipp wrote: > On Tue, Jan 24, 2012 at 12:15 PM, John Elrick >wrote: > > SNIP > http://www.sqlite.org/fenstra/sqlite-201103081639.zip This one gives the high number of mallocs just as does 3.7.6. > > http://www.sqlite.org/fenstra/sqlite-201103241737

Re: [sqlite] Found it

On Tue, Jan 24, 2012 at 1:18 PM, Dan Kennedy wrote: > On 01/25/2012 12:15 AM, John Elrick wrote: > >> On Tue, Jan 24, 2012 at 12:06 PM, Richard Hipp wrote: >> >> On Tue, Jan 24, 2012 at 11:59 AM, John >> Elrick >>> wrote: >>> >>> While reviewing our other thread, I noticed a piece o

Re: [sqlite] Found it

On 01/25/2012 12:15 AM, John Elrick wrote: On Tue, Jan 24, 2012 at 12:06 PM, Richard Hipp wrote: On Tue, Jan 24, 2012 at 11:59 AM, John Elrick wrote: While reviewing our other thread, I noticed a piece of information concerning sqlite3ResetInternalSchema. Based on the number of times that

Re: [sqlite] Found it

On Tue, Jan 24, 2012 at 12:15 PM, John Elrick wrote: > > > > Can you put a breakpoint on sqlite3ResetInternalSchema() and figure out > > from whence it is being called so often? > > > > > > Just did. Most of the calls are bubbling up from _sqlite3_step which I > mentioned before. There are no ne

Re: [sqlite] Found it

On Tue, Jan 24, 2012 at 12:06 PM, Richard Hipp wrote: > On Tue, Jan 24, 2012 at 11:59 AM, John Elrick >wrote: > > > While reviewing our other thread, I noticed a piece of information > > concerning sqlite3ResetInternalSchema. Based on the number of times > that I > > reported it had been called

Re: [sqlite] Found it

On Tue, Jan 24, 2012 at 11:59 AM, John Elrick wrote: > While reviewing our other thread, I noticed a piece of information > concerning sqlite3ResetInternalSchema. Based on the number of times that I > reported it had been called, I believe that something was changed between > 3.7.5 and 3.7.6 whic

Re: [sqlite] Found it

While reviewing our other thread, I noticed a piece of information concerning sqlite3ResetInternalSchema. Based on the number of times that I reported it had been called, I believe that something was changed between 3.7.5 and 3.7.6 which causes sqlite3ResetInternalSchema to be called when it wasn'

Re: [sqlite] Found it

Addendum. After looking further it appears that the reparsing is happening independently of triggering. In this case, the reparsing occurs 30,502 times for every view and trigger I have examined. Only one trigger appears in the 3.7.5 listing and it is parsed exactly 1 time. 3.7.5 CREATE TRIGGE

[sqlite] Found it

Richard, Your last request for some detailed information about what queries were being processed did the trick. Here is a description of the problem (and yes, it appears to be in SQLite). In 3.7.5, the number one malloc consumer is INSERT INTO SEQUENCE_ELEMENTS ( SEQUENCE_ELEMENT_OID, SEQUENCE_

Re: [sqlite] FOUND IT!!! What is the PDO last_insert_id method???`

Hey all, Found the PHP site page with all the PDO functions... gadzoooks! Skip Skip Evans wrote: > Hey all, > > I'm new to SQLite and having a heck of a time > finding the equivalent to MySQL's > > mysql_insert_id(). > > I'm using SQLite with PDO in PHP5. -- Skip Evans Big Sky Pengui