Easy solution to malformed SQLite databases

After many years of using my SQLite app some users are reporting that they can no longer open the database.
They get a message:
“database disk image is malformed, contact support”
There are now numerous messages on blogs and support forums with the same problem eg.
https://techblog.dorogin.com/sqliteexception-database-disk-image-is-malformed-77e59d547c50

It appears the only solution is to move all the data to a new database.
Has anyone else experienced this?

Did you try what is written in comment #2:

cd /home/user/web/site.com/public_html/database.db
cp database.db database.db_bak
sqlite3 database.db “.dump” > dump.sql
rm -f database.db
sqlite3 -init dump.sql database.db
.quit
chown -R user:user /home/user/web/site.com/public_html/database.db

Which version of SQLite is your app using?

are you using sqlite in a multi-user environment ?

That would be my guess. SQLite is hard to corrupt when used properly.

Though another thing to check is the journal mode. If an update to your app decided to disable journaling in favor of performance, that can cause corruption when things go wrong. In nearly every scenario, setting pragma journal_mode = wal is a smart move. You’ll have a very hard time corrupting a SQLite database with WAL enabled and used by one process at a time.

That’s my guess.

Here are some posts of my experience.
https://forum.xojo.com/37126-sqlite-error-database-disk-image-is-malformed/0
https://forum.xojo.com/37312-valentina-sqlite-server-bench-tests
https://forum.xojo.com/37330-converting-to-postgresql-from-sqlite/0

Single user, desktop program.
Journal Mode not disabled.

Received this message from SQLiteDoctor:
Database is severely corrupted SQLiteDoctor is not able to retrieve tables.
This is probably due to failed write operation that resulted in a database header corruption. There is no way to recover this problem and application must quit

Is SQLiteDoctor strong enough ?

[quote=412449:@Craig Grech]After many years of using my SQLite app some users are reporting that they can no longer open the database.
They get a message:
“database disk image is malformed, contact support”
There are now numerous messages on blogs and support forums with the same problem eg.
https://techblog.dorogin.com/sqliteexception-database-disk-image-is-malformed-77e59d547c50

It appears the only solution is to move all the data to a new database.
Has anyone else experienced this?[/quote]

You could always join, and then ask a question on, the SQLite users’ mailing list, where you will get responses from the developers.

See: http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Create a brand new project,
Add a Listbox,
Add a PushButton and place the code below:

[code]Sub Action() Handles Action
Dim Open_Dlg As New OpenDialog
Dim Open_FI As FolderItem

Dim FT_SQLite As New FileType
FT_SQLite.Name = “sqlite/db”
FT_SQLite.MacType = “”
FT_SQLite.MacCreator = “”
FT_SQLite.Extensions = “sqlite”

// Ask the user (you) to choose the bad db structure file
Open_Dlg.Title = “Select a .sqlite file”
Open_Dlg.Filter = FT_SQLite
Open_FI = Open_Dlg.ShowModal
If Open_FI = Nil Then
// User Cancelled
Return
End If

Dim db As New SQLiteDatabase

db.DatabaseFile = Open_FI
If Not db.Connect Then
MsgBox("The database couldn’t be opened. Error: " + db.ErrorMessage)
Return
End If

// Fill the Listbox Headers
Listbox1.ColumnCount = 5 // Just in case…
Listbox1.Heading(-1) = “Type” + Chr(9) + “Name” + Chr(9) + “Tbl_Name” + Chr(9) + “Root_Page” + Chr(9) + “SQL”

// Fill the Listbox with the sqlite_master data
Dim SM_RS As RecordSet

SM_RS = db.SQLSelect(“SELECT * FROM sqlite_master”)
If SM_RS = Nil Then
MsgBox “SM_RS is Nil”
Return
End If
If db.Error Then
MsgBox “An error occured” + EndOfLine + EndOfLine +_
"Error " + Str(db.ErrorCode) + ": " + db.ErrorMessage + “.”
Return
End If

// Clears the previous contents
Listbox1.DeleteAllRows

// To store the newly added Row # (used in the loop below)
Dim Loc_Row As Integer

// Scan and report each Record contents
While Not SM_RS.EOF
// Get the data from each Record and display them in the Listbox
Listbox1.AddRow SM_RS.Field(“type”).StringValue
Loc_Row = Listbox1.LastIndex
Listbox1.Cell(Loc_Row,1) = SM_RS.Field(“name”).StringValue
Listbox1.Cell(Loc_Row,2) = SM_RS.Field(“tbl_name”).StringValue
Listbox1.Cell(Loc_Row,3) = SM_RS.Field(“rootpage”).StringValue
Listbox1.Cell(Loc_Row,4) = SM_RS.Field(“sql”).StringValue

// Next Record, please
SM_RS.MoveNext

// To avoid infinite loop
If UserCancelled Then Exit

Wend
End Sub[/code]

Run.

If everything went OK, you have the contents of the sqlite_master RecordSet:
TABLE names and CREATE string (to create the TABLE).

Select a Row and Paste its contents in a text editor to watch the complete CREATE line.

[quote=412449:@Craig Grech]After many years of using my SQLite app some users are reporting that they can no longer open the database.
They get a message:
“database disk image is malformed, contact support”
There are now numerous messages on blogs and support forums with the same problem eg.
https://techblog.dorogin.com/sqliteexception-database-disk-image-is-malformed-77e59d547c50

It appears the only solution is to move all the data to a new database.
Has anyone else experienced this?[/quote]
Were you able to solve this by doing what is suggested in the article you cite here?

No, you cannot use SELECT statements, there is no tables or data. The database cannot be opened.

Oh my. And I guess there’s no recent backup?

Have you tried this one:

https://www.sysinfotools.com/sqlite-database-recovery.html

They offer a free trial download that can at least let you see if it can preview the data. It just won’t let you save to a new database.

One theory, on recent versions of my app I replaced the depreciated realSQLDatabase with SQLiteDatabase. It was about this time that these malformations started occurring.
What happens when you open a database created in the old format with SQLiteDatabase?
I cannot stop users from opening old data with the new app.

[quote=412826:@Craig Grech]One theory, on recent versions of my app I replaced the depreciated realSQLDatabase with SQLiteDatabase. It was about this time that these malformations started occurring.
What happens when you open a database created in the old format with SQLiteDatabase?
I cannot stop users from opening old data with the new app.[/quote]
There should be 100% compatiblity…

Both ways?
eg. Someone with the new SQLiteDatabase app sends their data to someone still on the old realSQLDatabase app.
I know some of my SELECT statements weren’t compatible when I upgraded because they didn’t include the primary key as one of the fields so they don’t behave in an identical manner.

The syntax to the DB engines might not be 100%, but the data and structure are (from my understanding) compatible all the way back to 2004 (version 3.0)

That’s reassuring.
Just trying to eliminate all possibilities.

[quote]
2.8.14 to 3.0.0 2004-06-18
Version 3.0.0 is a major upgrade for SQLite that incorporates support for UTF-16, BLOBs, and a more compact encoding that results in database files that are typically 25% to 50% smaller. The new file format is very different and is completely incompatible with the version 2 file format.[/quote]

That is the last documented SQLite “structure” change I could find.
Again the syntax for commands have changed, but that is just how data gets into and out of the database, once it is there, the structure is the same.