got a read-only connection from the connection pool after the db failover

[qianliniu]

a routine maintenance cause m-0 prior master restarted, and the db master switched to m-1. at 18:25 when m-0 finished the restart , it accept read-only connections. at 18:40 the application got a old connection from the pool, and use it to delete data, so failed for it is a read-only connection. Question: how to config the HikariCP to avoid to get a read-only old connection? -------db log------------

m-0:2021-07-03   15:17:33 UTC [] [00000] [28]: [7-1] user=,db=,client= LOG: received fast   shutdown request
m-0:2021-07-03 15:17:33 UTC [] [00000]   [28]: [8-1] user=,db=,client= LOG: aborting any active transactions
m-1:2021-07-03 15:17:40,126 INFO:   promoted self to leader by acquiring session lock
m-1:021-07-03 15:17:42 UTC [] [00000]   [27]: [6-1] user=,db=,client= LOG: database system is ready to accept   connections
m-0:2021-07-03 15:18:25 UTC [] [00000]   [28]: [5-1] user=,db=,client= LOG: database system is ready to accept read   only connections

-----application error------

Jul 3 11:18:40 PM   ymt-azkaban-55ff7d586d-frr5m ymt-azkaban [ERROR] [Azkaban] pool-7-thread-1   [azkaban.db.AzkabanQueryRunner] Update failed: Reached maximum number of retries: 5
java.sql.SQLException:   ERROR: cannot execute DELETE in a read-only transaction   Query: DELETE FROM xxxxx WHERE id in   (SELECT ctid FROM yyyyy where zzzz < ? LIMIT ? )   Parameters: [1618067914482, 1000]
at   org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:363)
at   org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:490)
at   org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:456)
at   azkaban.db.AzkabanQueryRunner.update(AzkabanQueryRunner.java:98)
at   azkaban.db.DatabaseOperator.update(DatabaseOperator.java:208)

-----my db connection pool setting-------- ConnectionTestQuery="select 1" , maxlifetime=300000 (5 mins), idleTimeout=60000 (1 min)

[codefish1]

You need to do a query which requires write permissions such as

select 1 for update

[qianliniu]

Thanks for your suggestion. select 1 for update it still returns data in a readonly transaction.

I try to query from a certain table, it does work, but I don’t know if it’s the best practice select 1 from xxx where 1=2 for update

[codefish1]

We are using Postgres and are using

select pg_current_wal_lsn() which throws an exception on our read only nodes, and therefore the connection isn't created.

Make sure you if you are handling failover by updating a DNS record that you have a short TTL set in Java and it's not cached the original record forever

[mysql-wkp]

I also encountered the same problem, ConnectionTestQuery="select 1 from test where 1=2 for update" , maxlifetime=300000 (5 mins), idleTimeout=60000 (1 min) ，read only still exists ......

[mysql-wkp]

I also encountered the same problem, ConnectionTestQuery="select 1 from test where 1=2 for update" , maxlifetime=300000 (5 mins), idleTimeout=60000 (1 min) ，read only still exists ......