I think you have hit on the issue. I added a db.commit() call before I attempted to read from the table and the read was successful.
Thanks so much. David On Jul 24, 2012, at 11:16 PM, Massimo Di Pierro wrote: > First of all we need to establish there is a problem and this is not the > expected behavior. I am not sure. > > MySQL has 4 different isolation modes and defaults to: > > http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html#isolevel_repeatable-read > > "All consistent reads within the same transaction read the snapshot > established by the first read." > > I think this means that your background process does not see new updated > records unless it starts a new transaction. You can check it. Try adding some > db.commit() to the background process, even if it does not write data. > > If this is the problem, you may also be able to change the isolation level > with SET TRANSACTION. > > Massimo > > > > On Tuesday, 24 July 2012 22:26:26 UTC-5, David Phillips wrote: > Hello, Massimo. > >> What changed? Did you upgrade? What web2py version? > > I removed a task in the background process that was periodically calling > db.commit. In its place, I started using memcache where I had been writing to > the db. > > I haven't upgraded. I've been using 1.99.7 all along. > >> When you say the select does not work anymore, dwhat do you mean? Does it >> lock or do you get a traceback? > > I add an account to the account table with account_id == 1 and id == 1 > using appadmin on the web server. > > Then I go to the background process and execute these commands both in my > code and using the debugger: > > rows = db (db.account.account_id == 1).select().first() > > returns None. > > count = db (db.account.id > 0).count() > > return zero. > > db.executesql("select account_id from account") > > returns None. > > No error messages, exceptions, lock-ups or the like. select() simply fails to > find the data in the table which I can see with appadmin and the mysql > command-line client. > > Curiously, if I stop the background process and restart it, I can read the > account from the account table: > > db (db.account.id > 0).count() > > returns 1. > > It's not a matter of the committing the database after the initial write. I > added a db.commit() call after writing the account to the db just to make > sure. It made no difference. > >> Which database driver? People have reported problems with pymysql but not >> with mysqldb. > > It's pymysql for both the development platform (Mac OS X 10.7) and the > production platform (CentOS). > > > Do you have any advice on how to diagnose my problem? > > Thanks, > David > > > > On Jul 24, 2012, at 8:50 PM, Massimo Di Pierro wrote: > >> What changed? Did you upgrade? What web2py version? When you say the select >> does not work anymore, dwhat do you mean? Does it lock or do you get a >> traceback? Which database driver? People have reported problems with pymysql >> but not with mysqldb. >> >> On Tuesday, 24 July 2012 19:42:29 UTC-5, David Phillips wrote: >> On the eve of delivering a project to a client, I've come up against a >> problem that has me stumped. select() statements on one of my mysql tables >> have stopped working. >> >> My application is a web2py web server and a background process (also called >> a homemade task queue in the web2py book). They share the database. The web >> server writes to the table from within an HTTP request, and several seconds >> later, I attempt to read the record in my background process. >> >> Up until yesterday, I didn't have any trouble reading from this or any of >> the tables. And now, all the others work fine. I can still write and read >> from the misbehaving table from the web server. >> >> I am at a loss. I'm not sure where to look to diagnose the problem. Any >> pointers would be gratefully received. >> >> >> -- >> >> >> > > > -- > > > --
