Re: [sqlite] database disk image is malformed
It was a typo. I was setting fullsync when the correct pragma name is
fullfsync.
On Fri, Sep 26, 2008 at 9:50 AM, Jeffrey Rennie (レニー） <[EMAIL PROTECTED]>wrote:
>
> 2008/9/25 D. Richard Hipp <[EMAIL PROTECTED]>
>
>>
>> You are confusing the statement journal with the rollback journal.
>> The statement journal has nothing to do with database recovery - that
>> is the task of the rollback journal. So the statement journal can be
>> deleted at will without damaging the database. And, in fact, the
>> statement journal is opened with delete-on-close.
>>
>
> Indeed I was.
>
> I gained some more insight into the problem. I set a breakpoint on line
> 950 of os_unix.cc:
>
>#if HAVE_FULLFSYNC
> if( fullSync ){
> >>>rc = fcntl(fd, F_FULLFSYNC, 0);
> }else{
>rc = 1;
> }
>
>
> And the breakpoint never hits, no matter what pragmas I set! Instead, it
> always executes the rc = 1; line.
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database disk image is malformed
2008/9/25 D. Richard Hipp <[EMAIL PROTECTED]>
>
> You are confusing the statement journal with the rollback journal.
> The statement journal has nothing to do with database recovery - that
> is the task of the rollback journal. So the statement journal can be
> deleted at will without damaging the database. And, in fact, the
> statement journal is opened with delete-on-close.
>
Indeed I was.
I gained some more insight into the problem. I set a breakpoint on line 950
of os_unix.cc:
#if HAVE_FULLFSYNC
if( fullSync ){
>>>rc = fcntl(fd, F_FULLFSYNC, 0);
}else{
rc = 1;
}
And the breakpoint never hits, no matter what pragmas I set! Instead, it
always executes the rc = 1; line.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database disk image is malformed
I noticed this bug fix in 3.5.7: - Store the statement journal in the temporary file directory instead of colocated with the database file. This seems fraught. Aren't temporary directories routinely purged? If you get unlucky and the temp directory is purged immediately after you reboot, then your DB will be corrupt. It also makes it much harder to debug issues on clients' machines, because you can no longer just say "send me all the files in the folder yadayada..." 2008/9/25 Jeffrey Rennie (レニー） <[EMAIL PROTECTED]> > Small correction: I'm using sqlite version 3.5.9. > > > On Thu, Sep 25, 2008 at 1:08 PM, Jeffrey Rennie (レニー） <[EMAIL > PROTECTED]>wrote: > >> I'm seeing this happen on Mac OS X, after the user hard reboots his >> computer. I'm compiling with -DHAVE_FULLFSYNC=1 and running with PRAGMA >> synchronous = 2. >> >> Is there anything else I should set to avoid this? I'm using >> sqlite3.3.4. Has this bug been fixed in a more recent version? >> >> Sincerely, >> Jeffrey Rennie >> > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database disk image is malformed
Small correction: I'm using sqlite version 3.5.9. On Thu, Sep 25, 2008 at 1:08 PM, Jeffrey Rennie (レニー） <[EMAIL PROTECTED]>wrote: > I'm seeing this happen on Mac OS X, after the user hard reboots his > computer. I'm compiling with -DHAVE_FULLFSYNC=1 and running with PRAGMA > synchronous = 2. > > Is there anything else I should set to avoid this? I'm using sqlite3.3.4. > Has this bug been fixed in a more recent version? > > Sincerely, > Jeffrey Rennie > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance on HP
Are there any other processes or threads trying to open your db file while
you run your tests?
On Mon, Jun 23, 2008 at 9:48 AM, Andrea Connell <[EMAIL PROTECTED]>
wrote:
>
>
> >> The program took 47 seconds to run, but the results only account for
> >> .39 seconds
> >
> > Most likely all the time is being spent in IO related system calls
> > - read(), write() and fsync().
> >
> > Dan.
>
>
> Thanks for the idea Dan. How can I confirm this or try reducing the time
> spent? I use the same method for reading my input file when I run both
> SQLite and our in house system, and the other way only takes 4 seconds
> total so I don't think it could be from that. Also, when I run our in
> house system and use the profiler the time spent adds up to 100%
>
> So this must be something within SQLite. I am using a transaction for my
> queries. How can I find all of that missing time?
>
>
> If anybody is interested, here is my main chunk of code. ReadLine()
> parses the input file and fills the required variables. This method is
> shared for both database systems (SQLite and ours). ReadSQLiteComponent
> just calls one of the sqlite3_column functions based on the type of the
> field, and a similar method is used for our system.
>
>
>std::ifstream inf(argv[1]);
>
>sqlite3 *db;
>sqlite3_stmt *stmt;
>sqlite3_stmt *stmt2;
>sqlite3_stmt *stmt3;
>int rc = sqlite3_open(argv[3], );
>if( rc )
>{
>printf("Can't open database: %s\n",
> sqlite3_errmsg(db));
>sqlite3_close(db);
>return -1;
>}
>sqlite3_prepare(db,"BEGIN TRANSACTION;", 100, ,0);
>sqlite3_step(stmt);
>
>char * qry = "SELECT * FROM LEVEL1 WHERE COUNTRY_ID = ?
> AND DIR_SEARCH_AREA1 = ? AND ADDRESS_TYPE = ? AND PHONETIC_KEY LIKE ?
> ;";
>int p = sqlite3_prepare_v2(db,qry,1000,,0);
> char * qry2 = "SELECT * FROM LEVEL2 WHERE PARENT_KEY = ?
> AND PRIM_NBR_LOW <= ? AND PRIM_NBR_HIGH >= ?;";
> int p2 = sqlite3_prepare_v2(db,qry2,1000,,0);
> char* qry3 = "SELECT * FROM LEVEL3 WHERE PARENT_KEY = ?
> ;";
> int p3 = sqlite3_prepare_v2(db,qry3,1000,,0);
>if ( p || p2 || p3 )
>{
>printf("Can't create prepared statement: %s\n",
> sqlite3_errmsg(db));
>sqlite3_close(db);
>return -1;
>}
>
>while (ReadLine(inf))
>{
>sqlite3_bind_text(stmt, 1, cntryid, -1,
> SQLITE_TRANSIENT);
>sqlite3_bind_int(stmt, 2, searcharea);
>sqlite3_bind_text(stmt, 3, addrtype, -1,
> SQLITE_TRANSIENT);
>int len = strlen(phnkey);
>phnkey[len] = '%';
>phnkey[len+1] = '\0';
>sqlite3_bind_text(stmt, 4, phnkey, -1,
> SQLITE_TRANSIENT);
>
>while(sqlite3_step(stmt)==SQLITE_ROW)
>{
>for(int i=0; i{
>ReadSQLiteComponent(0,i, stmt);
>if (i==51) //LEVEL1.RECORDKEY
>
> sqlite3_bind_int(stmt2,1,sqlite3_column_int(stmt, i));
>}
>sqlite3_bind_text(stmt2,2,prmlow, -1,
> SQLITE_TRANSIENT);
>sqlite3_bind_text(stmt2,3,prmhigh, -1,
> SQLITE_TRANSIENT);
>while(sqlite3_step(stmt2)==SQLITE_ROW)
>{
>for(int i=0; i i++)
>{
>ReadSQLiteComponent(1,i,
> stmt2);
>if (i==27)
> //LEVEL2.RECORDKEY
>
> sqlite3_bind_int(stmt3,1,sqlite3_column_int(stmt2, i));
>}
>
> while(sqlite3_step(stmt3)==SQLITE_ROW)
>{
>for(int i=0;
> i
> ReadSQLiteComponent(2,i, stmt3);
>q++;
>}
>sqlite3_reset(stmt3);
>}
>sqlite3_reset(stmt2);
>}
>sqlite3_reset(stmt);
>}
>
>sqlite3_prepare(db,"END TRANSACTION;", 100, ,0);
>sqlite3_step(stmt);
>
>sqlite3_finalize(stmt);
>sqlite3_finalize(stmt2);
>
[sqlite] One sqlite*, multiple threads
I looked in the documentation, and scanned the source code, but haven't yet been able to answer this question for myself: If two threads are simultaneously trying to execute READ transactions on a shared sqlite*, will they both progress simultaneously? Or will it be one after the other? In other words, is there a simple mutex in the sqlite* or a read/write lock? Sincerely, Jeffrey Rennie ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implicit INDEX?
On Mon, Apr 14, 2008 at 9:48 AM, Chris Tracy <[EMAIL PROTECTED]> wrote: > >> inserted into the database in ascending order, and where there may be > as > >> many as 500 hID entries for each timestamp. After a while, this table > > > Have you considered making timestamp a PRIMARY KEY? > > > > So, declare it as INTEGER PRIMARY KEY NOT NULL > >Unfortunately, the timestamps aren't unique, so I can't use > PRIMARY KEY to solve the problem. (Each run generates as many as 500 > entries, all with the same timestamp) > Are there ever identical rows? If not, just make the whole row a primary key: CREATE TABLE bridge_table ( timestamp INTEGER NOT NULL, hID INTEGER NOT NULL, sID INTEGER NOT NULL, pID INTEGER NOT NULL, *PRIMARY KEY (timestamp, hID, sID, pID) *); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Equal distribution from random rows
Here's a naive solution, which requires some programming language
around the SQL:
BEGIN TRANSACTION
n = (SELECT count(*) from table)
i = RandBetween(0, n)
row = (SELECT * from table LIMIT 1 OFFSET i)
END TRANSACTION
I'm posting this because I suspect that this naive solution isn't
correct, but I don't know why, and want to find out.
On May 30, 2007 3:55 PM, Alex Teslik <[EMAIL PROTECTED]> wrote:
On Wed, 30 May 2007 15:18:18 -0400, John Elrick wrote
> After running a simple test, I confirmed a suspicion. VACUUM
> doesn't reorder the ROWIDs, so you still have breaks.
My tests show otherwise:
[alex]# cat 01_vacuum_table_test.sql
CREATE TABLE foo (
string varchar(1) not null
);
INSERT INTO foo (string) VALUES ('a');
INSERT INTO foo (string) VALUES ('b');
INSERT INTO foo (string) VALUES ('c');
INSERT INTO foo (string) VALUES ('d');
[alex]# sqlite3 foo.db < 01_vacuum_table_test.sql
[alex]# sqlite3 --header --column foo.db "SELECT rowid,* FROM foo"
rowid string
-- --
1 a
2 b
3 c
4 d
[alex]# sqlite3 --header --column foo.db "DELETE FROM foo WHERE rowid = 2"
[alex]# sqlite3 --header --column foo.db "DELETE FROM foo WHERE rowid = 4"
[alex]# sqlite3 --header --column foo.db "SELECT rowid,* FROM foo"
rowid string
-- --
1 a
3 c
[alex]# sqlite3 --header --column foo.db "VACUUM"
[alex]# sqlite3 --header --column foo.db "SELECT rowid,* FROM foo"
rowid string
-- --
1 a
2 c
did I do something incorrectly?
>
> Two suggestions:
>
> 1. If the image data are fairly stable, copy the table to a new
> table so as to eliminate the breaks in the ROWIDs.
> 2. If the table is relatively small, load all the potential ids
> into memory as a list and select randomly from the list.
The table is small now, but expected to grow considerably to over a million
rows. I coded a random selector from an array at the application level, and it
works, but it will degrade dramatically over time.
Thanks,
Alex
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Running out of disk space.
On 2/9/07, Dennis Cote <[EMAIL PROTECTED]> wrote:
Jeffrey Rennie wrote:
> I think the code in the next higher stackframe may be the culprit.
>
> I inserted a new line of code at vbde.c:2374 so it now reads:
>
>if( pOp->p2 ){
> assert( i==1 );
> sqlite3RollbackAll(db);
> db->autoCommit = 1;
>}else{
> db->autoCommit = i;
> if( sqlite3VdbeHalt(p)==SQLITE_BUSY ){
>p->pTos = pTos;
>p->pc = pc;
>db->autoCommit = 1-i;
>p->rc = SQLITE_BUSY;
>return SQLITE_BUSY;
> }
> return SQLITE_OK == p->rc ? SQLITE_DONE : p->rc; // my new line
>}
>return SQLITE_DONE;
>
>
> And sqlite_step() now returns SQLITE_FULL as I had expected.
>
Jeffrey,
I'm a little suspicious of your fix. You said you are using version
3.3.4 and it only has the older version of sqlite_step which is
documented as only returning a subset of the sqlite error codes at
http://www.sqlite.org/capi3ref.html#sqlite3_step
In the lagacy interface, the return value will be either
SQLITE_BUSY, SQLITE_DONE, SQLITE_ROW, SQLITE_ERROR, or SQLITE_MISUSE.
So it should never return SQLITE_FULL. Under a disk full condition it
should return SQLITE_ERROR, and then you would get the SQLITE_FULL error
when you called sqlite_reset (see the section Goofy Interface Alert).
Indeed, my fix does not conform to the documentation.
Nonetheless, you are saying you are getting an SQLITE_DONE when the disk
is full.
Yes, I'm still seeing SQLITE_DONE when the disk is full.
But thanks for the pointer to the Goofy Interface Alert! Even though the
sqlite_step() returns SQLITE_DONE, the sqlite_finalize() call returns
SQLITE_FULL, so I am able to detect the disk full situation.
Thanks again! My problem is solved.
-Jeffrey Rennie
Re: [sqlite] Running out of disk space.
I think the code in the next higher stackframe may be the culprit.
I inserted a new line of code at vbde.c:2374 so it now reads:
if( pOp->p2 ){
assert( i==1 );
sqlite3RollbackAll(db);
db->autoCommit = 1;
}else{
db->autoCommit = i;
if( sqlite3VdbeHalt(p)==SQLITE_BUSY ){
p->pTos = pTos;
p->pc = pc;
db->autoCommit = 1-i;
p->rc = SQLITE_BUSY;
return SQLITE_BUSY;
}
return SQLITE_OK == p->rc ? SQLITE_DONE : p->rc; // my new line
}
return SQLITE_DONE;
And sqlite_step() now returns SQLITE_FULL as I had expected.
On 2/9/07, Dennis Cote <[EMAIL PROTECTED]> wrote:
Jeffrey Rennie wrote:
> Debugging the code:
>
> winWrite returns SQLITE_FULL, which propagates back up the stack to
> vdbeaux.c, line 1270, in function sqlite3VdbeHalt(Vdbe *p):
>
> }else if( rc!=SQLITE_OK ){
> p->rc = rc;
> sqlite3RollbackAll(db);
>
> Which is good, it's putting the SQLITE_FULL return code into p->rc and
> rolling everything back. Good.
>
> But then the function returns SQLITE_OK on line 1337, so sqlite_step
> returns
> SQLITE_DONE.
>
> So indeed, when a COMMIT TRANSACTION fails because there isn't enough
> disk
> space, sqlite_step returns SQLITE_DONE.
>
> Is there a bug filed for this? Has it been fixed in more recent
> releases?
>
Jeffrey,
This is not the problem. The assignment at 1270 is saving the error
return value into the sqlite3_stmt (or vdeb) structure to record the
failure of this statement. The value returned at 1337 simply tells the
caller that this op-code (Halt) executed correctly. This op-code is only
one step in the execution of the statement.
I'm not saying you haven't found a problem with respect to full disks,
but this code is not the culprit. You will need to keep digging.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Running out of disk space.
Debugging the code:
winWrite returns SQLITE_FULL, which propagates back up the stack to
vdbeaux.c, line 1270, in function sqlite3VdbeHalt(Vdbe *p):
}else if( rc!=SQLITE_OK ){
p->rc = rc;
sqlite3RollbackAll(db);
Which is good, it's putting the SQLITE_FULL return code into p->rc and
rolling everything back. Good.
But then the function returns SQLITE_OK on line 1337, so sqlite_step returns
SQLITE_DONE.
So indeed, when a COMMIT TRANSACTION fails because there isn't enough disk
space, sqlite_step returns SQLITE_DONE.
Is there a bug filed for this? Has it been fixed in more recent releases?
On 2/9/07, Jeffrey Rennie <[EMAIL PROTECTED]> wrote:
It looks like the journal file itself is running out of disk space. It
has only 512 bytes, even though I'm creating lots of tables in the
transaction, and the DB file itself is stuck at 0 bytes. Then, COMMIT
TRANSACTION returns SQLITE_DONE, the journal file disappears, and I'm left
with a db of size 0 bytes.
There is 6144 bytes of free disk space on my drive.
On 2/9/07, Jeffrey Rennie <[EMAIL PROTECTED]> wrote:
>
> Thanks Artem. Your description of events agrees with the documentation
> and what I would expect to happen, but not with what I'm observing in
> running code.
>
> I see that the sqlite_step() for the "COMMIT TRANSACTION" returns
> SQLITE_DONE, but then the changes in the transaction have been rolled back.
>
> On 2/8/07, Artem Yankovskiy < [EMAIL PROTECTED]> wrote:
> >
> > Hi.
> > You do not receive the error message until receive 0
> > free disk spaces.
> > When queryes are running in transaction, record in a
> > DB does not write, the journal-file is created only,
> > therefore you see your changes. As soon as you make
> > commit, there is a records of changes in a DB. During
> > this moment there can be an ending of an empty space
> > on volume, then sqlite will return an error and will
> > roll away changes.
> >
> > --- Jeffrey Rennie < [EMAIL PROTECTED]> wrote:
> >
> > > What happens, and/or what is supposed to happen when
> > > sqlite runs out of disk
> > > space?
> > >
> > > In an extremely disk-space constrained situation, I
> > > create a bunch of
> > > tables, without any sqlite errors, and then later
> > > the tables are not found.
> > > I see the same thing when inserting rows: no error,
> > > but later look-ups don't
> > > find inserted rows. I'd like to detect that the
> > > write to DB failed at time
> > > of write, not a later read. I'm also doing the
> > > INSERTS and CREATE TABLES
> > > within a transaction, and again all the sqlite calls
> > > succeed, even the
> > > COMMIT TRANSACTION. There are no other pending
> > > statements at the time of
> > > the COMMIT TRANSACTION.
> > >
> > > I'm using version 3.3.4 on Windows.
> > >
> > > Thanks,
> > > Jeffrey Rennie
> > >
> >
> >
> > Best regards,
> > Artem Yankovskiy
> >
> >
> >
> >
> >
> >
> >
> > Вы уже с Yahoo!?
> > Испытайте обновленную и улучшенную. Yahoo! Почту!
> > http://ru.mail.yahoo.com
> >
> >
> >
-
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
-
> >
> >
> >
>
Re: [sqlite] Running out of disk space.
It looks like the journal file itself is running out of disk space. It has only 512 bytes, even though I'm creating lots of tables in the transaction, and the DB file itself is stuck at 0 bytes. Then, COMMIT TRANSACTION returns SQLITE_DONE, the journal file disappears, and I'm left with a db of size 0 bytes. There is 6144 bytes of free disk space on my drive. On 2/9/07, Jeffrey Rennie <[EMAIL PROTECTED]> wrote: Thanks Artem. Your description of events agrees with the documentation and what I would expect to happen, but not with what I'm observing in running code. I see that the sqlite_step() for the "COMMIT TRANSACTION" returns SQLITE_DONE, but then the changes in the transaction have been rolled back. On 2/8/07, Artem Yankovskiy <[EMAIL PROTECTED]> wrote: > > Hi. > You do not receive the error message until receive 0 > free disk spaces. > When queryes are running in transaction, record in a > DB does not write, the journal-file is created only, > therefore you see your changes. As soon as you make > commit, there is a records of changes in a DB. During > this moment there can be an ending of an empty space > on volume, then sqlite will return an error and will > roll away changes. > > --- Jeffrey Rennie < [EMAIL PROTECTED]> wrote: > > > What happens, and/or what is supposed to happen when > > sqlite runs out of disk > > space? > > > > In an extremely disk-space constrained situation, I > > create a bunch of > > tables, without any sqlite errors, and then later > > the tables are not found. > > I see the same thing when inserting rows: no error, > > but later look-ups don't > > find inserted rows. I'd like to detect that the > > write to DB failed at time > > of write, not a later read. I'm also doing the > > INSERTS and CREATE TABLES > > within a transaction, and again all the sqlite calls > > succeed, even the > > COMMIT TRANSACTION. There are no other pending > > statements at the time of > > the COMMIT TRANSACTION. > > > > I'm using version 3.3.4 on Windows. > > > > Thanks, > > Jeffrey Rennie > > > > > Best regards, > Artem Yankovskiy > > > > > > > > Вы уже с Yahoo!? > Испытайте обновленную и улучшенную. Yahoo! Почту! > http://ru.mail.yahoo.com > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > >
Re: [sqlite] Running out of disk space.
Thanks Artem. Your description of events agrees with the documentation and what I would expect to happen, but not with what I'm observing in running code. I see that the sqlite_step() for the "COMMIT TRANSACTION" returns SQLITE_DONE, but then the changes in the transaction have been rolled back. On 2/8/07, Artem Yankovskiy <[EMAIL PROTECTED]> wrote: Hi. You do not receive the error message until receive 0 free disk spaces. When queryes are running in transaction, record in a DB does not write, the journal-file is created only, therefore you see your changes. As soon as you make commit, there is a records of changes in a DB. During this moment there can be an ending of an empty space on volume, then sqlite will return an error and will roll away changes. --- Jeffrey Rennie <[EMAIL PROTECTED]> wrote: > What happens, and/or what is supposed to happen when > sqlite runs out of disk > space? > > In an extremely disk-space constrained situation, I > create a bunch of > tables, without any sqlite errors, and then later > the tables are not found. > I see the same thing when inserting rows: no error, > but later look-ups don't > find inserted rows. I'd like to detect that the > write to DB failed at time > of write, not a later read. I'm also doing the > INSERTS and CREATE TABLES > within a transaction, and again all the sqlite calls > succeed, even the > COMMIT TRANSACTION. There are no other pending > statements at the time of > the COMMIT TRANSACTION. > > I'm using version 3.3.4 on Windows. > > Thanks, > Jeffrey Rennie > Best regards, Artem Yankovskiy Вы уже с Yahoo!? Испытайте обновленную и улучшенную. Yahoo! Почту! http://ru.mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Running out of disk space.
What happens, and/or what is supposed to happen when sqlite runs out of disk space? In an extremely disk-space constrained situation, I create a bunch of tables, without any sqlite errors, and then later the tables are not found. I see the same thing when inserting rows: no error, but later look-ups don't find inserted rows. I'd like to detect that the write to DB failed at time of write, not a later read. I'm also doing the INSERTS and CREATE TABLES within a transaction, and again all the sqlite calls succeed, even the COMMIT TRANSACTION. There are no other pending statements at the time of the COMMIT TRANSACTION. I'm using version 3.3.4 on Windows. Thanks, Jeffrey Rennie
