Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Will database file of SQLite3 be damaged when suddenly power-off or OS crash?

I open the database file and obtain a database connection using open() method of sqlite3 and the connection will not be closed until program exits. If there occurs an unexpected error such as computer's suddenly power-off or OS crash, will the mode of the database file be damaged, or its handle lost? More specifically, can it remain writable if I reboot my computer? BTW, I don't care about the data loss when errors occurs.

Thank you very much!

like image 200
quantity Avatar asked Sep 21 '09 03:09

quantity


People also ask

Can SQLite get corrupted?

An SQLite database can become corrupt if the file content changes due to a disk drive or flash memory failure. It is very rare, but disks will occasionally flip a bit in the middle of a sector.

How can I tell if SQLite database is corrupted?

To verify that you're truly suffering from database corruption, enter the following command into the shell: sqlite> PRAGMA integrity_check; If the response is anything other than ok, the database is integrity checks have failed and needs to be repaired.

Can SQLite work offline?

SQLite can be used in Cordova applications to store a large number of records, including images, on mobile devices. In a Cordova application, you can use SQLite to store a large amount of information that can be accessed offline on a device.


3 Answers

SQLite is specifically designed to protect against this. From the official SQLite is Transactional page:

All changes within a single transaction in SQLite either occur completely or not at all, even if the act of writing the change out to the disk is interrupted by

  • a program crash,
  • an operating system crash, or
  • a power failure.

The claim of the previous paragraph is extensively checked in the SQLite regression test suite using a special test harness that simulates the effects on a database file of operating system crashes and power failures.

You might also be interested in the SQLite article Atomic Commit in SQLite if you need to know the specific details on how they protect against crashes such as the above.


Regarding writing after a crash: (from File Locking and Concurrency)

A hot journal is created when a process is in the middle of a database update and a program or operating system crash or power failure prevents the update from completing. Hot journals are an exception condition. Hot journals exist to recover from crashes and power failures. If everything is working correctly (that is, if there are no crashes or power failures) you will never get a hot journal.

The worst that can happen will be that you need to delete the hot journal that is left over after a crash.

like image 132
Mark Rushakoff Avatar answered Oct 01 '22 23:10

Mark Rushakoff


As Sqlite is ACID-compliant, a power-off shouldn't be an issue.

http://en.wikipedia.org/wiki/ACID

like image 39
MartW Avatar answered Sep 29 '22 23:09

MartW


anything could potentially happen on sudden power off. However I'd suggest UPS to mitigate any risk.

like image 41
Preet Sangha Avatar answered Sep 29 '22 23:09

Preet Sangha