Corrupt Database Issue

[Bill Norris]

My app is a menu and winelist in the restaurant. I frequently update the database during business hours using push notification and httputils. 99% of the time all the tablets update successfully, but occasionally, after sending update message, one of them will crash with this error:

android.database.sqlite.SQLiteDatabaseCorruptException: database disk image is malformed: , while compiling: PRAGMA journal_mode=TRUNCATE

When I go into file manager to look at the .db file, it is much smaller than it should be. I am indeed using the HttpUtils.IsSuccess before overwriting the old DB with the newly downloaded one. I am also closing the database prior to the overwrite. When this occurs, I have tried sending another "download new database" push message to that particular device, but is does not resolve the problem. I end up having to email a copy of the database and manually copy it to the proper location.

My plan is add a try/catch block when initializing the database, and if an error is detected, copy a "backup" database instead. I will then add code to download a fresh copy of the db from the server.

My ultimate question is why might this be happening? At one point, I thought that if someone was interacting with the app during the database overwrite, this could cause a problem. I was told though that everything is happening in the main thread so that should not be an issue.

 

[mc73]

If a transaction is performed, we have a db-lock. Thus, if you send a copy of the locked db, the corresponding db-journal is missing, so you'll get the error you mention. Before sending a copy, make sure you closed the sql object (and all cursors first).
Another suggestion would be to send queries. I tend to use this instead of a complete db copy, since usually just a few items of my tables are really changed.
By the way, what do you mean by frequently updating your db? You mean in a central unit, or in all devices?

 

[Bill Norris]

RE:

I manage the db on a PC, then FTP to our server whenever I make any updates. I send push message to all devices to download fresh copy of the db. Once is on the device, the db is read only -- the app does not make any updates to the db, just reads the data. The sql object is initialized in the Main activity, and whenever a sub in another activity needs to access the database, it initializes a cursor object against that sql object. I always check to make sure the sql object is initialized before the cursor, and if sql is not, it gets done before the cursor is executed. As mentioned, I always close that sql object before I copy over the new db. Unless I missed one, somewhere, all cursor objects are closed whenever they are no longer needed, or when the acivitie(s) are closed.

 

[mc73]

I wasn't talking about the db at your terminals, but at your server (which I mistakenly thought it was an android device too). Now, here's a suggestion, if of course you haven't tried that already: Upload, as you ordinary do, your db to your ftp, then download to another pc and try to access it. The error you describe, should be there again. If everything runs smoothly, then we certainly have to deal with the devices. If that's the case, the first thing I would recommend, is immediately closing the app after successfully copying the new db, and even recommend the user to restart the device.
About cursors, now. I always check if they are open, I use the hard way of ctrl-f in order to search every single one of them. It's not only for locking issues but also for performance related ones.

 

[Bill Norris]

RE:

Appreciate the help, MC. Problem is, this app has been up and running fine on all devices, with almost daily download of updated database. I have changed nothing about the way I go about the updates. Suddenly, this error is appearing. Actually appeared again today on same device it occurred yesterday. Perplexed, I am. I need to take the device that it is happening on and do a database update on it singly as it is sitting in front of me and see what happens.

 

[Erel]

Can you post the code that replaces the old database with the new one?

 

[Bill Norris]

RE:

Dim FileUrl As String
         FileUrl=HttpUtils.tasks.Get(0)
         If HttpUtils.IsSuccess(FileUrl) Then 
            Dim database As InputStream
            database=HttpUtils.GetInputStream(FileUrl)
            Dim out As OutputStream
            Main.sql_main.Close
            out = File.OpenOutput(File.DirDefaultExternal,"WINE_TABLET.DB",False)
            File.Copy2(HttpUtils.GetInputStream(FileUrl), out)
            out.Close
            ToastMessageShow("Database Download Complete", True)
            'create a text file to show date of most recent db download
            Dim t As Long
            t = DateTime.Now
            writer.Initialize(File.OpenOutput(File.DirDefaultExternal, "db.txt" , False))
            writer.Writeline(DateTime.GetMonth(t) & "/" & DateTime.GetDayOfMonth(t) & "/" & DateTime.GetYear(t) & " - " & DateTime.Time(DateTime.Now))
            writer.Close
         End If

 

[Erel]

Your code seems to be correct. Try to reproduce this error and then check the database file. Is it really corrupted? What its size?

 

[Bill Norris]

RE:

I guess I am using the term "corrupted" rather loosely. The db should be 2.2 Meg. When the error occurs, it is only a few hundred K. Not always the same smaller size, but right around it. Problem is very intermittent.

 

[Erel]

Why is it smaller? Which step fails?

 

[Bill Norris]

RE:

Am not really seeing anything fail -- just when I look at the file in file manager after download and copy completes, the file is much smaller than it should be.

 

[mc73]

I know nothing about why this occurs, but I've just looked at a part of my code, and I saw I used httpUtils.savefile(newdb with a temp name), then file.delete(theOldDb) and then file.copy(newdb->olddb), without problems. Perhaps you could give it a try?

 

[Mahares]

I do not want to take the attention away from mc73 post, but the below link states that when copying large DB files, they need to be renamed with extension jpg as it is an Android bug. Is this relevant or Billy is dealing with a totally different issue.
http://www.b4x.com/forum/basic4android-updates-questions/15239-copying-files.html#post86479

 

[mc73]

I do not want to take the attention away from mc73 post, but the below link states that when copying large DB files, they need to be renamed with extension jpg as it is an Android bug. Is this relevant or Billy is dealing with a totally different issue.
http://www.b4x.com/forum/basic4android-updates-questions/15239-copying-files.html#post86479

I think this has to do with how android handles apks (compression of files). But who knows, seeing the 2mb size of the db under discussion, perhaps it is relevant!

 

[DouglasNYoung]

The size of the database should not be a problem as its being downloaded from the internet; the size problem was only for files embedded in the apk!

Sorry I can't realy add to a solution to the problem, bit I use a similar download technique and regularly download between 1 & 2 meg!