How do I fix a corrupt SQLite database?

In the event Monetra will not start because it reports the database integrity check has failed, you are going to need to perform a manual dump and reload of the sqlite database. Unfortunately, this is not a guaranteed fix, but will work in all but the most severe instances of database corruption.

Due to the atomic commit nature of SQLite, you should never experience database corruption. If you do, it could be due to enabling features such as Write Caching within your OS or in the hard drive itself, or could possibly even be caused by hardware failure. SQLite maintains a journal file to rollback commits and return the database to a consistent state in the event of a power failure or other abrupt shutdown. If corruption occurs, it means that the OS responded to SQLite stating a write operation had completed when in actuality, it wasn't physically committed to the media yet. Please ensure Write Caching is disabled on all drives (On windows, it is enabled by default, disable it), and if utilizing a RAID controller that Write Caching is only enabled if a BBU is present. For more information, please see the SQLite Atomic Commit reference.

To begin the recovery process, please ensure you have installed the sqlite3 command line utility. If you are on MacOSX or Linux, this utility is probably already installed, if not, you should obtain a pre-compiled package from your OS distributor, or see if www.sqlite.org provides a pre-compiled version for your OS, which at the time of this writing includes Windows, MacOSX, and Linux.

Windows Note: For Windows users it is recommended to simply extract the obtained sqlite Windows binary zip file into your Monetra data folder, which is by default:

C:\Program Files\Main Street Softworks\Monetra\data\

There should only be one file extracted, called sqlite3.exe.

General Note: As of Monetra v7.5, Monetra ships linked with SQLite 3.7 which enables WAL mode. Please ensure your SQLite3 executable is at least version 3.7 or this procedure will not work.

The first thing you need to do after ensuring you have the sqlite3 command line utility is locate your Monetra data folder. Assuming you used the default installation paths, on Windows, that should be:

C:\Program Files\Main Street Softworks\Monetra\data\

On MacOSX, Linux, and other Unix-like systems, that should be:

/usr/local/monetra/data/

In the event that you have installed into a different path, you will be responsible for determining the correct database directory and adjusting appropriately in the procedure to follow.

Once you have located your Monetra data folder, you should only see a single file: monetra.sqlite. In certain error conditions, you may see additional files with a similar naming convention such as:

monetra.sqlite
monetra.sqlite-wal
monetra.sqlite-shm
monetra.sqlite.journal

Note: If the .journal, -wal, or -shm files exist, please ensure Monetra is stopped before continuing, check the process list. Those files should be automatically cleaned up when Monetra is shut down. You should attempt to start Monetra and stop it again and see if these files disappear prior to continuing.

The default database name is monetra.sqlite but it could be different. If so, please replace all references in the below procedure as appropriate.

The below steps will create a backup directory which will hold your original database files which can be restored in the event of a failure of these steps.

Windows Users: Go to Start->Run, type cmd and press the ENTER key, then when the DOS Command Prompt window opens, type the commands below into the Window. Please remember to adjust these commands if you installed to a different path or your database name is different. If a failure of any one of these commands occurs, stop, re-evaluate, then contact support if you cannot determine the cause of the failure.

  1. C:
  2. cd "C:\Program Files\Main Street Softworks\Monetra\data\"
  3. mkdir backup
  4. move monetra.sqlite* .\backup
  5. sqlite3 .\backup\monetra.sqlite .dump | sqlite3 monetra.sqlite

MacOSX/Linux/Unix Users: Open a console and make sure you are the root user. You may need to type sudo -i to become the root user. If you are unfamiliar with opening a root console, then run the commands below. Please remember to adjust these commands if you installed to a different path or your database name is different. If a failure of any one of these commands occurs, stop, re-evaluate, then contact support if you cannot determine the cause of the failure.

  1. cd /usr/local/monetra/data
  2. mkdir backup
  3. mv monetra.sqlite* ./backup
  4. sqlite3 ./backup/monetra.sqlite .dump | sqlite3 monetra.sqlite
  5. chown monetra:monetra monetra.sqlite*
  6. chmod 660 monetra.sqlite*

Once those steps have been followed successfully, you should then be able to successfully start Monetra and process transactions. It is recommended to pull some reports to ensure data appears to be consistent with what you expect. If not, make sure you do not remove the backup directory you created during this process. In the event of a failure, the files in the backup directory should be moved back into their original location.