database operation fails after succsefull multi-processing.
196 views
Skip to first unread message
Geert Jan Talens
Aug 22, 2022, 4:21:18 PM8/22/22
to sqlalchemy
Hi,
I am trying to perform some database actions after multiprocessing some database manipulations but in most cases I get: sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (2006, 'MySQL server has gone away')
The code below is a working example of the problem I'm having, where the success of line 3 (in main) for some reason depends on what happens on line 1.
Any help would be appreciated!
Regards,
Geert Jan
Geert Jan
from sqlalchemy import create_engine, select
from sqlalchemy import Column, Integer
from sqlalchemy.orm import declarative_base, sessionmaker
import multiprocessing as mp
import numpy as np
Base = declarative_base()
Session = sessionmaker()
class MyTable(Base):
"""This table lists all observed frames."""
__tablename__ = 'mytable'
# Columns in the table.
id = Column(Integer, primary_key=True, autoincrement=False)
def insert_integer(integer):
with Session() as session:
record = MyTable(id=int(integer))
session.add(record)
session.commit()
return
def query_integer(integer):
with Session() as session:
statement = select(MyTable).where(MyTable.id == int(integer))
result = session.execute(statement).scalar_one_or_none()
return result
def database_action(integer):
result = query_integer(integer)
if result is None:
insert_integer(integer)
else:
print("integer already in database.")
return
def initializer(engine):
# Doesn't matter if I use on or the other.
engine.dispose(close=False)
# engine.pool = engine.pool.recreate()
def main():
# Database setup.
engine = create_engine("mysql+mysqldb://user:pass...@127.0.0.1/testdb")
Session.configure(bind=engine)
Base.metadata.drop_all(bind=engine)
Base.metadata.create_all(bind=engine)
# Line 1.
# If nothing happens on line 1, line 3 fails.
# insert_integer(0) # If I do this (insert-only) line 3 fails.
database_action(0) # If I do this (query, insert) line 3 works.
# Line 2.
with mp.Pool(2, initializer, (engine,)) as pool:
pool.map(database_action, np.arange(1, 11))
# Line 3.
database_action(20) # Fails if line 1 is insert_integer.
return
if __name__ == '__main__':
main()
from sqlalchemy import Column, Integer
from sqlalchemy.orm import declarative_base, sessionmaker
import multiprocessing as mp
import numpy as np
Base = declarative_base()
Session = sessionmaker()
class MyTable(Base):
"""This table lists all observed frames."""
__tablename__ = 'mytable'
# Columns in the table.
id = Column(Integer, primary_key=True, autoincrement=False)
def insert_integer(integer):
with Session() as session:
record = MyTable(id=int(integer))
session.add(record)
session.commit()
return
def query_integer(integer):
with Session() as session:
statement = select(MyTable).where(MyTable.id == int(integer))
result = session.execute(statement).scalar_one_or_none()
return result
def database_action(integer):
result = query_integer(integer)
if result is None:
insert_integer(integer)
else:
print("integer already in database.")
return
def initializer(engine):
# Doesn't matter if I use on or the other.
engine.dispose(close=False)
# engine.pool = engine.pool.recreate()
def main():
# Database setup.
engine = create_engine("mysql+mysqldb://user:pass...@127.0.0.1/testdb")
Session.configure(bind=engine)
Base.metadata.drop_all(bind=engine)
Base.metadata.create_all(bind=engine)
# Line 1.
# If nothing happens on line 1, line 3 fails.
# insert_integer(0) # If I do this (insert-only) line 3 fails.
database_action(0) # If I do this (query, insert) line 3 works.
# Line 2.
with mp.Pool(2, initializer, (engine,)) as pool:
pool.map(database_action, np.arange(1, 11))
# Line 3.
database_action(20) # Fails if line 1 is insert_integer.
return
if __name__ == '__main__':
main()
Mike Bayer
Aug 22, 2022, 7:19:15 PM8/22/22
to noreply-spamdigest via sqlalchemy
your program runs completely (thanks), and I can't reproduce any problem. changed line 1 as written to:
# Line 1.
# If nothing happens on line 1, line 3 fails.
insert_integer(0) # If I do this (insert-only) line 3 fails.
#database_action(0) # If I do this (query, insert) line 3 works.
and no issue. output is below. Send along a stack trace for more hints.
2022-08-22 19:16:02,322 INFO sqlalchemy.engine.Engine SELECT DATABASE()
2022-08-22 19:16:02,322 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-08-22 19:16:02,322 INFO sqlalchemy.engine.Engine SELECT @@sql_mode
2022-08-22 19:16:02,322 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-08-22 19:16:02,323 INFO sqlalchemy.engine.Engine SELECT @@lower_case_table_names
2022-08-22 19:16:02,323 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-08-22 19:16:02,323 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,324 INFO sqlalchemy.engine.Engine SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = %s AND table_name = %s
2022-08-22 19:16:02,324 INFO sqlalchemy.engine.Engine [generated in 0.00016s] ('test', 'mytable')
2022-08-22 19:16:02,325 INFO sqlalchemy.engine.Engine
DROP TABLE mytable
2022-08-22 19:16:02,325 INFO sqlalchemy.engine.Engine [no key 0.00012s] ()
2022-08-22 19:16:02,330 INFO sqlalchemy.engine.Engine COMMIT
2022-08-22 19:16:02,330 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,331 INFO sqlalchemy.engine.Engine SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = %s AND table_name = %s
2022-08-22 19:16:02,331 INFO sqlalchemy.engine.Engine [cached since 0.006941s ago] ('test', 'mytable')
2022-08-22 19:16:02,332 INFO sqlalchemy.engine.Engine
CREATE TABLE mytable (
id INTEGER NOT NULL,
PRIMARY KEY (id)
)
2022-08-22 19:16:02,333 INFO sqlalchemy.engine.Engine [no key 0.00140s] ()
2022-08-22 19:16:02,342 INFO sqlalchemy.engine.Engine COMMIT
2022-08-22 19:16:02,344 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,345 INFO sqlalchemy.engine.Engine INSERT INTO mytable (id) VALUES (%s)
2022-08-22 19:16:02,345 INFO sqlalchemy.engine.Engine [generated in 0.00021s] (0,)
2022-08-22 19:16:02,346 INFO sqlalchemy.engine.Engine COMMIT
2022-08-22 19:16:02,362 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,362 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,365 INFO sqlalchemy.engine.Engine SELECT mytable.id
FROM mytable
WHERE mytable.id = %s
2022-08-22 19:16:02,365 INFO sqlalchemy.engine.Engine SELECT mytable.id
FROM mytable
WHERE mytable.id = %s
2022-08-22 19:16:02,365 INFO sqlalchemy.engine.Engine [generated in 0.00024s] (1,)
2022-08-22 19:16:02,365 INFO sqlalchemy.engine.Engine [generated in 0.00024s] (3,)
2022-08-22 19:16:02,366 INFO sqlalchemy.engine.Engine ROLLBACK
2022-08-22 19:16:02,366 INFO sqlalchemy.engine.Engine ROLLBACK
2022-08-22 19:16:02,367 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,367 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,367 INFO sqlalchemy.engine.Engine INSERT INTO mytable (id) VALUES (%s)
2022-08-22 19:16:02,367 INFO sqlalchemy.engine.Engine [cached since 0.0218s ago] (3,)
2022-08-22 19:16:02,367 INFO sqlalchemy.engine.Engine INSERT INTO mytable (id) VALUES (%s)
2022-08-22 19:16:02,367 INFO sqlalchemy.engine.Engine [cached since 0.02189s ago] (1,)
2022-08-22 19:16:02,367 INFO sqlalchemy.engine.Engine COMMIT
2022-08-22 19:16:02,367 INFO sqlalchemy.engine.Engine COMMIT
2022-08-22 19:16:02,368 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,369 INFO sqlalchemy.engine.Engine SELECT mytable.id
FROM mytable
WHERE mytable.id = %s
2022-08-22 19:16:02,369 INFO sqlalchemy.engine.Engine [cached since 0.003665s ago] (4,)
2022-08-22 19:16:02,369 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,369 INFO sqlalchemy.engine.Engine SELECT mytable.id
FROM mytable
WHERE mytable.id = %s
2022-08-22 19:16:02,370 INFO sqlalchemy.engine.Engine [cached since 0.004593s ago] (2,)
2022-08-22 19:16:02,370 INFO sqlalchemy.engine.Engine ROLLBACK
2022-08-22 19:16:02,370 INFO sqlalchemy.engine.Engine ROLLBACK
2022-08-22 19:16:02,370 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,370 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,370 INFO sqlalchemy.engine.Engine INSERT INTO mytable (id) VALUES (%s)
2022-08-22 19:16:02,370 INFO sqlalchemy.engine.Engine [cached since 0.0254s ago] (4,)
2022-08-22 19:16:02,371 INFO sqlalchemy.engine.Engine INSERT INTO mytable (id) VALUES (%s)
2022-08-22 19:16:02,371 INFO sqlalchemy.engine.Engine [cached since 0.02555s ago] (2,)
2022-08-22 19:16:02,371 INFO sqlalchemy.engine.Engine COMMIT
2022-08-22 19:16:02,371 INFO sqlalchemy.engine.Engine COMMIT
2022-08-22 19:16:02,372 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,372 INFO sqlalchemy.engine.Engine SELECT mytable.id
FROM mytable
WHERE mytable.id = %s
2022-08-22 19:16:02,372 INFO sqlalchemy.engine.Engine [cached since 0.007373s ago] (5,)
2022-08-22 19:16:02,373 INFO sqlalchemy.engine.Engine ROLLBACK
2022-08-22 19:16:02,373 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,373 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,373 INFO sqlalchemy.engine.Engine SELECT mytable.id
FROM mytable
WHERE mytable.id = %s
2022-08-22 19:16:02,373 INFO sqlalchemy.engine.Engine [cached since 0.008211s ago] (7,)
2022-08-22 19:16:02,373 INFO sqlalchemy.engine.Engine INSERT INTO mytable (id) VALUES (%s)
2022-08-22 19:16:02,373 INFO sqlalchemy.engine.Engine [cached since 0.02819s ago] (5,)
2022-08-22 19:16:02,374 INFO sqlalchemy.engine.Engine ROLLBACK
2022-08-22 19:16:02,374 INFO sqlalchemy.engine.Engine COMMIT
2022-08-22 19:16:02,374 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,374 INFO sqlalchemy.engine.Engine INSERT INTO mytable (id) VALUES (%s)
2022-08-22 19:16:02,374 INFO sqlalchemy.engine.Engine [cached since 0.0291s ago] (7,)
2022-08-22 19:16:02,375 INFO sqlalchemy.engine.Engine COMMIT
2022-08-22 19:16:02,375 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,375 INFO sqlalchemy.engine.Engine SELECT mytable.id
FROM mytable
WHERE mytable.id = %s
2022-08-22 19:16:02,375 INFO sqlalchemy.engine.Engine [cached since 0.01013s ago] (6,)
2022-08-22 19:16:02,375 INFO sqlalchemy.engine.Engine ROLLBACK
2022-08-22 19:16:02,376 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,376 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,376 INFO sqlalchemy.engine.Engine INSERT INTO mytable (id) VALUES (%s)
2022-08-22 19:16:02,376 INFO sqlalchemy.engine.Engine [cached since 0.03086s ago] (6,)
2022-08-22 19:16:02,376 INFO sqlalchemy.engine.Engine SELECT mytable.id
FROM mytable
WHERE mytable.id = %s
2022-08-22 19:16:02,376 INFO sqlalchemy.engine.Engine [cached since 0.01102s ago] (8,)
2022-08-22 19:16:02,376 INFO sqlalchemy.engine.Engine COMMIT
2022-08-22 19:16:02,377 INFO sqlalchemy.engine.Engine ROLLBACK
2022-08-22 19:16:02,377 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,377 INFO sqlalchemy.engine.Engine INSERT INTO mytable (id) VALUES (%s)
2022-08-22 19:16:02,377 INFO sqlalchemy.engine.Engine [cached since 0.03192s ago] (8,)
2022-08-22 19:16:02,377 INFO sqlalchemy.engine.Engine COMMIT
2022-08-22 19:16:02,378 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,378 INFO sqlalchemy.engine.Engine SELECT mytable.id
FROM mytable
WHERE mytable.id = %s
2022-08-22 19:16:02,378 INFO sqlalchemy.engine.Engine [cached since 0.01299s ago] (9,)
2022-08-22 19:16:02,378 INFO sqlalchemy.engine.Engine ROLLBACK
2022-08-22 19:16:02,379 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,379 INFO sqlalchemy.engine.Engine INSERT INTO mytable (id) VALUES (%s)
2022-08-22 19:16:02,379 INFO sqlalchemy.engine.Engine [cached since 0.03381s ago] (9,)
2022-08-22 19:16:02,379 INFO sqlalchemy.engine.Engine COMMIT
2022-08-22 19:16:02,381 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,381 INFO sqlalchemy.engine.Engine SELECT mytable.id
FROM mytable
WHERE mytable.id = %s
2022-08-22 19:16:02,381 INFO sqlalchemy.engine.Engine [cached since 0.01605s ago] (10,)
2022-08-22 19:16:02,381 INFO sqlalchemy.engine.Engine ROLLBACK
2022-08-22 19:16:02,382 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,382 INFO sqlalchemy.engine.Engine INSERT INTO mytable (id) VALUES (%s)
2022-08-22 19:16:02,382 INFO sqlalchemy.engine.Engine [cached since 0.03694s ago] (10,)
2022-08-22 19:16:02,383 INFO sqlalchemy.engine.Engine COMMIT
2022-08-22 19:16:02,387 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,388 INFO sqlalchemy.engine.Engine SELECT mytable.id
FROM mytable
WHERE mytable.id = %s
2022-08-22 19:16:02,388 INFO sqlalchemy.engine.Engine [generated in 0.00011s] (20,)
2022-08-22 19:16:02,389 INFO sqlalchemy.engine.Engine ROLLBACK
2022-08-22 19:16:02,389 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,389 INFO sqlalchemy.engine.Engine INSERT INTO mytable (id) VALUES (%s)
2022-08-22 19:16:02,389 INFO sqlalchemy.engine.Engine [cached since 0.04428s ago] (20,)
2022-08-22 19:16:02,390 INFO sqlalchemy.engine.Engine COMMIT
--SQLAlchemy -The Python SQL Toolkit and Object Relational MapperTo post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.---You received this message because you are subscribed to the Google Groups "sqlalchemy" group.To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/5597b069-3725-44de-88c4-4964d95d84cen%40googlegroups.com.
Geert Jan Talens
Aug 23, 2022, 11:22:03 AM8/23/22
to sqlalchemy
Hello Mike,
database_action(20) # Fails if line 1 is insert_integer.
sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (2006, 'MySQL server has gone away')
Thank you for your reply. The traceback of the issue is below. I'm using sqlalchemy 1.4.40 and MySQLdb 2.1.1, the problem does not occur when using sqlite for the database instead.
Regards,
Geert Jan
Geert Jan
Traceback (most recent call last):
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
self.dialect.do_execute(
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/MySQLdb/cursors.py", line 206, in execute
res = self._query(query)
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/MySQLdb/cursors.py", line 319, in _query
db.query(q)
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/MySQLdb/connections.py", line 259, in query
_mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (2006, 'MySQL server has gone away')
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/talens/Research/HATPI/Projects/testing/testing/database_mp.py", line 84, in <module>
main()
File "/home/talens/Research/HATPI/Projects/testing/testing/database_mp.py", line 78, in main
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
self.dialect.do_execute(
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/MySQLdb/cursors.py", line 206, in execute
res = self._query(query)
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/MySQLdb/cursors.py", line 319, in _query
db.query(q)
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/MySQLdb/connections.py", line 259, in query
_mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (2006, 'MySQL server has gone away')
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/talens/Research/HATPI/Projects/testing/testing/database_mp.py", line 84, in <module>
main()
File "/home/talens/Research/HATPI/Projects/testing/testing/database_mp.py", line 78, in main
database_action(20) # Fails if line 1 is insert_integer.
File "/home/talens/Research/HATPI/Projects/testing/testing/database_mp.py", line 44, in database_action
result = query_integer(integer)
File "/home/talens/Research/HATPI/Projects/testing/testing/database_mp.py", line 37, in query_integer
result = session.execute(statement).scalar_one_or_none()
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1712, in execute
result = conn._execute_20(statement, params or {}, execution_options)
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 333, in _execute_on_connection
return connection._execute_clauseelement(
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
ret = self._execute_context(
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
self._handle_dbapi_exception(
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
util.raise_(
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
raise exception
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
self.dialect.do_execute(
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/MySQLdb/cursors.py", line 206, in execute
res = self._query(query)
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/MySQLdb/cursors.py", line 319, in _query
db.query(q)
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/MySQLdb/connections.py", line 259, in query
_mysql.connection.query(self, query)
result = query_integer(integer)
File "/home/talens/Research/HATPI/Projects/testing/testing/database_mp.py", line 37, in query_integer
result = session.execute(statement).scalar_one_or_none()
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1712, in execute
result = conn._execute_20(statement, params or {}, execution_options)
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 333, in _execute_on_connection
return connection._execute_clauseelement(
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
ret = self._execute_context(
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
self._handle_dbapi_exception(
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
util.raise_(
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
raise exception
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
self.dialect.do_execute(
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/MySQLdb/cursors.py", line 206, in execute
res = self._query(query)
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/MySQLdb/cursors.py", line 319, in _query
db.query(q)
File "/home/talens/anaconda3/envs/hatpi/lib/python3.9/site-packages/MySQLdb/connections.py", line 259, in query
_mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (2006, 'MySQL server has gone away')
[SQL: SELECT mytable.id
FROM mytable
WHERE mytable.id = %s]
[parameters: (20,)]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
FROM mytable
WHERE mytable.id = %s]
[parameters: (20,)]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
Mike Bayer
Aug 23, 2022, 12:04:38 PM8/23/22
to noreply-spamdigest via sqlalchemy
it happens every time the program is run? there's nothing really going on in the program here, if I take out the initializer, then the program produces lots of stack traces as expected, but even then not the "server has gone away" error.
can you try running on a different MySQL /MariaDB server ? like run a clean database in a container. What server version ?
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/fb678c9d-4a54-4436-a86e-be81d764af8bn%40googlegroups.com.
Mike Bayer
Aug 23, 2022, 12:05:24 PM8/23/22
to noreply-spamdigest via sqlalchemy
also try the pymysql DBAPI if that changes things, if this is mariadb try mariadb-connector also.
Geert Jan Talens
Aug 23, 2022, 1:12:32 PM8/23/22
to sqlalchemy
It does happen on every run. I'm not entirely sure how to run it on a different server, I'm fairly new to databases. I'm running "mysql Ver 8.0.30 for Linux on x86_64 (MySQL Community Server - GPL)".
The issue does not occur when using pymysql.
Mike Bayer
Aug 23, 2022, 1:59:31 PM8/23/22
to noreply-spamdigest via sqlalchemy
okey doke let's see what mysql client library you are running, here's a Python session that locates the .so file in use and then runs the ldd command, which can give us a clue what you are running, if pymysql has no issue then we need to suspect client library as a possible factor.
$ python
Python 3.10.0 (default, Nov 5 2021, 17:23:47) [GCC 11.2.1 20210728 (Red Hat 11.2.1-1)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> from MySQLdb import _mysql
>>> import os
>>> os.system(f"ldd {_mysql.__file__}")
linux-vdso.so.1 (0x00007ffea07ee000)
libmariadb.so.3 => /lib64/libmariadb.so.3 (0x00007f37c45fa000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f37c45f5000)
libc.so.6 => /lib64/libc.so.6 (0x00007f37c43ed000)
libz.so.1 => /lib64/libz.so.1 (0x00007f37c43d3000)
libssl.so.1.1 => /lib64/libssl.so.1.1 (0x00007f37c4336000)
libcrypto.so.1.1 => /lib64/libcrypto.so.1.1 (0x00007f37c4048000)
/lib64/ld-linux-x86-64.so.2 (0x00007f37c467f000)
0
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/2239bc2e-1ffb-45b5-9a54-df85fa205658n%40googlegroups.com.
Geert Jan Talens
Aug 23, 2022, 2:18:42 PM8/23/22
to sqlalchemy
For my purposes I'm perfectly happy to use pymysql instead of mysqldb, but I'll gladly help with further debugging if you wish to chase this down. Here's the output for the python session:
Type "help", "copyright", "credits" or "license" for more information.
>>> from MySQLdb import _mysql
>>> import os
>>> os.system(f"ldd {_mysql.__file__}")
Python 3.9.7 (default, Sep 16 2021, 13:09:58)
[GCC 7.5.0] :: Anaconda, Inc. on linux
[GCC 7.5.0] :: Anaconda, Inc. on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> from MySQLdb import _mysql
>>> import os
>>> os.system(f"ldd {_mysql.__file__}")
linux-vdso.so.1 (0x00007ffec8a71000)
libmysqlclient.so.18 => /home/talens/anaconda3/envs/hatpi/lib/libmysqlclient.so.18 (0x00007f60768ed000)
libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f60768b6000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f60766c4000)
libssl.so.1.1 => /home/talens/anaconda3/envs/hatpi/lib/./libssl.so.1.1 (0x00007f6076633000)
libcrypto.so.1.1 => /home/talens/anaconda3/envs/hatpi/lib/./libcrypto.so.1.1 (0x00007f6076366000)
libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f6076360000)
libstdc++.so.6 => /home/talens/anaconda3/envs/hatpi/lib/./libstdc++.so.6 (0x00007f60761e9000)
libgcc_s.so.1 => /home/talens/anaconda3/envs/hatpi/lib/./libgcc_s.so.1 (0x00007f60761d5000)
/lib64/ld-linux-x86-64.so.2 (0x00007f6076cc3000)
libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f6076086000)
0
libmysqlclient.so.18 => /home/talens/anaconda3/envs/hatpi/lib/libmysqlclient.so.18 (0x00007f60768ed000)
libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f60768b6000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f60766c4000)
libssl.so.1.1 => /home/talens/anaconda3/envs/hatpi/lib/./libssl.so.1.1 (0x00007f6076633000)
libcrypto.so.1.1 => /home/talens/anaconda3/envs/hatpi/lib/./libcrypto.so.1.1 (0x00007f6076366000)
libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f6076360000)
libstdc++.so.6 => /home/talens/anaconda3/envs/hatpi/lib/./libstdc++.so.6 (0x00007f60761e9000)
libgcc_s.so.1 => /home/talens/anaconda3/envs/hatpi/lib/./libgcc_s.so.1 (0x00007f60761d5000)
/lib64/ld-linux-x86-64.so.2 (0x00007f6076cc3000)
libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f6076086000)
0
Reply all
Reply to author
Forward
0 new messages