SQL exception with 1.3.1-beta4 on Oracle (monitoring plugin)

1.3.1-beta4 from SVN with the table updates manually applied - Can’t figure out how to make OF log the SQL used which causes the exception.

2013.03.19 21:54:30 org.jivesoftware.util.Log - Error selecting conversations

java.sql.SQLSyntaxErrorException: ORA-00997: illegal use of LONG datatype

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)

at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)

at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)

at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)

at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)

at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)

at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement .java:884)

at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:11 67)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:12 89)

at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatem ent.java:3584)

at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement .java:3628)

at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedSt atementWrapper.java:1493)

at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)

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

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

at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)

at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)

at oracle.jdbc.internal.OraclePreparedStatement$$EnhancerByProxool$$4a5c63d0.execu teQuery()

at com.reucon.openfire.plugin.archive.impl.JdbcPersistenceManager.getActiveConvers ations(JdbcPersistenceManager.java:381)

at com.reucon.openfire.plugin.archive.impl.ArchiveManagerImpl.(ArchiveManage rImpl.java:35)

at org.jivesoftware.openfire.plugin.MonitoringPlugin.initializePlugin(MonitoringPl ugin.java:154)

at org.jivesoftware.openfire.container.PluginManager.loadPlugin(PluginManager.java :483)

at org.jivesoftware.openfire.container.PluginManager.access$300(PluginManager.java :80)

at org.jivesoftware.openfire.container.PluginManager$PluginMonitor.run(PluginManag er.java:1073)

at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)

at java.util.concurrent.FutureTask$Sync.innerRunAndReset(FutureTask.java:351)

at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:178)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201 (ScheduledThreadPoolExecutor.java:165)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Schedu ledThreadPoolExecutor.java:267)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)

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

Worked with one of my DBAs on this. This is the query that fails.

SELECT DISTINCT ofConversation.conversationID, ofConversation.room,

ofConversation.isExternal, ofConversation.startDate,

ofConversation.lastActivity, ofConversation.messageCount,

ofConParticipant.joinedDate, ofConParticipant.leftDate,

ofConParticipant.bareJID, ofConParticipant.jidResource,

ofConParticipant.nickname, ofMessageArchive.fromJID, ofMessageArchive.toJID,

ofMessageArchive.sentDate, ofMessageArchive.body

FROM

ofConversation INNER JOIN ofConParticipant ON ofConversation.conversationID =

ofConParticipant.conversationID INNER JOIN ofMessageArchive ON

ofConParticipant.conversationID = ofMessageArchive.conversationID WHERE

ofConversation.lastActivity > :1

Oracle doesn’t permit using SELECT DISTINCT on a LONG column (which body is). We’re working on trying to change the column type to see if we can fix it.

We ended up changing body from LONG to VARCHAR2(4000) which works better!

Now we hit this constraint violation when it tries to insert a new archive entry:

2013.03.20 12:39:49 org.jivesoftware.openfire.archive.Conversation - ORA-01400: cannot insert NULL into (“OFADMIN”.“OFCONPARTICIPANT”.“JIDRESOURCE”)

jidResource is set to NOT NULL, and I see in the code when it runs INSERT_PARTICIPANT prepared statement it checks to see if user.getResource() is null or not and is supposed to insert “” instead of NULL.

Someone else mentioned seeing nulls for MUC participants, which sounds like the same problem - Just don’t know my way around the code to identify it.

Constraint violation only appears to happen when i ‘talk to myself’, although it doesn’t happen with the 1.2.0 monitoring plugin.

We ended up disabling the constraint also to bring the archiving back to being visible.

BTW Oracle treats “” as null as of 9i

I realize this post is a year old, but I’m running into the same issue. This appears to be a bug in the Monitoring plugin when used with Oracle. Won’t some of your chat messages get truncated by using VARCHAR2(4000)? CLOB seems like a more compatibile replace for LONG, but this doesn’t solve the SELECT DISTINCT issue.

I tried changing ofMessageArchive.body to CLOB and then modified the offending SELECT DISTINCT queries to use dbms_lob.substr(ofMessageArchive.body,4000,1). This will work for all duplicate records that have differences in the first 4000 characters of the body. From my analysis, none of the code that runs the queries reads the value of the body field so it must be included only for the DISTINCT clause.

My workaround seems pretty safe to me, but I wish I understood why DISTINCT is needed at all so I could understand the cases I am missing.