[sqlite] Segmentation fault in syncJournal (pPager=0x102028) at src/pager.c:2417
Hello, I use SQLite 3.5.2 on ARM9 embedded system. ( Linux 2.4 ) My program sometimes meets segmentation fault ! ( randomly, maybe several hours, maybe several days ! ) Here is a clip from GDB: -- snip -- (gdb) backtrace #0 syncJournal (pPager=0x102028) at src/pager.c:2417 #1 0x0007cbbc in sqlite3PagerCommitPhaseOne (pPager=0x102028, zMaster=0x0, nTrunc=0) at src/pager.c:3948 #2 0x00065354 in sqlite3BtreeCommitPhaseOne (p=0x0, zMaster=0x0) at src/btree.c:2074 #3 0x0005e698 in vdbeCommit (db=0x101ec8) at src/vdbeaux.c:1103 #4 0x0005ece0 in sqlite3VdbeHalt (p=0x37ec98) at src/vdbeaux.c:1442 #5 0x0008794c in sqlite3VdbeExec (p=0x37ec98) at src/vdbe.c:686 #6 0x00101ec8 in ?? () It seems that the segmentation fault is occured in syncJournal (pPager=0x102028) at src/pager.c:2417. Is there something wrong ? Such as the parameters of sqlite3BtreeCommitPhaseOne (p=0x0, zMaster=0x0) at src/btree.c:2074 ?? Or am I missing something to make this segmentation fault occured when I use SQLite ? Thanks. Sincerely Yours, Joseph Hsu Email : [EMAIL PROTECTED]
Re: [sqlite] db crash when creating rows
I compiled your program with MinGW gcc 3.4.2 on Windows with sqlite-amalgamation-3_5_3.zip. gcc -I. ged.c sqlite3.c -o ged.exe It runs fine. If another sqlite3 process issues a query like: select * from test; while ged.exe is running, then ged.exe will exit with: Creating 10 rows... died on row 2595 SQL error 2: database is locked So I'm wondering if another process accessed your database file while it was running. Or perhaps, are you running antivirus software on your PC? What happens if you retry sqlite3_exec a few times after failure instead of exitting? (throw in Sleep(50) after each failure for good measure) --- Ged Murphy <[EMAIL PROTECTED]> wrote: > I am doing some programmatic stress / benchmark testing on sqlite in the > hope it'll be of use to me in my project. > > I'm performing some simple stress tests to gauge read/write speeds, whereby > I fill a table with a large amount rows > > However, I hit the following problem on writing: > > Creating 10 rows... > > died on row 1075 > > SQL error 2: unable to open database fi > > Press any key to continue . . . > > I'm using version 3.5.2 and my stress test code (written in C) is attached. > > Does anyone have any ideas as to why it fails? Get easy, one-click access to your favorites. Make Yahoo! your homepage. http://www.yahoo.com/r/hs - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLITE_BUSY retry
Overall sqlite is losing its credits. Now when i run sqlite in multithread & multi process mode system experiences a infinite loop in the busy handler. Also there is no log which tell which process/thread has acquired the lock,hence needing complete system halt and restart. This observed in 3.4.0,3.4.2, 3.5.1,3.5.2 Also as stated elsewhere sqlite 3.5.2 now support passing one connecion acorss threads is not valid. regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - Original Message - From: John Stanton <[EMAIL PROTECTED]> Date: Thursday, November 29, 2007 1:34 am Subject: Re: [sqlite] SQLITE_BUSY retry > You could use a BEGIN IMMEDIATE to lock the DB before you launch > the > transaction and loop on SQLITE_BUSY or use the plain BEGIN which > will > allow reads during the transaction and not lock the DB until you > issue a > COMMIT (the END). Just loop on the BUSY on the END SQL statement > until > the user who has the DB locked releases it. > > A technique we use to get a minimum latency but reasonably > efficient > busy wait is to issue a yield call each time an SQLITE_BUSY is > encountered so that the time slice is dropped and other processes > can > run. A alternative is to issue a short delay or sleep. > > Joanne Pham wrote: > > Hi All, > > Here my statements to insert rows into the database > > Open the database connection > > BEGIN > > insert ...using sqlite3_step > > insert ...using sqlite3_step > > END > > So at the time I issued "END" transaction I got the error message > SQLITE_BUSY so I need to issue the "END" transaction again or What > should I do in this case to handle SQLITE_BUSY. > > Thanks a lot in advance for the help or advice. > > JP > > > > > > > > - Original Message > > From: Joanne Pham <[EMAIL PROTECTED]> > > To: sqlite-users@sqlite.org > > Sent: Wednesday, November 28, 2007 11:27:52 AM > > Subject: [sqlite] SQLITE_BUSY retry > > > > Hi All, > > I have used "BEGIN" and "END" Transaction to insert the data to > SQLite database. > > BEGIN > > insert ... > > insert ... > > END > > > > When I issued the "END" operation the error message return back > is "SQLITE_BUSY". > > What should I do if I want to handle SQLITE_BUSY /retry the > transaction. Should I execute "END" transaction again. > > How to handle the SQLITE_BUSY? > > Thanks, > > JP > > > > > > > > > Get easy, one-click access to your favorites. > > Make Yahoo! your homepage. > > http://www.yahoo.com/r/hs > > > > > > > > > Be a better sports nut! Let your teams follow you > > with Yahoo Mobile. Try it now. > http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] commit and rollback
about this synchronization of multiple writers, can you please explain on how to make this possible? or your just saying that i need to make a daemon that will eventually synchronize the writers? John Stanton-3 wrote: > > Multiple writers merely have to be synchronized. > > arbalest06 wrote: >> so there is really no way that multiple processes can write into the >> database?..but multiple processes can read at the same time right?.. >> >> >> Igor Tandetnik wrote: >>> arbalest06 <[EMAIL PROTECTED]> wrote: q#1: is it possible that multiple users can write into the database at the same time? >>> No. >>> q#2: if users A, B, C are writing to the database at the same time, >>> They can't. >>> q#3: if users A, B, C are writing to the database at the same time, >>> They can't. >>> >>> Igor Tandetnik >>> >>> - >>> To unsubscribe, send email to [EMAIL PROTECTED] >>> - >>> >>> >>> >> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > -- View this message in context: http://www.nabble.com/commit-and-rollback-tf4804976.html#a14018116 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLITE_BUSY retry
Joanne Pham wrote:
Hi John,
Thanks a lot for your response.
Make sure I am understanding your answer related to SQLITE_BUSY. So I need to
change my code from
Open the database connection
BEGIN
insert ...using sqlite3_step
insert ...using sqlite3_step
END
to
Open the database connection
BEGIN
insert ...using sqlite3_step
insert ...using sqlite3_step
...
do {
rc = END (transaction or commit)
while (rc == SQLITE_BUSY)
So I just loop on if the return statement is SQLITE_BUSY.
Is that correct John?
Thanks a ton,
JP
Correct. You might add a count to drop out if there is a deadlock.
e.g.
use sqlite3_prepare_v2
var count = MAX_SPINS;
while (1) {
rc = sqlite3_step();
if (rc == SQLITE_BUSY) yield();
else {
if ((rc != SQLITE_OK) || (count <= 0)) report_error();
break;
}
count--;
}
- Original Message
From: John Stanton <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, November 28, 2007 12:04:34 PM
Subject: Re: [sqlite] SQLITE_BUSY retry
You could use a BEGIN IMMEDIATE to lock the DB before you launch the
transaction and loop on SQLITE_BUSY or use the plain BEGIN which will
allow reads during the transaction and not lock the DB until you issue a
COMMIT (the END). Just loop on the BUSY on the END SQL statement until
the user who has the DB locked releases it.
A technique we use to get a minimum latency but reasonably efficient
busy wait is to issue a yield call each time an SQLITE_BUSY is
encountered so that the time slice is dropped and other processes can
run. A alternative is to issue a short delay or sleep.
Joanne Pham wrote:
Hi All,
Here my statements to insert rows into the database
Open the database connection
BEGIN
insert ...using sqlite3_step
insert ...using sqlite3_step
END
So at the time I issued "END" transaction I got the error message SQLITE_BUSY so I need
to issue the "END" transaction again or What should I do in this case to handle
SQLITE_BUSY.
Thanks a lot in advance for the help or advice.
JP
- Original Message
From: Joanne Pham <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, November 28, 2007 11:27:52 AM
Subject: [sqlite] SQLITE_BUSY retry
Hi All,
I have used "BEGIN" and "END" Transaction to insert the data to SQLite database.
BEGIN
insert ...
insert ...
END
When I issued the "END" operation the error message return back is "SQLITE_BUSY".
What should I do if I want to handle SQLITE_BUSY /retry the transaction. Should I execute "END" transaction again.
How to handle the SQLITE_BUSY?
Thanks,
JP
Get easy, one-click access to your favorites.
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs
Be a better sports nut! Let your teams follow you
with Yahoo Mobile. Try it now. http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Be a better pen pal.
Text or chat with friends inside Yahoo! Mail. See how. http://overview.mail.yahoo.com/
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] broken link in Nov 27 news and old downloads
looks like the link target and desc were reversed in the Nov 27 news item ... the target is "download" and the description is "download.html" when it should be the other way around also, are old releases archived somewhere ? i cant seem to find an "archives" or "old releases" link on the front page ... i have a modified tarball of an old version here and i need to compare it against the vanilla version to see if there's anything to be forward ported and pushed upstream ... -mike signature.asc Description: This is a digitally signed message part.
Re: [sqlite] ShawnMilo intro
On Wed, 28 Nov 2007 15:03:01 -0500, "Shawn Milochik" <[EMAIL PROTECTED]> wrote: >Hi. I just joined the list. I'm a developer who mainly works on the Linux >command line with Makefiles, Perl one-liners, and some Perl and Python >scripts. Nearly 100% of what I do is processing tab-delimited files. > >I live in Reading, PA. My main hobby is magic -- the art of illusion, not >the card game. > >I have a couple of questions, but I am going to lurk a bit first. However, >is there an archive? I didn't receive one by e-mailing the automated help >address for this list. I'd like to see if the answers are there first. > >Thanks, >Shawn > >MiloCast.com If you don't like to plough through one of the webarchives Trevor Talbot pointed to, but rather prefer to read from your mail client, I can send you an mbox format file with some 16 months of messages in this list. Over 13000 messages, 54 MByte raw, 8.5 MByte zipped. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Lemon: Conflicts with repeated TERMINALS
--- Ralf Junker <[EMAIL PROTECTED]> wrote: > article ::= blocks. > > blocks ::= block. > blocks ::= blocks block. > > block ::= heading. > block ::= paragraph. > > heading ::= HEADING_START text HEADING_END. > heading ::= HEADING_START text. > heading ::= HEADING_START. > > paragraph ::= text NEWLINE. > paragraph ::= paragraph text NEWLINE. > paragraph ::= text. > paragraph ::= paragraph text. > > text ::= textpiece. > text ::= text textpiece. > > textpiece ::= TEXT. > textpiece ::= LINK. Your grammar is ambiguous. The text tokens run together for various rules because the grammar lacks clear separators between them. You can fix it a million ways by altering your grammar. Here is one way: article ::= blocks. blocks ::= block. blocks ::= blocks block. block ::= heading. block ::= paragraph. heading ::= HEADING_START text HEADING_END. heading ::= HEADING_START text. heading ::= HEADING_START. paragraph ::= PARA text. text ::= textpiece. text ::= text textpiece. textpiece ::= TEXT. textpiece ::= LINK. Here's another: article ::= blocks. blocks ::= block. blocks ::= blocks block. block ::= heading NEWLINE. block ::= paragraph NEWLINE. heading ::= HEADING_START text HEADING_END. heading ::= HEADING_START text. heading ::= HEADING_START. paragraph ::= text. text ::= textpiece. text ::= text textpiece. textpiece ::= TEXT. textpiece ::= LINK. Lemon generates an .out file for the .y file processed. You can examine it for errors. Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now. http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLITE_BUSY retry
Hi John,
Thanks a lot for your response.
Make sure I am understanding your answer related to SQLITE_BUSY. So I need to
change my code from
Open the database connection
BEGIN
insert ...using sqlite3_step
insert ...using sqlite3_step
END
to
Open the database connection
BEGIN
insert ...using sqlite3_step
insert ...using sqlite3_step
...
do {
rc = END (transaction or commit)
while (rc == SQLITE_BUSY)
So I just loop on if the return statement is SQLITE_BUSY.
Is that correct John?
Thanks a ton,
JP
- Original Message
From: John Stanton <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, November 28, 2007 12:04:34 PM
Subject: Re: [sqlite] SQLITE_BUSY retry
You could use a BEGIN IMMEDIATE to lock the DB before you launch the
transaction and loop on SQLITE_BUSY or use the plain BEGIN which will
allow reads during the transaction and not lock the DB until you issue a
COMMIT (the END). Just loop on the BUSY on the END SQL statement until
the user who has the DB locked releases it.
A technique we use to get a minimum latency but reasonably efficient
busy wait is to issue a yield call each time an SQLITE_BUSY is
encountered so that the time slice is dropped and other processes can
run. A alternative is to issue a short delay or sleep.
Joanne Pham wrote:
> Hi All,
> Here my statements to insert rows into the database
> Open the database connection
> BEGIN
>insert ...using sqlite3_step
>insert ...using sqlite3_step
> END
> So at the time I issued "END" transaction I got the error message SQLITE_BUSY
> so I need to issue the "END" transaction again or What should I do in this
> case to handle SQLITE_BUSY.
> Thanks a lot in advance for the help or advice.
> JP
>
>
>
> - Original Message
> From: Joanne Pham <[EMAIL PROTECTED]>
> To: sqlite-users@sqlite.org
> Sent: Wednesday, November 28, 2007 11:27:52 AM
> Subject: [sqlite] SQLITE_BUSY retry
>
> Hi All,
> I have used "BEGIN" and "END" Transaction to insert the data to SQLite
> database.
>BEGIN
>insert ...
>insert ...
> END
>
> When I issued the "END" operation the error message return back is
> "SQLITE_BUSY".
> What should I do if I want to handle SQLITE_BUSY /retry the transaction.
> Should I execute "END" transaction again.
> How to handle the SQLITE_BUSY?
> Thanks,
> JP
>
>
>
>
> Get easy, one-click access to your favorites.
> Make Yahoo! your homepage.
> http://www.yahoo.com/r/hs
>
>
>
>
> Be a better sports nut! Let your teams follow you
> with Yahoo Mobile. Try it now.
> http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Be a better pen pal.
Text or chat with friends inside Yahoo! Mail. See how.
http://overview.mail.yahoo.com/
Re: [sqlite] LIKE operator extremely slow? more than 10 times slower than egrep?
If performance is very important to you and you have a need for SQL elsewhere consider using the Sqlite virtual table interface. Your virtual table can be a flat file which is memory mapped and you use either a regex or fast string search on it. You will have a result in milliseconds. You could return a rowid for further navigation through the database. String searching on a memory resident list will be faster than using an index up to quite a large size table. If you have a multi-processor machine you could partition the search into parallel tasks and get an advantage. It appears that you are doing a simple string search and don't need regular expression overhead. If you have a very large table you can build a B-tree index on it in your virtual table and access through that. The index can also be memory mapped and will deliver millisecond access times from tables with many millions of entries. We have achieved speed improvements on the scale of orders of magnitude by using such techniques on text and taking advantage of a high performance search algorithm. Spiros Ioannou wrote: John Stanton wrote: Sqlite does cater for text searching. Look at FTS. This is not text searching. No stemming, etc etc is required. Column has exactly 1 word, and the 'LIKE' substring matching is performed at the words' first characters (not end-characters). Thanks, -Spiros - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] ShawnMilo intro
On 11/28/07, Shawn Milochik <[EMAIL PROTECTED]> wrote: > I have a couple of questions, but I am going to lurk a bit first. However, > is there an archive? I didn't receive one by e-mailing the automated help > address for this list. I'd like to see if the answers are there first. Welcome :) There are 3 archives listed here: http://sqlite.org/support.html I think gmane is said to have a good search engine, but it's been ages since I've used it, so I can't be sure. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] LIKE operator extremely slow? more than 10 times slower than egrep?
On 11/28/07, Spiros Ioannou <[EMAIL PROTECTED]> wrote: > > egrep gets to work with a flat text file, which it can easily read > > sequentially and get optimal performance from the OS's file > > buffering/cache management. It only needs to read a piece of the file > > and scan for patterns, repeating until done. The only structure it > > needs to be aware of is line breaks, but that is so simple it can be > > folded into the pattern scan itself. > > You are mostly describing fgrep. Egrep does a regular expression search > thus honouring column layout in the example I gave earlier. (FYI fgrep > is 50% faster than egrep than egrep in this query) I was describing grep and all related tools. The only relevant difference between egrep and fgrep is what kind of pattern it looks for; the searching method is the same. Egrep is not aware of column structure, only lines. > The points are two: > 1) Could sqlite structure allow for faster full column scan queries? > More than 1000% slower performance seems way too much difference. The short answer is no. The longer answer is maybe in theory (see research into column-oriented or "vertical" datastores, such as the much-publicized Vertica), but SQLite's usage scenarios don't really make it appropriate. It would probably mean abandoning the single-file database, for instance, and would make many other common queries much less efficient. That's ignoring all sorts of other practical issues, like drh's careful avoidance of patent-encumbered things. It's possible there is room for optimizing this case a bit more the way it is, but it will always be much slower than grep. You may not realize what I mean about structured data. A plaintext file looks like this: LineLineLineLineLineLineLine... An sqlite database, on the other hand, has lots of structure. It's broken into fixed-sized pages, each of which has a small header at the front. Each table needs a way to find the rows in that belong to it (index on the rowid field). For each row, it needs to be able to locate each field of data belonging to that row. Each field has, besides the data itself, a type code and data length. All of this is encoded in various space-efficient forms in the same file. So, for instance, consider that a field of data is stored like: TypeLengthData... and you can see where this is going. When SQLite searches it, it needs to follow the structure to know where it is and what it's looking at. It can't simply read the next chunk of data and do pattern matching on it immediately, like grep can. > 2) an index could (and should) be used when using LIKE 'asdf' > > Well I actually solved this problem by using > PRAGMA case_sensitive_like = 1; > Now the index is being used (for all non-english characters). Which is what you wanted, right? > But the online manual states: > > " > if case_sensitive_like mode is enabled then the column must use the > default BINARY collating sequence, > if case_sensitive_like mode is disabled then the column must use the b > uilt-in NOCASE collating sequence. > > > NOCASE - The same as binary, except the 26 upper case characters used > by the English language are folded to their lower case equivalents > before the comparison is performed. > " > > so as I understand it there's no reason that the index was not used in > the NOCASE scenario. It should do case-insensitive matches for the 26 > latin characters and case-sensitive (binary) for all the others. It might be a little hard to process that part the first few times. You can declare a collation when creating the index itself, either BINARY or NOCASE. The default is BINARY, which is case sensitive (does not assume US ASCII or do anything with case). By default, LIKE is case insensitive, which means it does care about case so it can treat 'A' as 'a'. Therefore it can only use a NOCASE index for optimizing prefix matches, because the only way for an index to work is if it treats the characters the same way. NOCASE is not the index default though, and it isn't what you wanted because you have non-english data anyway. By turning case_sensitive_like on, you made LIKE look for an index with BINARY collation instead. That matches the index you created, so it can optimize that case now. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLITE_BUSY retry
You could use a BEGIN IMMEDIATE to lock the DB before you launch the transaction and loop on SQLITE_BUSY or use the plain BEGIN which will allow reads during the transaction and not lock the DB until you issue a COMMIT (the END). Just loop on the BUSY on the END SQL statement until the user who has the DB locked releases it. A technique we use to get a minimum latency but reasonably efficient busy wait is to issue a yield call each time an SQLITE_BUSY is encountered so that the time slice is dropped and other processes can run. A alternative is to issue a short delay or sleep. Joanne Pham wrote: Hi All, Here my statements to insert rows into the database Open the database connection BEGIN insert ...using sqlite3_step insert ...using sqlite3_step END So at the time I issued "END" transaction I got the error message SQLITE_BUSY so I need to issue the "END" transaction again or What should I do in this case to handle SQLITE_BUSY. Thanks a lot in advance for the help or advice. JP - Original Message From: Joanne Pham <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Wednesday, November 28, 2007 11:27:52 AM Subject: [sqlite] SQLITE_BUSY retry Hi All, I have used "BEGIN" and "END" Transaction to insert the data to SQLite database. BEGIN insert ... insert ... END When I issued the "END" operation the error message return back is "SQLITE_BUSY". What should I do if I want to handle SQLITE_BUSY /retry the transaction. Should I execute "END" transaction again. How to handle the SQLITE_BUSY? Thanks, JP Get easy, one-click access to your favorites. Make Yahoo! your homepage. http://www.yahoo.com/r/hs Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now. http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] ShawnMilo intro
Hi. I just joined the list. I'm a developer who mainly works on the Linux command line with Makefiles, Perl one-liners, and some Perl and Python scripts. Nearly 100% of what I do is processing tab-delimited files. I live in Reading, PA. My main hobby is magic -- the art of illusion, not the card game. I have a couple of questions, but I am going to lurk a bit first. However, is there an archive? I didn't receive one by e-mailing the automated help address for this list. I'd like to see if the answers are there first. Thanks, Shawn MiloCast.com
Re: [sqlite] LIKE operator extremely slow? more than 10 times slower than egrep?
John Stanton wrote: Sqlite does cater for text searching. Look at FTS. This is not text searching. No stemming, etc etc is required. Column has exactly 1 word, and the 'LIKE' substring matching is performed at the words' first characters (not end-characters). Thanks, -Spiros - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLITE_BUSY retry
Hi All, Here my statements to insert rows into the database Open the database connection BEGIN insert ...using sqlite3_step insert ...using sqlite3_step END So at the time I issued "END" transaction I got the error message SQLITE_BUSY so I need to issue the "END" transaction again or What should I do in this case to handle SQLITE_BUSY. Thanks a lot in advance for the help or advice. JP - Original Message From: Joanne Pham <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Wednesday, November 28, 2007 11:27:52 AM Subject: [sqlite] SQLITE_BUSY retry Hi All, I have used "BEGIN" and "END" Transaction to insert the data to SQLite database. BEGIN insert ... insert ... END When I issued the "END" operation the error message return back is "SQLITE_BUSY". What should I do if I want to handle SQLITE_BUSY /retry the transaction. Should I execute "END" transaction again. How to handle the SQLITE_BUSY? Thanks, JP Get easy, one-click access to your favorites. Make Yahoo! your homepage. http://www.yahoo.com/r/hs Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now. http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ
[sqlite] SQLITE_BUSY retry
Hi All, I have used "BEGIN" and "END" Transaction to insert the data to SQLite database. BEGIN insert ... insert ... END When I issued the "END" operation the error message return back is "SQLITE_BUSY". What should I do if I want to handle SQLITE_BUSY /retry the transaction. Should I execute "END" transaction again. How to handle the SQLITE_BUSY? Thanks, JP Get easy, one-click access to your favorites. Make Yahoo! your homepage. http://www.yahoo.com/r/hs
Re: [sqlite] LIKE operator extremely slow? more than 10 times slower than egrep?
Trevor Talbot wrote: On 11/27/07, Spiros Ioannou <[EMAIL PROTECTED]> wrote: I had a 135MB, 1256132 lines, '@' separated text file containing various words and text fields (like a dictionary). Example record: [EMAIL PROTECTED]@[EMAIL PROTECTED],[EMAIL PROTECTED] altana : μικρός ανθόκηπος - εξώστης, ταράτσα@@@ I imported the data in sqlite3.3.6 but when querying with the 'like' operator, the performance way too slow (about 1.5-2 seconds/query): >time sqlite3 dicts.db "select * from table1 where word like 'asdf%';" 1.156u 0.491s 0:01.64 100.0%0+0k 0+0io 0pf+0w FYI using egrep takes only 0.14s to get results in the worse case scenario: >time egrep -i "[EMAIL PROTECTED]@[EMAIL PROTECTED]@[EMAIL PROTECTED]@asdf" meta.txt 0.077u 0.069s 0:00.14 92.8% 0+0k 0+0io 0pf+0w 1) I know egrep is not a DB but does sqlite use such an inefficient search algorithm for content that cannot be indexed? Why not reverting to simple 'grep-like' methods? Or am I missing something trivial here? As a database, the file contains a LOT of structure. SQLite must follow the structure to locate the table, each record in the table, and expand the text field from its stored format. (The text itself is not a big deal, but the row/column that stores it must be found and extracted.) The data is not necessarily stored end-to-end sequentially in the file, as it's impossible to do that and still maintain all the necessary properties of a structured database. egrep gets to work with a flat text file, which it can easily read sequentially and get optimal performance from the OS's file buffering/cache management. It only needs to read a piece of the file and scan for patterns, repeating until done. The only structure it needs to be aware of is line breaks, but that is so simple it can be folded into the pattern scan itself. While someone would need to do profiling to examine exactly where the time goes, it would not suirprise me to find that SQLite's LIKE pattern matcher is more efficient than egrep, but that the overhead from dealing with structured data is responsible for the time difference. I don't find the time itself surprising at all. 2) Why doesn't an index raise performance at all in this case? Is it because non-latin chars are used? Careful use of an index should help for the specific query you posted (see http://sqlite.org/optoverview.html#like_opt), but it's not possible for an index to speed up arbitrary patterns. If you need to perform arbitrary pattern searches on a flat text file, SQLite (and most other structured storage for that matter) is simply the wrong tool for the job. grep and friends are highly optimized for just that purpose. Sqlite does cater for text searching. Look at FTS. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] commit and rollback
Multiple writers merely have to be synchronized. arbalest06 wrote: so there is really no way that multiple processes can write into the database?..but multiple processes can read at the same time right?.. Igor Tandetnik wrote: arbalest06 <[EMAIL PROTECTED]> wrote: q#1: is it possible that multiple users can write into the database at the same time? No. q#2: if users A, B, C are writing to the database at the same time, They can't. q#3: if users A, B, C are writing to the database at the same time, They can't. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] LIKE operator extremely slow? more than 10 times slower than egrep?
Firstly, thank you all for your answers, egrep gets to work with a flat text file, which it can easily read sequentially and get optimal performance from the OS's file buffering/cache management. It only needs to read a piece of the file and scan for patterns, repeating until done. The only structure it needs to be aware of is line breaks, but that is so simple it can be folded into the pattern scan itself. You are mostly describing fgrep. Egrep does a regular expression search thus honouring column layout in the example I gave earlier. (FYI fgrep is 50% faster than egrep than egrep in this query) The points are two: 1) Could sqlite structure allow for faster full column scan queries? More than 1000% slower performance seems way too much difference. 2) an index could (and should) be used when using LIKE 'asdf' Well I actually solved this problem by using PRAGMA case_sensitive_like = 1; Now the index is being used (for all non-english characters). But the online manual states: " if case_sensitive_like mode is enabled then the column must use the default BINARY collating sequence, if case_sensitive_like mode is disabled then the column must use the b uilt-in NOCASE collating sequence. NOCASE - The same as binary, except the 26 upper case characters used by the English language are folded to their lower case equivalents before the comparison is performed. " so as I understand it there's no reason that the index was not used in the NOCASE scenario. It should do case-insensitive matches for the 26 latin characters and case-sensitive (binary) for all the others. Or I'm still missing some essential sql parameter. Or perhaps my version is too old. Thank you all for your time, All the best, -Spiros - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Lemon: Conflicts with repeated TERMINALS
I am trying to write a Wiki parser with Lemon. The Lemon features suite my needs perfectly, but I am unfortunately stuck with the problem of parsing conflicts. All conflicts seem caused by repeat constructs like this: text ::= textpiece. text ::= text textpiece. The complete grammar follows below and results in 10 conflicts. I have read the manual, looked at tutorials, and searched the mailing list, but nothing helped me to reduce the number of conflicts. Changing token order even tends cause more of them. Reading similar grammars for Bison makes me wonder why Bison apparently has no problems with them but Lemon does. Am I doing something wrong or is this simply not possible with Lemon? Ralf --- article ::= blocks. blocks ::= block. blocks ::= blocks block. block ::= heading. block ::= paragraph. heading ::= HEADING_START text HEADING_END. heading ::= HEADING_START text. heading ::= HEADING_START. paragraph ::= text NEWLINE. paragraph ::= paragraph text NEWLINE. paragraph ::= text. paragraph ::= paragraph text. text ::= textpiece. text ::= text textpiece. textpiece ::= TEXT. textpiece ::= LINK. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite-3.5.3 checksum error
Hello all, Got a problem when trying to install the sqlite-3.5.3 build using port for GNU Darwin : ---> Verifying checksum(s) for sqlite3 Error: Checksum (sha1) mismatch for sqlite-3.5.3.tar.gz Error: Target org.macports.checksum returned: Unable to verify file checksums Error: Status 1 encountered during processing. Any thoughts would be appreciated. Cheers Adam
Re: [sqlite] How to run SQLite tests
On Nov 28, 2007, at 11:01 PM, Mark Brown wrote: Hi- Could someone please tell me how I can run the SQLite tests? I see them in CVS, but I'm not sure what to do with them. Since I'm running on vxWorks, I think they would provide an excellent way for me to know what problems I may have with my particular OS implementation. Depending on how you are compiling, you might just be able to run [make test]. Otherwise, you need to build a target called "testfixture". testfixture is a Tcl shell with the sqlite Tcl interface and some special test instrumentation built into it. Use testfixture to run the script "quick.test" from the test directory (takes from 2-4 minutes). Once that passes, run "all.test" (warning - takes upwards of 30 minutes to run). $ ./testfixture ../sqlite/test/quick.test Dan. Thanks, Mark -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] db crash when creating rows
Simon Davies wrote: > Compiled your snippet using VS2005 on XP, sqlite 3.4.2 - > ran to completion with no errors Yeah, I had no errors when I ran this using the native exports from the System.Data.SQLite.dll .NET wrapper from http://sqlite.phxsoftware.com This also uses the 3.4.2 library. I can only assume there's a problem with the 3.5.* lib somewhere when bulk filling in this manner. > PS Took nrly 2 hrs. Added "BEGIN" and "COMMIT" around insert loop, > reduced time to 10 seconds Yeah, it's a long way of filling but I needed to test it in this way as it'd be filled up by single queries in the real world Thanks, Ged. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] db crash when creating rows
Hi Ged, Compiled your snippet using VS2005 on XP, sqlite 3.4.2 - ran to completion with no errors Rgds, Simon PS Took nrly 2 hrs. Added "BEGIN" and "COMMIT" around insert loop, reduced time to 10 seconds On 27/11/2007, Ged Murphy <[EMAIL PROTECTED]> wrote: > > > > Hi, > > > > I am doing some programmatic stress / benchmark testing on sqlite in the > hope it'll be of use to me in my project. > > I'm performing some simple stress tests to gauge read/write speeds, whereby > I fill a table with a large amount rows > > However, I hit the following problem on writing: > > > > Creating 10 rows... > > died on row 1075 > > SQL error 2: unable to open database fi > > Press any key to continue . . . > > > > I'm using version 3.5.2 and my stress test code (written in C) is attached. > > Does anyone have any ideas as to why it fails? > > > > Thanks, > > Ged > > > > > > > > > > > > > - > To unsubscribe, send email to > [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Distinguishing empty-result SELECT from INSERT, UPDATE, DELETE, etc.
Joe & Igor - thanks for the tips! Mark > -Original Message- > From: Joe Wilson [mailto:[EMAIL PROTECTED] > Sent: Tuesday, November 27, 2007 5:39 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Re: Distinguishing empty-result SELECT > from INSERT, UPDATE, DELETE, etc. > > See also sqlite3_sql() if sqlite3_prepare_v2() or > sqlite3_prepare16_v2() was used. > > http://www.sqlite.org/cvstrac/chngview?cn=4543 > > --- Igor Tandetnik <[EMAIL PROTECTED]> wrote: > > Evans, Mark (Tandem) <[EMAIL PROTECTED]> wrote: > > > Would I be the first person to wish there were a way for, say, a > > > JDBC driver that uses SQLite to be able to tell what kind of SQL > > > statement it is executing? > > > > sqlite3_column_count returns 0 for a non-SELECT statement, and a > > non-zero value for SELECT. > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DESC indexes not available after a VACUUM?
Thanks for addressing this, Richard. Preset the legacy_file_format pragma to the value of the primary database so that a VACUUM will not unknowingly alter the setting. Ticket #2804. http://www.sqlite.org/cvstrac/chngview?cn=4574 Could you please update the PRAGMA documentation to reflect the new preset behavior of "PRAGMA legacy_file_format;"? --- Joe Wilson <[EMAIL PROTECTED]> wrote: > When I compare the database bytes before and after the 2nd > process VACUUM these bytes differ: > > zero-basedvalue value > byte offset beforeafter my guess of what it is > 2nd VAC 2nd VAC > --- --- --- -- > 272 5 File change counter? > 432 5 Schema cookie? > 474 1 meta[1], file format? > > Doesn't byte offset 47 correspond to meta[1], the file format > of the schema layer? > > --- Joe Wilson <[EMAIL PROTECTED]> wrote: > > Regarding http://www.sqlite.org/cvstrac/tktview?tn=2804 ... > > > > I'm confused by the explanation. > > Ignoring the PRAGMA for the moment, I was surprised that DESC > > indexes were not available after a reconnect and VACUUM, as shown > > in the example: > > > > $ rm -f foo.db > > $ ./sqlite3-3.5.3.bin foo.db > > SQLite version 3.5.3 > > Enter ".help" for instructions > > sqlite> PRAGMA legacy_file_format=OFF; > > sqlite> CREATE TABLE abc(a,b,c); > > sqlite> CREATE INDEX abc_i on abc(b desc, c asc, a desc); > > sqlite> explain query plan select * from abc order by b desc, c asc, a desc; > > 0|0|TABLE abc WITH INDEX abc_i ORDER BY > > sqlite> vacuum; > > sqlite> explain query plan select * from abc order by b desc, c asc, a desc; > > 0|0|TABLE abc WITH INDEX abc_i ORDER BY > > sqlite> .q > > > > $ ./sqlite3-3.5.3.bin foo.db > > SQLite version 3.5.3 > > Enter ".help" for instructions > > sqlite> explain query plan select * from abc order by b desc, c asc, a desc; > > 0|0|TABLE abc WITH INDEX abc_i ORDER BY > > sqlite> vacuum; > > sqlite> explain query plan select * from abc order by b desc, c asc, a desc; > > 0|0|TABLE abc > > > > Notice that the DESC index is no longer available after the second > > process' VACUUM was run. > > > > If this isn't the file format being changed, then why isn't the DESC > > index being used? > > > > Is this by design? Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now. http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] commit and rollback
On 11/28/07, arbalest06 <[EMAIL PROTECTED]> wrote: > ok..thanx for that..now if process A is writing into the database, and > process B attempts to write, does sqlite take note of B's attempt and gives > the permission to B when A is done? like would it be a queue that the first > process that attempted to write should be given priority to write? or is it > that when process A is done, then process C attempts to write, C is given > the permission to write even though process B attempted to write first than > C? There's no queue. When process B attempts to write, it will be refused, and must try again later itself. It controls if/when it does so; SQLite doesn't force any particular behavior. If process C tries before B tries again, then C will get to write and B will have to wait again. In API terms, an sqlite function will return SQLITE_BUSY if it can't do what it needs to do right now. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] commit and rollback
ok..thanx for that..now if process A is writing into the database, and process B attempts to write, does sqlite take note of B's attempt and gives the permission to B when A is done? like would it be a queue that the first process that attempted to write should be given priority to write? or is it that when process A is done, then process C attempts to write, C is given the permission to write even though process B attempted to write first than C? Sreedhar.a wrote: > >>so there is really no way that multiple processes can write into the > database?..but multiple processes can read at the >>same time right?.. > > --Yes > > -Sreedhar > > > Igor Tandetnik wrote: >> >> arbalest06 <[EMAIL PROTECTED]> wrote: >>> q#1: is it possible that multiple users can write into the database >>> at the same time? >> >> No. >> >>> q#2: if users A, B, C are writing to the database at the same time, >> >> They can't. >> >>> q#3: if users A, B, C are writing to the database at the same time, >> >> They can't. >> >> Igor Tandetnik >> >> -- >> --- To unsubscribe, send email to >> [EMAIL PROTECTED] >> -- >> --- >> >> >> > > -- > View this message in context: > http://www.nabble.com/commit-and-rollback-tf4804976.html#a13991789 > Sent from the SQLite mailing list archive at Nabble.com. > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > -- View this message in context: http://www.nabble.com/commit-and-rollback-tf4804976.html#a13992544 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] commit and rollback
>so there is really no way that multiple processes can write into the database?..but multiple processes can read at the >>same time right?.. --Yes -Sreedhar Igor Tandetnik wrote: > > arbalest06 <[EMAIL PROTECTED]> wrote: >> q#1: is it possible that multiple users can write into the database >> at the same time? > > No. > >> q#2: if users A, B, C are writing to the database at the same time, > > They can't. > >> q#3: if users A, B, C are writing to the database at the same time, > > They can't. > > Igor Tandetnik > > -- > --- To unsubscribe, send email to > [EMAIL PROTECTED] > -- > --- > > > -- View this message in context: http://www.nabble.com/commit-and-rollback-tf4804976.html#a13991789 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] commit and rollback
so there is really no way that multiple processes can write into the database?..but multiple processes can read at the same time right?.. Igor Tandetnik wrote: > > arbalest06 <[EMAIL PROTECTED]> wrote: >> q#1: is it possible that multiple users can write into the database >> at the same time? > > No. > >> q#2: if users A, B, C are writing to the database at the same time, > > They can't. > >> q#3: if users A, B, C are writing to the database at the same time, > > They can't. > > Igor Tandetnik > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > -- View this message in context: http://www.nabble.com/commit-and-rollback-tf4804976.html#a13991789 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: commit and rollback
arbalest06 <[EMAIL PROTECTED]> wrote: q#1: is it possible that multiple users can write into the database at the same time? No. q#2: if users A, B, C are writing to the database at the same time, They can't. q#3: if users A, B, C are writing to the database at the same time, They can't. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] --enable-cross-thread-connections
There is no association of this flag and code in sqlite3.5.2, does this really have any impact while building sqlite? regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] commit and rollback
good day! thanx guys for helping me out..i got it working already..i just misunderstood your solutions, that's why it took me a while to get it right.. =^D now i have another question, still related to this topic, but more on theoretical.. q#1: is it possible that multiple users can write into the database at the same time? q#1.1: if yes, what can i do to make this possible (e.g. compiling the sqlite source code with a macro for this scenario )? q#2: if users A, B, C are writing to the database at the same time, then user A commits first, followed by B, does the changes imposed by user A is ignored and is overwritten by the changes imposed by user B? q#3: if users A, B, C are writing to the database at the same time, then if user A commits first, followed by B, and if user C rolls back, from what point does the database rolls back? explanations and C sample codes are very much appreciated.. thanx and God bless! :working: -- View this message in context: http://www.nabble.com/commit-and-rollback-tf4804976.html#a13991028 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Undefined collation: Peculiar observations ...
>> Imagine that a SQLite3 database opened in a custom application with a
>> registered a collation sequence named "unknown" has created the following
>> table:
>>
>> CREATE TABLE a (b COLLATE unknown);
>>
>> Now open this table in the default SQLite3 CLI. Up to here, everything works
>> as expected.
>>
>> Now some peculiar observations:
>
>> 2. Running simple queries like "SELECT * FROM a;" work fine. But subselects,
>> in their most basic form and with no sorting or comparisons, do not:
>>
>> sqlite> SELECT * FROM a, (SELECT * FROM a);
>
>That's not just a subselect, it's also a join. Does a subselect on
>its own have the same behavior?
Thanks all for the feedback.
Trevor, I am not sure what you mean by "subselect on its own". Is this what you
are looking for?
sqlite> INSERT INTO a VALUES ('one');
sqlite> SELECT * FROM (SELECT * FROM a);
SQL error: no such collation sequence: unknown
sqlite> SELECT *, * FROM a;
one|one
Ralf
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
