Re: [sqlite] Data loss during the disk full condition
I haven't seen anything to say what journalling is being used (Rollback, WAL or none). If the latter then SQLite will have nothing to revert to on error. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 30 October 2016 at 00:22, Richard Hipp wrote: > On 10/29/16, Yuri wrote: > >>> What does "PRAGMA integrity_check" say about your database? >> >> It returns "ok", but this file has been opened and written into again. >> > > This suggests that the problem may be somewhere besides in SQLite. If > SQLite were getting confused and zeroing content as a result of the > disk-full error, then almost certainly integrity_check would report > errors. > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bus Error on OpenBSD
On Fri Oct 28, 2016 at 05:48:48PM +0700, Dan Kennedy wrote:
> On 10/28/2016 05:39 PM, no...@null.net wrote:
> > Hi Rowan,
> >
> > On Fri Oct 28, 2016 at 06:19:59PM +0800, Rowan Worth wrote:
> > > Every sqlite_stmt you use *must* be finalized via sqlite3_finalize.
> > > I'm not exactly sure what that looks like from the other side of DBD,
> > > but I would be checking your perl code for a statement/resultset
> > > object which outlives the database connection itself.
> > >
> > Some of my new debug statements appear to confirm that: database
> > handles are being cleaned up before statement handles, even though
> > presumably the statement handle still has a reference back to the
> > database.
>
> SQLite should handle that. If you call sqlite3_close() before all statement
> handles have been cleaned up, the call fails with SQLITE_MISUSE. Or if you
> use sqlite3_close_v2(), the call succeeds, but a reference count is used to
> ensure that the db handle object is not actually deleted until all
> statements are. close_v2() was added for this situation - where a garbage
> collectors or similar is responsible for closing db handles and finalizing
> statements.
The "handles" I was referring to above were Perl DBI handles. No doubt
they contain a real SQLite handle somewhere, but I don't think it is
safe to assume a one-to-one mapping. For example, the following appears
to create two Perl objects for each of the $db and $sth variables:
use DBI;
sub DBI::db::DESTROY {
warn "DESTROY @_";
}
sub DBI::st::DESTROY {
warn "DESTROY @_";
}
my $db = DBI->connect('dbi:SQLite:dbname=:memory:');
my $sth = $db->prepare('select 1');
# DESTROY DBI::db=HASH(0x9acf68c) at x line 4.
# DESTROY DBI::st=HASH(0x9acf95c) at x line 8.
# DESTROY DBI::st=HASH(0x9acf754) at x line 8.
# DBI st handle 0x9acf95c has uncleared implementors data.
# dbih_clearcom (sth 0x9acf95c, com 0x9ad1518, imp
DBD::SQLite::st):
# FLAGS 0x100113: COMSET IMPSET Warn PrintError
PrintWarn
# PARENT DBI::db=HASH(0x9acf600)
# KIDS 0 (0 Active)
# NUM_OF_FIELDS 1
# NUM_OF_PARAMS 0
# DESTROY DBI::db=HASH(0x9acf600) at x line 4.
# DBI db handle 0x9acf68c has uncleared implementors data.
# dbih_clearcom (dbh 0x9acf68c, com 0x9ac1fd8, imp
DBD::SQLite::db):
# FLAGS 0x100317: COMSET IMPSET Active Warn PrintError
#PrintWarn AutoCommit
# PARENT DBI::dr=HASH(0x9acf1a0)
# KIDS 0 (0 Active)
#
It is not obvious to me why that is so and I don't know the DBD::SQLite
code base so I won't speculate.
> If this is repeatable, try running it under valgrind. The valgrind
> error should make it pretty clear whether or not the statement handle
> really has already been finalized.
Well I have found what is probably the original source of the error: I
was keeping (Perl) statement handles around after the database handles
had expired. That doesn't mean that there isn't an issue with
how DBD::SQLite is using SQLite, but I no longer have the motivation to
track down that error when the easy answer to my problem is "don't do
that." Plus I have another error to report in a new thread :-(
Thanks everyone for listening.
--
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Autoincrement sequence not updated by UPDATE
On 10/27/16, Adam Goldman wrote: > I expected the test case below to print 5679, but it prints 1235 > instead. I tested under a few versions including 3.15.0. It's a bit of a > corner case and I worked around it in my application, but I guess it's a > bug. > > CREATE TABLE foo (bar INTEGER PRIMARY KEY AUTOINCREMENT); > INSERT INTO foo (bar) VALUES(1234); > UPDATE foo SET bar=5678; > DELETE FROM foo; > INSERT INTO foo DEFAULT VALUES; > SELECT * FROM foo; Yes, this is a discrepancy between the implementation and the documentation. But the implementation of AUTOINCREMENT has *never* before modified the content of the sqlite_sequence table on an UPDATE, since the AUTOINCREMENT feature was first introduced in 2002 (SQLite version 2.5.2). For 14 years, AUTOINCREMENT has always worked as it does in 3.15.0. If we "fix" the implementation now, we run a risk of breaking some of the millions of applications that use AUTOINCREMENT. For that reason, we have tentatively decided to update the documentation rather than the code. Note that the implied purpose of AUTOINCREMENT is to generate a unique and immutable identifier. Running an UPDATE on an immutable identifier breaks the contract. Even though this contract was not previously stated in the documentation, apparently most people understood it because this issue has never come up before in 14 years of heavy use. Thanks for pointing out the problem. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Autoincrement sequence not updated by UPDATE
> It guess it comes down to what one wants from "INTEGER PRIMARY KEY AUTOINCREMENT" What I would want, ...expect, is that a primary key autoincrement column would be left completely alone. And if was altered, it was altered on accident. I always thought "integer primary key" was synonymous with __rowid__. What application would want to mess with that? On Mon, Oct 31, 2016 at 12:56 PM, Richard Hipp wrote: > On 10/27/16, Adam Goldman wrote: > > I expected the test case below to print 5679, but it prints 1235 > > instead. I tested under a few versions including 3.15.0. It's a bit of a > > corner case and I worked around it in my application, but I guess it's a > > bug. > > > > CREATE TABLE foo (bar INTEGER PRIMARY KEY AUTOINCREMENT); > > INSERT INTO foo (bar) VALUES(1234); > > UPDATE foo SET bar=5678; > > DELETE FROM foo; > > INSERT INTO foo DEFAULT VALUES; > > SELECT * FROM foo; > > Yes, this is a discrepancy between the implementation and the > documentation. But the implementation of AUTOINCREMENT has *never* > before modified the content of the sqlite_sequence table on an UPDATE, > since the AUTOINCREMENT feature was first introduced in 2002 (SQLite > version 2.5.2). For 14 years, AUTOINCREMENT has always worked as it > does in 3.15.0. If we "fix" the implementation now, we run a risk of > breaking some of the millions of applications that use AUTOINCREMENT. > For that reason, we have tentatively decided to update the > documentation rather than the code. > > Note that the implied purpose of AUTOINCREMENT is to generate a unique > and immutable identifier. Running an UPDATE on an immutable > identifier breaks the contract. Even though this contract was not > previously stated in the documentation, apparently most people > understood it because this issue has never come up before in 14 years > of heavy use. > > Thanks for pointing out the problem. > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Website broken link
Going through the documentation at http://www.sqlite.org/arch.html In the Parser section there's a link for Lemon: http://www.sqlite.org/lemon.html which is coming up as page not found. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Website broken link
On 10/31/16, David Raymond wrote: > Going through the documentation at http://www.sqlite.org/arch.html > In the Parser section there's a link for Lemon: > http://www.sqlite.org/lemon.html which is coming up as page not found. Should be fixed now. Thanks. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Segmentation fault on OpenBSD
Operating system: OpenBSD 6.0
DBD::SQLite: 1.51_06
sqlite:3.15.0
Program received signal SIGSEGV, Segmentation fault.
sqlite3Step (p=0xb1480c37e08) at sqlite3.c:82102
82102 pCrsr = pC->uc.pCursor;
(gdb) list
82097
82098 assert( pOp->p1>=0 && pOp->p1nCursor );
82099 pC = p->apCsr[pOp->p1];
82100 assert( pC!=0 );
82101 assert( pC->eCurType==CURTYPE_BTREE );
82102 pCrsr = pC->uc.pCursor;
82103 res = 0;
82104 assert( pCrsr!=0 );
82105 rc = sqlite3BtreeLast(pCrsr, &res);
82106 pC->nullRow = (u8)res;
(gdb) backtrace
#0 sqlite3Step (p=0xb1480c37e08) at sqlite3.c:82102
#1 0x0b12443083c2 in sqlite3_step (pStmt=0xb1480c37e08) at sqlite3.c:75763
#2 0x0b12442a4499 in sqlite_st_execute (sth=0xb1517a8c860,
imp_sth=0xb14c73a6c00) at dbdimp.c:1038
#3 0x0b1244297733 in XS_DBD__SQLite__st_execute (cv=) at SQLite.xsi:614
#4 0x0b124435287e in XS_DBI_dispatch (cv=0xb14fd2eaec8) at DBI.xs:3781
#5 0x0b12443e8ab3 in Perl_pp_entersub () at pp_hot.c:2794
#6 0x0b12443e1b0d in Perl_runops_standard () at run.c:42
#7 0x0b1244384922 in perl_run (my_perl=) at perl.c:2460
#8 0x0b1244242a5d in main (argc=5, argv=0x7f7ce088) at bundle.c:15984
(gdb) print *p
$1 = {db = 0xb145e66b008, pPrev = 0x0, pNext = 0x0, pParse = 0x7f7cd610,
nVar = 1, nzVar = 0, magic = 770837923, nMem = 190, nCursor = 45,
cacheCtr = 1, pc = 0, rc = 0, nChange = 0, iStatement = 1, iCurrentTime = 0,
nFkConstraint = 0, nStmtDefCons = 1, nStmtDefImmCons = 0,
aOp = 0xb146d74d008, aMem = 0xb148a5c8070, apArg = 0xb14bcaac698,
aColName = 0x0, pResultSet = 0x0, zErrMsg = 0x0, apCsr = 0xb148a5caa00,
aVar = 0xb14bcaac698, azVar = 0x0, startTime = 0, nOp = 508, nResColumn = 0,
errorAction = 2 '\002', minWriteFileFormat = 4 '\004', expired = 0,
doingRerun = 0, explain = 0, changeCntOn = 1, runOnlyOnce = 0,
usesStmtJournal = 1, readOnly = 0, bIsReader = 1, isPrepareV2 = 1,
btreeMask = 1, lockMask = 0, aCounter = {42, 227, 2, 0, 0},
zSql = 0xb14b6dc7188 "INSERT INTO\nfunc_merge_deltas(\nmerge\n
)\nVALUES\n(\n?\n)\n;", pFree = 0x0, pFrame = 0xb148a5c8008,
pDelFrame = 0x0, nFrame = 5, expmask = 0, pProgram = 0xb146b8be548,
pAuxData = 0x0}
--
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Segmentation fault on OpenBSD
Is this reproducible?
Can you share with us the schema of the database file that was
connected when this error occurred?
On 10/31/16, mark wrote:
> Operating system: OpenBSD 6.0
> DBD::SQLite: 1.51_06
> sqlite:3.15.0
>
> Program received signal SIGSEGV, Segmentation fault.
> sqlite3Step (p=0xb1480c37e08) at sqlite3.c:82102
> 82102 pCrsr = pC->uc.pCursor;
>
> (gdb) list
> 82097
> 82098 assert( pOp->p1>=0 && pOp->p1nCursor );
> 82099 pC = p->apCsr[pOp->p1];
> 82100 assert( pC!=0 );
> 82101 assert( pC->eCurType==CURTYPE_BTREE );
> 82102 pCrsr = pC->uc.pCursor;
> 82103 res = 0;
> 82104 assert( pCrsr!=0 );
> 82105 rc = sqlite3BtreeLast(pCrsr, &res);
> 82106 pC->nullRow = (u8)res;
>
> (gdb) backtrace
> #0 sqlite3Step (p=0xb1480c37e08) at sqlite3.c:82102
> #1 0x0b12443083c2 in sqlite3_step (pStmt=0xb1480c37e08) at
> sqlite3.c:75763
> #2 0x0b12442a4499 in sqlite_st_execute (sth=0xb1517a8c860,
> imp_sth=0xb14c73a6c00) at dbdimp.c:1038
> #3 0x0b1244297733 in XS_DBD__SQLite__st_execute (cv=) at SQLite.xsi:614
> #4 0x0b124435287e in XS_DBI_dispatch (cv=0xb14fd2eaec8) at DBI.xs:3781
> #5 0x0b12443e8ab3 in Perl_pp_entersub () at pp_hot.c:2794
> #6 0x0b12443e1b0d in Perl_runops_standard () at run.c:42
> #7 0x0b1244384922 in perl_run (my_perl=) at perl.c:2460
> #8 0x0b1244242a5d in main (argc=5, argv=0x7f7ce088) at
> bundle.c:15984
>
> (gdb) print *p
> $1 = {db = 0xb145e66b008, pPrev = 0x0, pNext = 0x0, pParse = 0x7f7cd610,
> nVar = 1, nzVar = 0, magic = 770837923, nMem = 190, nCursor = 45,
> cacheCtr = 1, pc = 0, rc = 0, nChange = 0, iStatement = 1, iCurrentTime =
> 0,
> nFkConstraint = 0, nStmtDefCons = 1, nStmtDefImmCons = 0,
> aOp = 0xb146d74d008, aMem = 0xb148a5c8070, apArg = 0xb14bcaac698,
> aColName = 0x0, pResultSet = 0x0, zErrMsg = 0x0, apCsr = 0xb148a5caa00,
> aVar = 0xb14bcaac698, azVar = 0x0, startTime = 0, nOp = 508, nResColumn =
> 0,
> errorAction = 2 '\002', minWriteFileFormat = 4 '\004', expired = 0,
> doingRerun = 0, explain = 0, changeCntOn = 1, runOnlyOnce = 0,
> usesStmtJournal = 1, readOnly = 0, bIsReader = 1, isPrepareV2 = 1,
> btreeMask = 1, lockMask = 0, aCounter = {42, 227, 2, 0, 0},
> zSql = 0xb14b6dc7188 "INSERT INTO\nfunc_merge_deltas(\nmerge\n
>)\nVALUES\n(\n?\n)\n;", pFree = 0x0, pFrame =
> 0xb148a5c8008,
> pDelFrame = 0x0, nFrame = 5, expmask = 0, pProgram = 0xb146b8be548,
> pAuxData = 0x0}
>
> --
> Mark Lawrence
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Segmentation fault on OpenBSD
On 10/31/16, mark wrote: > On Mon Oct 31, 2016 at 04:04:00PM -0400, Richard Hipp wrote: >> Is this reproducible? > > Yes... in that I can reliably get it to segfault. Duplicating the build > and/or statements leading up to the fault outside of my environment > is not so easy. Unless I make the virtual machine image available to > you somehow... I cannot reproduce the problem on Linux. Even running under valgrind shows no issues. Can you please recompile using -DSQLITE_DEBUG and try again. If that works, please insert an extra SQL statement just before the SQL statement that is causing the problem, that says: PRAGMA debug=ON; -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Autoincrement sequence not updated by UPDATE
And that is why several SQL Engines prevent AUTOINCREMENT columns from being UPDATEd. I think it should be fixed in the Docs, not in Code. Richard Hipp schrieb am 31.10.2016 um 18:56: Note that the implied purpose of AUTOINCREMENT is to generate a unique and immutable identifier. Running an UPDATE on an immutable identifier breaks the contract. Even though this contract was not previously stated in the documentation, apparently most people understood it because this issue has never come up before in 14 years of heavy use. Thanks for pointing out the problem. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
