Android Question Problem Database "Malformed" on some tables.

vecino · May 13, 2021

I'm having trouble making a copy of the BD to save.
First I close the DB, then I do a file.Copy finally I open the DB.

SQL.Close, close pending transactions, if any?
Is there a command to check if there are pending transactions and be able to execute them?
Something like:
if SQL.INTRANSACTION Then.
SQL.TRANSACTION.CLOSE

I do not know if I should also save the file -Journal of the database.

Can you orient myself with this topic?

Thanks.

Mahares · May 13, 2021

vecino said:
Can you orient myself with this topic?

Suppose you delete all records and then change your mind, and you want to rollback the deletion, the code will be for something like this. Of course it hinges on whether you can get in the database to issue those commands.:

B4X:

SQL.BeginTransaction
    Dim strQuery As String ="DELETE FROM mytable"
    SQL.ExecNonQuery(strQuery)    
    SQL.ExecNonQuery("ROLLBACK")

If all fails, you may want to attach your database for other members to see if it is corrupted or not.

TILogistic · May 14, 2021

see:
This also happened to me when backing up a DBSQLite, it was a problem with the DATABASE journal,

I do not know if I should also save the file -Journal of the database.

SQLite Forum: backkup and restore sqlite database between different android phones

sqlite.org

Erel · May 14, 2021

If the journal mode is 'wal', and it is likely to be, then there will be usually two additional files that are part of the database. Use File.ListFiles and you will see them.

vecino · May 14, 2021

Mahares said:
...

Hello, the problem apparently arises when I close the dB, I do a backup (copy.file) and then reopen it again. It's already "Malformed" when I open it again.
For what information is lost from some tables, normally the table of sales lines.

vecino · May 14, 2021

oparra said:
...

That I have thought, but I do not know what solution to give it.
The process is the one that I have indicated, more or less:

DB.Close
Copy.File( FileDB, FileDBbak )
DB.Inicialize

And I can no longer access the table of sales lines.

vecino · May 14, 2021

Erel said:
If the journal mode is 'wal', and it is likely to be, then there will be usually two additional files that are part of the database. Use File.ListFiles and you will see them.

Yes, there is the DB file "-Journal", and there is not the DB "Wall". I guess the latter is eliminated when closing the dB.
And, yes, the DB is created with Journal Mode "Wall".

vecino · May 14, 2021

oparra said:
see:
This also happened to me when backing up a DBSQLite, it was a problem with the DATABASE journal,

SQLite Forum: backkup and restore sqlite database between different android phones

sqlite.org

I just read in the link that when the DB closes, the "Journal" must be deleted, and if it does not delete it, it is because the DB is still doing something or has any connection.

So, I ask, what do I do to close it completely? Does not it serve db.close?

Can I repeat the order several times?
Db.close.
Db.close.
Db.close.

??? I do not know what to do.

vecino · May 14, 2021

I can not find information to ensure the closure of the DB, the file "-journal" is always there, it is never deleted :/

Erel · May 16, 2021

The wal files will not be deleted when the database is closed. The database is made of these three files.

vecino · May 16, 2021

Erel said:
The wal files will not be deleted when the database is closed. The database is made of these three files.

This is not what the documentation says.

A rollback journal is a temporary file used to implement atomic commit and rollback capabilities in SQLite. (For a detailed discussion of how this works, see the separate document titled Atomic Commit In SQLite.) The rollback journal is always located in the same directory as the database file and has the same name as the database file except with the 8 characters "-journal" appended. The rollback journal is usually created when a transaction is first started and is usually deleted when a transaction commits or rolls back. The rollback journal file is essential for implementing the atomic commit and rollback capabilities of SQLite. Without a rollback journal, SQLite would be unable to rollback an incomplete transaction, and if a crash or power loss occurred in the middle of a transaction the entire database would likely go corrupt without a rollback journal.
The rollback journal is usually created and destroyed at the start and end of a transaction, respectively. But there are exceptions to this rule.
If a crash or power loss occurs in the middle of a transaction, then the rollback journal file is left on disk. The next time another application attempts to open the database file, it notices the presence of the abandoned rollback journal (we call it a "hot journal" in this circumstance) and uses the information in the journal to restore the database to its state prior to the start of the incomplete transaction. This is how SQLite implements atomic commit.

The exceptions for not deleting the "journal" do not occur in this case, there is no "crash", no "power loss", no "locking_mode exclusive".
That is why I think, I believe, I deduce, it seems to me that something strange is happening and I don't know what it is. My knowledge on the subject is not enough.

Erel · May 16, 2021

Assuming that the journal mode is actually wal then there are two other temporary files: https://sqlite.org/tempfiles.html

vecino · May 16, 2021

Yes, indeed, but the "wal" file is created and destroyed on exit. The one that is never destroyed is the "journal", and that is what worries me because I don't know if it is normal or if there is a problem.

Anyway, I think that running from the application: sql.execnonquery(".backup dbbackup.db") I can make backups without problems and I can sleep more relaxed. It's not good that a client calls you to say that the DB is broken when a backup has been done, that's what happened until now, and the method I was using was that:

DB.close
file.copy("data.db", "databackup.db")
DB.Inicialize(...

The DB was already broken!!!!!

KMatle · May 16, 2021

Try it in B4J. Closing the db results in cleaning up all temp files (the journal contains unwritten data). I don't think SQlite reacts different in Android.

vecino · May 16, 2021

vecino said:
Anyway, I think that running from the application: sql.execnonquery(".backup dbbackup.db") I can make backups without problems and I can sleep more relaxed. It's not good that a client calls you to say that the DB is broken when a backup has been done, that's what happened until now, and the method I was using was that: The DB was already broken!!!!!

Okay, that doesn't work. I don't know how to run ".dump", nor do I know how to run ".backup".
I will continue to investigate.
Thanks, folks.

Android Question Problem Database "Malformed" on some tables.

vecino

Well-Known Member

Mahares

Expert

TILogistic

Expert

SQLite Forum: backkup and restore sqlite database between different android phones

Erel

B4X founder

vecino

Well-Known Member

vecino

Well-Known Member

vecino

Well-Known Member

vecino

Well-Known Member

SQLite Forum: backkup and restore sqlite database between different android phones

vecino

Well-Known Member

Erel

B4X founder

vecino

Well-Known Member

Erel

B4X founder

vecino

Well-Known Member

KMatle

Expert

vecino

Well-Known Member

Similar Threads

Android Question Problem Database "Malformed" on some tables.

Well-Known Member

Expert

Expert

B4X founder

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

B4X founder

Well-Known Member

B4X founder

Well-Known Member

Expert

Well-Known Member

Similar Threads

Privacy & Transparency

Privacy & Transparency