[sqlite] ESC_BAD_ACCESS when using a background thread
I'm the OP, just closing the loop on this thread. I solved the crashing and the memory allocation issues. The crashing was due to NSMutableDictionary not being thread safe (can't do setObject:forKey on a background thread). The memory allocation issue (memory kept increasing) was due to a proliferation of un-finalized prepared statements. All my bad -- I'm fessing up. Thank you for your suggestions. Jeff
[sqlite] ESC_BAD_ACCESS when using a background thread
On Aug 24, 2015, at 11:50 AM, Roger Binns wrote: > On 08/24/2015 03:08 AM, Jeff M wrote: >> I've checked all of your suggestions and nothing is amiss. > > You ran valgrind and it said everything is fine? That would be shocking. You caught me. I wasn't familiar with valgrind, so I passed over that suggestion. I tried to install valgrind (on Mac OS Yosemite), but I can't get it to compile. I don't have the fortitude to work out the install issues. Looking at Xcode's memory report (and using Instruments), I see that memory does ratchet up, despite my being very careful matching alloc/releases (this app is pre-ARC). I need to relearn Instruments. Among other things, I see many persistent 4K memory blocks resulting from sqlite3MemRealloc, but I haven't figured out the call tree to get there. On Aug 24, 2015, at 11:50 AM, Roger Binns wrote: > ... there is [likely] a bug in [your] code related to object/pointer > lifetimes or similar. [C]hanging things changes where collateral damage > happens, but doesn't actually fix the issue. Yup. Jeff
[sqlite] ESC_BAD_ACCESS when using a background thread
Simon, Roger: I've checked all of your suggestions and nothing is amiss. The background thread fetches the image data and caches it by adding the data to an NSMutableDictionary. The main thread checks the dictionary and does the lazy-load only if the desired image data is not in the cache. When the exception occurs on the main thread, execution stops where the dictionary is being checked (via objectForKey:), and the lazy-load background thread is executing inside of sqlite_step (sqlite_step has not returned when the exception occurs on the main thread). This perplexes me. I don't understand how the main thread can run before the background task has completed -- and specifically while sqlite_step is still executing. Apparently, sqlite_step() can relinquish control to the main thread. I'd appreciate some enlightenment on this implementation issue. My solution -- The problem goes away if I move the caching step (setObject:forKey:) to the main thread (via dispatch_async(dispatch_get_main_queue() ). It was intermittent so I can't be certain, but I've not been able to force a crash since making that change. Jeff > On Aug 23, 2015, at 7:05 AM, Simon Slavin wrote: > > Are you checking the values returned by sqlite3_prepare, sqlite3_bind, and > sqlite3_step, to make sure they return SQLITE_OK ? > On Aug 23, 2015, at 7:00 PM, Roger Binns wrote: > > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 08/23/2015 03:31 AM, Jeff M wrote: >> sqlite3_step(); // occasionally crashes here (showing >> ESC_BAD_ACCESS on main thread) > > That has three very likely causes. The first is that your internal > state gets messed up, and the statement has actually been > finalized/freed. ie it is junk memory. You can add assertions that > check it is still a known statement pointer by checking this returns it: > > http://sqlite.org/c3ref/next_stmt.html > > The second is that the memory where you stored the statement pointer > is what is trashed. > > The final cause is that some other code has memory bugs, causing > damage to SQLite's data structures. > >> It's not a zombie object issue (tested with NSZombieEnabled). > > Sadly that only checks Objective C objects, and not all memory. > >> Any ideas on how to debug this? > > I used valgrind running the app in the simulator. (I also configure > valgrind to never actually reuse memory.) > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1 > > iEYEARECAAYFAlXaXpEACgkQmOOfHg372QRKZACfWyT6pEyNQ9sEKPbhFQ4pI/5G > Nh0AniO5ESx9CIbB484/gYqjtfCsGUrM > =Op+8 > -END PGP SIGNATURE- > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ESC_BAD_ACCESS when using a background thread
My iOS app displays a gallery of thumbnails in a tableView (four to a row). To
allow smooth scrolling, I lazy-load the image data on a background thread using
dispatch_async().
sqlite3_threadsafe() returns true.
sqlite3_open_v2() uses SQLITE_OPEN_FULLMUTEX.
queue is a serial queue from dispatch_queue_create(...).
There is only one connection to the database.
lazy load summary logic:
dispatch_async(queue, ^{
done once: sqlite3_prepare_v2( "SELECT ..." ... );
sqlite3_bind_int(...);
sqlite3_bind_int(...);
sqlite3_step(); // occasionally crashes here (showing ESC_BAD_ACCESS
on main thread)
... sqlite3_column_blob(...);
... sqlite3_column_bytes(...);
// ...
// occasionally crashes here (showing ESC_BAD_ACCESS on main thread)
not inside SQLite
// ...
sqlite3_reset();
});
The main thread is not doing anything with the DB when the crash occurs (it has
previously loaded an array with all the id values needed for the lazy-load's
SELECT statement). It doesn't crash very often (might take 10, 20 or 30
trials), but when it happens, it crashes while loading one of the first few
thumbnails.
It's not a zombie object issue (tested with NSZombieEnabled).
Any ideas on how to debug this?
Jeff
[sqlite] When sqlite3_close() returns SQL_BUSY
The doc page for SQLITE_BUSY covers SQLITE_BUSY when there are multiple connections to the database. SQLite Result Codes (SQLITE_BUSY) http://www.sqlite.org/rescode.html#busy But, I have only one connection. I believe the case where SQLITE_BUSY is returned by sqlite_close() due to unfinalized prepared statements should be mentioned there. Jeff > On Aug 21, 2015, at 3:51 AM, R.Smith wrote: > > Hi Jeff, > > On 2015-08-21 07:30 AM, Jeff M wrote: >> Sometimes my iOS app creates an unreasonable number of prepared statements >> (perhaps 1,000, an app bug that I'm fixing). These prepared statements are >> later finalized just prior to doing sqlite3_close(), which sometimes returns >> SQL_BUSY. The docs say SQL_BUSY will be returned if I haven't finalized all >> prepared statements, but I believe I have done so. My iOS app has only one >> connection to the DB and I'm doing all this work on the main thread. >> >> 1. The docs don't say what to do in the case of SQL_BUSY. Does that mean >> I've certainly failed to finalize one or more prepared statements, or does >> SQLite just need more time (in which case can I loop on sqlite3_close() >> until I get SQLITE_OK)? > > SQL_BUSY does not mean anything bad except that you are trying to do some > work on a query (read: prepared statement) while another is still not done > with its duties. These duties may in your case simply mean that the "closing" > of a previous prepared statement is still under way, so yes, it just needs a > moment. You can wait a moment and try again. > > I will mention (as Simon is likely to point out soon!) that the good news is: > SQLite will do this waiting-and-retrying for you if you simply set a suitable > time-out, perhaps in the order of a minute or more, using the pragma: > > http://www.sqlite.org/pragma.html#pragma_busy_timeout > > or, if you prefer using the C-interface: > http://www.sqlite.org/c3ref/busy_timeout.html >
[sqlite] When sqlite3_close() returns SQL_BUSY
Sometimes my iOS app creates an unreasonable number of prepared statements (perhaps 1,000, an app bug that I'm fixing). These prepared statements are later finalized just prior to doing sqlite3_close(), which sometimes returns SQL_BUSY. The docs say SQL_BUSY will be returned if I haven't finalized all prepared statements, but I believe I have done so. My iOS app has only one connection to the DB and I'm doing all this work on the main thread. 1. The docs don't say what to do in the case of SQL_BUSY. Does that mean I've certainly failed to finalize one or more prepared statements, or does SQLite just need more time (in which case can I loop on sqlite3_close() until I get SQLITE_OK)? 2. Does SQLite keep a record of prepared statements? If so, is there a way I can ask SQLite to close them all, or are there any tricks to debugging which statements have not been finalized? Jeff
[sqlite] Extending VFS documentation
I searched stack overflow for "sqlite3_vfs_register" and got a few hits. For example, this was a good starting point on which I based my own VFS. http://stackoverflow.com/a/3842409/236415 Jeff > On May 24, 2015, at 4:39 AM, Philip Bennefall wrote: > > Hi all, > > I have been interested in the VFS layer of SqLite for a while, and found the > following article to be very useful: > > http://www.sqlite.org/vfs.html > > However, it ends right when it is about to go into the actual details of how > to implement a VFS. It would be great if that essay could be completed. I > have looked at the example VFS implementations, but it is hard to determine > which parts of the code that are implementation details as opposed to being > part of the stable public API so to speak. > > Kind regards, > > Philip Bennefall >
[sqlite] Copying an open DB file (iOS)
I'm using SQLite (via FMDB) with an on-disk database in several iOS apps. In each case, the DB file is only used by the one app and there's no DB activity on background threads. The apps have a backup feature (initiated by the user) that simply copies the database file. There is no other activity (in my app) when the backup is started (all reads and writes would have been completed at least a few seconds earlier), and I'm not concerned about a power or system failure during the copy. For a restore, the current DB is closed, then a backup file is copied in and opened. I've been using this scheme for years without a problem. But, should I flush, or close, or lock before the copy? Jeff
[sqlite] Integrating sqlite with Core Data and iCloud
> On Apr 24, 2015, at 2:44 AM, Simon Slavin wrote: > > On 24 Apr 2015, at 6:59am, Jeff M wrote: > >> I don't need to map SQLite to iCloud -- I only need to map SQLite to Core >> Data. Core Data then takes care of the iCloud issues. > > I imagine you'd do that by writing a VFS which used Core Data for storage. > Core Data could store your data in any of the formats it has drivers for, > including plaintext files and SQLite. And Core Data could store your data in > any medium it has drivers for, including local storage and iCloud. > > The result might be slow and inefficient, since you're building a DBMS > (SQLite) on top of a DBMS (Core Data) on top of a DBMS (SQLite). > > If you use Core Data the resulting file wouldn't look like a normal SQLite > database. Core Data stores objects. It doesn't store the rows and columns > you refer to with SQL commands. Maybe your objects would be table rows. > > Simon. Originally, I wanted to map my tables, rows, and columns to similarly-named Core Data entities and attributes so I could continue to use the SQL language. I was hoping to hook into the SQL parser to get the benefits of where.c, but I realize now that's impractical. Your suggestion of working at the file system level is interesting, but my objects would be disk blocks. I could use a simple Core Data model: one entity (representing the entire database file) and create one object per block (each having a binary attribute containing one block of data). It would be easy to map each file system read() and write() to the corresponding objects. Using Core Data as a memory array would earn me the Kludge of The Year Award. But, I see data corruption in my future. Can you point me to some sample source code (outside of SQLite itself) that implements sqlite3_vfs_register()? Jeff
[sqlite] Integrating sqlite with Core Data and iCloud
On Apr 23, 2015, at 3:51 PM, Simon Slavin wrote: > > On 23 Apr 2015, at 9:29pm, Jeff M wrote: > >> Has there been any discussion about integrating sqlite with Apple's iCloud, >> either by using Apple's Core Data as the Virtual Machine's database engine >> (so the VM would operate on Core Data objects) or by otherwise modifying the >> existing backend? > > Apple has already tightly integrated SQLite with iCloud. It's precisely that tight integration that I want to take advantage of, but without using Core Data directly and losing the ability to write queries using SQL. Core Data's use of SQLite is just coincidental and would not change. SQLite would be in the path twice: My app --> my special SQLite --> Core Data backend --> Core Data's normal SQLite and iCloud. Such a version of SQLite might be useful for people upgrading legacy apps for the cloud and who (like me) much prefer using the SQL language instead of Core Data objects. > You might instead be asking whether you could write an app which makes SQLite > calls to have data stores in iCloud. Yes, almost... > This could be done by writing a SQLite VFS which stored data in iCloud. I > don't know how well this would work, especially if more than one user was > trying to use a database. It might be difficult to implement because the > iCloud API doesn't map neatly onto the SQLite VFS API. I don't need to map SQLite to iCloud -- I only need to map SQLite to Core Data. Core Data then takes care of the iCloud issues. Also, I have no multi-user issues -- one app, one user, one database. > What would be easier is to use SQLite to store a database on your iCloud > Drive, as available in OS X 10.10 and above and some versions of Windows. > But there's nothing to do here: the drive is just a mounted volume like a > Flash drive or an AFP mount. Just use the right path in sqlite3_open(). And > experiment with concurrent multi-user use before you promise it, since iCloud > Drive locking is, I think, implemented with using SMB2. It the database file was small, iCloud Drive or Dropbox might work. But that fails with large files -- the user's device would be constantly syncing the entire database, even for small changes. This is why Core Data works -- it only syncs transactions. Jeff
[sqlite] Integrating sqlite with Core Data and iCloud
Has there been any discussion about integrating sqlite with Apple's iCloud, either by using Apple's Core Data as the Virtual Machine's database engine (so the VM would operate on Core Data objects) or by otherwise modifying the existing backend? My motivation is to allow existing apps that using the SQL language (via FMDB or the sqlite API) to gain Core Data features such as iCloud integration for syncing data between devices. Refactoring apps to use Core Data is often impractical. My dream architecture is: SQL Language <--> FMDB <--> CD_sqlite (using Core Data (sqlite)) <--> iCloud. What is the feasibility of making sqlite a wrapper for Core Data, or integrating iCloud directly into sqlite? Crazy? Jeff
