Ticket #222 (closed defect: fixed)
Failed statements prevent me from closing database
|Reported by:||Richard Cobbe <cobbe@…>||Owned by:||jaymccarthy|
DrScheme? version version 22.214.171.124-svn4nov2009 [3m].
If a database statement fails to complete for any of various reasons, it can leave the database in a state where it's difficult to close the database.
To reproduce, create a SQLite database with the following schema:
CREATE TABLE table_1 (id integer primary key, name text not null);
CREATE TABLE table_2 (id integer not null references table_1 (id), age integer not null);
CREATE TRIGGER trigger_1 before insert on table_2 when
not exists (select 1 from table_1 where id = new.id)
select raise(abort, 'constraint failed');
Save it where you like; I put it in /tmp/database.sqlite3
Start DrScheme?. At the REPL, try the following:
> (define db (open (string->path "/tmp/database.sqlite")))
> (insert db "insert into table_2 values (1, 42)")
. . SQLite Error: Abort due to contraint violation
The error is perfectly correct; this is a constraint violation. But now my first attempt to close the database fails:
> (close db)
. . SQLite Error: The database file is locked
I can close the database on the second attempt.
I've seen the paragraph in the docs that suggest that this last error message is due to some unfinalized sql statements hanging around. But I'm not using prepared statements directly; the "insert" call created the prepared statement, and I don't have access to the prepared statement in order to finalize it.