Forums

How can I increase max allowed size of packet for MySQL ?

Hi,

I'm encountering problem with MySQL. I'm getting error Lost connection to MySQL server during query. On StackOverflow they say that very likely max_allowed_packet in MySQL config is too low.

I've tried to change it in /etc/mysql/my.cnf but the file isn't editable.

What can I do ?

deleted-user-7794456 | 25 posts | March 19, 2021, 11:22 a.m. | permalink

Is the query taking a long time, or do you get this error immediately?

Staff pafk | 2973 posts | PythonAnywhere staff | March 19, 2021, 11:32 a.m. | permalink

Immediately.

deleted-user-7794456 | 25 posts | March 19, 2021, 11:37 a.m. | permalink

Could you show us the code you're using to connect and the full error message?

Staff pafk | 2973 posts | PythonAnywhere staff | March 19, 2021, 11:49 a.m. | permalink

I'm using SQLAlchemy:

config.py:

class ProductionConfig(BaseConfig):
    SQLALCHEMY_DATABASE_URI = 'mysql://markalexa:iamtheone@markalexa.mysql.pythonanywhere-services.com/markalexa$blog'

init.py:

import os
from logging import FileHandler
from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy

if os.environ.get('FLASK_ENV') == 'production':
    from project.config import ProductionConfig
    current_config = ProductionConfig
elif os.environ.get('FLASK_ENV') == 'staging':
    from project.config import StagingConfig
    current_config = StagingConfig


db = SQLAlchemy()
...
...


def create_app(script_info=None):
    if os.environ.get('FLASK_ENV') == 'production':
         app = Flask(__name__, template_folder='/home/markalexa/blog/client/build', static_folder='/home/markalexa/blog/client/build/static')
        file_handler = FileHandler('/home/markalexa/blog/project/errorlog.txt')
    elif os.environ.get('FLASK_ENV') == 'staging':
         app = Flask(__name__, template_folder='/home/pyseostaging/staging/client/build', static_folder='/home/pyseostaging/staging/client/build/static')
        file_handler = FileHandler('/home/pyseostaging/staging/project/errorlog.txt')
    app.logger.addHandler(file_handler)
    app.config.from_object(current_config)
    db.init_app(app)
    ...
    ...

    return app


app = create_app()

creating entry:

new_post = Posts( ... )
db.session.add(new_post)
db.session.commit()
deleted-user-7794456 | 25 posts | March 19, 2021, 12:05 p.m. | permalink

Error output:

Traceback (most recent call last):
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (2013, 'Lost connection to MySQL server during query')
**NO MATCH**
The above exception was the direct cause of the following exception:
**NO MATCH**
Traceback (most recent call last):
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/flask/app.py", line 2447, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/flask/app.py", line 1952, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/flask/app.py", line 1821, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/flask/app.py", line 1950, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/flask/app.py", line 1936, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/home/markalexa/blog/project/api/blog.py", line 203, in blog_posts
    db.session.commit()
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/sqlalchemy/orm/scoping.py", line 163, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1046, in commit
    self.transaction.commit()
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 504, in commit
    self._prepare_impl()
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 483, in _prepare_impl
    self.session.flush()
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2540, in flush
    self._flush(objects)
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2682, in _flush
    transaction.rollback(_capture_exception=True)
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2642, in _flush
    flush_context.execute()
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
    rec.execute(self)
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 586, in execute
    persistence.save_obj(
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 239, in save_obj
    _emit_insert_statements(
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 1135, in _emit_insert_statements
    result = cached_connections[connection].execute(
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
    ret = self._execute_context(
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/home/markalexa/blog/venv/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (2013, 'Lost connection to MySQL server during query')
[SQL: INSERT INTO posts (title, post1, post2, code1, code2, footer, category, slug, post_image1, post_image2, post_image3, post_image1_alt, post_image2_alt, post_image3_alt, post_image1_m, post_image2_m, post_image3_m, card_image, card_image_m, card_img_desc, timestamp) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)]
deleted-user-7794456 | 25 posts | March 19, 2021, 12:14 p.m. | permalink

Can anyone please give me a hand here ? I've got work I need to deliver and I'm stuck on database interaction. This seems awful lot like PA-specific problem as I can't change the config file as I need.

deleted-user-7794456 | 25 posts | March 19, 2021, 3 p.m. | permalink

We have a help page on using sqlalchemy with MySQL: https://help.pythonanywhere.com/pages/UsingSQLAlchemywithMySQL/

Staff glenn | 9498 posts | PythonAnywhere staff | March 19, 2021, 3:17 p.m. | permalink

Thank you, glenn. I added SQLALCHEMY_POOL_RECYCLE = 280 to my config and it works !

deleted-user-7794456 | 25 posts | March 19, 2021, 4:17 p.m. | permalink

Excellent, thanks for confirming!

Staff giles | 11800 posts | PythonAnywhere staff | March 19, 2021, 5:01 p.m. | permalink