I've written a program in Python 2.7, using SQLalchemy, on an Ubuntu 16.04.5 machine, with PHP 7.2.15-1+ubuntu16.04.1+deb.sury.org+1, and mysql server version 5.7.25.
The program seems to run perfectly - I'm able to query, compare, and store database data. And the operation works fast! I was so pleased, until I realized a high number of aborted_connect Aborted_clients and Aborted_connects.
All connections to the database are being done from this same machine. I am not connecting to the database from an external IP.
So, suspecting that perhaps I'd forgotten to close some sessions in my long code, I began deconstructing my program and testing it. As I removed parts, the error continued, until I reached the near-absolutely minimum. This is now the whole program that's running, and yielding aborted_connects and aborted_clients.
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.pool import NullPool
from sqlalchemy import (Table, Column, Integer, String, DateTime, ForeignKey, schema, func)
from sqlalchemy.ext.declarative import declarative_base
from datetime import date, timedelta, datetime
from sqlalchemy.orm import relationship, backref
engine = create_engine('mysql+pymysql://landingpages-username:password/Database?unix_socket=/var/run/mysqld/mysqld.sock', pool_recycle=3600, echo=False)
conn = engine.connect()
Base = declarative_base()
Session = sessionmaker(bind=engine)
class LandingPagesSimplifi(Base):
__tablename__ = 'landingpages_simplifi'
id = Column(Integer, primary_key=True)
client_id = Column(Integer(), ForeignKey('landingpages_clients.id'))
client_id_simple = Column(String(128), nullable=True)
campaign_id = Column(Integer, nullable=True)
campaign_name = Column(String(256), nullable=True)
ad_id = Column(Integer, nullable=True)
ad_url = Column(String(512), nullable=True)
ad_url_utm = Column(String(512), nullable=True)
created_on = Column(DateTime(),default=datetime.now)
class LandingPagesClients(Base):
__tablename__ = 'landingpages_clients'
id = Column(Integer(), primary_key=True)
name = Column(String(256))
id_google = Column(String(14), nullable=True)
id_facebook = Column(String(64), nullable=True)
id_simplifi = Column(Integer(), nullable=True)
user = Column(Integer(), ForeignKey('landingpages_users.id'))
class LandingPagesUsers(Base):
__tablename__ = 'landingpages_users'
id = Column(Integer(), primary_key=True)
name = Column(String(128))
email = Column(String(256))
session = Session()
first_record = session.query(LandingPagesSimplifi).first()
print first_record.id;
session.close()
No surprise, this returns '1'. Without error.
But if I run sudo tail /var/log/mysql/error.log immediately after this, I will see:
[Note] Aborted connection 212 to db: 'LandingPages' user: 'username' host: 'localhost' (Got an error reading communication packets)
Doing my very best to troubleshoot matters before coming to Stackoverflow, I've tried the following, and in each case immediate error still occurs after running the program.
First off I tried changing the host and socket for the engine, from...
engine = create_engine('mysql+pymysql://landingpages-username:password/Database?unix_socket=/var/run/mysqld/mysqld.sock', pool_recycle=3600, echo=False)
to
engine = create_engine('mysql+pymysql://localhost/landingpages-username:password/Database', pool_recycle=3600, echo=False)
It yields the answer '1', but creates the same error.
or to
engine = create_engine('mysql+pymysql://127.0.0.1/landingpages-username:password/Database', pool_recycle=3600, echo=False)
Which also yields '1', but creates this error. The following additional actions will yield '1', but still cause this error.
If I remove the pool_recycle bit.
Alter the create_engine so it connects to a different database on this server.
Connect to the database using different authorized credentials.
Changing the bind address to my machine's external IP, to 127.0.0.1, to 0.0.0.0, and commenting this line out completely.
The only thing which seems to help is if I include from sqlalchemy.pool import NullPool to my program, and add poolclass=NullPool to the create_engine arguments. But I really want to be using a pool if I can, right?
Still, this probably gives a clue as to where the problem lies. Yet, I can find no help by researching on my own. I must not asking the right question. Does anyone have any suggestion about where the problem might lie?
One other important bit of info: This same sql server is hosting two other databases which are accessed locally by this machine (via a Laravel php web app), and 0 aborted_connect or aborted_clients come from that program.
Other important info:
show global variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 28800 |
+-----------------------------+----------+
13 rows in set (0.00 sec)
Many thanks in advance for any help.
