We use QNX 6.3 on a ppc with sqlite as database for our application. As
there was a speed problem in older sqlite versions we used the PRAGMA
SYNCHRONOUS = OFF command before creating an INSERT and the PRAGMA
SYNCHRONOUS = ON afterwards. We used version 3.4.2 for a long time, 2
month ago we changed to version 3.6.11 (now without pragma options).
On our machines there are several programs that access the databases (we
use 4 dbs) and also the programs have all several threads. This worked for
over a year now on 10 machines. I only saw two times a database that was
corrupted. Always messages like this: On page 6928 at right child: 2nd
reference to page 7003 Page 3805 is never used. But that never caused a
totally corruption of the database. I added a program to run the
integrity_check and performing a ".dump |" if a problem was found.
Today I had the first total crash. No program could start at all. All
programs (written in c) got database locked (11) and ended. I was lucky to
have a telnet connection to the customer, so I run an integrity_check and
got a long list with errors. I tried to .dump | but the most important
table was so much damaged, that it was empty after the .dump. Also there
was the data.s3db-journal file, but no connection to the database was
opened.
I don`t have an idea how to find the reason for the problem. The customer
had problems with the machine, so he switched off/on very often the last
days. But I thought sqlite should be save enough to handle power fails. I
need to find a solution where quickly, if I have the same problem at a
customer without internet connection I will have a big problem.
I think my greatest risk of database corruptions are the inserts. As many
programs may access the database, it is often locked. So I wrote a
function that handles every insert to the database, it tries several time
to access it. Perhaps it is also wrong, that different threads inside a
program use the same sqlite3* connection for the database. (But it`s OK
according to sqlite.org, files are compiled SQLITE_THREADSAFE=1 )
int sqlite3_exec_save(sqlite3* database, const char *command, int
(*callback)(void *ag1,int arg2,char **arg3,char **arg4), void *arg1 , char
**aErrmsg, const char *errString, int printMessage ){
int counter = 0;
int returnCode;
char help_str[500];
char *errmsg = 0;
srand (pthread_self());
//try up to 300 times in up to 9 sec
do{
returnCode = sqlite3_exec(database,
command, NULL, NULL, &errmsg);
if((returnCode == SQLITE_BUSY) || (errmsg
!= NULL && returnCode != SQLITE_ERROR)){
usleep(20000 +
(rand()%10000));//several threads wait different times counter++;
}
}while ((returnCode == SQLITE_BUSY && counter < 300) ||
(errmsg != NULL && counter < 300 && returnCode != SQLITE_ERROR)); //only
repeat non SQL-Errors
if (errmsg != NULL ){
sprintf(help_str,"%s (%i:%s) [attempt
%i]:", errString, returnCode, errmsg, counter);
eprintf("could not insert to database");
logPoint(help_str);
logPoint(command);
}else if( counter > 5){
eprintf("needed %i attempts to insert to
database", counter);
}
//if somebody wants to use the error messagae outside the
function
if (aErrmsg != NULL && errmsg != NULL){
*aErrmsg = malloc(strlen(errmsg)+1);
strcpy(*aErrmsg,errmsg);
}
return returnCode;
}
Is it a good way to use an insert function like that?
Should every thread have an own sqlite* connetion?
Should a program hold the sqlite* connection opened or should it close
after every insert and open again?
I read much about the journal handling of sqlite. As I use 4 databases I
should get a Master Journal File if I update more then one database at a
time. But I´m not doing that, so there should only be a "normal" -journal
file for the database to be changed. When I looked on the corrupted
database, there still was a -journal file, I thried to open the database,
but got an error with every SELECT. On sqlite.org is written, that the
database should repair itself on next open, but that seems not to work
every time. Now me question to this section: Could that be a problem with
ATTACH. I didn`t care until now if all databases are attached in every
program or the order of attaching. Could the scenario "Open 3 dbs with
attach, power failure while writing, after start open 4 dbs in other
order" cause my problems?
Please give me some advice.
Best regards / Mit freundlichen Grüssen
Stefan Breitholz
-----------------------------------------------------------------------------------------------------------
Staeubli GmbH - Theodor-Schmidt-Str. 19
DE - 95448 Bayreuth
Phone: +49(0)921/883-126 Fax: +49(0)921/883-58126
mailto:s.breith...@staubli.com
http://www.staubli.com
Registered under HRB 175 - Local Court Bayreuth - Managing Director: Karl
Kirschner
-----------------------------------------------------------------------------------------------------------
This e-mail and any attachment (the 'message') are confidential and privileged
and intended solely for the person or the entity to which it is adressed. If
you have received it in error, please advise the sender by return e-mail and
delete it immediately. Any use not in accordance with its purpose, any
dissemination or reproduction, either whole or partial, by entities other than
the intended recipient is strictly prohibited.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
