You could use the new client side load balancing feature in Avatica, which solves the stickiness problem.
See https://issues.apache.org/jira/browse/CALCITE-5581 On Thu, Jan 11, 2024 at 7:31 AM Sophie Wang <[email protected]> wrote: > Hi Calcite Devs, > > Happy new year! Hope you’re all well. > > I have a question related to the Avatica Driver server implementation > (specifically about caching in Meta / JdbcMeta components). > > https://github.com/apache/calcite-avatica/blob/78c5f54280b2fe3813aea0cbd9fbb7faf39[ > …]ver/src/main/java/org/apache/calcite/avatica/jdbc/JdbcMeta.java > > We are implementing a JDBC Query Proxy microservice using Avatica > components. However, our end-to-end querying (i.e. client using Avatica > Driver -> Query Proxy -> JDBC Driver -> Database Server) does not work if > our Query Proxy is deployed multiple nodes, and throws > NoSuchConnectionException. (Querying works fine if the proxy is only > deployed on 1 node) > > We use the JdbcMeta as per the snippet below: > > > val meta = JdbcMeta("jdbc:mysql://{databaseUrl}:{port}/{schema}", > > properties) > > val service = LocalService(meta) > > return LocalProtobufService(service, protobufTranslation()) > > > And, when querying the Proxy, we receive NoSuchConnectionException if the > proxy is hosted on 2 nodes, due to the connectionCache in the Meta: > > https://github.com/apache/calcite-avatica/blob/78c5f54280b2fe3813aea0cbd9fbb7faf39[ > …]ver/src/main/java/org/apache/calcite/avatica/jdbc/JdbcMeta.java. > The Proxy will have 1x JdbcMeta per node (and therefore 1x connectionCache > and 1x statementCache per node), for a total of 2x connectionCache and 2x > statementCache objects across the proxy. > > Thus, upon querying with JDBC: > > - The client sends an OpenConnectionRequest with connectionId abcde and > hits node1 , and the connection is stored in node1's connectionCache > - The ConnectionSyncRequest for the same connectionId abcde hits node2 , > due to load balancing. This connectionId does not exist in node2's > connectionCache, it is in node1’s connectionCache, and we receive > NoSuchConnectionException. > > Would you have any recommendations on how to solve this problem without > using sticky sessions? Apache Druid implements their own DruidMeta class, > which has a similar connectionCache/statementCache implementation to > JdbcMeta in the Avatica Server dependency > > https://github.com/apache/druid/blob/master/sql/src/main/java/org/apache/druid/sql/avatica/DruidMeta.java#L101 > . > But they seem to solve this multi-node problem with connection stickiness > via the router > < > https://github.com/apache/druid/blob/master/sql/src/main/java/org/apache/druid/sql/avatica/DruidMeta.java#L101 > > > . > > Thank you so much for your time, it's greatly appreciated! Please let me > know if you have questions or want more details :) > > Warm regards, > Sophie > -- *István Tóth* | Sr. Staff Software Engineer *Email*: [email protected] cloudera.com <https://www.cloudera.com> [image: Cloudera] <https://www.cloudera.com/> [image: Cloudera on Twitter] <https://twitter.com/cloudera> [image: Cloudera on Facebook] <https://www.facebook.com/cloudera> [image: Cloudera on LinkedIn] <https://www.linkedin.com/company/cloudera> ------------------------------ ------------------------------
