Latest update: 1. Psycopyg2 works with connect_timeout - Direct SQL call 2. sqlalchemy works with connect_timeout (Direct SQL call no ORM) 3. Flask-sqlalchemy works with connect_timeout (Direct SQL call, no ORM)
Next up on our troubleshooting tree.. Test sqlalchemy with ORM calls with Connect_timeout.. Weird... If anyone wants to look at our code to see what our app is doing: https://github.com/bcgov/queue-management/api Karim On Thursday, 27 February 2020 10:24:25 UTC-8, Mike Bayer wrote: > > OK so that script I gave you, the exact SQLAlchemy equivalent is: > > > from sqlalchemy import create_engine > > > e = create_engine("postgresql+psycopg2://scott:tiger@localhost/test", > connect_args={"connect_timeout": 2}) > c = e.connect() > > > it also will accept it as: > > e = > create_engine("postgresql+psycopg2://scott:tiger@localhost/test?connect_timeout=2") > > however, in the above case the timeout is passed as a string "2" and not > an integer, which may interfere with it working, im not sure. the > separate connect_args approach is safer. > > > > On Thu, Feb 27, 2020, at 12:57 PM, Karim Gillani wrote: > > Thank you for the great idea. I did not see the forest through the > trees. Haha. > > We have the pscopyg2 script running and things seems to be working fine > with that. We can see without the connect_timeout that the delay is about > 2 minutes and with the connect_timeout set, the delay is only 2 seconds. > > Next, we are going to wrap the script (if possible) with Alchemy only and > test. > Then after that, we will wrap flask-alchemy. > > Thanks > Karim > > > > On Thursday, 27 February 2020 06:48:00 UTC-8, Mike Bayer wrote: > > Can you write a script that uses psycopg2 directly and see if you can > replicate the issue in that way? this would elimiante SQLAlchemy as part > of the issue and you can interact with the psycopg2 developers directly: > > import psycopg2 > > connection = psycopg2.connect( > user="scott", > password="tiger", > host="localhost", > database="test", > connect_timeout=2, > ) > > > There's no need to do pdb inside of psycopg2 itself. Within SQLAlchemy > you could do this at lib/sqlalchemy/dialects/postgresql/psycopg2.py however > only if using raw psycopg2 above proves that it solves your connection > issue. > > > On Wed, Feb 26, 2020, at 11:19 AM, Karim Gillani wrote: > > Thanks. I don't believe this is a Red Hat issue as much as a > configuration issue. This is because I can replicate the issue using PSQL > and using the connect_timeout argument in PSQL, I can fix it. With the > complexity of using flask-sqlalchemy which uses sqlalchemy to pass > arguments to psycopg2 to libpq, it is difficult to see where the issue > actually is. Based on the links you provided, I am passing the > connect_timeout parameter correctly. I am not sure what to do now. I am > guessing I will need to somehow figure out if the parameter is being > passed. I think I will play with getting connection.get_dsn_parameters() > function to display the parameters. > > Karim > > On Wed, 26 Feb 2020 at 07:30, Mike Bayer <mik...@zzzcomputing.com> wrote: > > > This would be a psycopg2 level setting which are documented at > https://www.psycopg.org/docs/module.html#psycopg2.connect and the > available values are ultimately at > https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS > > . On the SQLAlchemy side you pass these to create_engine using > connect_args: > https://docs.sqlalchemy.org/en/13/core/engines.html?highlight=connect_args#custom-dbapi-args > > > > On Wed, Feb 26, 2020, at 12:11 AM, Karim Gillani wrote: > > We are using openshift which uses HAProxy. What I am looking for is why > the connect_timeout may not being applied so that we can retry the call > after re-connect to the other database pod. > > It is taking *up to two minutes* before I get > the psycopg2.OperationalError. It's very strange. The code will > automatically retry and work after this error shows up. I am guessing but > I don't think my setup is passing the connect_timeout to libpq. > > Thanks > > On Tuesday, 25 February 2020 10:37:19 UTC-8, Mike Bayer wrote: > > "could not connect" means your server is unreachable from a network > standpoint. You want to make sure the server is running and that it is > routable over the network you are trying to connect from. > > in an HA scenario, depending on what software is in use and how it does > switchover, there can be long network lags, such as if it is moving a VIP > from one machine to another. You'd want to be using a proxy server such as > HAProxy or PGBouncer rather than relying upon changing network routing. > > > > On Tue, Feb 25, 2020, at 12:07 PM, Karim Gillani wrote: > > Please note I am using a Pool. And some other connections seem to recover > fine. After two minutes I get this message and things start working again: > > ==> AE K:11oh392; T:1; E:(psycopg2.OperationalError) could not connect to > server: Connection timed out> Is the server running on host > "patroni-mastertheq" (172.50.46.180) and accepting> TCP/IP connections on > port 5432?>>(Background on this error at: http://sqlalche.me/e/e3q8 ) > Thanks > Karim > > On Tuesday, 25 February 2020 08:49:19 UTC-8, Karim Gillani wrote: > > I am having an issue where when my HA postgres database drops and is > automatically moved to a backup database, the timeout doesn't seem to be > working. > > My config.py shows the following: > > SQLALCHEMY_DATABASE_URI = > '{engine}://{user}:{password}@{host}:{port}/{name}'.format( > > engine=DB_ENGINE, > > user=DB_USER, > > password=DB_PASSWORD, > > host=DB_HOST, > > port=DB_PORT, > > name=DB_NAME, > > ) > SQLALCHEMY_ENGINE_OPTIONS > = { 'pool_size' : pool_size, 'max_overflow' : max_overflow, > 'pool_pre_ping' : True, 'pool_timeout': DB_POOL_TIMEOUT, 'pool_recycle': > 3600, 'connect_args': { 'connect_timeout': 5, 'options' : '-c > statement_timeout=5000' } } > > Is there something I set wrong? My timeout seems to be about 2 minutes > instead of 5 secs. > > Any help would be appreciated, > > Thanks > Karim > > > -- > 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 sqlal...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/ab3e5681-d9be-468f-8160-97bddcca57fb%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/ab3e5681-d9be-468f-8160-97bddcca57fb%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 sqlal...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/06c8e91e-4f36-4192-90d0-acbe840478ef%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/06c8e91e-4f36-4192-90d0-acbe840478ef%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 sqlal...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/82280db0-b63e-4e31-9657-66eb8bcd79b7%40www.fastmail.com > > <https://groups.google.com/d/msgid/sqlalchemy/82280db0-b63e-4e31-9657-66eb8bcd79b7%40www.fastmail.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 sqlal...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CAOpL_VFw3U7vZhiLnQ8gB7-zUe%3Dc14QU019w9bQ25B4jPs6MvA%40mail.gmail.com > > <https://groups.google.com/d/msgid/sqlalchemy/CAOpL_VFw3U7vZhiLnQ8gB7-zUe%3Dc14QU019w9bQ25B4jPs6MvA%40mail.gmail.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 sqlal...@googlegroups.com <javascript:>. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/c9d76a8f-a4cb-4219-8994-9594b25a0084%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/c9d76a8f-a4cb-4219-8994-9594b25a0084%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/c29e155b-ea8f-4d42-b869-2a8086efc7f8%40googlegroups.com.
