web2py hanging after DB error - Windows / rocket / pyodbc / SQL Server

[David Rager]

Hey all - we have a persistent issue in our staging environment where our site will hang after certain DB errors.  It feels like a transaction is hanging on the DB but am still confused as we instruct rocket to use multiple threads, so am unsure why it would block other requests.

Is it possible that the rocket deploy on Windows is not the idea deployment?  Should we switch to apache? 

I think I found an old, similar issue where an auto_commit was added to the dal object instantiation but am not clear on the implementation or the side effects.  

Appreciate any ides.  On our prod environment where DB errors have mostly been knocked down we don't see this type of behavior, but the fear is it will show up at some point.

Thanks!

[Niphlod]

it's pretty obscure the way you describe those errors: "certain DB errors" are what ? unique constraints not respected? db migrations ? heavy complicated queries ? file uploads ?

[David Rager]

Typically they are DDL errors after making changes to the DAL.

[David Rager]

Here is a more specific error as I just hit this issue again (paraphrased):

Cannot create table xxxx because it already exists in the database.

I fixed the error but had to restart the web2py service to get the site to respond after the correction was made.

On Monday, August 20, 2012 12:49:34 PM UTC-6, Niphlod wrote:

[Andrew Buchan]

We have the same (similar) issue using mssql on Windows: when we get a pyodbc.ProgrammingError typically relating to a foreign key, we have to restart web2py.

This is particularly frustrating problem as these errors happen every time you rename/delete a column with a foreign key.

 E.g. create a field that references another table:
.....
    Field('a_field', db.another_table, ondelete='CASCADE'),
....
Then remove/rename ;a_field', and you get "ALTER TABLE DROP COLUMN a_field failed because one or more objects access this column."

This causes web2py to hang, so we have to go into management studio, delete the constraint, and restart web2py because it has become unresponsive.

I think that, aside from resolving why web2py hangs, we could also improve migration so that it deletes any constraints automatically when we drop a column. 

While we're at it, web2py could check a column actually exists before calling a DROP statement. It frequently happens that things do get out of synch with fake migrates and manually modifying the table etc, and for a DROP statement to be called on a column that has been removed. There is really no benefit in web2py failing if the column to delete isn't found.

I thought I'd reply to this thread (ps: right thing to do?) and see if anyone had any suggestions/info before I go raising as bugs/features, or attempting to produce a patch...

[Massimo Di Pierro]

Could this be a pyodbc issue? Are you sure web2py hangs or only this one session hangs? You can try from two separate browsers. It is possible pyodbc does not return (because for example a circular reference is on delete cascade) and web2py keeps the session blocked. All requests from this user/session are queued.

[Andrew Buchan]

Massimo, 

I tried opening a base url from IE when it had crashed in a session in Chrome and got no response, so assume it is indeed web2py that crashed. This was with a field which says ondelete='NO ACTION'. Can we not infer from the fact pyodbc raised an Exception that was caught by web2py that control did indeed return to web2py, or is that not correct? 

Some info:

web2py™ Version 2.5.1-stable+timestamp.2013.06.06.15.39.19

Traceback (most recent call last):

  File "/home/mdipierro/make_web2py/web2py/gluon/restricted.py", line 212, in restricted

  File "N:/IT/Dev/Code/HubForms/web2py/applications/HubForms/models/7_models_boots_database.py", line 104, in <module>

  File "/home/mdipierro/make_web2py/web2py/gluon/dal.py", line 7788, in define_table

  File "/home/mdipierro/make_web2py/web2py/gluon/dal.py", line 7825, in lazy_define_table

  File "/home/mdipierro/make_web2py/web2py/gluon/dal.py", line 991, in create_table

  File "/home/mdipierro/make_web2py/web2py/gluon/dal.py", line 1094, in migrate_table

  File "/home/mdipierro/make_web2py/web2py/gluon/dal.py", line 1784, in execute

  File "/home/mdipierro/make_web2py/web2py/gluon/dal.py", line 1778, in log_execute

ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]The object 'boots_project_rfi_testing__constraint' is dependent on column 'testing'. (5074) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE DROP COLUMN testing failed because one or more objects access this column. (4922)")

--
 
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/yaW04rGM-gY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Massimo Di Pierro]

You are right. Not sure why this happens but I will look into it. Could you please open a ticket so this is tracked?