Easy solution to malformed SQLite databases

  1. 10 months ago

    Craig G

    1 Nov 2018 Australia

    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?

  2. Markus W

    1 Nov 2018 Pre-Release Testers #JeSuisHuman New Zealand, Auc...

    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

  3. Tim S

    1 Nov 2018 Canterbury, UK

    @Craig G After many years of using my SQLite app some users are reporting that they can no longer open the database.

    Which version of SQLite is your app using?

  4. Jean-Yves P

    1 Nov 2018 Pre-Release Testers, Xojo Pro Europe (France, Besançon)

    are you using sqlite in a multi-user environment ?

  5. Thom M

    1 Nov 2018 Pre-Release Testers Greater Hartford Area, CT

    @Jean-YvesPochez 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.

  6. Neil B

    1 Nov 2018 Pre-Release Testers

    @Jean-YvesPochez are you using sqlite in a multi-user environment ?

    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

  7. 9 months ago

    Craig G

    3 Nov 2018 Australia
    Edited 9 months ago

    @Jean-YvesPochez

    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

  8. Emile S

    3 Nov 2018 Europe (France, Strasbourg)

    Is SQLiteDoctor strong enough ?

  9. Tim S

    3 Nov 2018 Canterbury, UK

    @Craig G 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?

    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

  10. Emile S

    3 Nov 2018 Europe (France, Strasbourg)

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

    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

    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.

  11. Ralph A

    3 Nov 2018 Santa Monica, California

    @Craig G 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?

    Were you able to solve this by doing what is suggested in the article you cite here?

  12. Craig G

    3 Nov 2018 Australia

    @Ralph A 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.

  13. Ralph A

    3 Nov 2018 Santa Monica, California

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

  14. Ralph A

    3 Nov 2018 Santa Monica, California

    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.

  15. Craig G

    4 Nov 2018 Australia

    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.

  16. Dave S

    4 Nov 2018 San Diego, California USA

    @Craig G 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.

    There should be 100% compatiblity....

  17. Craig G

    4 Nov 2018 Australia

    @Dave S 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.

  18. Dave S

    4 Nov 2018 San Diego, California USA

    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)

  19. Craig G

    4 Nov 2018 Australia
    Edited 9 months ago

    @Dave S 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.

  20. Newer ›

or Sign Up to reply!