Forums

I realize this is a known issue and I've read about it a lot and I'm extremely confused. This is my first web app so please bear with me. I have an app where a user logs in and a schedule is queried from the database. The user then selects certain events and submits their selections back to the db. Sometimes users just view the schedule and submit no selections at that time. Whether selections are made or not, after a certain amount of time, if a user tries to login and see the schedule, the 2006 error occurs. From my reading I "think" what's happening is the query is not being closed and is timing out. I built this app following different tutorials on YouTube plus, again, I'm extremely new to all this and it definitely is hacked together. I built the app with Postgres in mind. I'm using the free account and I have no experience with MySQL. I'm using Python 3.5 and Flask. I was considering moving to a paid plan down the road, so my first question is if I'm using Postgres will this still be an issue? Secondly, is there an "easy" fix to this with MySQL that someone very new can understand? There seems to be some success resolving this but I'm having serious difficulty understanding it. Here some of my code. Not sure if you need more or not. Any help is greatly appreciated.

<code>
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] =     ('mysql+mysqldb://username:password@
username.mysql.pythonanywhere-services.com/database')
db = SQLAlchemy(app)
app.config['SQLALCHEMY_POOL_RECYCLE'] = 280
app.config['SQLALCHEMY_POOL_TIMEOUT'] = 20
app.config['SECRET_KEY'] = 'secret'
app.config['SECURITY_REGISTERABLE'] = True
Base = declarative_base()

class NFL(db.Model):
__tablename__ = 'NFL01'

index = db.Column('index', db.Integer, primary_key=True)
KICKOFF = db.Column('KICKOFF', db.DateTime)
AWAY_TEAM = db.Column('AWAY_TEAM', db.Unicode)
AWAY_SIDE = db.Column('AWAY_SIDE', db.Integer)
HOME_TEAM = db.Column('HOME_TEAM', db.Unicode)
HOME_SIDE = db.Column('HOME_SIDE', db.Integer)
TOTAL = db.Column('TOTAL', db.Integer)


class CFB(db.Model):
__tablename__ = 'CFB01'

index = db.Column('index', db.Integer, primary_key=True)
KICKOFF = db.Column('KICKOFF', db.DateTime)
AWAY_TEAM = db.Column('AWAY_TEAM', db.Unicode)
AWAY_SIDE = db.Column('AWAY_SIDE', db.Unicode)
HOME_TEAM = db.Column('HOME_TEAM', db.Unicode)
HOME_SIDE = db.Column('HOME_SIDE', db.Unicode)
TOTAL = db.Column('TOTAL', db.Integer)

statsNFL = NFL.query.filter(NFL.KICKOFF > cur_time)
statsCFB = CFB.query.filter(CFB.KICKOFF > cur_time)
</code>

That error normally happens if the SQLALCHEMY_POOL_RECYCLE variable isn't set correctly. I can see that you've got some code to set it to the correct value, but that is after you've initialised the SQLAlchemy object -- and I think that it only looks at parameters that were set when it was created.

So if you re-order the start of your code so that it goes:

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = ('mysql+mysqldb://username:password@username.mysql.pythonanywhere-services.com/database')
app.config['SQLALCHEMY_POOL_RECYCLE'] = 280
app.config['SQLALCHEMY_POOL_TIMEOUT'] = 20
app.config['SECRET_KEY'] = 'secret'
app.config['SECURITY_REGISTERABLE'] = True
db = SQLAlchemy(app)

...then it should work.

Thank you very much. I haven't had any issues since making the change. Your help is greatly appreciated.

Excellent! Glad I could help :-)

Hi all, we now have a help page on managing mysql connections, which includes info on the mysql server has gone away error 2006