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. >> > > -- > > > > > > --
