Forums

Bottle web app for reading out of database, struggling with "(2006, 'MySQL server has gone away')"

Hi all,

I've been struggling with a MySQL server getting the (2006, 'MySQL server has gone away') error.

Just to give a little background I'm a self-taught "hobby" programmer. I want to put all my learning to the test so I'm building a Kivy app that marks bathrooms near you. I'm running a Kivy app that uses Mapview to generate a map and place markers on itself. When the Map is being initialized it uses Python 'requests' to call to the Bottle web app which makes and returns a database query. (This is my first time doing this so I'm sure I'm missing something obvious or doing something inefficient)

So the first time I run the app it works fine. The Kivy app makes a request to the Bottle app, I use ".json()" on the return and read lat/lon coordinates out of.to place a marker on the Map.... BUT if I wait a little while, close the app, and try to do it again (or just access the website normally) I get a "500 internal server error". When I check the error log it shows (2006, 'MySQL server has gone away').

I only have 1 row in 1 table in MySQL database so I'm assuming it isn't a size issue. I'm assuming there is some way I'm not closing the connection (although I tried to put in fail safes for this).

The relevant code snippet from my Kivy app and my entire Bottle app are shown below. Any help is appreciated and I'll check frequently to provide any additional information if needed. Thanks!

REQUEST FUNCTION FROM KIVY (snippet):

def find_me():
    with requests.Session() as s:
        query_dict = s.get('http://MY URL').json()
    my_info = (query_dict['1']['name'], query_dict['1']['description'], query_dict['1']['lon'], query_dict['1']['lat'])
    return my_info

BOTTLE APP CONTAINING THE QUERY:

from bottle import default_app, route, hook
from peewee import *
from babybottom import BoyBathroom

db = MySQLDatabase(*ALL DATABSE INFO ENTERED CORRECTLY HERE*)

@hook('before_request')
def _connect_db():
    db.connect()


@hook('after_request')
def _close_db():
    if not db.is_closed():
        db.close()

@route('/')
def hello_world():
    test = BoyBathroom.select().where(BoyBathroom.name == 'Nick House').get()
    info_dict = {test.id:{'name':test.name, 'description':test.description,'lon':test.lon, 'lat':test.lat}}
    return info_dict


application = default_app()

Hmm, interesting. The problem is the one described on this help page, but I'd expect your after_request handler to fix it. On this Peewee help page they seem to be suggesting that you simply close the database in your after_request handler without checking if it's already open -- that is:

@hook('after_request')
def _close_db():
    db.close()

Maybe you could try that and see if it helps?

EDIT 3: And my EDIT2 theory was incorrect. Problem still persists. Seems to only occur after a long-ish gap between requests. For example, I can Run and Exit the Kivy app (making the request every time) multiple times within a minute and have no issues but if I wait a few minutes in between requests then I get the "2006 MySQL error" and the website shows a "500 Internal Server error"

EDIT 2: So I just had a possible realization that I'm only getting this issue after I physically go to my PythonAnywhere website URL via my browser. When I'm just requesting it via the app it doesn't seem to be an issue. I will keep testing this. I'm new to using web apps to query databases, could the accessing via browser versus via python 'requests' be an issue? Does the browser not properly end the request even after I close the tab?

EDIT: Tried your suggestion and unfortunately still got the same error [ _mysql_exceptions.OperationalError: (2006, 'MySQL server has gone away') ]

@giles

Thanks for the suggestions! I believe I tried that before but I don't remember so I'm going to try it again and see if I get the same error.

Some comments: edit 2: No - how you access the web app will not change how the database is accessed by the web app.

edit 1: Have you confirmed that the after_request code is actualyy run after each request?

Perhaps you could try moving the code that instantiates the database object into the before_request handler.

@glenn

I took your suggestion and tried it along with a few other small fixes I found online. None of which worked. I even put my own decorator on there and can confirm that it is closing the database. (db.is_closed returns True)

So is there a possibility that the error is caused by something other than the database not being closed?

So I'm way out of my element looking through these error logs but:

I added a print to print the status of the database (True if closed).

1. I Reloaded the server at "20:09:38" (which is the first entry in the below log)

2. At 20:09:40 the decorator print ( Db is closed via wrapper: True ) printed. I never attempted to access the website via app or browser. Is this automatic?

3. My first request to the website via the app was at 20:11:05, which generated the "announcing my loyalty to the Emperor". All subsequent requests generated nothing in the server log. My thinking is I would expect to see my print statement (among I assume other things), every time I make a request?

2017-09-25 20:09:38 Mon Sep 25 20:09:38 2017 - received message 1 from emperor

2017-09-25 20:09:38 ...gracefully killing workers...

2017-09-25 20:09:38 Gracefully killing worker 1 (pid: 30556)...

2017-09-25 20:09:38 Gracefully killing worker 2 (pid: 30559)...

2017-09-25 20:09:40 *** Starting uWSGI 2.0.14 (64bit) on [Mon Sep 25 20:09:40 2017] ***

2017-09-25 20:09:40 compiled with version: 5.4.0 20160609 on 30 June 2017 17:32:30

2017-09-25 20:09:40 os: Linux-4.4.0-83-generic #106-Ubuntu SMP Mon Jun 26 17:54:43 UTC 2017

2017-09-25 20:09:40 nodename: harry-liveweb1

2017-09-25 20:09:40 machine: x86_64

2017-09-25 20:09:40 clock source: unix

2017-09-25 20:09:40 pcre jit disabled

2017-09-25 20:09:40 detected number of CPU cores: 2

2017-09-25 20:09:40 current working directory: (unreachable)/etc/uwsgi/vassals

2017-09-25 20:09:40 detected binary path: /usr/local/bin/uwsgi

2017-09-25 20:09:40 *** dumping internal routing table ***

2017-09-25 20:09:40 [rule: 0] subject: path_info regexp: .svgz$ action: addheader:Content-Encoding:gzip

2017-09-25 20:09:40 [rule: 1] subject: path_info regexp: ^/ action: basicauth:Default Realm,babykivy:knockknock469

2017-09-25 20:09:40 *** end of the internal routing table ***

2017-09-25 20:09:40 chdir() to /home/nickcook530/

2017-09-25 20:09:40 limiting number of processes to 30...

2017-09-25 20:09:40 your processes number limit is 30

2017-09-25 20:09:40 your memory page size is 4096 bytes

2017-09-25 20:09:40 detected max file descriptor number: 123456

2017-09-25 20:09:40 building mime-types dictionary from file /etc/mime.types...

2017-09-25 20:09:40 536 entry found

2017-09-25 20:09:40 lock engine: pthread robust mutexes

2017-09-25 20:09:40 thunder lock: disabled (you can enable it with --thunder-lock)

2017-09-25 20:09:40 uwsgi socket 0 bound to UNIX address /var/sockets/nickcook530.pythonanywhere.com/socket fd 3

2017-09-25 20:09:40 Python version: 3.6.0 (default, Jan 13 2017, 00:00:00) [GCC 4.8.4]

2017-09-25 20:09:40 *** Python threads support is disabled. You can enable it with --enable-threads ***

2017-09-25 20:09:40 Python main interpreter initialized at 0xb49300

2017-09-25 20:09:40 your server socket listen backlog is limited to 100 connections

2017-09-25 20:09:40 your mercy for graceful operations on workers is 60 seconds

2017-09-25 20:09:40 setting request body buffering size to 65536 bytes

2017-09-25 20:09:40 mapped 500928 bytes (489 KB) for 2 cores

2017-09-25 20:09:40 *** Operational MODE: preforking ***

2017-09-25 20:09:40 initialized 50 metrics

2017-09-25 20:09:40 Db is closed via wrapper: True

2017-09-25 20:09:40 WSGI app 0 (mountpoint='') ready in 0 seconds on interpreter 0xb49300 pid: 30653 (default app)

2017-09-25 20:09:40 *** uWSGI is running in multiple interpreter mode ***

2017-09-25 20:09:40 gracefully (RE)spawned uWSGI master process (pid: 30653)

2017-09-25 20:09:40 spawned uWSGI worker 1 (pid: 30655, cores: 1)

2017-09-25 20:09:40 spawned 2 offload threads for uWSGI worker 1

2017-09-25 20:09:40 spawned uWSGI worker 2 (pid: 30660, cores: 1)

2017-09-25 20:09:40 metrics collector thread started

2017-09-25 20:09:40 spawned 2 offload threads for uWSGI worker 2

2017-09-25 20:09:43 announcing my loyalty to the Emperor...

2017-09-25 20:11:05 announcing my loyalty to the Emperor...

Again, super out of my element here but any help is appreciated

Depending on how your framework is configured, you may need to send your prints to stderr for them to appear. In that case, they may appear in your server log or your error log. Also, since we're doing this to check if code is running, you may not be seeing them because the code is not running.

@glenn

Thanks for your suggestions. So it appears I "fixed" it last night. Instead of importing my database classes (tables) from a separate .py document I moved them all into my main bottle.py file and it has been working fine with no errors since last night.

I'm not sure why this solved the problem but it has so that's good for the short term, although I would like to eventually understand the "why" =)

Thanks everyone!