[sqlite] database is locked and is malformed ANSWER
This is an answer to the post of Dr. Hipp to my question. I tried several times to send the mail as Re: [sqlite] database is locked and is malformed but it did not work. So I have to create a new thread. Hello Dr. Hipp, thank you for your response. First off all I want to apologize for my entry in the ticket on sqlite.org, feel free to delete it. I will also look for an other possibility to format my emails, we use a Lotus Notes Client in the company and I don`t have an influence on footers, perhaps I find something about the sending format. Should I add some html-tags for line feed? We don`t use the PRAGMA synchronous=off anymore. I read the information about the atomic commit behaviour when I started programing 2 or 3 years ago and now I had problems, in studied it in detail again. As far as I can judge it, we nearly fit all assumtions, but 1. We use a SiliconDrive SSD-D04G-3500 Flash Drive as Harddisk 2. We had problems in the past that fsync() was very slow (that`s why we had to use the pragma command) The compilation of sqlite3 is done by our qnx expert, but I can get all settings and arrange changes very fast. When I logged on to the corrupted machine there was the normal data.s3db with around 200k and a data.s3db-journal file. I tried to use the database, but only got errors. Than I tried to .dump it, but this also did not work. Than I, perhaps unfortunately, deleted the journal file and dumped again, with the result, that the new data.s3db was only 80k big and the most important table was completely empty. Afterwards I told the customer to restore a backup which is one week old (absolutely healthy). I still could get the corrupted database, but after all I read it want help without the journal file. I did that all under extreme time pressure, the machine was standing the whole sunday and the customer was very angry. So I did`t try something with the journal file, I can´t say something about the size and the readability. Next time I know better. To the situation on the machine: I tried to do most ot the inserts to the database by a single thread. All other programs send message to that database manager and it makes the inserts. That is true for about 95% off all inserts. Now, after thinking two days about the crash and reading your advices, I believe that the crash had nothing to do with concurrent inserts. On power off I may get many errors from devices of the machine (most devices loose power earlier than the controll unit). So the possibility of writing exact at the time when the controll unit losses power is very high. What I`m going to do next: 1. I will try to make the behaviour in case off power loss saver. There must be a way to check the situation before starting an insert. 2. I will try to make all Inserts that belong together as one atomic commit. Is it enought to write serveral Inserts in one sqlite3_exec() call or must I do such a procedure with manual steps ? 3. I will try the PRAGMA journal_mode=PERSIST option to increase the speed of inserts 4. I will test the sqlite3_busy_timeout() command as I`m not happy with my current solution 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
[sqlite] database is locked or is malformed
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(2 +
(rand()%1));//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: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
[sqlite] database is locked and is malformed
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(2 +
(rand()%1));//several threads wait different timescounter++;
}
}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
Re: [sqlite] database is locked and is malformed
On May 13, 2009, at 4:09 AM, s.breith...@staubli.com wrote:
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).
To be clear: Do you, or do you not use PRAGMA synchronous=OFF?
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.
Have you read http://www.sqlite.org/atomiccommit.html to understand
the assumptions SQLite makes about the hardware during a power cycle?
Are these assumption met in your hardware?
Does your device use flash memory? We are told that some flash memory
devices, when powered off in the middle of write operation, will
randomly corrupt sectors - sectors that were completely unrelated to
the sectors actually being written. If you have such a situation,
database corruption might occur on a power loss even if the database
files were completely unused at the time of the power cycle.
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(2 +
(rand()%1));//several threads wait different timescounter++;
}
}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?
Using sqlite3_busy_timeout() would probably be easier.
Should every thread have an own sqlite* connetion?
Not necessarily.
Should a program hold the sqlite* connection opened or should it close
after every insert and open again?
Keep the connection open.
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.
Is the journal file readable? Is it empty?
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?
ATTACHing in a different order should work fine.
Please give me some advice.
D. Richard Hipp
d...@hwaci.com
