Databases
StackOverflow2013
Postsdbo
POSqlite3 Transaction not Commiting & Journal not Deleting
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSqlite3 Transaction not Commiting & Journal not Deleting
    primarykey
    data
    text
    <p>Recently I have added <code>BEGIN</code> and <code>COMMIT</code> transaction to all the database calls against my sqlcipher sqlite3 database for an iOS application I am in the process of working on.</p> <p>I am doing this through the following methods:</p> <pre><code>#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, &amp;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)); } } </code></pre> <p>These methods are called directly after opening (and setting PRAGMAs) and before closing the database connection.</p> <p>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 <code>COMMIT</code> not persisting my <code>CREATE TABLE</code> or <code>INSERT</code> statements to disk? </p> <p>After reading the documentation, it would appear that unless explicitly set, the <code>journal_mode</code> of the sqlite database should be set to <code>DELETE</code>. 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?</p> <p>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 <code>SQLITE_OK</code>. This odd behavior only occurs in situations where I only insert one record before attempting to <code>COMMIT</code> any changes. </p> <p>The only other option I have changed from default sqlite configuration is the page size (<code>PRAGMA cipher_page_size = 4096</code>). 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. </p> <p>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. </p> <p>Would anyone have any ideas of why this might be happening or anything else I can try?</p> <p>Thanks in advance. </p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USKrejko
    1. USKrejko
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COShow the code that inserts the record.
      singulars
      1. POSqlite3 Transaction not Commiting & Journal not Deleting
      1. USCL.
    2. COUnfortunately, 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.
      singulars
      1. POSqlite3 Transaction not Commiting & Journal not Deleting
      1. USKrejko
    3. COIt is likely that you do not have correctly finalized some statement.
      singulars
      1. POSqlite3 Transaction not Commiting & Journal not Deleting
      1. USCL.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload