On Oct 12, 2011, at 9:16 AM, Michael Ellis wrote: > Hmmm, still not completely out of the woods. I've seen one instance > of the error so far on one of the new systems. I ran "PRAGMA > integrity_check" from a sqlite3 command line and saw a bunch of "page > never used" reports. Then I ran VACUUM and re-ran the integrity check > which came back ok.
Does the error ordinarily happen often enough that you can be confident that the barrier option makes any difference at all? It could easily be something else, though the fact that you don't see the problem on OS X suggests a file system issue. The other thing you might experiment with is to create a small ext2 file system and put your database there, just to isolate the file system issues a bit more. > > On Oct 12, 12:03 pm, Michael Ellis <michael.f.el...@gmail.com> wrote: >>> I'd turn on the ext4 barrier option just for the heck of it; see what >>> happens. >> >>> http://lwn.net/Articles/283161/ >> >> Thank you, Jonathan! That seems promising. I'm now testing it on two >> systems. So far so good. Let's see if it holds up for a few hours. >> >> I can't believe the Linux maintainers decided to disable that just to >> get a little better performance :-( Yuck! >> >> On Oct 12, 10:59 am, Jonathan Lundell <jlund...@pobox.com> wrote: >> >> >> >> >> >> >> >>> On Oct 12, 2011, at 7:48 AM, Michael Ellis wrote: >> >>>> Help! I'm running into a serious and puzzling problem running multiple >>>> web2py shell processes against a common database. I'm using web2py >>>> 1.97 and sqlite3 under Linux Mint 10 with an ext4 file system. In a >>>> nutshell, the problem is that I'm getting various DatabaseErrors when >>>> one process attempts to read while another is inserting or updating. I >>>> was under the impression that the DAL would apply the necessary locks >>>> to prevent this kind of conflict but it's not working out very well. >>>> I've made sure all my inserts and updates are wrapped in code (see >>>> below) that performs rollbacks and retries. Do I also need to >>>> manually lock the db (and if so, how)? >> >>>> One puzzling aspect of this problem is that while it occurs repeatably >>>> on different machines running Linux, I do not see it when testing the >>>> same code under OS X. >> >>>> Any advice appreciated! >> >>> SQLite does its own locking as well:http://www.sqlite.org/lockingv3.html >> >>> I'd turn on the ext4 barrier option just for the heck of it; see what >>> happens. >> >>> http://lwn.net/Articles/283161/ >> >>>> ----------- ERROR EXAMPLE------------------ >>>> In this example process id 20180 has entered the update routine and >>>> process id >>>> 19980 attempted a query that failed when SQLite reported a "malformed" >>>> error. >>>> The update, however, succeeded with no errors reported. >>>> --------------------------------------------------------- >>>> DEBUG:20180:Entering db_update(). >>>> ERROR:19980:Traceback (most recent call last): >>>> File "applications/sunrex/modules/fls_data_handler.py", line 631, in >>>> runMissingReports >>>> create_missing_report_records(earliest, end) >>>> File "applications/sunrex/modules/fls_data_handler.py", line 428, in >>>> create_missing_report_records >>>> rows = db(qry).select(tbl.ALL).as_list() >>>> File "/home/solarmax/web2py/gluon/dal.py", line 5394, in select >>>> return self.db._adapter.select(self.query,fields,attributes) >>>> File "/home/solarmax/web2py/gluon/dal.py", line 1176, in select >>>> rows = response(sql) >>>> File "/home/solarmax/web2py/gluon/dal.py", line 1166, in response >>>> self.execute(sql) >>>> File "/home/solarmax/web2py/gluon/dal.py", line 1251, in execute >>>> return self.log_execute(*a, **b) >>>> File "/home/solarmax/web2py/gluon/dal.py", line 1246, in log_execute >>>> ret = self.cursor.execute(*a,**b) >>>> DatabaseError: database disk image is malformed >>>> DEBUG:20180:Leaving db_update(). >>>> ------------------------------------------------------ >> >>>> -------- CODE USED TO WRAP ALL INSERTS AND UPDATES ------------- >>>> def db_insert(table, data): >>>> """ >>>> Make sure all insert operations go through this routine to >>>> ensure transactional integrity. Table is a DAL table object, >>>> data is a dictionary of keyed values. >>>> """ >>>> ## In web2py, updating to id 0 is an insert operation. >>>> db_update(table, 0, data) >> >>>> def db_update(table, record_id, data): >>>> """ >>>> Make sure all update operations go through this routine to ensure >>>> transactional integrity. >>>> Table is a DAL table object, >>>> record_id is an integer >= 0 >>>> data is a dictionary of keyed values. >>>> """ >>>> debug("Entering db_update().") >>>> assert isinstance(table, db.Table) >>>> assert isinstance(record_id, int) and record_id >= 0 >>>> assert isinstance(data, dict) >>>> retries = 0 >>>> while True: >>>> try: >>>> table[record_id] = data >>>> except: >>>> db.rollback() >>>> if retries > 10: >>>> raise >>>> else: >>>> retries += 1 >>>> warning("db_update() failed. Retrying ...") >>>> ## Allow interfering process time to complete. >>>> time.sleep(1) >>>> else: >>>> db.commit() >>>> break >>>> debug("Leaving db_update().") >>>> -----------------------------------------------------------------
