[sqlite] malformed database schema
Dear group,
We encountered a somewhat obscure scenario that leaves an sqlite database in a
corrupted state (tested with sqlite 3.8.8).
Our case deals with
- an in-memory database "db" with an attached file database "other.db".
- "db" starts a transaction,
- "db" tries to create a table in the attached "other.db" using "create table
... as select ...", but the "select" raises an error.
- "db" commits the transaction.
As a result, the attached database is corrupted. The corruption seems to take
place during the final commit.
The following Tcl script reproduces the problem:
package require sqlite3
sqlite3 db :memory:
catch {file delete other.db}
sqlite3 odb other.db
odb eval {create table dummy(whatever text);}
puts "check that other.db is ok:[odb eval {select count(*) from dummy}]"
rename odb ""
db function myerror myerror
proc myerror args {error "deliberate error"}
db eval {attach [other.db] as other}
catch {
db transaction {
catch {
db eval {create table other.xxx as select myerror()}
}
# error "--- without this error the other.db gets corrupted ---"
}
}
sqlite3 odb2 other.db
puts "check that other.db is ok:[odb2 eval {select count(*) from dummy}]"
This is the output:
check that other.db is ok:0
malformed database schema (?)
while executing
"odb2 eval {select count(*) from dummy}"
invoked from within
"puts "check that other.db is ok:[odb2 eval {select count(*) from dummy}]""
(file "bugreport.tcl" line 26)
If the commented line is activated the other.db is not corrupted.
Best Regards,
Christian
Re: [sqlite] Mozilla Firefox 3.0.7 using SQLITE 3.5.9 crash on AIX
Shailendra Jain wrote: > While running Mozilla Firefox 3 on AIX platform, the application crashed > with below stacktrace information. There were some problems on AIX when you compiled sqlite with IBM's native xlc compiler. See for example ticket 3344 http://www.sqlite.org/cvstrac/tktview?tn=3344 Possible solutions might be - upgrade to a more recent sqlite version (at least 3.6.2) - compile with xlc but without optimisation - compile with gcc HTH, Christian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tcl - timeout method
Ulrich Schöbel wrote: > I thought about that, but I didn't want to include > the selects into the locked phase, keeping lock > times as short as possible. Shouldn't it work > correctly with a deferred lock? I don't think so: imagine two processes that have succesfully carried out their selects (possible, since you've only got a shared lock at that point) and now want to proceed to the update: SELECT(1) SELECT(2) <-- process 1 here<-- process 2 here UPDATE(1) UPDATE(2) At this point SQLite cannot allow UPDATE(1) because it might potentially invalidate the result of SELECT(2) (and vice versa). So there's no sane way through and at least one transaction is forced to error out. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Tcl - timeout method
I would guess that your requests acquire (shared) read locks when the
transaction starts and then fail/deadlock when they try to upgrade this
to a write lock in your insert/update statement. Maybe it would help to
start the transactions with "BEGIN IMMEDIATE" or "BEGIN EXCLUSIVE"...?
Ulrich Schöbel wrote:
> Hi all,
>
> I'm using sqlite3 inside a Tcl module (mod_websh) of
> apache. Everything works fine, except when it comes
> to concurrent write accesses.
>
> There's a small transaction where I update two rows.
> When I stress the web server it generates a
> "database locked" error. I invoke a "db timeout 2000"
> just before the transaction, but it doesn't seem to
> have any effect.
>
> When I stumbled over this problem I increased the
> timeout to 50, which is much longer than the
> stress test takes, but the error persists. What's
> going wrong here?
>
> Here's a short code snippet:
>
> db timeout 50
> db transaction {
> set last_acc [lindex [db eval {
> select acc_time from last_hit where site = $site and from_ip = $ip
> }] 0]
> if {![string length $last_acc]} {
> db eval {
> insert into last_hit values ($site , $ip , $now)
> }
> set last_acc 0
> } else {
> db eval {
> update last_hit set acc_time = $now where site = $site and from_ip =
> $ip }
> }
> ..
> } ;# end of transaction
>
> There's another update inside this transaction, but
> it's always the one above that fails.
>
> Maybe the "busy" method is better suited here, but
> the docs say nothing about its usage.
>
> Thanks for any help
>
> Ulrich
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Syntax Errors with various strings?
Have you tried using the quote() function? >From http://www.sqlite.org/lang_expr.html quote(X)This routine returns a string which is the value of its argument suitable for inclusion into another SQL statement. Strings are surrounded by single-quotes with escapes on interior quotes as needed. BLOBs are encoded as hexadecimal literals. The current implementation of VACUUM uses this function. The function is also useful when writing triggers to implement undo/redo functionality. Gussimulator wrote: > Hi, When I have strings with "-", "!", "\" or similar characters I get a > syntax error.. Now, I thought this would happen with a few of this chars so I > made 2 routines in my program, one that converts each of this chars into a > flag string, which then, by the other routine can be reverted to the original > characters to obtain the string in its original form, so I can later work > with it by my side. > > Now, I've found myself with syntax errors even on strings that didnt had any > strange characters, So.. I'm wondering, what can I do to prevent this? Does > SQLite provide a "format" routine or is there anything I can do to prevent > the syntax errors... like this ones? (since my data wont get into the db if > theres an error, of course, thats why Im concerned). > > I'm really worried about this, hence I subscribed on the list (first > message!). I hope someone can help me out on this one, thanks. > > And, Indeed.. I'm quite a newbie on SQL but, I never thought I'd find myself > with this type of problem. > > -- --- Christian Nassau Software Developer --- Swissrisk Holzhausenstrasse 44, 60322 Frankfurt, Germany tele: +49 69 50952-266 fax: +49 69 50952-299 www.swissrisk.com ---
[sqlite] Re: How to realize the ROWID in a view?
I think you could use min(rowids) as your new rowid like this:
sqlite> create view v as select x,min(id) as id from foo group by x;
sqlite> select * from v;
x id
X 1
Y 2
Z 4
Does this do what you want?
PY wrote:
> Hi All,
>
> I have a problem about the ROWID in a view. I want to simulate a ROWID
in a
> view just like the same purpose in a table.
>
> For Example:
>
> Create Table foo(id INTEGER PRIMARY KEY AUTOINCREMENT, x TEXT);
> insert into foo(x) values('X');
> insert into foo(x) values('Y');
> insert into foo(x) values('X');
> insert into foo(x) values('Z');
>
> Create View v_foo AS
> SELECT distinct(x) FROM foo
> ORDER BY x desc;
>
>
> SELECT * from foo;
>
> id x
> -- --
> 1 X
> 2 Y
> 3 X
> 4 Z
>
>
> My expect result of "select * from v_foo;" is
>
> id x
> -- --
> 1 Z
> 2 Y
> 3 X
>
>
>
> Would you please help to tell me how to finish that?
> Thanks for your grest help.
>
>
>
>
> Thanks,
> VK
>
