Forums

Hi,

I've been reading the forums, but haven't found what might be going wrong. I'm using Flask and Flask SQL-alchemy. It looks like SQL-alchemy should handle the potential time-out issues. Hitting the back putting and repeating the post has worked to fix things so far, but I'd like a better fix. Thoughts? (I'm familiar with python, but not really with SQL).

Error:

OperationalError: (OperationalError) (2013, 'Lost connection to MySQL server during query') 'INSERT INTO user (`Add_to_Avg`, `Submitted_Comments`, username, date_time, `Ask_percent_correct`, `Learn_percent_correct`, `Look_percent_correct`, `Play_percent_correct`, `Think_percent_correct`, `Fuse_percent_correct`, `Choose_percent_correct`, `Make_percent_correct`, `Asks_json`, `Learns_json`, `Looks_json`, `Plays_json`, `Thinks_json`, `Fuses_json`, `Chooses_json`, `Makes_json`, `QuizComments`, `WebsiteComments`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)' (1, 0, '', datetime.datetime(2013, 12, 8, 22, 20, 8, 767414), 83.33333333333333, 25.0, 50.0, 83.33333333333333, 50.0, 100.0, 16.666666666666668, 66.66666666666667, '"[\\"1\\", \\"0\\", \\"1\\", \\"1\\", \\"1\\", \\"1\\"]"', '"[\\"0\\", \\"0\\", \\"1\\", \\"0\\", \\"1\\", \\"0\\", \\"0\\", \\"0\\"]"', '"[\\"1\\", \\"1\\", \\"1\\", \\"0\\", \\"0\\", \\"0\\"]"', '"[\\"1\\", \\"1\\", \\"0\\", \\"1\\", \\"1\\", \\"1\\"]"', '"[\\"1\\", \\"0\\", \\"1\\", \\"0\\", \\"1\\", \\"0\\"]"', '"[\\"1\\", \\"1\\", \\"1\\", \\"1\\", \\"1\\", \\"1\\"]"', '"[\\"0\\", \\"1\\", \\"0\\", \\"0\\", \\"0\\", \\"0\\"]"', '"[\\"0\\", \\"1\\", \\"0\\", \\"1\\", \\"1\\", \\"1\\"]"', '', '')

[edit by admin: formatting]

Could you provide more information:

• What is your code doing when this happens? (besides the obvious SQL INSERT)
• How do you learn it timed out? Is it in the logs or something you are getting immediately in your browser?
• What is the timing of these happenings?
• Was this something that was working for you and recently broke? If yes when & have you made changes to related parts of your code?
• Other ideas along this line of thinking that could be helpful.

TIA for the feedback...☺

a2j thanks for asking and thanks to anyone for input on how I might better handle this type of error situation. Responses:

What is your code doing when this happens? (besides the obvious SQL INSERT)

Handling a post method from the 52 question form (yes/no radio buttons and a name).

How do you learn it timed out? Is it in the logs or something you are getting immediately in your browser?

I saw an error when I hit submit on a POST. When I went back and re-submitted it went away. Soon after a user reported the same behavior and noted:

"The server encountered an internal error and was unable to complete your request. Either the server is overloaded or there is an error in the application."

which corresponded to the errors in the logs. Since then I haven't changed anything in the code, but it appears the error hasn't happened again.

So I wonder if it it a sporadic error in my code; that my code isn't as robust against db issues as it needs to be; or PA had some brief error.

What is the timing of these happenings?

So far just 3 times.

Was this something that was working for you and recently broke? If yes when & have you made changes to related parts of your code?

It's hard to say given the error has been sporadically seen. I'd developed the code a bit on PA. Then I switched to developing on my home machine using sqlite (where I never saw the error or problem). Then I uploaded the code, switched back to mysql, set up the database. The errors happened relatively soon after that.

Stoph

We do have a timeout on database connections to prevent users from simply opening one and forgetting about it. That means that some connection management is necessary. As far as I understand, sqlalchemy does do it for you, but you have to configure it (i.e. the default is that it doesn't). If your errors tend to happen after a period of inactivity, that would support the hypothesis that you're bumping into our connection timeout.

@glenn: There goes my hope of blaming MySQL and telling him to switch to PostgreSQL...☺

~a2j

Glenn,

Can you say more about the configuration required? I imagine, you're suggest would relate to an alternative way of creating the engine, but not sure what would need to be set.

I'm using Flask-SQLAlchemy which handles (and hides) most of these issues. For instance:

"You have to commit the session, but you don’t have to remove it at the end of the request, Flask-SQLAlchemy does that for you." from Flask-SQLAlchemy

Thanks!

Stoph

@Stophlong: Does this send you in the right direction?

@Glenn: yes, thanks the error appears to be timeout to be related. I've let the connection sit without submitting the form and both times I've received the same error. And when I say sit I mean less than a hour or so.

1. I also updated flask from 0.9 to 0.10.1 to see if Flask updates have helped this. [Edit: It didn't.]

2. It appears that Flask might have some specific issues related to this. If the update doesn't work then I may try following this stackoverflow advice which includes trying:

   app.config['SQLALCHEMY_POOL_RECYCLE'] = 7200

@A2J: Thanks! I'm digging around in that as well as the Flask-sqlalchemy ones. Hopefully it'll come together.

I think you've got it there -- the SQLALCHEMY_POOL_RECYCLE setting is almost certainly the right one to fix this issue. I'd suggest you set it to 270, though. The number is the number of seconds it will keep connection before assuming that it's dead, and PythonAnywhere's SQL connections die after 300 seconds of inactivity. So 270 should make sure that you'll never try to use one that's expired.

[edit: timeout number was wrong]

@giles,

Thanks.

A. Sadly, that doesn't seem to have done the trick. I've viewing the Flask-SQLAlchemy docs to see if I can find the issue.

B. I've now tried the following:

app.config['SQLALCHEMY_POOL_RECYCLE'] = 7200

and separately:

app.config['SQLALCHEMY_POOL_RECYCLE'] = 499

and separately:

app.config['SQLALCHEMY_POOL_RECYCLE'] = 7200
app.config['SQLALCHEMY_POOL_TIMEOUT'] = 20

and separately:

app.config['SQLALCHEMY_POOL_RECYCLE'] = 499
app.config['SQLALCHEMY_POOL_TIMEOUT'] = 20

C. Unless I find another thing to try related to the above, I'll put the db connection in a try/except loop as mentioned in a different context (here)[https://www.pythonanywhere.com/wiki/ManagingDatabaseConnections].

try:
    admin = User(stuff)
    db.session.add(admin)
    db.session.commit()
except (AttributeError, MySQLdb.OperationalError):
    #the right refresh thing here for flask sql-alchemy
    admin = User(stuff)
    db.session.add(admin)
    db.session.commit()

That's really strange, I would have expected that to work. One possibility -- did you reload your web app after making each change? Sorry if that's an obvious thing, but it's surprisingly easy to forget...

@giles:

I'm 99.9% sure I reloaded it (yes, it is easy to forget). Happily now that I've added the try/except code, it appears stable! Unless there is a reason to avoid that try/except approach I'm going to leave it be! So my final code is the following.

app.config['SQLALCHEMY_POOL_RECYCLE'] = 499
app.config['SQLALCHEMY_POOL_TIMEOUT'] = 20

....

try:
    admin = User(stuff)
    db.session.add(admin)
    db.session.commit()
except (AttributeError, MySQLdb.OperationalError):
    admin = User(stuff)
    db.session.add(admin)
    db.session.commit()

Thanks to everyone for the guidance!

OK! Glad it works now, anyway :-)

I'm having the same problem with PythonAnywhere and Flask-SQLAlchemy. It's in the .../files/var/log/hostname.pythonanywhere.com.error.log:

OperationalError: (OperationalError) (2013, 'Lost connection to MySQL server during query') 'SELECT count(*) AS ...

My site doesn't work if I click a link for the first time in an hour or so ("internal server error"), and then if I push refresh it works.

Shouldn't this work automatically without using a Try Except in our program? We have to add a retry ourselves whenever the database is accessed?

SQLAlchemy is written so that you have a lot of control over how your database is used and their default behviour is not the best for PythonAnywhere, so you have to do a little extra work.

Have a look at this page in the SQLAlchemy docs and see if that helps. It will mean that you don't have to wrap every database access in a try..except.