Android Question Doubt about data loss in sqlite

[vecino]

Hello friends, I have had a sales application for android for years. All customers use it daily and it works properly.
However, there is one customer who has a rather serious problem and I can't seem to find the reason.
Basically what happens is that he makes a sale, another sale, another sale, another sale, another sale, etc. and finally instead of all sales, some are missing or even all are missing, they are not there.
I added a "log" file to save in text format every sale line I made. And indeed they are there in the log, but they are NOT IN THE DB.
In the creation of the DB I use "PRAGMA journal_mode = wal;".

It is the typical sales program with header table and line table.
Headers and their lines disappear completely.
Sometimes some sales (their headers and their lines) and sometimes all sales.

I need help!!!
Thanks.

 

[LucaMs]

Nobody will be able to help you with this very little information.

 

[vecino]

I know, just in case you know of similar cases caused by some sqlite configuration.

That's the problem, I've been almost sleepless for several weeks trying to solve the problem and I can't find any clue to help me.

Besides it is something simple and totally illogical. They are simply "insert into table values (?,?,.....) that are saved well, however, a while later those records have disappeared from the database.

If I delete "PRAGMA journal_mode = wal;" what will it affect, will it be good or bad, will it do any good... no idea.
Anyway, desperate.
Thanks.

 

[josejad]

Hummm... some hide debug line you use to delete and you forgot to comment, and it's triggered in some condition just this customer meets?
Search for "delete" or something similar in your code?

 

[vecino]

No, there is nothing like that, nor does this customer use anything special. He has even had his tablet exchanged for another... 4 times.

The truth is that I have been dealing with this issue for several weeks now, trying everything imaginable and unimaginable.
The last thing I thought is that the client deletes that data "Delete invoice", to complain and say "as the program fails and loses data, then I don't pay you what I owe you".
But I added without his knowledge an option that saves in text format everything he saves and deletes, every button he presses, etc. that is, a complete log.
I installed it this afternoon and a while later it was already warning that it had lost information (several invoices). I checked the DB and apparently it is fine. I checked the log file and indeed there were all the invoices that I had made and that were not in the DB.

It happens at any time, for example, it makes invoices, closes the program, the next day those invoices have disappeared. Other times he makes invoices, goes to print them... and they no longer exist. It is something without any logic.

That's why I think that it could be something in the DB, in sqlite, that caches and doesn't finish saving it in the DB, and when closing the program it is lost for some reason.
I don't know, but it is not any "delete" option because in the text log file you can check that he has not pressed that option.
Thanks.

 

[FrostCodes]

You could try logging the SQL that runs when he tries to save data. If you allowed special characters or accented characters to be savedd, it possible that SQLite is rejecting your save queries and you just didn't see this.

After you log some sample queries try and run them manually and see if it save. You can use an SQLite tool to do this.

 

[sfsameer]

Hello,

There are many cases that this could happen but every case is related on how you handle the connection,update,insert, delete,etc...

In my case, we have never faced this problem before and we do have clients that use projects we developed using SQLite + B4A and their daily records are more than 3000+

So i would suggest couple of tips :
1- Make sure when ever you do "ExecNonQuery" you put :

    sql1.BeginTransaction
   
    sql1.ExecNonQuery....... Your code here
   
    sql1.TransactionSuccessful
   
    sql1.EndTransaction

2- Make sure to close the Cursor once the select is completed

3- Make sure to use ExecNonQuery2 in case there are special objects you are sending the DB

But 99% of the comments you will get in this post will be speculations, share a very small part of your code where this problem you suspect is happening and we can inspect it better

Thank you,
Saif

 

[Erel]

Reminder: in wal mode the database is made of 3 files. If for some reason you are making a copy of the main file only then data will be lost.
Closing the database doesn't merge these files.

 

[vecino]

Hello friends, thank you very much for all your advice. I have not been able to do anything because of a family incident that has kept me busy.
I will continue with this topic as soon as I can.
Once again, thank you very much.

 

[Mahares]

This post is not necessarily addressed to vecino as he has his hands full with more important family matters. But, every time I create a new SQLite database on any device, the journal mode defaults to TRUNCATE. I have seen that when created on a PC using a SQLite program it is DELETE. The TRUNCATE journal works well. Why not stick with that instead of WAL which appears more complex when reading about it in the documentation.

n my case, we have never faced this problem before and we do have clients that use projects we developed using SQLite + B4A and their daily records are more than 3000+

As a curiosity rubbernecker, I ask, what is the journal for these successful databases. I am guessing most likely TRUNCATE or DELETE.

 

[sfsameer]

This post is not necessarily addressed to vecino as he has his hands full with more important family matters. But, every time I create a new SQLite database on any device, the journal mode defaults to TRUNCATE. I have seen that when created on a PC using a SQLite program it is DELETE. The TRUNCATE journal works well. Why not stick with that instead of WAL which appears more complex when reading about it in the documentation.

As a curiosity rubbernecker, I ask, what is the journal for these successful databases. I am guessing most likely TRUNCATE or DELETE.

Hello,

Well have certain procedures we follow when we develop a project containing an sqlite database :
1- We never create the Sqlite database dynamically, we always create the database in "sqlitebrowser.org"
and in case we need to add certain column or table then we do that in the B4X code, but the main database is always pre-created

When you create a database in sqlitebrowser ,2 files will be created :

but as soon as you click on write changes (that means the database is ready to be distributed) then temp file will be deleted automatically resulting in one file which is the main DB :

2- We always close the connections when we use the cursor or using the ExecNonQuery Eample :

sql1.BeginTransaction
  
    sql1.ExecNonQuery....... Your code here
  
    sql1.TransactionSuccessful
  
    sql1.EndTransaction

3- Global Sqlite connection, i have seen many times that there are developers that open an Sqlite connection in every activity then close it after closing the activity which is really wrong, we use one global connection in the main activity.

and the list goes on, Sqlite Database is powerful unlike what some people think but sometimes one mistake could lead to a DB lock or even data loss

 

[Mahares]

3- Global Sqlite connection, i have seen many times that there are developers that open an Sqlite connection in every activity then close it after closing the activity which is really wrong, we use one global connection in the main activity.

What about the case where you have several apps working with the same SQLite database. For instance, one app does a CRUD, the other app creates reports, the next app may handle graphs. What do you do about opening and closing the database within each app that works independently of the others.

 

[sfsameer]

What about the case where you have several apps working with the same SQLite database. For instance, one app does a CRUD, the other app creates reports, the next app may handle graphs. What do you do about opening and closing the database within each app that works independently of the others.

Hello,

We wouldn't use it that way, we would do the following :

1- Each app has it's own Sqlite Database, lets call them (temp1.db, temp2.db ,etc...)
2- Then we would create a Central Database (Master Database) , lets call it (CentralDB.db)
3- Then we would do one of the below :
3-1 Either create a background service that does the data sync between the apps databases and the central app
3-2 Once the application is closed the data would be synced between the apps databases and the central app

Thank you,
Saif

 

[Albert Kallal]

Hum, I don't have much to add here either. and I never bothered to set the mode. I just update tables. I have quite a bit of code like this:

Main.sqlite.ExecNonQuery2(strSQL,pValues)

So I create the instance of sqlite ONE time in the main - as global. I been running a database quite hard for about a year - never a problem. I wonder if say a form/view is filled out - and then say the power button is hit or some such.
I in near all cases force a save of the data in Activity_Pause event. (and thus on resume the record WAS saved, and then on resume, the code re-loads that same record - hence automatic state management).

So, perhaps the data is entered, not saved, and then the phone is paused. On resume, the code would HAVE to re-load that data - but if not saved, then no data would be avaible. Since I save on activity pause, then even when a user hits power button or the hard back button - the data is saved right at that point in time.
As noted, we are in speculating territory.

And like Saif, I always have created and setup the database using dbBrowser (desktop). And only the db file was sent/saved to the phone. And I don't delete/re-create a file. (os caching is really nasty if the file is transferred from the sd or memory to say a computer - so we need to know if some file copy occurs during this process in any case. (I could NEVER copy a file correctly unless I re-booted the phone).

This database is small - about 7 tables - (but all related data), and largest table is is 2500 records.

My bets? the phone has limited memory, or some kind of SD card manager (that I had to re-boot for a file copy on my phone).
And note that power-off button, back button, or switch app buttons will triggers the pause event - the poster will have to come back and note/explain or look at what occurs when activity pause occurs.

Back button, power button, switch application button - ALL of these will trigger a pause event, and then resume if app is used again - - and then we down to what occurs on resume event - and that's where my betting money is right now.

Regards,
Albert D. Kallal

Edmonton, Alberta Canada

 

[vecino]

Hello friends, first of all I want to thank you for your advices and comments.
Now I have been able to spend a little time to review everything you have commented.
A few days ago I installed a version of the program for the customer that saves in a log file everything he does: "He clicked the create a sale button", "He clicked the delete a sale button", etc. and so on with all the options that can create, edit and delete sales documents.
Since that version was installed, the customer has not lost anything from the database.
I have come to the conclusion that it was all an excuse not to pay me, or to delay payment.
The situation was not logical, it was not normal that several random invoices with continuous numbering were lost from the database, every day, moreover, complete invoices with all the lines of items sold were always "lost", but nevertheless, the others that were not lost were complete and nothing was ever missing. It was clear that he was deliberately deleting them, or they simply did not exist and he told the story that they had disappeared.
These "picaresque" situations often happen with unscrupulous people.
Anyway, so far everything is working fine, I have not changed anything in the database, that reaffirms me that everything has been a "theater" to avoid paying.
Once again, thank you all very much.
A great greeting.

 

[tchart]

Very interesting thread. I use SQLite a lot, I've been using it since it the early 2000's and I dont recall any instances of lost data.

I know you've mentioned the problem is likely the user but here's some thoughts anyway.

Since this is an Android thread I'm assuming the database is accessed by a single user. So don't use WAL.

As you said the app is for recording sales then performance isn't that important since it's mostly just CRUD. In that case I would probably open/close the database when you need to rather than have it open while the app is open. Yes there is a hit to performance but this is one way to ensure data is saved and the database not left in some odd state.