OpenFire 3.10.2 SQL Connection issues with jtds 1.3.1 vs 1.2.2

General Discussion
1

I am running OpenFire 3.10.2 on a Java JRE 1.8.0_77 system with JTDS 1.2.2 connecting to a local MS-SQL database - and everything runs great.

I then swap out the JTDS for the 1.3.1 version and everything stills runs without a noticeable issue, but now I get database connection errors (see below) reported by OpenFire.

Even with the errors- it appears things are running as it should, message archiving, etc.

I have only seen the errors reported for these 4 operations:

roster.DefaultRosterItemProvider.getItems()

group.DefaultGroupProvider.getGroupNames()

reporting.stats.RrdSqlBackend.exists()

group.AbstractGroupProvider.getPublicSharedGroupNames()

It is like they are unique in the way they access SQL through JTDS.

Here is a full message…

2016.04.22 10:08:47 org.jivesoftware.database.DbConnectionManager - Unable to get a connection from the database pool (attempt 0 out of 10).

java.sql.SQLException: Network error IOException: \localhost\pipe\sql\query (The system cannot find the path specified)

at net.sourceforge.jtds.jdbc.JtdsConnection.(JtdsConnection.java:436)

at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)

at java.sql.DriverManager.getConnection(Unknown Source)

at java.sql.DriverManager.getConnection(Unknown Source)

at org.logicalcobwebs.proxool.DefaultConnectionBuilder.buildConnection(DefaultConn ectionBuilder.java:39)

at org.logicalcobwebs.proxool.Prototyper.buildConnection(Prototyper.java:159)

at org.logicalcobwebs.proxool.ConnectionPool.getConnection(ConnectionPool.java:211 )

at org.logicalcobwebs.proxool.ProxoolDriver.connect(ProxoolDriver.java:89)

at java.sql.DriverManager.getConnection(Unknown Source)

at java.sql.DriverManager.getConnection(Unknown Source)

at org.jivesoftware.database.DefaultConnectionProvider.getConnection(DefaultConnec tionProvider.java:86)

at org.jivesoftware.database.DbConnectionManager.getConnection(DbConnectionManager .java:125)

at org.jivesoftware.openfire.roster.DefaultRosterItemProvider.getItems(DefaultRost erItemProvider.java:245)

at org.jivesoftware.openfire.roster.Roster.(Roster.java:133)

:

So that one was for roster.DefaultRosterItemProvider.getItems(). I also see it for:

at org.jivesoftware.database.DbConnectionManager.getConnection(DbConnectionManager .java:125)

at org.jivesoftware.openfire.group.DefaultGroupProvider.getGroupNames(DefaultGroup Provider.java:292)

at org.jivesoftware.openfire.group.AbstractGroupProvider.getSharedGroupNames(Abstr actGroupProvider.java:170)

and

at org.jivesoftware.database.DbConnectionManager.getConnection(DbConnectionManager .java:125)

at org.jivesoftware.openfire.reporting.stats.RrdSqlBackend.exists(RrdSqlBackend.ja va:211)

at org.jivesoftware.openfire.reporting.stats.RrdSqlBackendFactory.exists(RrdSqlBac kendFactory.java:43)

and

at org.jivesoftware.database.DbConnectionManager.getConnection(DbConnectionManager .java:125)

at org.jivesoftware.openfire.group.AbstractGroupProvider.getPublicSharedGroupNames (AbstractGroupProvider.java:232)

at org.jivesoftware.openfire.group.AbstractGroupProvider.getSharedGroupNames(Abstr actGroupProvider.java:175)

Named pipes is allowed in MS SQL Config Mgr

Thanks,

Scott

2

I think I found my answer. I ran across this post: Ticket Request for jDTS Driver update

I didn’t know that OpenFire had a jtds.jar file bundled in with it. Our deployment was also including a JTDS driver under the Java folder.

As long as the versions of the two jtds.jar files matched, OpenFire ran without errors.

So to move to 1.3.1 of jtds, I needed to copy the file to both locations.

I am still not clear how OpenFire and Java use the two jtds.jar files. I suspect within OpenFire or a dependent module there are different ways of referencing the database driver. For instance the RRD stuff in jrobin seems to reference a jtds different that the other database access code. FYI, we use MS-SQL local on the OpenFire box and our openfire.xml contains:

<className>org.jivesoftware.database.DefaultConnectionProvider</className>

<defaultProvider>

  <driver>net.sourceforge.jtds.jdbc.Driver</driver>

  <serverURL>jdbc:jtds:sqlserver://localhost/OpenfireDB;namedPipe=true;</serverUR L>

  <username encrypted="true">12345blahblah123456</username>

  <password encrypted="true">12345blahblah123456</password>

  <testSQL>select 1</testSQL>

  <testBeforeUse>false</testBeforeUse>

  <testAfterUse>false</testAfterUse>

  <minConnections>5</minConnections>

  <maxConnections>75</maxConnections>

  <connectionTimeout>1.0</connectionTimeout>

</defaultProvider>

So if anyone has any other thoughts on this - I would like to hear them. For now - I have moved past my immediate problem.