Database in inconsistent state in user data (login in a web server) retrieval

[Hector Blanco]

Hello everyone!

I am currently working with a webserver (Grok) that starts different
threads (automatically) for the requests that arrive to it.

The information is serialized in a MySQL database (which is acceded
through SqlAlchemy). The users' information is stored in that MySQL
database. The plugin that said server uses to check if a user is
logged in or not is called very often. The way it is programmed now
(I'll improve that in the future) is: it goes to the database, tries
to extract the user whose "username" matches with the logged (or the
one trying to log) one and checks if the password stored in the
database matches with the one provided by the user.

For some reason, if a user cancels the login process (hits Esc in his
browser) and tries to login again, something (I don't know what)
crashes:

2010-12-23 13:45:50,841 WARNING [sqlalchemy.pool.QueuePool.0x...5450]
Error closing cursor: (2014, "Commands out of sync; you can't run this
command now")

I'm not sure if is is because the cancelling/reloading starts a new
thread (and messes up the transactions that were pending) or because
when the user cancels the loading, the username provided gets
"corrupted" (empty or something) or what.

This is what I do (as I said, very often) to load the user:

@staticmethod
def getByName(userName):
retval = None
try:
retval = Database.session.query(User.User).filter(User.User.userName
== userName).scalar()
finally:
Database.session.commit()
return retval

The session is a "global" object created in the Database.py file this way:

Session = scoped_session(sessionmaker(bind="..."))
session = Session()

I've tried restarting the server (to get a new session) flushing,
expunging, commiting... Even dropping and re-creating the schema.
Nothing seems to work. I don't know if it's because I'm trying to
access the database too often, or because if userName is None, it
leaves the session in a "weird" state... (as you can see, I don't
catch any exceptions... yet)

Does anyone have any idea of what can be going wrong? I know it's a
long shot, and that I'm not providing much information, but who
knows... maybe any of you has a hint or a "why don't you try this?..."
idea. Anything would be appreciated.

Thank you.

[Michael Bayer]

On Dec 23, 2010, at 2:09 PM, Hector Blanco wrote:

> Hello everyone!
>
> I am currently working with a webserver (Grok) that starts different
> threads (automatically) for the requests that arrive to it.
>
> The information is serialized in a MySQL database (which is acceded
> through SqlAlchemy). The users' information is stored in that MySQL
> database. The plugin that said server uses to check if a user is
> logged in or not is called very often. The way it is programmed now
> (I'll improve that in the future) is: it goes to the database, tries
> to extract the user whose "username" matches with the logged (or the
> one trying to log) one and checks if the password stored in the
> database matches with the one provided by the user.
>
> For some reason, if a user cancels the login process (hits Esc in his
> browser) and tries to login again, something (I don't know what)
> crashes:
>
> 2010-12-23 13:45:50,841 WARNING [sqlalchemy.pool.QueuePool.0x...5450]
> Error closing cursor: (2014, "Commands out of sync; you can't run this
> command now")

If connection resources are returned to the pool via garbage collection, this may happen in a distinct, deferred gc thread, producing errors like this. It is common for web app servers to throw some kind of interruption exception when the connection is unexpectedly closed. The error is caught and logged as a warning only and should be otherwise harmless. If you could apply a "finally:" block around connection interruption errors and cleanly close the session, that would probably alleviate the warnings.

[Hector Blanco]

Thank you for your quick reply!

I tried to change the method that grabs the user to:

def getByName(userName):
retval = None
try:
retval = Database.session.query(User.User).filter(
User.User.userName== userName
).scalar()
finally:

Database.session.close()
return retval

but it still doesn't seem to work.

Maybe it's a problem with threading... I setup the database (create
the tables, create and insert in the database a sample "test" user...)
using Google Chrome and I try to log in with Firefox. The database is
setup in one of the pages the server provides: I write
http://127.0.0.1/test/initdb as the address of the web page to load in
Chrome and when the page is rendered, the database is setup. After
that, I try to log in with the "test" user using Firefox and that's
when I get the "Error closing cursor: (2014, "Commands out of sync;
you can't run this command now")" I am using MySql administrator and
the values for that "test" user seem to be properly created properly.

Maybe when I create the database with Chrome Firefox doesn't "see" it properly?

To add a user, I have created a method "update" like this:

@staticmethod
def update(user):
if isinstance(user, User.User):
try:
if user.id:
#User already exists in the database
user=Database.session.merge(user)
else:
#User is new
user=Database.session.add(user)
Database.session.commit()
finally:
Database.session.close()
else:
raise TypeError("Received parameter %s of type %s when expecting
%s" % (user, type(user), User.User))

When (from Chrome) I invoke the page that creates the database, a new
User() instance is created, with a few default values (userName =
test, for instance) and it's added to the database using this "update"
method (said user doesn't have an id, so it should be added using
add(user) ). Then I try to login with the user "test" from Firefox and
it breaks...

2010/12/23 Michael Bayer <mik...@zzzcomputing.com>:

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>
>

[Michael Bayer]

it only has to do with what your appserver does when a connection is broken. The "finally" method is probably never called. You can change the Python warnings filter to emit those warnings as exceptions, in which you should be able to get stack traces in your logs.

[Hector Blanco]

Ok! I'll let you know...

Thank you so much!

It seems to fail less with the finally, though :)

2010/12/23 Michael Bayer <mik...@zzzcomputing.com>:

[Hector Blanco]

With that (catching all the errors) seems to work better.

It also seems that the "problem" improves if I wait a bit (4 or 5
seconds) after the server is started...

2010/12/23 Hector Blanco <white...@gmail.com>: