[resending, with small clarification edits, since I didn't see it,
probably because I wasn't yet subscribed, I hope it won't appear twice]
Hello,
I'm using sqlite 3.3.8 under linux (mandriva 2007.1).
Maybe it's something that's not advisable (I don't know) but in my
program I have various threads, each with the same database file opened.
Additionally I have a couple more programs (one guiless and the other
with a gui to view manipulate the database) that also open the same
database file.
The main program has to run unattended 24/7.
A while ago, the visualization program crashed, leaving the database in
an inconsistent state, hence the main program gave sqlite_corrupt problems.
In order to avoid those problems (and probably I did it wrong), each
time I open the database, I issue a "pragma integrity_check". In case it
fails or every time, with the database opened, I detect an
sqlite_corrupt, I try a vacuum (which I saw that fixed the problems if
invoked manually with the command line utility).
This provokes that the other open connections see the SQLITE_SCHEMA
error (and sometimes, stupidly, I try a vacuum again, that exacerbates
the problem, I'll change that to just retry).
Well, eventually one of the vacuum wiped the database completely clean.
This is what I see in the log of my program(s):
(the uppercase name after [INFO], [ERROR], [AVISO] identifies the thread
and/or the program, in this case "LISTA CARGA" is another program that
is spawned from time to time).
[INFO] LISTA CARGA: startup
[ERROR] LISTA CARGA: PRAGMA integrity_check devuelve error:
[ERROR] LISTA CARGA: rowid 17653 missing from index fifo_carroceria
[ERROR] LISTA CARGA: wrong # of entries in index fifo_carroceria
[ERROR] LISTA CARGA: wrong # of entries in index fifo_referencias
[ERROR] LISTA CARGA: trying a VACUUM
[INFO] LISTA CARGA: VACUUM ok
[INFO] LISTA CARGA: terminated
I would like to know why this happened (I don't see any error previous
to this one).
Then this provoked the following cascade of events:
[ERROR] DESCARGA: sqlite3_step: UPDATE bandejas SET
cantidad=cantidad-1 WHERE numero=4 AND posicion=1 SQLITE_SCHEMA -
database schema has changed
[ERROR] DESCARGA: Detectado fallo en la base de datos, intento un VACUUM
[INFO] DESCARGA: VACUUM ok
this is my fault, I suppose I shouldn't vacuum here, but simply retry,
I'll fix it.
[ERROR] COMPR.NIVEL CARGA: sqlite3_step: SELECT
modelos.referencia,count(*) FROM fifo_fis LEFT OUTER JOIN modelos ON
fifo_fis.modelo=modelos.modelo and fifo_fis.color=modelos.color GROUP BY
fifo_fis.modelo,fifo_fis.color SQLITE_SCHEMA - database schema has changed
[INFO] COMPR.NIVEL CARGA: reintenta 1
ok, the above succeeded by simply retrying.
[ERROR] COMPR.NIVEL CARGA: sqlite3_step: SELECT sum(cantidad) FROM
bandejas WHERE referencia='W06J3809905 S3H' SQLITE_SCHEMA - database
schema has changed
[INFO] COMPR.NIVEL CARGA: reintenta 1
same here
[ERROR] CARGA: sqlite3_step: SELECT ocupacion,cantidad_por_caja FROM
referencias WHERE referencia='W06L6809905G C9Z' SQLITE_SCHEMA - database
schema has changed
[INFO] CARGA: reintenta 1
and here
Then the real problem ensues:
[ERROR] COMUNICACION FIS: sqlite3_step: SELECT secuencia FROM fifo_fis
WHERE nr_carroceria='60920850' SQLITE_CORRUPT - database disk image is
malformed
[ERROR] COMUNICACION FIS: intento un VACUUM
[INFO] COMUNICACION FIS: VACUUM ok
[INFO] COMUNICACION FIS: reintenta 1
[ERROR] COMUNICACION FIS: sqlite3_prepare: SELECT secuencia FROM
fifo_fis WHERE nr_carroceria='60920850' SQLITE_ERROR - no such table:
fifo_fiS
at this point the database is clean: there are no tables.
Apart from the glaring errors on my part that I'll try to fix:
1) is sqlite suitable when you have multiple threads accessing the same
database? Or should I delegate the access in a single thread and
serialize the queries from the various threads?
2) is sqlite suitable when you access the database from multiple programs?
3) why did the first error (rowid missing, wrong # of entries) occur?
4) is "VACUUM" the best way to correct such problems?
5) what is the way to recover from "SQLITE_SCHEMA"? close and reopen the
database?
In case it is useful to spot what I did wrong, this is the procedure I
use to execute a query (it is pascal, but it should be readable):
function TSQLiteQuery.Execute(query: string): boolean;
var vm:pointer;
finished:boolean;
procedure AddRow;
var i:integer;
TempRow:TStringList;
begin
TempRow:=TStringList.create;
for i:=0 to sqlite3_column_count(vm)-1 do
TempRow.Add(StrPas(sqlite3_column_text(vm,i)));
FRows.Add(TempRow);
end;
begin
result:=false;
finished:=false;
FRows.Clear;
if FSQLiteHandle=nil then exit;
FSQLiteReturnId:=sqlite3_prepare(FSQLiteHandle,PChar(query),-1,@vm,nil);
if FSQLiteReturnId<>SQLITE_OK then
begin
Flog.Error('sqlite3_prepare: '+query+' '+SqliteReturnString);
exit;
end;
repeat
FSQLiteReturnId:=sqlite3_step(vm);
case FSQLiteReturnId of
SQLITE_DONE:
begin
result:=true;
finished:=true;
end;
SQLITE_ROW: AddRow;
else {SQLITE_ERROR or SQLITE_MISUSE}
begin
FSQLiteReturnId:=sqlite3_reset(vm);
FLog.error('sqlite3_step: '+query+' '+SqliteReturnString);
finished:=true;
If FSQLiteReturnId=SQLITE_CORRUPT then
begin
FLog.Error('intento un VACUUM');
if Vacuum then FLog.Info('VACUUM ok');
end;
end;
end;
until finished;
sqlite3_finalize(vm);
end;
TIA
Bye
--
Luca Olivetti
Wetron Automatización S.A. http://www.wetron.es/
Tel. +34 93 5883004 Fax +34 93 5883007
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
