[sqlite] sites inaccessible

[jungle Boogie]

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

[Richard Hipp]

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

[Jens Alfke]

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

[Dan Kennedy]

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

[John Reynolds]

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!

[contact]

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

[Dan Kennedy]

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!

[Bernardo Sulzbach]

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!

[contact]

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

[David Raymond]

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

[Simon Slavin]

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

[Stephen Chrzanowski]

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

[Dan Kennedy]

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

[nomad]

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

[Rowan Worth]

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

[nomad]

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

[J Decker]

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

[Dan Kennedy]

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

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Radovan Antloga]

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