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 dont 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* handle for the database.
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 a 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?
Please give me some advice. Stefan
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:[email protected]
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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
