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:pass...@127.0.0.1/testdb
> <http://user:password@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()
>
>
> --
> 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 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
>
> <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 sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/fb678c9d-4a54-4436-a86e-be81d764af8bn%40googlegroups.com.
