5 replies to this topic

[rhossis]

Hi,

I have a really critical problem and would appreciate any assistance regarding corrupt Sqlite3 database file. Recently, power went off abruptly on a PC running GnuCash accounting software (Version 2.3 for Windows). The database back-end configuration is for Sqlite3. I managed to recover the sqlite3 database file and I can see all the records on the table named entries, which stores accounting entries, when I open the file in a binary text editor like notepad. However, when opened with GnuCash or an SQLite database editor, the table entries is only created but not populated. I have not used SQLite before and would like to know whether such a file can be fixed. I have run the "PRAGMA integrity_check" command, which reveals no errors, but I am unable to access this data in a meaningful format. This really should be an issue for GnuCash forum but I also read sqlite3 databases could have issues in sudden power losses so I decided to pose it to the db community :thumbup:

[fayyazlodhi]

Can you paste the exact sqlite3 commands you are using to query the database?

Sqlite3 editor is a little unfriendly for some one who hasn't used it before and is accustomed to GUI based other commercial databases.

So i needed to ensure you are doing it right. Probably i would be able to suggest some more commands like querying your mastertable, schema etc.

[rhossis]

Hi fayyazlodhi

I don't have understanding of Sqlite3 for sure :) I have been using the Firefox Sqlite manager add on. When I browse the table in the sqlite manager it has 0 records and thus cannot be queried for selecting data, and reports no errors, but when I opened it in notepad I can see the sqlite file littered with what look like the ledger entries, entries, such as below.

))9fc311abb9c086f25847d01a1890c660749fe3b0cfe1b03b51af3390e67c2f5c42010010821000020101125102639Unaccounted withdrawaly^MM

))96c14550ba35c7be690cc629101aaa90449fe3b0cfe1b03b51af3390e67c2f5c42010010921000020101125102722Unaccounted withdrawalr_MM

))+a6fc1cc61909fcc39cd8d089e65ff7f949fe3b0cfe1b03b51af3390e67c2f5c42010011021000020101125102945Lunch Allowance`MM

))i9333735670a69288cdf32f4b524bd88a49fe3b0cfe1b03b51af3390e67c2f5c42010011221000020101125103045Stationery, Book Purchase, Think and Grow RichoaMM

))%e552701d64b50c81c42854643f24a6ab49fe3b0cfe1b03b51af3390e67c2f5c42010011321000020101125103222Browsing FeebMM

))Ee8b8c0bd4f98d7598ee8dda9f5cbdde149fe3b0cfe1b03b51af3390e67c2f5c42010011221000020101125103336Internet Security, KarpenskyscMM

))-8960daf3f69685c69ae291dccdf5db0149fe3b0cfe1b03b51af3390e67c2f5c42010011421000020101125103707Repairs on PhoneydMM

))937e0deb70a2a98bd5af9ca2dbbe351b649fe3b0cfe1b03b51af3390e67c2f5c42010011421000020101129113241Unaccounted withdrawal

. I am in a real quandary with this one :confused:

[fayyazlodhi]

Apologies for the delay - To be able to attempt whether your file is recoverable or not, i suggest you try sqlite command line editor

Command Line Shell For SQLite

It can be downloaded from the above site (download amalgamation which contains code along with a command line exe named 'sqlite3') as well as the very link pasted, gives summary of commands that you can execute on a file containing a database. For e.g. things like querying the master table in file, as well as the db itself.

I believe it might also have some db repair option, though i am not certain about it, but hopefully it can be looked up.

Hope it helps. Else you might just be able to send the file across if it is not too large and i will try it out on my setup.

[rhossis]

Hi fayyazlodhi. I am really sorry for the late reply. I will download the command line editor. I am new to this so you can expect some questions. Thanks :)

[fayyazlodhi]

Sure please put those when you try out - I would be happy to help with what i can