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 ?
On Tue, Aug 23, 2022, at 11:22 AM, Geert Jan Talens wrote:
> Hello Mike,
>
> 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
>
> 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
> 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)
> 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)
>
> On Monday, 22 August 2022 at 19:19:15 UTC-4 Mike Bayer wrote:
>> __
>> 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
>>
>>
>> On Mon, Aug 22, 2022, at 4:21 PM, Geert Jan Talens wrote:
>>> 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
>>>
>>> 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:[email protected]/testdb
>>> <http://user:[email protected]/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()
>>>
>>>
>>> --
>>> SQLAlchemy -
>>> The Python SQL Toolkit and Object Relational Mapper
>>>
>>> http://www.sqlalchemy.org/
>>>
>>> To 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 [email protected].
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/sqlalchemy/5597b069-3725-44de-88c4-4964d95d84cen%40googlegroups.com
>>>
>>> <https://groups.google.com/d/msgid/sqlalchemy/5597b069-3725-44de-88c4-4964d95d84cen%40googlegroups.com?utm_medium=email&utm_source=footer>.
>>
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To 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 [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/fb678c9d-4a54-4436-a86e-be81d764af8bn%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/fb678c9d-4a54-4436-a86e-be81d764af8bn%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/fa1062bf-eafa-40bc-aa8e-cfb1e2b2c5ee%40www.fastmail.com.
