TDS Protocol error

Openfire Openfire Dev
1

I have ~2300 rooms configured in ofRooms and every time openfire is throwing the following error when it is coming up and because of which rooms are not read from DB. Any suggestions?

** ERROR [main]: org.jivesoftware.openfire.muc.spi.MUCPersistenceManager - A database error prevented MUC rooms to be loaded from the database.**

java.sql.SQLException: TDS Protocol error: Invalid packet type 0x4f

** at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2486)**

** at net.sourceforge.jtds.jdbc.TdsCore.getNextRow(TdsCore.java:805)**

** at net.sourceforge.jtds.jdbc.JtdsResultSet.next(JtdsResultSet.java:611)**

** at org.jivesoftware.openfire.muc.spi.MUCPersistenceManager.loadRooms(MUCPersistenc eManager.java:484)**

** at org.jivesoftware.openfire.muc.spi.MUCPersistenceManager.loadRoomsFromDB(MUCPers istenceManager.java:445)**

** at org.jivesoftware.openfire.muc.spi.MultiUserChatServiceImpl.start(MultiUserChatS erviceImpl.java:1246)**

** at org.jivesoftware.openfire.component.InternalComponentManager.addComponent(Inter nalComponentManager.java:159)**

** at org.jivesoftware.openfire.muc.MultiUserChatManager.registerMultiUserChatService (MultiUserChatManager.java:161)**

** at org.jivesoftware.openfire.muc.MultiUserChatManager.start(MultiUserChatManager.j ava:112)**

** at org.jivesoftware.openfire.XMPPServer.startModules(XMPPServer.java:591)**

** at org.jivesoftware.openfire.XMPPServer.start(XMPPServer.java:455)**

** at org.jivesoftware.openfire.XMPPServer.(XMPPServer.java:169)**

** at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)**

** at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessor Impl.java:62)**

** at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructor AccessorImpl.java:45)**

** at java.lang.reflect.Constructor.newInstance(Constructor.java:423)**

** at java.lang.Class.newInstance(Class.java:442)**

** at org.jivesoftware.openfire.starter.ServerStarter.start(ServerStarter.java:105)**

** at org.jivesoftware.openfire.starter.ServerStarter.main(ServerStarter.java:56)**

Caused by: net.sourceforge.jtds.jdbc.ProtocolException: Invalid packet type 0x4f

** at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2475)**

** … 18 more**

2016.10.07 14:16:57 WARN [HouseKeeper]: org.jivesoftware.util.log.util.CommonsLogFactory - #0002 was active for 326860 milliseconds and has been removed automaticaly. The Thread responsible was named ‘main’, but the last SQL it performed is unknown because the trace property is not enabled.

2

Apparently, you’ve added the jTDS JDBC driver to Openfire, to connect to your database. This driver is generating an error. I can’t tell you why: that’s something you’ll probably have to check for with the jTDS support channel.

From what I’ve learned in a one-minute checkup, the jTDS driver offers a java alternative to other drivers. Perhaps you should use the original java driver for our database, if one is available.

3

what are you using for your database?

4

We are using libraries from freetds to connect to Microsoft SQL Server.

jTDS JDBC Driver

jTDS - SQL Server and Sybase JDBC driver

sqljdbc42.jar is the version that is being used.

5

I don’t think you are using the library that you think you are.

From openfire admin console

go to Server Manager>Database

This will tell you what driver and version you are using.

6

Please see the server details:

912×513 13.5 KB

this is the latest JDBC driver available I guess.

7

I haven’t tested the driver with Ms sql 2012, but that might be your issue.

You might want to give microsofts java driver a try.

Download Microsoft JDBC Drivers 6.0, 4.2, 4.1, and 4.0 for SQL Server from Official Microsoft Download Center

You may need to tweak your connection string, so be sure to read any relative docs

8

Thanks. I will try and update.

FYI (forgot to mention earlier). I have used freeTDS JDBC driver based on the openfire installation notes.

1339×385 16.2 KB

9

Oh wow, that’s severely outdated

10

Tried the following and not sure whether my configuration is correct or not.

Database URL is : jdbc:sqlserver://MSSQLSERVER02.c.com.iqor.qor.com;databaseName=QX;username=XXX;password=XXX

Following is the error I am getting

2016.10.12 06:44:15 WARN [HouseKeeper]: org.jivesoftware.util.log.util.CommonsLogFactory - #1901 was active for 320545 milliseconds and has been removed automaticaly. The Thread responsible was named ‘Jetty-QTP-AdminConsole-25’, but the last SQL it performed is unknown because the trace property is not enabled.

changed the URL with the following

jdbc:sqlserver://MSSQLSERVER02.c.com;databaseName=QX;user=XXX;password=XXX

*** and the result is same***

2016.10.12 08:50:08 WARN [HouseKeeper]: org.jivesoftware.util.log.util.CommonsLogFactory - #0008 was active for 302881 milliseconds and has been removed automaticaly. The Thread responsible was named ‘Jetty-QTP-AdminConsole-20’, but the last SQL it performed is unknown because the trace property is not enabled.

/opt/openfire/lib/

11

1049×774 19.9 KB

12

open your openfire.xml settings and look for your database

net.sourceforge.jtds.jdbc.Driver

jdbc:jtds:sqlserver://localhost/openfire;instance=sqlexpress</serverU RL>

faadsfasdfasdfasdfasdfa

adfasdfasdfasdfasdfasdf

your serverURL doesn’t require the username and password. openfire will pipe this in for you

to use the microsoft driver, try the following changes

com.microsoft.sqlserver.jdbc.SQLServerDriver

jdbc:sqlserver://localhost\sqlexpress;databaseName=openfire</serverUR L>

13

Thanks for the quick response.

I tried with the following values in openfire.xml

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

<defaultProvider>

  <driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>

  <serverURL>jdbc:sqlserver://MSSQL02Server.C.com;databaseName=QXXX</serverURL>

  <testSQL>select 1</testSQL>

  <testBeforeUse>true</testBeforeUse>

  <testAfterUse>true</testAfterUse>

  <minConnections>5</minConnections>

  <maxConnections>25</maxConnections>

  <connectionTimeout>1.0</connectionTimeout>

  <username encrypted="true">8028fb0ac86f45c189795d94e72662d043d6cdff31cc77a6</username>

  <password encrypted="true">c723830706265ac41b65dcc9b12fe2c139336ff56bf5e419</password>

</defaultProvider>

false

<enabled>false</enabled>

But when I click continue, following error shows up.

2016.10.12 14:44:22 WARN [Jetty-QTP-AdminConsole-15]: org.eclipse.jetty.servlet.ServletHandler -

javax.servlet.ServletException: java.lang.ExceptionInInitializerError

at org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl .java:905)

at org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.j ava:834)

at org.jivesoftware.openfire.admin.setup.setup_002ddatasource_002dstandard_jsp._js pService(setup_002ddatasource_002dstandard_jsp.java:446)

at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)

at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:812)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1669)

at com.opensymphony.module.sitemesh.filter.PageFilter.parsePage(PageFilter.java:11 8)

at com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(PageFilter.java:52)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1652)

at org.jivesoftware.util.LocaleFilter.doFilter(LocaleFilter.java:76)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1652)

at org.jivesoftware.util.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingF ilter.java:53)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1652)

at org.jivesoftware.admin.PluginFilter.doFilter(PluginFilter.java:80)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1652)

at org.jivesoftware.admin.AuthCheckFilter.doFilter(AuthCheckFilter.java:162)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1652)

at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:585)

at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)

at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:577)

at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:22 3)

at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:11 27)

at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515)

at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185 )

at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:106 1)

at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)

at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandler Collection.java:215)

at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.jav a:110)

at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)

at org.eclipse.jetty.server.Server.handle(Server.java:499)

at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:311)

at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257)

at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:544)

at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635 )

at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555)

at java.lang.Thread.run(Thread.java:745)

Caused by: java.lang.ExceptionInInitializerError

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.ja va:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.logicalcobwebs.cglib.proxy.Enhancer.setCallbacksHelper(Enhancer.java:616)

at org.logicalcobwebs.cglib.proxy.Enhancer.setThreadCallbacks(Enhancer.java:609)

at org.logicalcobwebs.cglib.proxy.Enhancer.createUsingReflection(Enhancer.java:631 )

at org.logicalcobwebs.cglib.proxy.Enhancer.firstInstance(Enhancer.java:538)

at org.logicalcobwebs.cglib.core.AbstractClassGenerator.create(AbstractClassGenera tor.java:225)

at org.logicalcobwebs.cglib.proxy.Enhancer.createHelper(Enhancer.java:377)

at org.logicalcobwebs.cglib.proxy.Enhancer.create(Enhancer.java:285)

at org.logicalcobwebs.proxool.ProxyFactory.getProxy(ProxyFactory.java:116)

at org.logicalcobwebs.proxool.ProxyFactory.getWrappedConnection(ProxyFactory.java: 85)

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

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

at java.sql.DriverManager.getConnection(DriverManager.java:664)

at java.sql.DriverManager.getConnection(DriverManager.java:208)

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

at org.jivesoftware.database.DbConnectionManager.setConnectionProvider(DbConnectio nManager.java:602)

at org.jivesoftware.openfire.admin.setup.setup_002ddatasource_002dstandard_jsp._js pService(setup_002ddatasource_002dstandard_jsp.java:220)

… 34 more

Caused by: org.logicalcobwebs.cglib.core.CodeGenerationException: java.lang.reflect.InvocationTargetException–>null

at org.logicalcobwebs.cglib.core.AbstractClassGenerator.create(AbstractClassGenera tor.java:237)

at org.logicalcobwebs.cglib.reflect.FastClass$Generator.create(FastClass.java:64)

at org.logicalcobwebs.cglib.proxy.MethodProxy.helper(MethodProxy.java:81)

at org.logicalcobwebs.cglib.proxy.MethodProxy.create(MethodProxy.java:46)

at $java.sql.Connection$$EnhancerByProxool$$700d1a6f.CGLIB$STATICHOOK1( )

at $java.sql.Connection$$EnhancerByProxool$$700d1a6f.()

… 54 more

Caused by: java.lang.reflect.InvocationTargetException

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.ja va:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.logicalcobwebs.cglib.core.ReflectUtils.defineClass(ReflectUtils.java:384)

at org.logicalcobwebs.cglib.core.AbstractClassGenerator.create(AbstractClassGenera tor.java:219)

… 59 more

Caused by: java.lang.SecurityException: class “com.microsoft.sqlserver.jdbc.ISQLServerConnection$$FastClassByProxool$$2b8cf6a f”'s signer information does not match signer information of other classes in the same package

at java.lang.ClassLoader.checkCerts(ClassLoader.java:895)

at java.lang.ClassLoader.preDefineClass(ClassLoader.java:665)

at java.lang.ClassLoader.defineClass(ClassLoader.java:758)

… 65 more

14

oh…this is the issue, I believe

“com.microsoft.sqlserver.jdbc.ISQLServerConnection$$FastClassByProxool$$2b8cf6a f”'s signer information does not match signer information of other classes in the same package

i ran into this issue too. as a work around I did the following:

open the sqljdbc42.jar with winrar. in META-INF, delete MANIFEST.MF, MSFTSIG.SF and MSFTSIG.RSA

I have no idea if that was the correct way to get around this issue or not… :confused:

15

Yes thanks. This helps and now SQL driver installation is successful.

1259×377 16 KB

Back to addressing the MUC room not loading to DB issue (this is still occurring)

16

are you still getting the same error as before?

17

Yes. It looks like sql connection is closed after ~5 mts.

2016.10.13 07:00:56 INFO [main]: org.jivesoftware.openfire.muc.spi.MultiUserChatServiceImpl - Multi User Chat domain: conference.openfire-xxx-alpha.xyz.com

2016.10.13 07:06:21 WARN [HouseKeeper]: org.jivesoftware.util.log.util.CommonsLogFactory - #0001 was active for 324321 milliseconds and has been removed automaticaly. The Thread responsible was named ‘main’, but the last SQL it performed is unknown because the trace property is not enabled.

2016.10.13 07:06:21 ERROR [main]: org.jivesoftware.openfire.muc.spi.MUCPersistenceManager - A database exception prevented one particular MUC room to be loaded from the database.

com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.

    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerEx ception.java:191)

    at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnectio n.java:395)

    at com.microsoft.sqlserver.jdbc.SQLServerStatement.checkClosed(SQLServerStatement. java:1051)

    at com.microsoft.sqlserver.jdbc.SQLServerResultSet.checkClosed(SQLServerResultSet. java:377)

    at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getLong(SQLServerResultSet.java :2332)

    at org.jivesoftware.openfire.muc.spi.MUCPersistenceManager.loadRooms(MUCPersistenc eManager.java:487)

    at org.jivesoftware.openfire.muc.spi.MUCPersistenceManager.loadRoomsFromDB(MUCPers istenceManager.java:445)

    at org.jivesoftware.openfire.muc.spi.MultiUserChatServiceImpl.start(MultiUserChatS erviceImpl.java:1246)

    at org.jivesoftware.openfire.component.InternalComponentManager.addComponent(Inter nalComponentManager.java:159)

    at org.jivesoftware.openfire.muc.MultiUserChatManager.registerMultiUserChatService (MultiUserChatManager.java:161)

    at org.jivesoftware.openfire.muc.MultiUserChatManager.start(MultiUserChatManager.j ava:112)

    at org.jivesoftware.openfire.XMPPServer.startModules(XMPPServer.java:591)

    at org.jivesoftware.openfire.XMPPServer.start(XMPPServer.java:455)

    at org.jivesoftware.openfire.XMPPServer.<init>(XMPPServer.java:169)

    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessor Impl.java:62)

    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructor AccessorImpl.java:45)

    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)

    at java.lang.Class.newInstance(Class.java:442)

    at org.jivesoftware.openfire.starter.ServerStarter.start(ServerStarter.java:105)

    at org.jivesoftware.openfire.starter.ServerStarter.main(ServerStarter.java:56)
18

slightly different error…im out of ideas. perhaps this is a timeout issue?

19

Yes it is timing out after 5 mts and 23 seconds.
From DB side query is returning immediately but it has a wait type of ASYNC_NETWORK_IO.
It is somehow able to load if ofMucRoom has 200 rows and having the above issue for anything more than 200.

20

I think i found the issue…this looks like a bug in openfire. the active timeout is set for 15 minutes, but its not being used. Instead its falling back to a default of 5 mins, and closing the connection. That’s only part of the problem…the other would be why its taking that long to process the query. That question is outside my skill set. I’ll open a ticket and ping one of the devs.