I have a question related to Prepared Steatement pooling (across all connections). Here's the config file
<datasources>
<local-tx-datasource>
<jndi-name>JNDI-NAME</jndi-name>
<connection-url>jdbc:mysql://<server_name>/<database_name>?useServerPrepStmts=true</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>xxx</user-name>
<password>xxxxx</password>
<min-pool-size>10</min-pool-size>
<max-pool-size>20</max-pool-size>
<idle-timeout-minutes>20</idle-timeout-minutes>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.
vendor.MySQLExceptionSorter</exception-sorter-class-name>
<valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.
vendor.MySQLValidConnectionChecker</valid-connection-checker-class-name>
<background-validation>true</background-validation>
<background-validation-minutes>5</background-validation-minutes>
<prepared-statement-cache-size>100</prepared-statement-cache-size>
<share-prepared-statements>true</share-prepared-statements>
<!-- sql to call when connection is created
<new-connection-sql>some arbitrary sql</new-connection-sql>
-->
<!-- sql to call on an existing pooled connection when it is obtained from pool - MySQLValidConnectionChecker is preferred for newer drivers
<check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
-->
<!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml -->
<metadata>
<type-mapping>mySQL</type-mapping>
</metadata>
</local-tx-datasource>
</datasources>
It seems that this line:
<background-validation-minutes>5</background-validation-minutes>
doesn't cause any problems with Prepared Statements, but:
<idle-timeout-minutes>20</idle-timeout-minutes>
causes that all connections are removed and re-created if there was no traffic for the last 20 minutes. Because of that existing Prepared Statements are removed from the pool of cached Prepared Statements. How to overcome this issue? I have to use idle-timeout-minutes because MySQL server closes the connection after 8h
