0

Recently I have added BEGIN and COMMIT transaction to all the database calls against my sqlcipher sqlite3 database for an iOS application I am in the process of working on.

I am doing this through the following methods:

#define kTRANSACTION_BEGIN @"BEGIN"
#define kTRANSACTION_COMMIT @"COMMIT"

-(void)transactionBegin
{
    int status = sqlite3_exec(self.Database, kTRANSACTION_BEGIN.UTF8String, NULL, NULL, NULL);
    NSLog(@"BEGIN Status = %i", status);
}

-(void)transactionEnd
{
    char* errorMessage;
    int status = 0;

    status = sqlite3_exec(self.Database, kTRANSACTION_COMMIT.UTF8String, NULL, NULL, &errorMessage);
    NSLog(@"COMMIT Status = %i", status);

    if (status != SQLITE_OK) {
        NSLog(@"ERROR: SQL Error closing transaction (%i):  %s\n %s",status, errorMessage ,sqlite3_errmsg(self.Database));
    }
}

These methods are called directly after opening (and setting PRAGMAs) and before closing the database connection.

Both of these appear to execute successfully. However, upon inspecting the location of the database, the db.sqlite file remains 0 bytes with a db.sqlite-journal file at 512 bytes. Queries run agains the database continue to execute successfully until the app is closed and then all the data inserted is lost. Why is my COMMIT not persisting my CREATE TABLE or INSERT statements to disk?

After reading the documentation, it would appear that unless explicitly set, the journal_mode of the sqlite database should be set to DELETE. If this were true, I should no longer have a -journal file after the commit has been completed. Would there be any way through the c API that I would be able to test this?

I am also led to believe that the code is in functional order due to how I am using my database wrapper to act upon a few different database files. Upon successful beginning and committing of the transaction, the BEGIN and COMMIT status codes are always 0 which indicates SQLITE_OK. This odd behavior only occurs in situations where I only insert one record before attempting to COMMIT any changes.

The only other option I have changed from default sqlite configuration is the page size (PRAGMA cipher_page_size = 4096). The page size has been adjusted to help increase performance of some of the other larger databases that I also use with this wrapper. Removing my modification to the page size does not resolve the issue.

Lastly, I have noticed that if I remove the transactions all together, I do not experience this issue at all. Preferably, I would like to leave in the transactions for the increase in performance, but having it not persisting my data defeats the purpose of the database.

Would anyone have any ideas of why this might be happening or anything else I can try?

Thanks in advance.

Krejko
  • 901
  • 1
  • 9
  • 23
  • Show the code that inserts the record. – CL. Aug 08 '13 at 19:43
  • Unfortunately, the creation and execution of my insert statements is highly abstracted across numerous classes and would probably not be of much help if posted here. However, I can say that without the transactions that the database performs exactly as intended. If need-be, I can attempt to simplify and post my procedure for the sake of this discussion. Please let me know if this is desired. Thank you for your response. – Krejko Aug 08 '13 at 20:11
  • It is likely that you do not have correctly finalized some statement. – CL. Aug 08 '13 at 20:53
  • That was my first thought too. But I have just verified; Upon completion of each query, I am calling sqlite3_clear_bindings, sqlite3_reset, and then sqlite3_finalize. – Krejko Aug 08 '13 at 21:09
  • Are you checking all return codes? – CL. Aug 09 '13 at 07:01
  • I have been. All the SQL code is executing successfully (returning `SQLITE_OK`). I can continue to run `SELECT` statements after `INSERT`ing the records and they are correctly returned to me. The issue I seem to be having seems to be related to how Sqlite is managing its journals. It doesn't seem to take my `COMMIT` statement (event though it returns `SQLITE_OK`) and therefore always rolls back whenever the app is restarted because the transaction was not complete. – Krejko Aug 09 '13 at 14:02
  • Are you checking the return codes of *all* functions? – CL. Aug 09 '13 at 14:06
  • I do believe that I am. I can not find any instance where I am not. All statements executed either result in `SQLITE_OK`, `SQLITE_ROW`, or `SQLITE_DONE`. – Krejko Aug 09 '13 at 14:31
  • There is an easy way to rule out sqlcipher as the cause of this. Just drop the part where you set the key via pragma or sqlite3_key. If you still see the same problem with an unencrypted databased then the problem is definitely with the application code. – Stephen Lombardo Aug 09 '13 at 21:23
  • This was an excellent idea. I have tried removing the key and running the queries on the new unencrypted database files but, unfortunately, the journal files still remain and the records do not persist. While this is not the most desirable solution, I have been forced to remove transactions until the cause of why they are not functioning properly can be determined. – Krejko Aug 15 '13 at 13:36

0 Answers0