4.5. Frequently asked questions

Q1. Any note for Microsoft SQL?
Q2. How to remove the idle MySQL connections?
Q3. How to enable managed connection?

Q1.

Any note for Microsoft SQL?

Particularly to Microsoft SQL, you need to add sendStringParametersAsUnicode=false to the connection url as in the example below:


<Resource name="exo-idm_portal" auth="Container" type="javax.sql.DataSource"
initialSize="5" maxActive="20" minIdle="5" maxIdle="15" maxWait="10000"
validationQuery="SELECT 1" validationQueryTimeout="5"
testWhileIdle="true" testOnBorrow="true" testOnReturn="false"
timeBetweenEvictionRunsMillis="30000" minEvictableIdleTimeMillis="60000"
removeAbandoned="true" removeAbandonedTimeout="300" logAbandoned="false"
poolPreparedStatements="true"
username="plf" password="plf" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" 
url="jdbc:sqlserver://localhost:1433;databaseName=plf;sendStringParametersAsUnicode=false" />

This parameter is necessary to avoid any possible performance problem. MS SQL Server differentiates Unicode data types (such as nchar) from ASCII data types (such as char). By default (without this parameter), all the JDBC drivers send strings in Unicode format to SQL Server. When, for example, doing a comparison on a non-Unicode column, SQL Server tries to convert data in the table into Unicode first. This conversion might cause a serious performance issue.

The parameter is a bit different between JDBC Drivers. See more details here.

Q2.

How to remove the idle MySQL connections?

Some RDBMSs, like MySQL, close the idle connections after a period (8 hours on MySQL by default). Thus, a connection from the pool will be invalid and any application SQL command will fail, resulting in errors as below:

org.hibernate.SessionException: Session is closed!
at org.hibernate.impl.AbstractSessionImpl.errorIfClosed(AbstractSessionImpl.java:72)
at org.hibernate.impl.SessionImpl.getTransaction(SessionImpl.java:1342)

To avoid this, you can use DBCP to monitor the idle connections and drop them when they are invalid, with the testWhileIdle, timeBetweenEvictionRunsMillis, and validationQuery parameters.

The validation query is specific to your RDBMS. For example, on MySQL, you would use:

testWhileIdle="true" timeBetweenEvictionRunsMillis="300000" validationQuery="SELECT 1"
  • testWhileIdle activates the idle connections monitoring.

  • timeBetweenEvictionRunsMillis defines the time interval between two checks in milliseconds (5 minutes in the example).

  • validationQuery provides a simple SQL command to validate connection to the RDBMS.

For more details, refer to the following:

Q3.

How to enable managed connection?

This question is specific to the JCR datasource in Platform JBoss package.

When you want to use managed connections, set "true" for the gatein.jcr.datasource.managed property in the $PLATFORM_JBOSS_HOME/standalone/configuration/gatein/exo.properties file. See Configuration overview if you have not created this file yet.

gatein.jcr.datasource.managed=true

To be clear, this property needs to be "true" in two cases:

  • You use a datasource with JTA enabled:

    
    <datasource jndi-name="java:/comp/env/exo-jcr_portal" jta="true" .../>
  • You use an xa-datasource:

    
    <xa-datasource  jndi-name="java:/comp/env/exo-jcr_portal" .../>

Using managed connections has pros and cons, so only do it if you know what you are doing. By default eXo Platform JBoss uses datasource jta="false".

Copyright ©. All rights reserved. eXo Platform SAS
blog comments powered byDisqus