Bug Report VCard Update Failing: Issue with an external Database

Openfire Openfire Support
1

Hi,

the following Bug applies to Wildfire 3.2.4 with a DB/2 backend (DB2/LINUX SQL09010) on Linux and Spark 2.5.1 and 2.5.2.b1. A confirmation would be fine, if this applies to other settings/DBs as well.

Bug Description: The Avatar image is not updated to the external database. The client shows the image because of caching mechanisms.

Reproduction: Choose an avatar image and update your profile. The error log shows an error (see below). The client shows the avatar. Log on /log off from the server. The client still shows the Avatar. Clear your cache on the server. Select Update profile within spark. The avatar is not within your vcard xml file. This is observed in Raw Received Packages.

There are reports about this with other DB and russian characters and MS SQL Server http://www.igniterealtime.org/forum/thread.jspa?messageID=141289&#141289. However, this seems to be a connection problem to external databases. The problem can not be reproduced with the embedded DB ( HSQL Database Engine 1.8.0) and Openfire 3.3.0 on Windows.

Regards

Walter Ebeling

Error Log:

2007.04.23 11:15:55 org.jivesoftware.wildfire.vcard.DefaultVCardProvider.updateVCard(DefaultVCardPro vider.java:138) Error updating vCard of username: walter.ebeling

com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -302, SQLSTATE: 22001, SQLERRMC: null

at com.ibm.db2.jcc.c.fg.d(fg.java:1340)

at com.ibm.db2.jcc.b.gb.k(gb.java:351)

at com.ibm.db2.jcc.b.gb.a(gb.java:60)

at com.ibm.db2.jcc.b.w.a(w.java:52)

at com.ibm.db2.jcc.b.wb.c(wb.java:213)

at com.ibm.db2.jcc.c.gg.ab(gg.java:1779)

at com.ibm.db2.jcc.c.gg.d(gg.java:2324)

at com.ibm.db2.jcc.c.gg.d(gg.java:2420)

at com.ibm.db2.jcc.c.gg.W(gg.java:457)

at com.ibm.db2.jcc.c.gg.executeUpdate(gg.java:440)

at org.jivesoftware.wildfire.vcard.DefaultVCardProvider.updateVCard(DefaultVCardPr ovider.java:135)

at org.jivesoftware.wildfire.vcard.VCardManager.setVCard(VCardManager.java:130)

at org.jivesoftware.wildfire.handler.IQvCardHandler.handleIQ(IQvCardHandler.java:8 2)

at org.jivesoftware.wildfire.handler.IQHandler.process(IQHandler.java:48)

at org.jivesoftware.wildfire.IQRouter.handle(IQRouter.java:300)

at org.jivesoftware.wildfire.IQRouter.route(IQRouter.java:104)

at org.jivesoftware.wildfire.spi.PacketRouterImpl.route(PacketRouterImpl.java:67)

at org.jivesoftware.wildfire.net.StanzaHandler.processIQ(StanzaHandler.java:289)

at org.jivesoftware.wildfire.net.ClientStanzaHandler.processIQ(ClientStanzaHandler .java:79)

at org.jivesoftware.wildfire.net.StanzaHandler.process(StanzaHandler.java:254)

at org.jivesoftware.wildfire.net.StanzaHandler.process(StanzaHandler.java:153)

at org.jivesoftware.wildfire.nio.ConnectionHandler.messageReceived(ConnectionHandl er.java:131)

at org.apache.mina.common.support.AbstractIoFilterChain$TailFilter.messageReceived (AbstractIoFilterChain.java:703)

at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(Ab stractIoFilterChain.java:362)

at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilt erChain.java:54)

at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceive d(AbstractIoFilterChain.java:800)

at org.apache.mina.filter.codec.support.SimpleProtocolDecoderOutput.flush(SimplePr otocolDecoderOutput.java:62)

at org.apache.mina.filter.codec.ProtocolCodecFilter.messageReceived(ProtocolCodecF ilter.java:200)

at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(Ab stractIoFilterChain.java:362)

at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilt erChain.java:54)

at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceive d(AbstractIoFilterChain.java:800)

at org.apache.mina.filter.executor.ExecutorFilter.processEvent(ExecutorFilter.java :266)

at org.apache.mina.filter.executor.ExecutorFilter$ProcessEventsRunnable.run(Execut orFilter.java:326)

at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.lang.Thread.run(Unknown Source)

Debug Log:

2007.04.23 11:15:55 Received presence packet:

Message was edited by: webeling

2

I have to be a little more precise…

The same DB error shows up, if a field of the profile contains a german umlaut e.g. ü.

Walter

3

Hi,

after some further investigation, we were able to identify one reason for the avatar update failiure. The database for the vcard is allowing only 2000 Byte VCHAR type. We have extended the field and the avatar is working.

Recommendation: The creation script should be changed to create 32 KByte for the vcard data entry.

Walter

4

Hi Walter,

I can’'t reproduce this problem using an external HSQLDB with these connection parameters within openfire.xml

<connectionProvider>     <className>org.jivesoftware.database.DefaultConnectionProvider</className>   </connectionProvider>    <database>     <defaultProvider>       <driver>org.hsqldb.jdbcDriver</driver>        <serverURL>jdbc:hsqldb:hsql://localhost/openfire</serverURL>        <username>sa</username>        <password></password>        <minConnections>5</minConnections>        <maxConnections>15</maxConnections>        <connectionTimeout>1.0</connectionTimeout>     </defaultProvider>   </database>

The German IBM resource http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2. udb.doc/core/rsql0300.htm reads “SQL0302N Der Wert einer Hostvariablen in der Anweisung EXECUTE oder OPEN ist zu groß.”

I guess that the base64 encoded PHOTO string is too long to be inserted by DB2.

LG