[sqlite] Sqlite Page Explorer

[Arun - Siara Logics (cc)]

Hi,

Try my free and open source application to learn internal structure of SQLite 
databases. Explore the organisation of various objects such as schema, tables 
and indices. View hidden pages and data deleted.  Available for Mac, WIndows 
and Linux.

Applications:
- Viewing internal organisation of Sqlite databases for software development, 
ethical hacking and troubleshooting
- Studying the format of Sqlite databases for academic purposes
- Forensic investigators may use it to view data from deleted pages

GitHub page:
https://github.com/siara-cc/sqlite3_page_explorer

Mac App Store URL:
https://itunes.apple.com/app/id1444019689?fbclid=IwAR1af-OcB0a4zqWG_BKsKvRAHSwKO-Cwpuy9sOglOpQ30q92GJoxH9I6TEo

Regards
Arun


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how do i unsubscibe (eom)

[Simon Slavin]

Click the link at the bottom of every post to the list, including this one.

Look near the bottom of that web page.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how do i unsubscibe (eom)

[j oconnor]

 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Boosting insert and indexing performance for 10 billion rows (?)

[Simon Slavin]

Ignore multiprocessing for the inserting.  You'll just get contention when 
accessing the database.  And I think you are already trying the right PRAGMAs.

I think you've done this already, but just in case ...

Insert rows in batches.  Experiment with the batch size: maybe a thousand 
INSERTs per batch, maybe a million.  Try all the orders in between.  So ...

DROP all INDEXes
BEGIN
   INSERT first thousand rows
END
BEGIN
   INSERT next thousand rows
END
...
CREATE all INDEXes

Try that and time it.  Then change 'thousand' to 'ten thousand', up to a 
million.  Curve the times and figure out where your sweet spot is.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Boosting insert and indexing performance for 10 billion rows (?)

[AJ Miles]

Thank you for the good suggestions. I've been applying them to a smaller
subset of my database to see how it might perform.

I had tried fiddling with the cache size but it seemed to make performance
slightly degrade in my case. In principle it should work, so perhaps my
smaller database isn't entirely representative of the larger one. I will
test it out fully later. However, the threading pragma is showing a pretty
dramatic (3-4x increase in speed) improvement, so I think this will be a
huge benefit. For some reason I had overlooked that setting.

The attach limit looks like it would be good to try at some point. I am
using Windows, but unfortunately I am preparing this database for use in a
tool that will be shared with other installations/operating systems. In
order to keep it extremely simple for the end user, I'm sticking to whatever
I can rig through the pre-compiled Python version, but for my own personal
projects I will try to fiddle with attach. The ability to split the database
into multiple files will come in handy if I end up tackling anything larger.

-AJ



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Boosting insert and indexing performance for 10 billion rows (?)

[David Raymond]

Sounds like you're already doing the few things I would have recommended.

As an FYI in regards to the attached database limitation, you can compile your 
own .dll with a higher number for SQLITE_MAX_ATTACHED, up to 125
https://www.sqlite.org/limits.html#max_attached
and then swap out the default Python sqlite3.dll for yours in the 
\Python37\DLLs folder.
(Or at least you can in Windows land. No clue about other OS's)



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of AJ M
Sent: Wednesday, November 28, 2018 12:03 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Boosting insert and indexing performance for 10 billion rows 
(?)

Hi everyone -

I've been using SQLite through Python (3.7) for a scientific project. The
data comes out to 10 billion rows of an 8 byte signed integer (~200-300 gb
pre-index), and while insertion takes ~6 hours, indexing takes 8 hours by
itself. Indexing also seems to slow as it is built. Does this sound
reasonable for this amount of data? I'm running this on an SSD to try and
help the IO as best I can.

While I've been getting a lot of mileage out of this, I was wondering if
you had any tips on getting it to run faster. I've tried various PRAGMA
modifications to try and help the insert, but I'm wondering if there's
anything I can do to appreciably speed any of this up.

For my purposes, I don't need any sort of safeguards for power loss etc. -
I've already turned the journal and synchronous to off. This is a database
that will be built one time and accessed on occasion, and query speed is
fine as-is. The only things I can think about are perhaps partitioning the
table and running the indexing in parallel on the partitions, but this
seems clunky, especially with Python's 10-database ATTACH limit. The
parameter for modifying this is non-obvious in the Python package, and
since I haven't done the experiment, I don't know to what extent that would
help.

Thank you for any insight into this. The database is working fine as-is,
but I am trying to see for the sake of convenience and education if I can
get it to insert and/or index faster.

Cheers,

-AJ
___
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] Boosting insert and indexing performance for 10 billion rows (?)

[Keith Medcalf]

Have you increased the paltry default cache size?  (PRAGMA CACHE_SIZE)  The 
bigger the better, especially since you are sorting and balancing large 
B-Tree's.  The more this can be done in memory without having to spill to slow 
disk (or disk cache) the faster it will go ... (the best way to optimize I/O is 
to not do it)

You can also enable multithreaded merge sorts.  It has been my experience 
though that enabling multithreaded merge sorts slows things down rather than 
speeds them up however YMMV.  You might just have a database size that is in 
the realm of scale the multithreaded sorter was intended to address.

https://sqlite.org/pragma.html#pragma_threads


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of AJ M
>Sent: Wednesday, 28 November, 2018 10:03
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Boosting insert and indexing performance for 10
>billion rows (?)
>
>Hi everyone -
>
>I've been using SQLite through Python (3.7) for a scientific project.
>The
>data comes out to 10 billion rows of an 8 byte signed integer (~200-
>300 gb
>pre-index), and while insertion takes ~6 hours, indexing takes 8
>hours by
>itself. Indexing also seems to slow as it is built. Does this sound
>reasonable for this amount of data? I'm running this on an SSD to try
>and
>help the IO as best I can.
>
>While I've been getting a lot of mileage out of this, I was wondering
>if
>you had any tips on getting it to run faster. I've tried various
>PRAGMA
>modifications to try and help the insert, but I'm wondering if
>there's
>anything I can do to appreciably speed any of this up.
>
>For my purposes, I don't need any sort of safeguards for power loss
>etc. -
>I've already turned the journal and synchronous to off. This is a
>database
>that will be built one time and accessed on occasion, and query speed
>is
>fine as-is. The only things I can think about are perhaps
>partitioning the
>table and running the indexing in parallel on the partitions, but
>this
>seems clunky, especially with Python's 10-database ATTACH limit. The
>parameter for modifying this is non-obvious in the Python package,
>and
>since I haven't done the experiment, I don't know to what extent that
>would
>help.
>
>Thank you for any insight into this. The database is working fine as-
>is,
>but I am trying to see for the sake of convenience and education if I
>can
>get it to insert and/or index faster.
>
>Cheers,
>
>-AJ
>___
>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] SQLITE_LOCKED and SQLITE_BUSY

[Keith Medcalf]

No.  It is not correct.  Have you read the documentation?

https://sqlite.org/rescode.html#locked

Multiple threads cannot perform operations at the same time on the same 
connection.  This is verboten.  Forbidden.  Does not work.  Will cause 
explosions and death of children.  Do not do it.  Ever.  Period.  End of Line.

"The SQLITE_LOCKED result code differs from SQLITE_BUSY in that SQLITE_LOCKED 
indicates a conflict on the same database connection (or on a connection with a 
shared cache) whereas SQLITE_BUSY indicates a conflict with a different 
database connection"

So if you do something like this:

open database using connection1
on connection1 SELECT * FROM DATA
while got some rows:
  on connection1 DROP TABLE DATA

the DROP TABLE DATA will get a SQLITE_LOCKED because it cannot delete the table 
DATA because it is being read ON THE SAME CONNECTION.

conversely if you do something like this:

open database using connection1
open database using connection2
on connection1 SELECT * FROM DATA
while got some rows:
  on connection2 DROP TABLE DATA

the DROP TABLE DATA will get a SQLITE_BUSY because it cannot delete the table 
DATA because it is being read ON A DIFFERENT CONNECTION.

"threads" have nothing to do with anything whatsoever except that if you make 
any sqlite3_x call simultaneously using THE SAME CONNECTION (or something 
derived from the same connection, like a cursor) then the universe will explode 
and children will die!  Do not do that.  Ever.

NB:  different "connections" to the same "shared cache" are really the "same 
connection" ... for the purposes of the SQLITE_LOCKED / SQLITE_BUSY and 
different connections for the purposes of the universe exploding and children 
dying.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Prajeesh Prakash
>Sent: Wednesday, 28 November, 2018 08:36
>To: SQLite mailing list
>Subject: [sqlite] SQLITE_LOCKED and SQLITE_BUSY
>
>Hi members,
>
>The SQLITE_LOCKED error will happen on same database connection when
>two thread trying to do read/write operation at same time.
>SQLITE_BUSY will get when one thread on one connection is doing
>read/write operation and another thread on another connection trying
>to read/write the DB. Is it correct?
>
>
>Thank you
>___
>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] Boosting insert and indexing performance for 10 billion rows (?)

[AJ M]

Hi everyone -

I've been using SQLite through Python (3.7) for a scientific project. The
data comes out to 10 billion rows of an 8 byte signed integer (~200-300 gb
pre-index), and while insertion takes ~6 hours, indexing takes 8 hours by
itself. Indexing also seems to slow as it is built. Does this sound
reasonable for this amount of data? I'm running this on an SSD to try and
help the IO as best I can.

While I've been getting a lot of mileage out of this, I was wondering if
you had any tips on getting it to run faster. I've tried various PRAGMA
modifications to try and help the insert, but I'm wondering if there's
anything I can do to appreciably speed any of this up.

For my purposes, I don't need any sort of safeguards for power loss etc. -
I've already turned the journal and synchronous to off. This is a database
that will be built one time and accessed on occasion, and query speed is
fine as-is. The only things I can think about are perhaps partitioning the
table and running the indexing in parallel on the partitions, but this
seems clunky, especially with Python's 10-database ATTACH limit. The
parameter for modifying this is non-obvious in the Python package, and
since I haven't done the experiment, I don't know to what extent that would
help.

Thank you for any insight into this. The database is working fine as-is,
but I am trying to see for the sake of convenience and education if I can
get it to insert and/or index faster.

Cheers,

-AJ
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_OPEN_FULLMUTEX vs SQLITE_OPEN_NOMUTEX

[Simon Slavin]

On 28 Nov 2018, at 3:26pm, Prajeesh Prakash  
wrote:

> Then is that SQLITE_LOCKED error will happen because of a conflict within the 
> same database connection. Or in case of two connection two separate thread 
> trying to do operation?

Your software should never make two simultaneous API calls with the same 
connection.  It is the programmer's responsibility to make sure this never 
happens.  One of the ways you can avoid it happening is to use 
SQLITE_OPEN_FULLMUTEX , which is the default setting.

SQLITE_LOCKED happens when two different connections conflict.  But it is 
normal to set a timeout of a minute or two to allow each connection to back off 
and retry a few times before it gives up and reports SQLITE_LOCKED.  See



The timeout must be set separately for each connection.  You can also set 
timeout using a PRAGMA.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE_LOCKED and SQLITE_BUSY

[Prajeesh Prakash]

Hi members,

The SQLITE_LOCKED error will happen on same database connection when two thread 
trying to do read/write operation at same time. SQLITE_BUSY will get when one 
thread on one connection is doing read/write operation and another thread on 
another connection trying to read/write the DB. Is it correct?


Thank you
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_OPEN_FULLMUTEX vs SQLITE_OPEN_NOMUTEX

[Prajeesh Prakash]

Then is that SQLITE_LOCKED error will happen because of a conflict within the 
same database connection. Or in case of two connection two separate thread 
trying to do operation?

> 
> On November 28, 2018 at 8:16 PM Keith Medcalf  wrote:
> 
> The difference is that if both threads call the library on the same 
> connection at the same time (that is, two calls are into the library are 
> active at the same time) then all hell will break loose. You application will 
> fail. Memory will be corrupted. You database will be corrupted. Hell may 
> freeze over. Under no circumstances whatsoever must you *ever* allow this to 
> happen.
> 
> SQLITE_OPEN_FULLMUTEX (the default) means that Sqlite3 will, each time 
> you make a call into the library, spend a few CPU cycles to ENSURE that you 
> do not break the rules and that the consequences described above will not 
> happen.
> 
> SQLITE_OPEN_NOMUTEX (not the default) means that Sqlite3 WILL NOT, each 
> time you make a call into the library, spend those few CPU cycles ensuring 
> that you have not broken the rules because YOU have explicitly taken 
> responsibility upon yourself to ensure that you do no break the rules. 
> However, if you do break the rules, then you have obviously intended that the 
> described consequences should come to pass and when they do, they should have 
> been expected.
> 
> You can also compile the code so the default is SQLITE_OPEN_NOMUTEX and 
> turn on a special idiot mode that will simply crash (fail an assert) if you 
> break the rules, rather than letting the aforesaid consequences happen. This 
> takes more CPU cycles that having "pure" SQLITE_OPEN_NOMUTEX and less that 
> SQLITE_OPEN_FULLMUTEX.
> 
> So you get to choose the level of consequence and risk that you are 
> willing to tolerate.
> 
> So, if you have no mutex and two threads use the same connection to 
> update the same table, then either both will work or you will suffer the 
> consequences described above. If you use the default serialized (full mutex) 
> then both will work and there will be no consequence as described above. In 
> both cases neither operation will be isolated in any way from the other (that 
> requires using separate connections).
> 
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven 
> says a lot about anticipated traffic volume.
> 
> > > 
> > -Original Message-
> > From: sqlite-users [mailto:sqlite-users-
> > boun...@mailinglists.sqlite.org] On Behalf Of Prajeesh Prakash
> > Sent: Wednesday, 28 November, 2018 07:16
> > To: SQLite mailing list
> > Subject: [sqlite] SQLITE_OPEN_FULLMUTEX vs SQLITE_OPEN_NOMUTEX
> > 
> > Hi Members,
> > 
> > Can any one please give a clear idea about SQLITE_OPEN_FULLMUTEX and
> > SQLITE_OPEN_NOMUTEX because i am totally confused with the concept.
> > If we enable FULLMUTEX what will happen if two thread trying to
> > update the same table (Both thread are from same DB connection) in
> > case of NOMUTEX what is the flow
> > 
> > Thank you
> > 
> > ___
> > 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] SQLITE_OPEN_FULLMUTEX vs SQLITE_OPEN_NOMUTEX

[Prajeesh Prakash]

Thank you very much for the detailed explanation. Now i understood the concept.

> On November 28, 2018 at 8:16 PM Keith Medcalf  wrote:
>
>
>
> The difference is that if both threads call the library on the same 
> connection at the same time (that is, two calls are into the library are 
> active at the same time) then all hell will break loose. You application will 
> fail. Memory will be corrupted. You database will be corrupted. Hell may 
> freeze over. Under no circumstances whatsoever must you *ever* allow this to 
> happen.
>
> SQLITE_OPEN_FULLMUTEX (the default) means that Sqlite3 will, each time you 
> make a call into the library, spend a few CPU cycles to ENSURE that you do 
> not break the rules and that the consequences described above will not happen.
>
> SQLITE_OPEN_NOMUTEX (not the default) means that Sqlite3 WILL NOT, each time 
> you make a call into the library, spend those few CPU cycles ensuring that 
> you have not broken the rules because YOU have explicitly taken 
> responsibility upon yourself to ensure that you do no break the rules. 
> However, if you do break the rules, then you have obviously intended that the 
> described consequences should come to pass and when they do, they should have 
> been expected.
>
> You can also compile the code so the default is SQLITE_OPEN_NOMUTEX and turn 
> on a special idiot mode that will simply crash (fail an assert) if you break 
> the rules, rather than letting the aforesaid consequences happen. This takes 
> more CPU cycles that having "pure" SQLITE_OPEN_NOMUTEX and less that 
> SQLITE_OPEN_FULLMUTEX.
>
> So you get to choose the level of consequence and risk that you are willing 
> to tolerate.
>
> So, if you have no mutex and two threads use the same connection to update 
> the same table, then either both will work or you will suffer the 
> consequences described above. If you use the default serialized (full mutex) 
> then both will work and there will be no consequence as described above. In 
> both cases neither operation will be isolated in any way from the other (that 
> requires using separate connections).
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
> lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Prajeesh Prakash
> >Sent: Wednesday, 28 November, 2018 07:16
> >To: SQLite mailing list
> >Subject: [sqlite] SQLITE_OPEN_FULLMUTEX vs SQLITE_OPEN_NOMUTEX
> >
> >Hi Members,
> >
> >Can any one please give a clear idea about SQLITE_OPEN_FULLMUTEX and
> >SQLITE_OPEN_NOMUTEX because i am totally confused with the concept.
> >If we enable FULLMUTEX what will happen if two thread trying to
> >update the same table (Both thread are from same DB connection) in
> >case of NOMUTEX what is the flow
> >
> >
> >Thank you
> >___
> >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] SQLITE_OPEN_FULLMUTEX vs SQLITE_OPEN_NOMUTEX

[Keith Medcalf]

The difference is that if both threads call the library on the same connection 
at the same time (that is, two calls are into the library are active at the 
same time) then all hell will break loose.  You application will fail.  Memory 
will be corrupted.  You database will be corrupted.  Hell may freeze over.  
Under no circumstances whatsoever must you *ever* allow this to happen.

SQLITE_OPEN_FULLMUTEX (the default) means that Sqlite3 will, each time you make 
a call into the library, spend a few CPU cycles to ENSURE that you do not break 
the rules and that the consequences described above will not happen.

SQLITE_OPEN_NOMUTEX (not the default) means that Sqlite3 WILL NOT, each time 
you make a call into the library, spend those few CPU cycles ensuring that you 
have not broken the rules because YOU have explicitly taken responsibility upon 
yourself to ensure that you do no break the rules.  However, if you do break 
the rules, then you have obviously intended that the described consequences 
should come to pass and when they do, they should have been expected.

You can also compile the code so the default is SQLITE_OPEN_NOMUTEX and turn on 
a special idiot mode that will simply crash (fail an assert) if you break the 
rules, rather than letting the aforesaid consequences happen.  This takes more 
CPU cycles that having "pure" SQLITE_OPEN_NOMUTEX and less that 
SQLITE_OPEN_FULLMUTEX.

So you get to choose the level of consequence and risk that you are willing to 
tolerate.

So, if you have no mutex and two threads use the same connection to update the 
same table, then either both will work or you will suffer the consequences 
described above.  If you use the default serialized (full mutex) then both will 
work and there will be no consequence as described above.  In both cases 
neither operation will be isolated in any way from the other (that requires 
using separate connections).

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Prajeesh Prakash
>Sent: Wednesday, 28 November, 2018 07:16
>To: SQLite mailing list
>Subject: [sqlite] SQLITE_OPEN_FULLMUTEX vs SQLITE_OPEN_NOMUTEX
>
>Hi Members,
>
>Can any one please give a clear idea about SQLITE_OPEN_FULLMUTEX and
>SQLITE_OPEN_NOMUTEX because i am totally confused with the concept.
>If we enable FULLMUTEX what will happen if two thread trying to
>update the same table (Both thread are from same DB connection) in
>case of NOMUTEX what is the flow
>
>
>Thank you
>___
>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] SQLITE_OPEN_FULLMUTEX vs SQLITE_OPEN_NOMUTEX

[Prajeesh Prakash]

Hi Members,

Can any one please give a clear idea about SQLITE_OPEN_FULLMUTEX and 
SQLITE_OPEN_NOMUTEX because i am totally confused with the concept. If we 
enable FULLMUTEX what will happen if two thread trying to update the same table 
(Both thread are from same DB connection) in case of NOMUTEX what is the flow


Thank you
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug? Confused data entry with column name

[Keith Medcalf]

That is because some daft person is using the wrong quotes, doh!


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Dale Mellor
>Sent: Tuesday, 27 November, 2018 22:42
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Bug? Confused data entry with column name
>
>  THIS VERSION
>SQLite 3.25.3 2018-11-05 20:37:38
>89e099fbe5e13c33e683bef07361231ca525b88f7907be7092058007b750alt1
>zlib version 1.2.8
>gcc-8.2.0
>
>
>>   THIS SCRIPT
>create table test (id varchar, description varchar);
>insert into test (id, description) values ("Description", "Duh");
>insert into test (id, description) values ("Daft", "Daft");
>select rowid, * from test where id="Description";
>
>
>
>>>   PRODUCES
>2|Daft|Daft
>
>
>
>>>   BUT EXPECTED
>1|Description|Duh



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] SQLITE_OPEN_FULLMUTEX

[Keith Medcalf]

SQLITE_OPEN_FULLMUTEX ensures that EACH CONNECTION is SERIALLY ENTRANT into the 
Sqlite3 library code (ie, that two threads cannot make a call into the library 
on different threads AT THE SAME TIME, or put another way that only ONE THREAD 
at a time on EACH CONNECTION may make a call into the library).  The Sqlite3 
database engine code is only MULTIPLY ENTRANT on separate connections, but is 
required to be SERIALLY ENTRANT with respect to a (as in one) particular 
connection.  The default is serialized, or OPEN_FULLMUTEX, in order to prevent 
you from exploding something due to careless use of a connection in multiple 
threads.

Since transaction locking is handled at the CONNECTION level, if you use the 
same connection simultaneously from multiple threads they all share THE SAME 
ONE SINGLE transaction context.  In order to get an SQLITE_LOCKED response 
there must be more than one connection to the database, and one of those 
connections must be locking the database, in order for THE OTHER CONNECTION to 
get an SQLITE_LOCKED response.

There is no difference between using a SINGLE CONNECTION in a SINGLE THREAD or 
in MULTIPLE THREADS so long so long as you abide by the SINGLE ENTRANCE 
requirement.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter
>Sent: Wednesday, 28 November, 2018 00:54
>To: 'SQLite mailing list'
>Subject: Re: [sqlite] [EXTERNAL] SQLITE_OPEN_FULLMUTEX
>
>Using SQLITE_OPEN_FULLMUTEX puts SQLite into serialized mode. This
>means that mutltiple threads can share a single connection but will
>block until the mutex is freed because the thread currently holding
>the mutex has left the SQLite code. Blocked threads will only
>experience a time delay (concurrency is reduced).
>
>This is distinct from the SQLITE_LOCKED, which means that there is a
>(logical) conflict iin the same DB connection, e.g. attempting to
>drop a table in one thread while reading from the same table in
>another thread.
>
>-Ursprüngliche Nachricht-
>Von: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] Im Auftrag von Prajeesh Prakash
>Gesendet: Mittwoch, 28. November 2018 08:33
>An: SQLite mailing list 
>Betreff: [EXTERNAL] [sqlite] SQLITE_OPEN_FULLMUTEX
>
>Hi Members,
>
>I enabled the SQLITE_OPEN_FULLMUTEX in that case if one thread is
>trying to write in to the DB and other thread is trying to read from
>the DB (Same connection). In that case will it cause any
>SQLITE_LOCKED error. In some of the forum i found that if we enable
>the SQLITE_OPEN_FULLMUTEX  the  sqlite handle ( sqlite3 * handle)
>contains the mutex filed so when the sqlite library is invoking that
>API will wait for the mutex to get open.
>
>
>
>Thank you
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>___
> Gunter Hick | Software Engineer | Scientific Games International
>GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
>0430013 | (O) +43 1 80100 - 0
>
>May be privileged. May be confidential. Please delete if not the
>addressee.
>___
>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] [EXTERNAL] SQLITE_OPEN_FULLMUTEX

[Keith Medcalf]

No, you are incorrect.  Isolation is only BETWEEN DIFFERENT CONNECTIONS, and 
has nought whatsoever to do with threads ...


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Prajeesh Prakash
>Sent: Wednesday, 28 November, 2018 01:04
>To: SQLite mailing list; Hick Gunter
>Subject: Re: [sqlite] [EXTERNAL] SQLITE_OPEN_FULLMUTEX
>
>That means (with SQLITE_OPEN_FULLMUTEX) if two threads are trying to
>do write and read to the table only one will get the chance to do the
>operation other thread needs to wait until the first thread finish
>its job . Am i correct
>
>>
>> On November 28, 2018 at 1:24 PM Hick Gunter 
>wrote:
>>
>> Using SQLITE_OPEN_FULLMUTEX puts SQLite into serialized mode.
>This means that mutltiple threads can share a single connection but
>will block until the mutex is freed because the thread currently
>holding the mutex has left the SQLite code. Blocked threads will only
>experience a time delay (concurrency is reduced).
>>
>> This is distinct from the SQLITE_LOCKED, which means that there
>is a (logical) conflict iin the same DB connection, e.g. attempting
>to drop a table in one thread while reading from the same table in
>another thread.
>>
>> -Ursprüngliche Nachricht-
>> Von: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] Im Auftrag von Prajeesh Prakash
>> Gesendet: Mittwoch, 28. November 2018 08:33
>> An: SQLite mailing list 
>> Betreff: [EXTERNAL] [sqlite] SQLITE_OPEN_FULLMUTEX
>>
>> Hi Members,
>>
>> I enabled the SQLITE_OPEN_FULLMUTEX in that case if one thread
>is trying to write in to the DB and other thread is trying to read
>from the DB (Same connection). In that case will it cause any
>SQLITE_LOCKED error. In some of the forum i found that if we enable
>the SQLITE_OPEN_FULLMUTEX the sqlite handle ( sqlite3 * handle)
>contains the mutex filed so when the sqlite library is invoking that
>API will wait for the mutex to get open.
>>
>> Thank you
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>> ___
>> Gunter Hick | Software Engineer | Scientific Games
>International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a,
>HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0
>>
>> May be privileged. May be confidential. Please delete if not
>the addressee.
>>
>> ___
>> 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] [EXTERNAL] Bug? Confused data entry with column name

[E.Pasma]

and the confusing behaviour is admitted to be a "quirk" in SQLite:
https://sqlite.org/quirks.html#double_quoted_string_literals_are_accepted
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Bug? Confused data entry with column name

[Hick Gunter]

Works as advertised. "Description" (with double quotes) is a field name. 
'Description' with single quotes is a string constant.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dale Mellor
Gesendet: Mittwoch, 28. November 2018 06:42
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Bug? Confused data entry with column name

  THIS VERSION
SQLite 3.25.3 2018-11-05 20:37:38
89e099fbe5e13c33e683bef07361231ca525b88f7907be7092058007b750alt1
zlib version 1.2.8
gcc-8.2.0


>   THIS SCRIPT
create table test (id varchar, description varchar); insert into test (id, 
description) values ("Description", "Duh"); insert into test (id, description) 
values ("Daft", "Daft"); select rowid, * from test where id="Description";



>>   PRODUCES
2|Daft|Daft



>>   BUT EXPECTED
1|Description|Duh


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug? Confused data entry with column name

[Shawn Wagner]

Use single quotes, not double. Double quotes are used for identifiers, not
strings, so that matches rows where id and description have the same value.

On Wed, Nov 28, 2018, 1:19 AM Dale Mellor    THIS VERSION
> SQLite 3.25.3 2018-11-05 20:37:38
> 89e099fbe5e13c33e683bef07361231ca525b88f7907be7092058007b750alt1
> zlib version 1.2.8
> gcc-8.2.0
>
>
> >   THIS SCRIPT
> create table test (id varchar, description varchar);
> insert into test (id, description) values ("Description", "Duh");
> insert into test (id, description) values ("Daft", "Daft");
> select rowid, * from test where id="Description";
>
>
>
> >>   PRODUCES
> 2|Daft|Daft
>
>
>
> >>   BUT EXPECTED
> 1|Description|Duh
> ___
> 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] [Fwd: Bug? Confused data entry with column name]

[Dale Mellor]

 Forwarded Message 
From: Dale Mellor 
To: sqlite-users@mailinglists.sqlite.org
Subject: Bug?  Confused data entry with column name
Date: Wed, 28 Nov 2018 05:42:16 +

> > > > > > > > > >   THIS SCRIPT
> 
> create table test (id varchar, description varchar);
> insert into test (id, description) values ("Description", "Duh");
> insert into test (id, description) values ("Daft", "Daft");
> select rowid, * from test where id="Description";


Okay,  single quotes/double quotes,  my mistake.  I just read the SQL
standard.  Please ignore my previous posting.

Sorry,
Dale


signature.asc
Description: This is a digitally signed message part
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug? Confused data entry with column name

[Dale Mellor]

  THIS VERSION
SQLite 3.25.3 2018-11-05 20:37:38
89e099fbe5e13c33e683bef07361231ca525b88f7907be7092058007b750alt1
zlib version 1.2.8
gcc-8.2.0


>   THIS SCRIPT
create table test (id varchar, description varchar);
insert into test (id, description) values ("Description", "Duh");
insert into test (id, description) values ("Daft", "Daft");
select rowid, * from test where id="Description";



>>   PRODUCES
2|Daft|Daft



>>   BUT EXPECTED
1|Description|Duh


signature.asc
Description: This is a digitally signed message part
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] SQLITE_OPEN_FULLMUTEX

[Prajeesh Prakash]

Yes they are sharing the same DB connection
> On November 28, 2018 at 1:57 PM Simon Slavin  wrote:
> 
> 
> On 28 Nov 2018, at 8:03am, Prajeesh Prakash  
> wrote:
> 
> > That means (with SQLITE_OPEN_FULLMUTEX) if two threads are trying to do 
> > write and read to the table only one will get the chance to do the 
> > operation other thread needs to wait until the first thread finish its job 
> > . Am i correct
> 
> Are the two threads sharing the same database connection or do they each have 
> their own connection ?
> 
> Simon.
> ___
> 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] sqlite3 Asynchronous I/O

[Olivier Mascia]

Dear,

> Le 27 nov. 2018 à 10:24, Prajeesh Prakash  a 
> écrit :
> 
> I am using sqlite3 Asynchronous I/O on my application. So is there any way to 
> get the status of the DB update (After the write operation) from the 
> asyncWriterThread  so that my application can do the proper error handling.

Then:

>>On November 27, 2018 at 3:12 PM Shawn Wagner  
>> wrote:
>> 
>>By asynchronous i/o, do you mean the vfs module described here:
>>https://www.sqlite.org/asyncvfs.html ?
> 
> Le 27 nov. 2018 à 10:47, Prajeesh Prakash  a 
> écrit :
> 
> Yes it is, On sqlite3async.c file i found asyncWriterThread but i didn't find 
> any callback to let the application know the status of the DB update. How 
> this thread writing the data to DB, I am little bit confused.

Are you 100% sure that choosing the asyncvfs is the right thing to do for your 
needs?

As clearly written on https://www.sqlite.org/asyncvfs.html:

"...this module is no longer supported. The source code continues to exist in 
the SQLite source tree, but it is not a part of any standard build and is no 
longer maintained. This documentation is retained for historical reference."

Then the paragraph 1.0 on that page clearly describe the intent of this 
asyncvfs, to decouple the requests for writes and the writes themselves, 
immediately returning to the caller before the writes have occurred: 
"Asynchronous I/O appears to give better responsiveness, but at a price.  You 
lose the Durable property."

All this seems to go in the opposite direction of your intent (seeing your 
other posts around this asyncvfs subject). But I may be wrong about your needs 
and intents, as you didn't explained what problem you are trying to solve by 
choosing to use some old code from the attic and then looking for ways to get 
it do things it clearly wasn't designed to do.

Maybe some background information about the specifics constraints of your 
runtime environment, which lead you to believe the asyncvfs was the right thing 
to use, would help people understand and guide you to a proper solution.

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] SQLITE_OPEN_FULLMUTEX

[Simon Slavin]

On 28 Nov 2018, at 8:03am, Prajeesh Prakash  
wrote:

> That means (with SQLITE_OPEN_FULLMUTEX) if two threads are trying to do write 
> and read to the table only one will get the chance to do the operation other 
> thread needs to wait until the first thread finish its job . Am i correct

Are the two threads sharing the same database connection or do they each have 
their own connection ?

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] SQLITE_OPEN_FULLMUTEX

[Prajeesh Prakash]

That means (with SQLITE_OPEN_FULLMUTEX) if two threads are trying to do write 
and read to the table only one will get the chance to do the operation other 
thread needs to wait until the first thread finish its job . Am i correct

> 
> On November 28, 2018 at 1:24 PM Hick Gunter  wrote:
> 
> Using SQLITE_OPEN_FULLMUTEX puts SQLite into serialized mode. This means 
> that mutltiple threads can share a single connection but will block until the 
> mutex is freed because the thread currently holding the mutex has left the 
> SQLite code. Blocked threads will only experience a time delay (concurrency 
> is reduced).
> 
> This is distinct from the SQLITE_LOCKED, which means that there is a 
> (logical) conflict iin the same DB connection, e.g. attempting to drop a 
> table in one thread while reading from the same table in another thread.
> 
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] 
> Im Auftrag von Prajeesh Prakash
> Gesendet: Mittwoch, 28. November 2018 08:33
> An: SQLite mailing list 
> Betreff: [EXTERNAL] [sqlite] SQLITE_OPEN_FULLMUTEX
> 
> Hi Members,
> 
> I enabled the SQLITE_OPEN_FULLMUTEX in that case if one thread is trying 
> to write in to the DB and other thread is trying to read from the DB (Same 
> connection). In that case will it cause any SQLITE_LOCKED error. In some of 
> the forum i found that if we enable the SQLITE_OPEN_FULLMUTEX the sqlite 
> handle ( sqlite3 * handle) contains the mutex filed so when the sqlite 
> library is invoking that API will wait for the mutex to get open.
> 
> Thank you
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> ___
> Gunter Hick | Software Engineer | Scientific Games International GmbH | 
> Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) 
> +43 1 80100 - 0
> 
> May be privileged. May be confidential. Please delete if not the 
> addressee.
> 
> ___
> 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