when iq command is issued for message retrieval tigase throws an exception in the logs and the command fails with internal server error. (#391)

Pradeep Mathan opened 1 decade ago

Due Date
2014-12-02

when iq command is issued for message retrieval tigase throws an exception in the logs and the command fails with internal server error.

<!-- Out -->
<iq type="get" id="page1">
  <retrieve xmlns="urn:xmpp:archive"
            with="pradeep@m41Win8/Smack"
            start="1970-01-01T00:00:00Z">
    <set xmlns="http://jabber.org/protocol/rsm">
      <max>2</max>
    </set>
  </retrieve>
</iq>

<!-- In -->
<iq id="page1" xmlns="jabber:client" to="pradeep@m41win8/Gajim" type="error">
<retrieve xmlns="urn:xmpp:archive" start="1970-01-01T00:00:00Z" with="praveen@m41Win8/Smack">
    <set xmlns="http://jabber.org/protocol/rsm">
      <max>2</max>
    </set>
  </retrieve>
<error code="500" type="wait">
<internal-server-error xmlns="urn:ietf:params:xml:ns:xmpp-stanzas"/>
<text xmlns="urn:ietf:params:xml:ns:xmpp-stanzas" xml:lang="en">Database error occured</text>
</error>
</iq>

Tigase Server Logs:-

java.sql.SQLException: Incorrect syntax near 'limit'.

Can you please look into this issue.

Exceptions:

I am attaching the full stacktrace.

java.sql.SQLException: Incorrect syntax near 'limit'.
        at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
        at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2894)
        at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2334)
        at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:643)
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:506)
        at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:979)
        at tigase.archive.MessageArchiveDB.getItems(MessageArchiveDB.java:669)
        at tigase.archive.MessageArchiveDB.getItems(MessageArchiveDB.java:514)
        at tigase.archive.MessageArchiveComponent.getMessages(MessageArchiveComponent.java:407)
        at tigase.archive.MessageArchiveComponent.processActionPacket(MessageArchiveComponent.java:227)
        at tigase.archive.MessageArchiveComponent.processPacket(MessageArchiveComponent.java:107)
        at tigase.server.AbstractMessageReceiver$QueueListener.run(AbstractMessageReceiver.java:1475)

Caused by: java.sql.SQLException: 'date' is not a recognized built-in function name.
	at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
	at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2894)
	at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2334)
	at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:643)
	at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:506)
	at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:979)
	at tigase.archive.db.JDBCMessageArchiveRepository.getCollectionsCount(JDBCMessageArchiveRepository.java:676)
	at tigase.archive.db.JDBCMessageArchiveRepository.getCollections(JDBCMessageArchiveRepository.java:471)
	at tigase.archive.MessageArchiveComponent.listCollections(MessageArchiveComponent.java:307)
	at tigase.archive.MessageArchiveComponent.processActionPacket(MessageArchiveComponent.java:257)
	at tigase.archive.MessageArchiveComponent.processPacket(MessageArchiveComponent.java:111)
	at tigase.server.AbstractMessageReceiver$QueueListener.run(AbstractMessageReceiver.java:1490)

Activities

Artur Hefczyc commented 1 decade ago

I think this is duplicate to #2441
Wojciech Kapcia (Tigase) commented 1 decade ago

Pradeep Mathan, can you provide full stacktrace of the error?
Pradeep Mathan commented 1 decade ago

We use the stable version of Tigase 5.2.0, The message archiving component source code on inspection has problems for sql server regarding limit and offset. Anyway I will try to reproduce the error and send the server side stacktrace. I had included the SqlException error message.

Pradeep Mathan commented 1 decade ago

I am attaching the full stacktrace.

java.sql.SQLException: Incorrect syntax near 'limit'.
        at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
        at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2894)
        at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2334)
        at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:643)
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:506)
        at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:979)
        at tigase.archive.MessageArchiveDB.getItems(MessageArchiveDB.java:669)
        at tigase.archive.MessageArchiveDB.getItems(MessageArchiveDB.java:514)
        at tigase.archive.MessageArchiveComponent.getMessages(MessageArchiveComponent.java:407)
        at tigase.archive.MessageArchiveComponent.processActionPacket(MessageArchiveComponent.java:227)
        at tigase.archive.MessageArchiveComponent.processPacket(MessageArchiveComponent.java:107)
        at tigase.server.AbstractMessageReceiver$QueueListener.run(AbstractMessageReceiver.java:1475)

Wojciech Kapcia (Tigase) commented 1 decade ago

Pradeep Mathan - thank you for the stacktrace. It looks like the limitations of MS SQL in regard to currently used schema.

Andrzej, can you make the adjustments to the message archive schema and make it compatible with MS SQL server?
Andrzej Wójcik (Tigase) commented 1 decade ago

It is not issue with SQLServer schema but with SQLServer not supporting limit and @offset@.
Wojciech Kapcia (Tigase) commented 1 decade ago

(updated description with exceptions)

Pradeep Mathan commented 1 decade ago

Issue is not fixed. We are using Sql Server 2008.

tigase.db.TigaseDBException: Cound not retrieve items

    at tigase.archive.db.JDBCMessageArchiveRepository.getItems(JDBCMessageAr

chiveRepository.java:620)

    at tigase.archive.MessageArchiveComponent.getMessages(MessageArchiveComp

onent.java:425)

    at tigase.archive.MessageArchiveComponent.processActionPacket(MessageArc

hiveComponent.java:270)

    at tigase.archive.MessageArchiveComponent.processPacket(MessageArchiveCo

mponent.java:111)

    at tigase.server.AbstractMessageReceiver$QueueListener.run(AbstractMessa

geReceiver.java:1490)

Caused by: java.sql.SQLException: Incorrect syntax near 'limit'.

    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.j

ava:372)

    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2894)

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

    at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:643)

    at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement

.java:506)

    at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPrep

aredStatement.java:979)

    at tigase.archive.db.JDBCMessageArchiveRepository.getItems(JDBCMessageAr

chiveRepository.java:778)

    at tigase.archive.db.JDBCMessageArchiveRepository.getItems(JDBCMessageAr

chiveRepository.java:609)

    at tigase.archive.MessageArchiveComponent.getMessages(MessageArchiveComp

onent.java:425)

    at tigase.archive.MessageArchiveComponent.processActionPacket(MessageArc

hiveComponent.java:270)

    at tigase.archive.MessageArchiveComponent.processPacket(MessageArchiveCo

mponent.java:111)

    at tigase.server.AbstractMessageReceiver$QueueListener.run(AbstractMessa

geReceiver.java:1490)

Pradeep Mathan commented 1 decade ago

Am I missing something out, I tested with Nov 19 2014 nightly build, it was not working I had posted the stacktrace, Nov 20 2014 nightly build does not have Message Archiving Changes. I did not understand why the status was changed to closed. Can you kindly help us out.
Artur Hefczyc commented 1 decade ago

Pradeep, I am sorry, this is my mistake. I saw that it is marked as duplicated to #2441 which was closed already, so I closed this one as well. Reverting back to new.
Andrzej Wójcik (Tigase) commented 1 decade ago

I fixed SQL queries generated for SQLServer to retrieve list of messages and list of message collections.

It will be fixed in next nightly build of Tigase Message Archiving component.
Artur Hefczyc commented 1 decade ago

Pradeep, please check it our and let us know if the problem is resolved.

Pradeep Mathan commented 1 decade ago

Artur,

I checked the new tigase nightly build on Nov 30 and Dec 2 2014. I am using Sql Server 2008 as the sql database. I get an exception. Am I missing something out. This is the stacktrace.

SEVERE: Error retrieving messages

tigase.db.TigaseDBException: Cound not retrieve items

    at tigase.archive.db.JDBCMessageArchiveRepository.getItems(JDBCMessageAr

chiveRepository.java:646)

    at tigase.archive.MessageArchiveComponent.getMessages(MessageArchiveComp

onent.java:425)

    at tigase.archive.MessageArchiveComponent.processActionPacket(MessageArc

hiveComponent.java:270)

    at tigase.archive.MessageArchiveComponent.processPacket(MessageArchiveCo

mponent.java:111)

    at tigase.server.AbstractMessageReceiver$QueueListener.run(AbstractMessa

geReceiver.java:1490)

Caused by: java.sql.SQLException: Incorrect syntax near 'limit'.

    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.j

ava:372)

    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2894)

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

    at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:643)

    at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement

.java:506)

    at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPrep

aredStatement.java:979)

    at tigase.archive.db.JDBCMessageArchiveRepository.getItems(JDBCMessageAr

chiveRepository.java:804)

    at tigase.archive.db.JDBCMessageArchiveRepository.getItems(JDBCMessageAr

chiveRepository.java:635)

    at tigase.archive.MessageArchiveComponent.getMessages(MessageArchiveComp

onent.java:425)

    at tigase.archive.MessageArchiveComponent.processActionPacket(MessageArc

hiveComponent.java:270)

    at tigase.archive.MessageArchiveComponent.processPacket(MessageArchiveCo

mponent.java:111)

    at tigase.server.AbstractMessageReceiver$QueueListener.run(AbstractMessa

geReceiver.java:1490)

Artur Hefczyc commented 1 decade ago

Andrzej, please review stack trace and assist with resolution.
Andrzej Wójcik (Tigase) commented 1 decade ago

Could you share example JDBC connection string you pass to --user-repo-uri property in etc/init.properties file? I fix this issue and it works fine on my installation but I would like to see your connection string as from it I can get information which MSSQL Server database driver for Java is used so I could check this in same configuration.
Pradeep Mathan commented 1 decade ago

I am attaching the etc/init.properties file

--comp-class-1 = tigase.muc.MUCComponent

--virt-hosts = M41WIN8

--user-db-uri = jdbc:jtds:sqlserver://hostname;databaseName=tigasedb_Nov30;user=username;password=password;schema=dbo;lastUpdateCount=false;cacheMetaData=false

--user-db = sqlserver

--admins = admin@M41WIN8

--comp-name-4 = message-archive

--comp-name-3 = proxy

config-type = --gen-config-all

--comp-name-2 = pubsub

--comp-name-1 = muc

--cluster-mode = true

--sm-plugins = +message-archive-xep-0136

--debug = server

--comp-class-4 = tigase.archive.MessageArchiveComponent

--comp-class-3 = tigase.socks5.Socks5ProxyComponent

--comp-class-2 = tigase.pubsub.PubSubComponent
Artur Hefczyc commented 1 decade ago

What is the progress of this issue? It is blocking another ticket, so this is important to resolve it asap.
Andrzej Wójcik (Tigase) commented 1 decade ago

This issue is solved. Root cause of this issue was that we have 2 types of JDBC drivers for MSSQL and pervious fix was for SQLServer driver while I forgot to apply this fix also when we are using JTDS driver.
Login to comment

Type	Bug
Priority	Critical
Assignee	Artur Hefczyc
RedmineID	2442
Estimation	0
Spent time	0

Issue Votes (0)

Watchers (0)

Reference

tigase/_server/server-core#391