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.
On Tuesday, 23 August 2022 at 12:05:24 UTC-4 Mike Bayer wrote:
> also try the pymysql DBAPI if that changes things, if this is mariadb try
> mariadb-connector also.
>
> On Tue, Aug 23, 2022, at 12:04 PM, Mike Bayer wrote:
>
> 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/2239bc2e-1ffb-45b5-9a54-df85fa205658n%40googlegroups.com.
