SQlite save database at powerfailure
I am using Qt5.1 and SQlite. This is the configuration of my Database:
db = QSqlDatabase::addDatabase("QSQLITE");
query.exec("PRAGMA page_size = 4096;");
query.exec("PRAGMA cache_size = 16384;");
query.exec("PRAGMA temp_store = MEMORY;");
query.exec("PRAGMA journal_mode = PERSIST;");
query.exec("PRAGMA locking_mode = EXCLUSIVE;");
query.exec("PRAGMA synchronous = OFF;");
everything works fine. I can write and read from the Database. But here's the problem, when I unplug the power and the System turns off hard sometimes the database is damaged. I write very often into the database so the possibility that there is an aktive writing command at the powerfailure is high. Im working on an windows embedet statdart 7 system. Is there any possibility to save the database from damage?
Tank you for your help
AFAIK you can do nothing in case of an unplanned power outage. But when anything is being written into a database a record of the original unchanged database content is added to rollback journal. So when the system is back online it searches for any journal files and restores the database as per those files. See "RollBack Journal":http://sqlite.org/lockingv3.html and "http://www.sqlite.org/howtocorrupt.html":http://www.sqlite.org/howtocorrupt.html for more details
there is a journal file, but my software dosn't restore the database. Do i have to start a rollback by myself, or is this made automatically?
From the SQLite docs "If the journal files are moved, deleted, or renamed after a crash or power failure, then automatic recovery will not work and the database may go corrupt.". What is the name of your journal file ? Also is there any particular reason you have "PRAGMA synchronous = OFF;" when executing query ?
the journal file is there:
I set PRAGMA synchronous = OFF because when I set it to NORMAL the database is way to slow for my application.
If you really have that much power outage happening to your system or if you want it to be resistant to these, you should also have some sort of UPS that would allow it to shutdown gracefully. A standard system might recover from the occasional unplanned power off, but if it's happening regularly you might damage the filesystem and not only your database file.
The Rollback happens automatically as soon as the DB is opened. But it will fail if the process can't lock the database. Is any other program accessing the database ? Check if the database is locked.