I am facing an issue with my SQLite Database . If I force restart the iOS device while its inserting rows into table, after device boots up the its not recovering all the data that has been inserted before force restart.

When I was inserting rows into table in a transaction, SQlite creates a journal file. After every insert I was printing last row id but when I did a force restart and when iPhone reboots, it shows last rowid as less then what I have seen before force restart.

Thanks in advance

---

Test environment details

---

Test Device - iPhone6S Test Device OS - iOS 12.3 SQLite Version - "3.37.0" SQLITE Configuration - Jornal Mode - Delete Locking Mode - Normal Synchronous - Full Page Size - 4096

Yes every insert we are processing in different transaction. And I can see committed transactions are also getting lost after force restart.

The data that is being inserted isn't actually considered to be IN the database until you successfully commit it. If you abort the program in the middle of a transaction, that transaction will get rolled back when you next connect to that database.

Does that explain what is happening?

In addition to the other posts asking whether you're using a transaction …

Which language are you programming in ? Which SQLite library are you using (or are you using the C API directly) ? Does your application respond to the 'quit' notification by closing your SQLite connection, or does it just quit ?

When you say "forced restart" are you talking about telling the iPhone to turn off, or are you using the iPhone interface to force-quit just your application ? Are you trying to simulate what would happen if the iPhone lost power, or are you testing for some other condition ?

Thanks for reply! I am using objective C programming language. I am using FMDB wrapper written over SQLITE C API. FMDB using sqlite-amalgamation-3370000 version.

Force restart - I use power button and home button to force restart the iPhone.

In case of power off it works fine. I am facing only when I do force restart.

Thanks

The code block that I am using to reproduce the data loss scenario

---

sqlite3 *database = nil; sqlite3_stmt *statement = nil;

---

if (sqlite3_open(dbpath, &database) == SQLITE_OK) {

---

 if(sqlite3_exec(database, "BEGIN EXCLUSIVE TRANSACTION", 0, 0, 0) != SQLITE_OK) { }

---

    NSString *insertSQL = [NSString stringWithFormat:@"insert into studentsDetail (name) values (\"%@\")", studentName];

---

    const char *insert_stmt = [insertSQL UTF8String];

---

    sqlite3_prepare_v2(database, insert_stmt,-1, &statement, NULL);

---

    if (sqlite3_step(statement) == SQLITE_DONE) { }

---

    sqlite3_reset(statement);

---

    sqlite3_finalize(statement);

---

   sqlite3_close(database)

---

}

---

The above code I am running in a while loop with a sleep(0.5) And while its inserting, I am force restarting the iPhone.

After reboot sometime I see that committed transactions are also getting lost and sometime database getting corrupted Error msg

---

(11) database corruption at line 67776 of [378629bf2e]

---

(11) statement aborts at 4: [insert into studentsDetail (name) values ("test_name_1")] database disk image is malformed

---

2022-01-06 15:19:46.485384+0400 RawSqlitePOC[257:5093] SQL error failed to commit transaction: database disk image is malformed

---

You don't also need both sqlite3_finalize() and sqlite3_reset(). But I suppose that might be an artefact of writing test code rather than a real program.

You have a BEGIN in there but no END or COMMIT. This means you're explicitly starting a transaction but not explicitly ending it. If your code executes BEGIN each time around the loop it should also have an END each time around the loop. Presumably between sqlite3_finalize() and sqlite3_close(). This is the transaction problem other people asked you about. Here is the equivalent of what you're doing:

simon@183 Desktop % sqlite3 test.db
SQLite version 3.36.0 2021-06-18 18:58:49
Enter ".help" for usage hints.
sqlite> BEGIN;
sqlite> BEGIN;
Error: cannot start a transaction within a transaction
sqlite> END;
sqlite> END;
Error: cannot commit - no transaction is active
sqlite> CREATE TABLE t (a INT);
sqlite> BEGIN;
sqlite> INSERT INTO t VALUES (77);
sqlite> SELECT * FROM t;
77
sqlite> .quit
simon@183 Desktop % sqlite3 test.db
SQLite version 3.36.0 2021-06-18 18:58:49
Enter ".help" for usage hints.
sqlite> .tables
t
sqlite> SELECT * FROM t;
sqlite> BEGIN;
sqlite> INSERT INTO t VALUES (88);
sqlite> SELECT * FROM t;
88
sqlite> END;
sqlite> SELECT * FROM t;
88
sqlite> .quit
simon@183 Desktop % sqlite3 test.db
SQLite version 3.36.0 2021-06-18 18:58:49
Enter ".help" for usage hints.
sqlite> .tables
t
sqlite> SELECT * FROM t;
88
sqlite> .quit
simon@183 Desktop % 

Given the above problem, one thing you should not be seeing is a corrupt database – one which gi. SQLite should be recovering a usable database whether or not your last transactions is in it.

About database corruption: https://sqlite.org/howtocorrupt.html