Hi everybody,
I just subscribed and this is my first post - I hope I am not making
any deadly mistake by posting about a problem I have with SQLite. As
the title suggests, I have some trouble with SQL statements randomly
not executing. Here are the details.
First, the machine. It is running a fully updated Arch Linux system, with:
$uname -a -> Linux iridia-pc20 2.6.30-ARCH #1 SMP PREEMPT Sat Jul 4
02:24:43 CEST 2009 x86_64 Pentium(R) Dual-Core CPU E5200 @ 2.50GHz
GenuineIntel GNU/Linux
$sqlite3 --version -> gives 3.6.15
$gcc --version -> gcc (GCC) 4.4.0 20090630 (prerelease)
Copyright (C) 2009 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
$ruby --version -> ruby 1.8.7 (2009-06-12 patchlevel 174) [x86_64-linux]
$gem --version -> 1.3.3
$gem list --local -> sqlite3-ruby (1.2.4)
As Arch Linux is essentially a binary distribution, I do not know of
the compilation flags, though I would gladly post them should anyone
tell me how I can get to know them.
I am trying to implement some kind of graph algorithm using the
database, because almost all the computations involved are easy and
natural to describe as SQL statements. I first implemented the
algorithm in Ruby, using the sqlite3 gem, and it works fine, but is a
bit slow. Furthermore, once I will be confident I have rooted out all
the bugs, I intend to run this algorithm in parallel on multiple
processors. I have thus tried to translate the algorithm to C (for
speed and OS-level multithreading, though the version of the program
I'm speaking about here is a direct translation of the ruby code, with
no threading added yet), which seemed quite easy, but now yields this
weird result. I have narrowed the problem to the few following lines :
#define DEBUG
int db_execute(sqlite3 *db, char stmt[STR_LEN]) {
DEBUG PRINT("\nentering db_execute with statement %s.", stmt);
int ret=0;
sqlite3_stmt *s;
int err = sqlite3_prepare_v2(db, stmt, -1,&s,0);
DEBUG if(err != SQLITE_OK) {
DEBUG PRINT("\nSQLite error %d.\n", err);
DEBUG PRINT(stmt);
DEBUG exit(err);
DEBUG }
if(sqlite3_step(s) == SQLITE_ROW)
ret = sqlite3_column_int(s,0);
sqlite3_finalize(s);
DEBUG PRINT("\nleaving db_execute with value %d\n",ret);
DEBUG system("sqlite3 tmpc/db.sqlite3 \"SELECT * FROM slinks;\" >> temp");
return ret;
}
void remove_link(sqlite3 *db, int current) {
DEBUG PRINT("\nentering remove_link");
db_execute(db, "DELETE FROM slinks WHERE subgraph = -2;");
[...]
}
And, in the file "temp", what I get at some point of the execution is
entering remove_link
entering db_execute with statement DELETE FROM slinks WHERE subgraph = -2;.
leaving db_execute with value 0
4146406|3032044|-2|0|14688
4169484|3032044|-1|0|15741
4281995|3032044|-1|0|21582
4503874|3032044|-1|0|32113
4522221|3032044|-1|0|32975
4558479|3032044|-1|0|34754
4589986|3032044|-1|0|36247
4626358|3032044|-1|0|37945
4749478|3032044|-1|0|43661
4776954|3032044|-1|0|44842
4778599|3032044|-1|0|44923
4839063|3032044|-1|0|47672
4950393|3032044|-1|0|52562
6274047|3032044|-1|0|94705
6406621|3032044|-1|0|97782
where the third column is subgraph. Of course, the next steps rely on
the fact that the DELETE statement should have deleted the first row.
Here is the schema of this table :
CREATE TABLE slinks (linking INTEGER, linked INTEGER, subgraph
INTEGER, marked INTEGER, subid INTEGER PRIMARY KEY);
Now, this code is called a lot of times in the course of the
execution, and it usually works. It seems to randomly crash, which is
quite puzzling to me as this application is not multi-threaded and
does not use any manual memory management (no mallocs and no frees;
this means as far as I can tell that it should not be a memory
allocation problem nor a race conditions one). I really can't see
where this randomness can come from.
It is not always this DELETE statement which seems to be skipped. On
another run of the program, I got the following output :
entering db_execute with statement UPDATE slinks SET subgraph = -1
WHERE (linked = 3029162 OR linking = 3029162) AND subgraph = 83;.
leaving db_execute with value 0
3859144|3029162|-2|0|69
4011107|3029162|83|0|7895
4268582|3029162|83|0|20898
4402764|3029162|83|0|27330
4934254|3029162|83|0|51846
which is as clearly bogus as the first example, as all these "83"
should have been replaced by "-1". I also get the same kind of
problems on another machine, where:
$uname -a -> Darwin gary-verhaegens-macbook.local 9.7.0 Darwin Kernel
Version 9.7.0: Tue Mar 31 22:52:17 PDT 2009;
root:xnu-1228.12.14~1/RELEASE_I386 i386
$sqlite3 --version -> 3.6.14.1
$gcc --version -> i686-apple-darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc.
build 5490)
Copyright (C) 2005 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
Based on previous results, I expect the computation to take
approximately two hours. The crash really seem random to me : I am
always using the same code on the same data, and it somehow sometimes
crashes after 30 seconds, sometimes after half an hour, and sometimes
not at all during the whole computation. I ran the Ruby version
multiple times to completion, without ever encountering that kind of
bug, which would lead me to think that the problem is in my
application, not in SQLite itself.
So, am I doing something wrong with my db_execute wrapper ? Does
anyone know of such a bug as a statement that does not get executed
while the returned error code is SQLITE_OK ? Should I step more than
one time through a DELETE or an UPDATE statement ?
Thanks in advance to anyone who took the time to read this, and my
apologies for my bad grammar - I am not a native English speaker.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
