Hi all, Lately I ran into an interesting problem. The problem is solved, but I'm really curious about the inner works of the sqlite_master table.
I have an iPhone application. In the previous version, there was a very slight chance (about 1%) that a query would fail. The user might be wondering what happened because their data didn't show up as expected. Close and re-open the app solved the problem, but that was not a fix. And the fact it was very difficult to reproduce made me wonder there must have been something deep beneath the surface. It turned out that when the table in question was to be shown, I did an extra check if the table existed by querying the sqlite_master table. I did it by first preparing a statement then stepping on it. Here's the interesting part. When my app failed, the preparation always returned SQLITE_MISUSE. That was a very curious error. I searched the documents, and it wasn't entirely clear to me why one would get SQLITE_MISUSE when preparing a statement. Some casual search in SQLite's source code revealed that it was about some safety check mechanism (sqlite->magic), but it still didn't tell me when and what constitues a misuse. The problem was solved by finalziing the prepared statement *immediately* after it was used. Still, the question is why? Before the fix, what I did is I wrapped the SQLite statement as an Objective-C object. After I used the statement, I put the object into an "autorelease pool". This is a nice design in Apple's Cocoa framework that lets you drop used objects into the pool, and when the execution goes back to the event loop, the event loop releases (decreases the ref count) of every object in the pool. This is a nice way to do refcount-based memory management because you don't need to remember to call release in every exit point of a scope. The catch seems to be (at least from my observation): Because when an autorelease pool is "drained", the order the objects are released (refcount--) is not deterministic. I might have a bunch of prepared SQLite statements released in a batch. Interestingly, statements that involve other tables seem to do fine with the indeterminsitic prepare/ finalize order. But for the statements prepared on sqlite_master, it seems *probable* that if the prepared statement is not finalized before any other new statements are prepared, it constitutes a SQLITE_MISUSE. Also, if I got a SQLITE_MISUSE, all subsequent prepare calls got SQLITE_MISUSE in return. When means my app couldn't function further (although the situation was rare and mild/robust enough, no data was harmed, and the app wouldn't crash). A user reported that if there was a background process running (for example iPod.app, the music player), there seemed to be a higher probability such failure would happen. My app did have some multithreading code, but I ruled it out in the early stage by ensuring that statements were prepared/used in the main thread. Or is there something special in Apple's version of SQLite (shared SQLite global data for all apps, for example? That was my very random conjecture). Lukhnos D. Liu _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
