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(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? 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
