On 4/19/2015 2:02 PM, Richard Hipp wrote: > On 4/19/15, Joe Pasquariello <joep at fenway.com> wrote: >> Some users of my application have their servers in remote locations that >> lose power. They have UPS backup, but the battery runs down and the >> computers will suddenly stop. When power returns and the application >> starts, the SQLITE database is sometimes corrupt. The vast majority of >> writes to the database are to a table that could be TEMPORARY, but is >> currently not. If that table was created as TEMPORARY, and it was in >> MEMORY, as opposed to FILE, would the likelihood of corruption on >> restart be reduced? > Yes. Corruption is only possible if power is lost in the middle of a > COMMIT and either you have PRAGMA synchronous=off, or your OS/hardware > combination is not doing real fsync()s when requested. So if you > dramatically reduce the number of COMMITs you also reduce the number > of windows of vulnerability. > >> If not, is there another change (schema or >> otherwise) that would help? >> > Using WAL mode will help, if that is possible. Your phrase "remote > servers" is not exactly clear to me? Are they using a network > filesystem?
Richard, thanks for your reply. By "remote servers" I mean the servers are physically remote and the users access them via a remote desktop solution, such as VNC. They application is running under Windows 2003 Server, and the sqlite database file is on the local disk. They are not using a network file system. The application is reading/writing the sqlite database via the FireDAC components of C++ Builder (formerly of Borland, and now Embarcadero). They seem to have synchronous=0 (off) by default. I read the SQLite documentation on synchronous. Since power loss occurs relatively frequently, do you recommend synchronous=1 (Normal) or 2 (Full)? I'm not sure whether you're saying that the use of TEMPORARY does or does not reduce the likelihood of corruption. Can a power failure during a COMMIT to a TEMPORARY table in memory, with synchronous=0, result in corruption? Just curious, but since the data is in memory, what is it that is corrupt after the restart? Joe
