JndI connection pool error

[former_member637095]

Hi All,

I am having difficulties in configuring JNDI database connection pool. Appreciate if anyone could help here.

The issue is, whenever DB server switches(Primary and Secondary - takes a min or so), Hybris keeps invalid open DB connections and use the same for transactions thus throwing errors and we must restart the server to overcome this error.

I tried fixing this by

Option 1:

 db.pool.testOnBorrow=true
 db.pool.validationQuery=SELECT 1 FROM INFORMATION_SCHEMA.SYSTEM_USERS
 db.pool.validationQueryTimeout=1000

But, this validation never happens and found out that in “JDBCConnectionFactory” it is disabled ( maybe I am wrong)

 protected boolean mustValidate(Object obj)
 {
 return false;
 }

Option 2:

1. Enabled Tomcat connection resource

2. Copied the msqsql db driver to tomcat/lib folder

Connection properties

 db.pool.fromJNDI=java:/comp/env/jdbc/hybrisDS
 db.pool.fromJNDI.dbtype=sqlserver
 db.tableprefix=nextgen
 db.pool.testOnBorrow=true
 db.pool.validationQuery=SELECT 1
 db.pool.validationQueryTimeout=1000

When I start the server I get the below error and server won’t stnd I get the following error.

INFO | jvm 1 | main | 2019/06/04 10:33:11.998 | INFO [localhost-startStop-1] [MetaInformationManagerEJB] error getting system init/update timestamp: Invalid object name 'props'. – ignored

Error

 INFO   | jvm 1    | main    | 2019/06/04 10:41:03.364 | sql error creating table via 'CREATE TABLE nextgennumberseries ( serieskey varchar(255)  NOT NULL ,template varchar(255)  ,seriestype integer default 0  ,currentValue bigint   , CONSTRAINT PK_3372db0f3c0 PRIMARY KEY NONCLUSTERED ( serieskey ) )' : com.microsoft.sqlserver.jdbc.SQLServerException: There is already an object named 'nextgennumberseries' in the database.
 INFO   | jvm 1    | main    | 2019/06/04 10:41:03.370 | com.microsoft.sqlserver.jdbc.SQLServerException: There is already an object named 'nextgennumberseries' in the database.
 INFO   | jvm 1    | main    | 2019/06/04 10:41:03.371 |    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
 INFO   | jvm 1    | main    | 2019/06/04 10:41:03.375 |    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
 INFO   | jvm 1    | main    | 2019/06/04 10:41:03.376 |    at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:792)
 INFO   | jvm 1    | main    | 2019/06/04 10:41:03.377 |    at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689)
 INFO   | jvm 1    | main    | 2019/06/04 10:41:03.384 |    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
 INFO   | jvm 1    | main    | 2019/06/04 10:41:03.385 |    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
 INFO   | jvm 1    | main    | 2019/06/04 10:41:03.386 |    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
 INFO   | jvm 1    | main    | 2019/06/04 10:41:03.386 |    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
 INFO   | jvm 1    | main    | 2019/06/04 10:41:03.391 |    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(SQLServerStatement.java:642)
 INFO   | jvm 1    | main    | 2019/06/04 10:41:03.391 |    at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
 INFO   | jvm 1    | main    | 2019/06/04 10:41:03.392 |    at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
 INFO   | jvm 1    | main    | 2019/06/04 10:41:03.394 |    at de.hybris.platform.jdbcwrapper.StatementImpl.executeUpdate(StatementImpl.java:183)
 INFO   | jvm 1    | main    | 2019/06/04 10:41:03.402 |    at de.hybris.platform.persistence.numberseries.DefaultSerialNumberDAO.createSeriesTable(DefaultSerialNumberDAO.java:748)
 INFO   | jvm 1    | main    | 2019/06/04 10:41:03.403 |    at de.hybris.platform.persistence.numberseries.DefaultSerialNumberDAO.initTable(DefaultSerialNumberDAO.java:491)
 INFO   | jvm 1    | main    | 2019/06/04 10:41:03.404 |    at de.hybris.platform.persistence.numberseries.DefaultSerialNumberDAO.initPersistence(DefaultSerialNumberDAO.java:388)
 INFO   | jvm 1    | main    | 2019/06/04 10:41:03.405 |    at de.hybris.platform.core.AbstractTenant.initNumberSeriesPersistence(AbstractTenant.java:835)
 INFO   | jvm 1    | main    | 2019/06/04 10:41:03.407 |    at de.hybris.platform.core.AbstractTenant.doStartupSafe(AbstractTenant.java:695)
 INFO   | jvm 1    | main    | 2019/06/04 10:41:03.407 |    at de.hybris.platform.core.AbstractTenant.doStartUp(AbstractTenant.java:650)
 

Has anyone seen this error before? Hybris version is 5.3 Or how can I enable “testOnBorrow” property for DB connection pool. Or, how to make Hybris dump error connections and try to create new ones.

Appreciate your help here.

Thanks.