Re: [sqlite] sites inaccessible
On 10/29/2016 12:28 PM, jungle Boogie wrote: Hi Dr. Hipp, Probably a low concern for you at 1:30am your time but I can't connect to fossil-scm.org or sqlite.org over port 80. $ curl http://sqlite.org/ curl: (7) Failed to connect to sqlite.org port 80: Connection refused $ curl http://fossil-scm.org curl: (7) Failed to connect to fossil-scm.org port 80: Connection refused Thanks for posting this. Restarted xinetd and things seem to be working again. Dan. https does work: $ curl https://www.fossil-scm.org Redirect to Location: https://www.fossil-scm.org/index.html/doc/trunk/www/index.wiki $ curl https://www.sqlite.org http://www.w3.org/TR/html4/strict.dtd";> SQLite Home Page ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sites inaccessible
Hi Dr. Hipp, Probably a low concern for you at 1:30am your time but I can't connect to fossil-scm.org or sqlite.org over port 80. $ curl http://sqlite.org/ curl: (7) Failed to connect to sqlite.org port 80: Connection refused $ curl http://fossil-scm.org curl: (7) Failed to connect to fossil-scm.org port 80: Connection refused https does work: $ curl https://www.fossil-scm.org Redirect to Location: https://www.fossil-scm.org/index.html/doc/trunk/www/index.wiki $ curl https://www.sqlite.org http://www.w3.org/TR/html4/strict.dtd";> SQLite Home Page -- --- inum: 883510009027723 sip: jungleboo...@sip2sip.info ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Thread-safety of user-defined functions
On 10/28/16, Jens Alfke wrote: > Do I need to worry about concurrent calls to custom functions (or virtual > tables) that I register with SQLite? They’re associated with only a single > connection, but with Serialized mode, that connection could be used from > multiple threads. And what if I use `pragma threads` to enable helper > threads? An application defined function or virtual table might be called at the same time from multiple threads, but only from separate database connections. Within a single database connection, all calls to functions and virtual table methods are serialized by mutexes internal to SQLite. -- 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] Thread-safety of user-defined functions
Do I need to worry about concurrent calls to custom functions (or virtual tables) that I register with SQLite? They’re associated with only a single connection, but with Serialized mode, that connection could be used from multiple threads. And what if I use `pragma threads` to enable helper threads? —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Adding comments to a ticket
On 10/28/2016 11:44 PM, John Reynolds wrote: I've submitted a ticket, https://system.data.sqlite.org/index.html/tktview?name=d4728aecb7, and want to add a comment to it. I can't find any obvious way to do it in the ticket page (I'm logged on as anonymous). Is it possible? Click the "Edit" link at the top of the page linked above. Dan. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Adding comments to a ticket
I've submitted a ticket, https://system.data.sqlite.org/index.html/tktview?name=d4728aecb7, and want to add a comment to it. I can't find any obvious way to do it in the ticket page (I'm logged on as anonymous). Is it possible? -John ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange thing!
Thank you, I think you're right, I did not see this in the documentation. Thanks. -Message d'origine- De : sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] De la part de Bernardo Sulzbach Envoyé : vendredi 28 octobre 2016 17:49 À : sqlite-users@mailinglists.sqlite.org Objet : Re: [sqlite] Strange thing! On 10/28/2016 01:42 PM, cont...@comadd.fr wrote: > > I think the value of 'match' should be FULL instead of NONE! > From the docs, "SQLite parses MATCH clauses (i.e. does not report a syntax error if you specify one), but does not enforce them. All foreign key constraints in SQLite are handled as if MATCH SIMPLE were specified". I think this is the issue you are facing. -- Bernardo Sulzbach http://www.mafagafogigante.org/ mafagafogiga...@gmail.com ___ 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] WAL pragma question
On 10/28/2016 09:53 PM, David Raymond wrote: So my new understanding: This happens at the end of the write to the WAL file, but before the actual checkpoint. And then any checkpoint just works normally. So basically, even with journal_size_limit = 0, the WAL will always be at least as large as the last write, even if checkpointed successfully and completely. Right. The truncation is the last step in a database write, not part of a checkpoint operation. -So I insert 10 GB of data, the WAL grows to 10GB. -At the end of writing to the WAL it says "yup, you don't have extra, so I'm leaving you alone." -The auto checkpoint runs, (let's says it completes everything), and rewinds the WAL (if possible) without truncating it, because auto checkpoints are passive. -Now the main DB file is all synched, but I have a 10GB WAL file, which stays that way until the next write. -I insert 1 byte of data -If the WAL was rewound successfully it writes the new data at the start of the WAL. -At the end of writing that to the WAL it says "wow you're bloated, I'm truncating you." and cuts it back to 1 page in size. -Checkpoint runs on the 1 byte insert. WAL stays at 1 page. Starting to make sense now, thanks. It was that "hey, I'm all checkpointed and synched up but the file's still huge" bit there throwing me off. Of course I have to ask again here, am I understanding it correctly now? Yep, that's it. Dan. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Dan Kennedy Sent: Friday, October 28, 2016 5:21 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] WAL pragma question On 10/28/2016 03:16 AM, David Raymond wrote: I'm playing around with WAL mode here for the first time, along with some of the pragmas, and I'm getting some weird results. I was hoping someone could let me know if I'm missing something, or if yes, it is indeed weird. For starters, I'm looking at the journal_size_limit pragma. http://www.sqlite.org/pragma.html#pragma_journal_size_limit In its description it does say that it works for WAL mode. "To always truncate rollback journals and WAL files to their minimum size, set the journal_size_limit to zero." So I create a new database, turn on WAL mode, set that pragma, create a table, and insert some stuff into it. Then I check the file sizes, and the -wal file hasn't shrunk at all. I made sure it was large enough to go over the wal_autocheckpoint threshold and it didn't shrink. I ran a manual "pragma wal_checkpoint;" and it didn't shrink. I tried with specifying passive, full, and restart and it didn't shrink. It seems that I can only get it to shrink by doing an explicit "pragma wal_checkpoint(truncate);" But if that's the only way to shrink the file down, then what's the point of the pragma here? Or, as is more likely, what obvious thing is my brain missing at the end of the day? In wal mode, the wal file is truncated according to "PRAGMA journal_size_limit" after the first transaction is written following a checkpoint. Or, technically, after a writer writes a transaction into the beginning of the physical file. So: sqlite> PRAGMA journal_mode = wal; wal sqlite> PRAGMA journal_size_limit = 0; 0 sqlite> CREATE TABLE t1(a, b); sqlite> INSERT INTO t1 VALUES(randomblob(10), randomblob(10)); /* WAL file is now roughly 200KiB */ sqlite> PRAGMA wal_checkpoint; 0|52|52 /* Still roughly 200KiB */ sqlite> INSERT INTO t1 VALUES(1, 1); /* Now truncated to 4KiB */ This is because the locking scheme only allows writers to modify the wal file, not checkpointers. Dan. ___ 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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange thing!
On 10/28/2016 01:42 PM, cont...@comadd.fr wrote: I think the value of 'match' should be FULL instead of NONE! From the docs, "SQLite parses MATCH clauses (i.e. does not report a syntax error if you specify one), but does not enforce them. All foreign key constraints in SQLite are handled as if MATCH SIMPLE were specified". I think this is the issue you are facing. -- Bernardo Sulzbach http://www.mafagafogigante.org/ mafagafogiga...@gmail.com ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Strange thing!
Hello, I use "SQLite for UWP" Release 3.15.0 for "Windows Store" developments. I detected a strange thing on the SQLite database. 1. create two table : CREATE TABLE artist (id INTEGER PRIMARY KEY, name TEXT); CREATE TABLE track (id INTEGER PRIMARY KEY, name TEXT, artist_id INTEGER REFERENCES artist (id) ON UPDATE CASCADE ON DELETE CASCADE MATCH FULL); 2. verify the SQL stored : SELECT name,sql FROM [sqlite_master] WHERE [type]='table' AND [name]='track'; => result: CREATE TABLE [track] ([id] INTEGER PRIMARY KEY, [name] TEXT, [artist_id] INTEGER REFERENCES [artist] ([id]) ON UPDATE CASCADE ON DELETE CASCADE MATCH FULL) It is OK. 3. verify Foreign Key PRAGMA foreign_key_list ([track]); => result: id|seq|table |from |to|on_update|on_delete|match 0| 0|artist|artist_id|id|CASCADE |CASCADE |NONE I think the value of 'match' should be FULL instead of NONE! Best regards ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL pragma question
So my new understanding: This happens at the end of the write to the WAL file, but before the actual checkpoint. And then any checkpoint just works normally. So basically, even with journal_size_limit = 0, the WAL will always be at least as large as the last write, even if checkpointed successfully and completely. -So I insert 10 GB of data, the WAL grows to 10GB. -At the end of writing to the WAL it says "yup, you don't have extra, so I'm leaving you alone." -The auto checkpoint runs, (let's says it completes everything), and rewinds the WAL (if possible) without truncating it, because auto checkpoints are passive. -Now the main DB file is all synched, but I have a 10GB WAL file, which stays that way until the next write. -I insert 1 byte of data -If the WAL was rewound successfully it writes the new data at the start of the WAL. -At the end of writing that to the WAL it says "wow you're bloated, I'm truncating you." and cuts it back to 1 page in size. -Checkpoint runs on the 1 byte insert. WAL stays at 1 page. Starting to make sense now, thanks. It was that "hey, I'm all checkpointed and synched up but the file's still huge" bit there throwing me off. Of course I have to ask again here, am I understanding it correctly now? -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Dan Kennedy Sent: Friday, October 28, 2016 5:21 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] WAL pragma question On 10/28/2016 03:16 AM, David Raymond wrote: > I'm playing around with WAL mode here for the first time, along with some of > the pragmas, and I'm getting some weird results. I was hoping someone could > let me know if I'm missing something, or if yes, it is indeed weird. > > For starters, I'm looking at the journal_size_limit pragma. > http://www.sqlite.org/pragma.html#pragma_journal_size_limit > In its description it does say that it works for WAL mode. "To always > truncate rollback journals and WAL files to their minimum size, set the > journal_size_limit to zero." So I create a new database, turn on WAL mode, > set that pragma, create a table, and insert some stuff into it. Then I check > the file sizes, and the -wal file hasn't shrunk at all. I made sure it was > large enough to go over the wal_autocheckpoint threshold and it didn't > shrink. I ran a manual "pragma wal_checkpoint;" and it didn't shrink. I tried > with specifying passive, full, and restart and it didn't shrink. It seems > that I can only get it to shrink by doing an explicit "pragma > wal_checkpoint(truncate);" But if that's the only way to shrink the file > down, then what's the point of the pragma here? > > Or, as is more likely, what obvious thing is my brain missing at the end of > the day? In wal mode, the wal file is truncated according to "PRAGMA journal_size_limit" after the first transaction is written following a checkpoint. Or, technically, after a writer writes a transaction into the beginning of the physical file. So: sqlite> PRAGMA journal_mode = wal; wal sqlite> PRAGMA journal_size_limit = 0; 0 sqlite> CREATE TABLE t1(a, b); sqlite> INSERT INTO t1 VALUES(randomblob(10), randomblob(10)); /* WAL file is now roughly 200KiB */ sqlite> PRAGMA wal_checkpoint; 0|52|52 /* Still roughly 200KiB */ sqlite> INSERT INTO t1 VALUES(1, 1); /* Now truncated to 4KiB */ This is because the locking scheme only allows writers to modify the wal file, not checkpointers. Dan. ___ 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] Autoincrement sequence not updated by UPDATE
SQLite version 3.14.0 2016-07-26 15:17:14 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE foo (bar INTEGER PRIMARY KEY AUTOINCREMENT); sqlite> INSERT INTO foo (bar) VALUES(1234); sqlite> SELECT * FROM foo; 1234 sqlite> UPDATE foo SET bar=5678; sqlite> SELECT * FROM foo; 5678 sqlite> DELETE FROM foo; sqlite> SELECT * FROM foo; sqlite> INSERT INTO foo DEFAULT VALUES; sqlite> SELECT * FROM foo; 1235 sqlite> Suggests that in 3.14.0 the autoincrement number isn't changed by UPDATE. It guess it comes down to what one wants from "INTEGER PRIMARY KEY AUTOINCREMENT". If the requirement is only-ever-increasing then this is a bug. Simon. ___ 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
Works here; SQLite version 3.13.0 2016-05-18 10:57:30 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE foo (bar INTEGER PRIMARY KEY AUTOINCREMENT); sqlite> INSERT INTO foo (bar) VALUES(1234); sqlite> SELECT * FROM foo; 1234 sqlite> UPDATE foo SET bar=5678; sqlite> SELECT * FROM foo; 5678 sqlite> DELETE FROM foo; sqlite> SELECT * FROM foo; sqlite> INSERT INTO foo DEFAULT VALUES; sqlite> SELECT * FROM foo; And in one transaction; sqlite> begin; sqlite> CREATE TABLE foo (bar INTEGER PRIMARY KEY AUTOINCREMENT); sqlite> INSERT INTO foo (bar) VALUES(1234); sqlite> SELECT * FROM foo; 1234 sqlite> UPDATE foo SET bar=5678; sqlite> SELECT * FROM foo; 5678 sqlite> DELETE FROM foo; sqlite> SELECT * FROM foo; sqlite> INSERT INTO foo DEFAULT VALUES; sqlite> SELECT * FROM foo; 1235 sqlite> commit; On Fri, Oct 28, 2016 at 2:46 AM, Radovan Antloga wrote: > After line: > UPDATE foo SET bar=5678; > > put this sql command: > COMMIT; > > If you execute all statements in one sql > (except last), they are executed in one transaction. > > Regards > Radovan > > > Adam Goldman je 27.10.2016 ob 11:52 napisal: > > Hi, >> >> 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; >> >> -- Adam >> ___ >> 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-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 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. This looks like the statement handle being passed to sqlite3_finalize() has already been finalized. Or perhaps that it is just a stray pointer that was never a statement handle. To confirm, jump back to the sqlite3VdbeFinalize() frame of your stacktrace and do "print *p". The entire object has likely been 0xdf'd out. 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. Dan. So I also did some googling on that topic, and it appears that during Perl's global destruction phase objects may not necessarily be destroyed in the right order. That is something I unfortunately don't have any easy control over :-( Perhaps I can be more explicit somewhere... In any event this is probably not an sqlite issue. Thanks for commenting. Mark. ___ 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
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. So I also did some googling on that topic, and it appears that during Perl's global destruction phase objects may not necessarily be destroyed in the right order. That is something I unfortunately don't have any easy control over :-( Perhaps I can be more explicit somewhere... In any event this is probably not an sqlite issue. Thanks for commenting. Mark. -- Mark Lawrence ___ 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
Hi Mark, A quick google suggests this is a use after free error, as OpenBSD's allocator apparently fills freed memory pages with the pattern 0xdfdfdfdfdf. The stack trace reads like it is crashing while finalizing an sqlite_stmt, as part of some automatic perl destructor logic. 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. -Rowan On 28 October 2016 at 18:10, wrote: > I am seeing a Bus Error at the end of a program that to my > inexperienced eye appears to have something to do with SQLite: > > This GDB was configured as "amd64-unknown-openbsd6.0"... > Core was generated by `bif'. > Program terminated with signal 10, Bus error. > Loaded symbols for /mark/src/bif/static/bif > Reading symbols from /usr/lib/libm.so.9.0...done. > Loaded symbols for /usr/lib/libm.so.9.0 > Reading symbols from /usr/lib/libc.so.88.0...done. > Loaded symbols for /usr/lib/libc.so.88.0 > Reading symbols from /usr/libexec/ld.so...done. > Loaded symbols for /usr/libexec/ld.so > #0 releaseMemArray (p=0xdfdfdfdfdfdfdfdf, N=286555) at sqlite3.c:71943 > 71943 sqlite3 *db = p->db; > > (gdb) backtrace > #0 releaseMemArray (p=0xdfdfdfdfdfdfdfdf, N=286555) at sqlite3.c:71943 > #1 0x1285167d22e4 in sqlite3VdbeClearObject > (db=0xdfdfdfdfdfdfdfdf, p=0x12873d135408) at sqlite3.c:73412 > #2 0x1285167d23d2 in sqlite3VdbeDelete (p=0x12873d135408) at > sqlite3.c:73444 > #3 0x128516815191 in sqlite3VdbeFinalize (p=0x12873d135408) at > sqlite3.c:73362 > #4 0x1285168152bd in sqlite3_finalize (pStmt=0x12873d135408) at > sqlite3.c:75209 > #5 0x1285167b5ef5 in sqlite_st_destroy (sth=0x1287bf19a198, > imp_sth=0x128791361b00) at dbdimp.c:1256 > #6 0x1285167ab7b3 in XS_DBD__SQLite__st_DESTROY (cv=Variable "cv" > is not available.) at SQLite.xsi:799 > #7 0x12851685e60a in XS_DBI_dispatch (cv=0x12878d7123c8) at > DBI.xs:3781 > #8 0x1285168e8ab7 in Perl_pp_entersub () at pp_hot.c:2794 > #9 0x128516884036 in Perl_call_sv (sv=0x12878d7123c8, flags=45) > at perl.c:2775 > #10 0x1285168f0c21 in S_curse (sv=0x1287b8a0f1a8, > check_refcnt=true) at sv.c:6704 > #11 0x1285168f0e07 in Perl_sv_clear (orig_sv=0x1287b8a0f1a8) at > sv.c:6326 > #12 0x1285168f15b9 in Perl_sv_free2 (sv=0x1287b8a0f1a8, > rc=Variable "rc" is not available.) at sv.c:6805 > #13 0x1285168e8bc3 in S_visit (f=0x1285168f181c , > flags=2048, mask=2048) at sv.c:485 > #14 0x1285168f1ab0 in Perl_sv_clean_objs () at sv.c:640 > #15 0x128516886bc3 in perl_destruct (my_perl=Variable "my_perl" is > not available.) at perl.c:804 > #16 0x128516742a66 in main (argc=5, argv=0x7f7bef58) at > bundle.c:15988 > > I find the pointer address 0xdfdfdfdfdfdfdfdf to be a little > suspicious. > > The program is a static build of Perl that embeds DBD::SQLite which > embeds sqlite. I have seen the error with sqlite version 3.10.2 and > 3.15.0. I have only seen the error on OpenBSD - my Linux builds seem to > have no problem. > > Any ideas how I could debug this further? > > Mark > -- > Mark Lawrence > ___ > 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] Bus Error on OpenBSD
I am seeing a Bus Error at the end of a program that to my inexperienced eye appears to have something to do with SQLite: This GDB was configured as "amd64-unknown-openbsd6.0"... Core was generated by `bif'. Program terminated with signal 10, Bus error. Loaded symbols for /mark/src/bif/static/bif Reading symbols from /usr/lib/libm.so.9.0...done. Loaded symbols for /usr/lib/libm.so.9.0 Reading symbols from /usr/lib/libc.so.88.0...done. Loaded symbols for /usr/lib/libc.so.88.0 Reading symbols from /usr/libexec/ld.so...done. Loaded symbols for /usr/libexec/ld.so #0 releaseMemArray (p=0xdfdfdfdfdfdfdfdf, N=286555) at sqlite3.c:71943 71943 sqlite3 *db = p->db; (gdb) backtrace #0 releaseMemArray (p=0xdfdfdfdfdfdfdfdf, N=286555) at sqlite3.c:71943 #1 0x1285167d22e4 in sqlite3VdbeClearObject (db=0xdfdfdfdfdfdfdfdf, p=0x12873d135408) at sqlite3.c:73412 #2 0x1285167d23d2 in sqlite3VdbeDelete (p=0x12873d135408) at sqlite3.c:73444 #3 0x128516815191 in sqlite3VdbeFinalize (p=0x12873d135408) at sqlite3.c:73362 #4 0x1285168152bd in sqlite3_finalize (pStmt=0x12873d135408) at sqlite3.c:75209 #5 0x1285167b5ef5 in sqlite_st_destroy (sth=0x1287bf19a198, imp_sth=0x128791361b00) at dbdimp.c:1256 #6 0x1285167ab7b3 in XS_DBD__SQLite__st_DESTROY (cv=Variable "cv" is not available.) at SQLite.xsi:799 #7 0x12851685e60a in XS_DBI_dispatch (cv=0x12878d7123c8) at DBI.xs:3781 #8 0x1285168e8ab7 in Perl_pp_entersub () at pp_hot.c:2794 #9 0x128516884036 in Perl_call_sv (sv=0x12878d7123c8, flags=45) at perl.c:2775 #10 0x1285168f0c21 in S_curse (sv=0x1287b8a0f1a8, check_refcnt=true) at sv.c:6704 #11 0x1285168f0e07 in Perl_sv_clear (orig_sv=0x1287b8a0f1a8) at sv.c:6326 #12 0x1285168f15b9 in Perl_sv_free2 (sv=0x1287b8a0f1a8, rc=Variable "rc" is not available.) at sv.c:6805 #13 0x1285168e8bc3 in S_visit (f=0x1285168f181c , flags=2048, mask=2048) at sv.c:485 #14 0x1285168f1ab0 in Perl_sv_clean_objs () at sv.c:640 #15 0x128516886bc3 in perl_destruct (my_perl=Variable "my_perl" is not available.) at perl.c:804 #16 0x128516742a66 in main (argc=5, argv=0x7f7bef58) at bundle.c:15988 I find the pointer address 0xdfdfdfdfdfdfdfdf to be a little suspicious. The program is a static build of Perl that embeds DBD::SQLite which embeds sqlite. I have seen the error with sqlite version 3.10.2 and 3.15.0. I have only seen the error on OpenBSD - my Linux builds seem to have no problem. Any ideas how I could debug this further? Mark -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL pragma question
Also if you have any connections open, the journal may exist, so it doesn't have to constantly open and close it. On Fri, Oct 28, 2016 at 2:21 AM, Dan Kennedy wrote: > On 10/28/2016 03:16 AM, David Raymond wrote: > >> I'm playing around with WAL mode here for the first time, along with some >> of the pragmas, and I'm getting some weird results. I was hoping someone >> could let me know if I'm missing something, or if yes, it is indeed weird. >> >> For starters, I'm looking at the journal_size_limit pragma. >> http://www.sqlite.org/pragma.html#pragma_journal_size_limit >> In its description it does say that it works for WAL mode. "To always >> truncate rollback journals and WAL files to their minimum size, set the >> journal_size_limit to zero." So I create a new database, turn on WAL mode, >> set that pragma, create a table, and insert some stuff into it. Then I >> check the file sizes, and the -wal file hasn't shrunk at all. I made sure >> it was large enough to go over the wal_autocheckpoint threshold and it >> didn't shrink. I ran a manual "pragma wal_checkpoint;" and it didn't >> shrink. I tried with specifying passive, full, and restart and it didn't >> shrink. It seems that I can only get it to shrink by doing an explicit >> "pragma wal_checkpoint(truncate);" But if that's the only way to shrink the >> file down, then what's the point of the pragma here? >> >> Or, as is more likely, what obvious thing is my brain missing at the end >> of the day? >> > > In wal mode, the wal file is truncated according to "PRAGMA > journal_size_limit" after the first transaction is written following a > checkpoint. Or, technically, after a writer writes a transaction into the > beginning of the physical file. So: > > sqlite> PRAGMA journal_mode = wal; > wal > sqlite> PRAGMA journal_size_limit = 0; > 0 > sqlite> CREATE TABLE t1(a, b); > sqlite> INSERT INTO t1 VALUES(randomblob(10), randomblob(10)); > /* WAL file is now roughly 200KiB */ > sqlite> PRAGMA wal_checkpoint; > 0|52|52 > /* Still roughly 200KiB */ > sqlite> INSERT INTO t1 VALUES(1, 1); > /* Now truncated to 4KiB */ > > This is because the locking scheme only allows writers to modify the wal > file, not checkpointers. > > Dan. > > > ___ > 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] WAL pragma question
On 10/28/2016 03:16 AM, David Raymond wrote: I'm playing around with WAL mode here for the first time, along with some of the pragmas, and I'm getting some weird results. I was hoping someone could let me know if I'm missing something, or if yes, it is indeed weird. For starters, I'm looking at the journal_size_limit pragma. http://www.sqlite.org/pragma.html#pragma_journal_size_limit In its description it does say that it works for WAL mode. "To always truncate rollback journals and WAL files to their minimum size, set the journal_size_limit to zero." So I create a new database, turn on WAL mode, set that pragma, create a table, and insert some stuff into it. Then I check the file sizes, and the -wal file hasn't shrunk at all. I made sure it was large enough to go over the wal_autocheckpoint threshold and it didn't shrink. I ran a manual "pragma wal_checkpoint;" and it didn't shrink. I tried with specifying passive, full, and restart and it didn't shrink. It seems that I can only get it to shrink by doing an explicit "pragma wal_checkpoint(truncate);" But if that's the only way to shrink the file down, then what's the point of the pragma here? Or, as is more likely, what obvious thing is my brain missing at the end of the day? In wal mode, the wal file is truncated according to "PRAGMA journal_size_limit" after the first transaction is written following a checkpoint. Or, technically, after a writer writes a transaction into the beginning of the physical file. So: sqlite> PRAGMA journal_mode = wal; wal sqlite> PRAGMA journal_size_limit = 0; 0 sqlite> CREATE TABLE t1(a, b); sqlite> INSERT INTO t1 VALUES(randomblob(10), randomblob(10)); /* WAL file is now roughly 200KiB */ sqlite> PRAGMA wal_checkpoint; 0|52|52 /* Still roughly 200KiB */ sqlite> INSERT INTO t1 VALUES(1, 1); /* Now truncated to 4KiB */ This is because the locking scheme only allows writers to modify the wal file, not checkpointers. Dan. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users