Error detaching SQLite database - database is locked

Question

I have a system based on SQLite database. Each client has a local database, and from time to time the update comes from the main server, as well as to a small delta.db file. The task is to combine with a local database with a delta file, the scheme is identical in both.

For my database management, I use fmdb wrapper, which can be found here . In the main thread, I maintain a connection to the local database. The delta file arrives in the background, and I want to do a merge in the background to avoid any user interface freezing that this might cause.

As for the merge itself, the only option I found was to bind the delta database to the local database, then insert / update rows and finally delete the delta. It is not as smooth as I expected.

Code Description:

• The onDeltaGenerated method is called in the background thread whenever the delta database is ready for processing (it arrives from the server and is stored in a readable place).
• DeltaDBPath is the absolute location of the delta database in the file system.
• References to db variables open the FMDataBase connection.

The code:

- (void)onDeltaGenerated:(NSNotification*)n { NSString* deltaDBPath = [[n userInfo] objectForKey:@"deltaPath"]; @synchronized(db) { [db executeUpdate:@"ATTACH DATABASE ? AS delta", deltaDBPath]; if ([db hadError]) { NSLog(@" ****ERROR*** %d: %@", [db lastErrorCode], [db lastErrorMessage]); } else { NSLog(@"Delta attached from %@", deltaDBPath); } [db beginTransaction]; BOOL update1 = NO; BOOL update2 = NO; BOOL transaction = NO; update1 = [db executeUpdate:@"INSERT OR REPLACE INTO equipment SELECT * FROM delta.equipment"]; if (!update1) { NSLog(@" *** ERROR *** update 1 failed!"); NSLog(@" ****ERROR*** %d: %@", [db lastErrorCode], [db lastErrorMessage]); } update2 = [db executeUpdate:@"INSERT OR REPLACE INTO equipmentExt SELECT * FROM delta.equipmentExt"]; if (!update2) { NSLog(@" *** ERROR *** update 2 failed!"); NSLog(@" ****ERROR*** %d: %@", [db lastErrorCode], [db lastErrorMessage]); } transaction = [db commit]; if (!transaction) { NSLog(@" *** ERROR *** transaction failed!"); NSLog(@" ****ERROR*** %d: %@", [db lastErrorCode], [db lastErrorMessage]); } [db executeUpdate:@"DETACH DATABASE delta"]; if ([db hadError]) { NSLog(@" ****ERROR*** %d: %@", [db lastErrorCode], [db lastErrorMessage]); } else { NSLog(@"Delta detached"); } } 

}

After this method is called for the first time, everything seems fine until I try to detach the database. When I try to do this, I get the following error:

 2012-01-11 12:08:52.106 DBApp[1415:11507] Error calling sqlite3_step (1: SQL logic error or missing database) SQLITE_ERROR 2012-01-11 12:08:52.107 DBApp[1415:11507] DB Query: DETACH delta 2012-01-11 12:08:52.107 DBApp[1415:11507] ****ERROR*** 1: database delta is locked 

I also tried to do the same, but without marks in the transaction, the result was identical. Another thing is to remove @synchronized clause, but also no luck. I assume that if it fails while trying to access the local database connection from the background thread, but then, how does it succeed, can I attach and paste? Any help was appreciated.

Edit

I moved the code to the main thread, so now db is accessed only from the main thread. The problem remains.

Edit2

Well, therefore, having tried everything, I gave up for a moment, and then returned when the first answer appeared. Surprisingly, now everything is working fine, so my code should be correct. I suspect this was a problem with different threads blocking the file, as I used Xcode, SQLiteDatabaseBrowser and my application to open the database. Although lsof showed that the file was not locked, I think it was wrong, and Xcode or SQLiteDatabaseBrowser blocked it. I believe that the problem is solved, and the lesson taken from this is not so fixated, and the next time it’s better to plan debugging.

Answer 1

Just checking to see if NSLog(@"Delta attached from %@", deltaDBPath); prints successfully NSLog(@"Delta attached from %@", deltaDBPath); , and the errors that you describe occur after that?

---

The Error calling sqlite3_step (1: SQL logic error or missing database) SQLITE_ERROR is probably the most interesting bit to learn.

After a bit of googling, one problem that appears is a database file that cannot be written. http://www.iphonedevsdk.com/forum/iphone-sdk-development/20142-problem-insert-fmdb.html

If the main update of your database is in the application bundle, you are not allowed to modify it - first you must make a copy in Documents or another writable directory.

---

Is there really an error when trying to disconnect, or is it actually happening when trying to execute an INSERT OR REPLACE transaction?

If you put another if ([db hadError]) {… right after these statements?

Answer 2

You already

 [db open]; 

in the other place?

Answer 3

Are you sure u reset all db requests? Make sure you call sqlite3_reset(stmt) .

Answer 4

Here is one that just bit me (you know where): I was thoroughly (too much, it seems) testing while creating a new function that required an additional ATTACHed database (by the way, in .NET). So i

 var i = query.ExecuteNonQuery("ATTACH DATABASE @FilePath AS `MergeDestination`;", fullPath); FakeCallThatDoesNothing() var i = query.ExecuteNonQuery("DETACH DATABASE `MergeDestination`;"); 

which, apparently, does not allow enough time for attachment and there is a large scratch on the head.