MYSQL server selection from applications

[supriya sindagi]

Hi Team,

We are using below 

WildFly Version 26.0.0.Final,  2021-12-14T21:55:34 in production and have configured below servers in standalone.xml and env variable for connection to underlying server to be able to handle DB write requests.

<datasource jta="false" jndi-name="java:/jdbc/appReadDatabase" pool-name="jdbc_appReadDatabase" statistics-enabled="true">
<connection-url>jdbc:mysql:loadbalance://${env.DB_HOSTS}/app_database?characterEncoding=UTF-8&amp;loadBalanceBlocklistTimeout=5000&amp;connectTimeout=1000&amp;socketTimeout=1000&amp;useSSL=false&amp;allowPublicKeyRetrieval=true&amp;scrollTolerantForwardOnly=true</connection-url>
<datasource jta="false" jndi-name="java:/jdbc/appWriteDatabase" pool-name="jdbc_appWriteDatabase" statistics-enabled="true">
<connection-url>jdbc:mysql:loadbalance://${env.DB_GEO_HOSTS}/app_database?characterEncoding=UTF-8&amp;loadBalanceBlocklistTimeout=5000&amp;connectTimeout=1000&amp;socketTimeout=1000&amp;useSSL=false&amp;allowPublicKeyRetrieval=true&amp;scrollTolerantForwardOnly=true</connection-url>

 The applications deployed on Wildly read the below env variables to handle write requests.

[root@machin2 home]# printenv | grep -i db
DB_HOSTS=mysql-server-4:3306,mysql-server-3:3306,mysql-server-2:3306,mysql-server-1:3306
DB_GEO_HOSTS=mysql-master-1:3306,mysql-master-2:3306,mysql-master-3:3306,mysql-master-4:3306

What is the mechanism of selecting DB GEO HOSTS from Wildly ? It is dependent on the JDBC Driver from MYSQL Server ?

Kindly respond.

[Bartosz Baranowski]

WDYM "what is the mechanism" ? Its connection configuration that you pass to inform driver of how it should handled DB connection. That URL should describe your DB setup.

[Jose Socola]

Wildfly doesn't handle that, just simply resolves the ${env.DB_GEO_HOSTS} expressions and passes the connection string to the driver, Connector/J do that &loadBalanceStrategy=serverAffinity &serverAffinityOrder=region1-db1:3306,region1-db2:3306,region2-db1:3306,region2-db2:3306

jdbc:mysql:loadbalance://[host1][:port],[host2][:port][,[host3][:port]]...[/[database]] » [?propertyName1=propertyValue1[&propertyName2=propertyValue2]...]

https://dev.mysql.com/doc/connector-j/en/connector-j-usagenotes-j2ee-concepts-managing-load-balanced-connections.html

Regards,

JS

--
You received this message because you are subscribed to the Google Groups "WildFly" group.
To unsubscribe from this group and stop receiving emails from it, send an email to wildfly+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/wildfly/17663ffb-6c3c-4268-8a12-1ee7bd37f886n%40googlegroups.com.