powered by Jive Software

Problem with Offline messages and MySQL


the stanza column in jiveOffline’s MySQL schema is type ‘text’ which limits offline messages to 65536 bytes. But openfire itself doesn’t seem to enforce this limit on offline messages and any message longer than this is just cut off. This becomes a problem as soon as the user goes back online and openfire tries to deliver these messages. The xml tags at the end of the message are missing then:

Error retrieving offline messages of username: foobar

org.dom4j.DocumentException: Error on line 874 of document  : XML document structures must start and end within the same ent
ity. Nested exception: XML document structures must start and end within the same entity.

This prohibits delivery off all offline messages for this user until the faulty offline message is deleted from the database table.

This issue exists at least with openfire 3.4.1 and 3.4.5.

For IBM DB2 databases this is limited to 2000 chars, which is a bit small, I think…in this case the above bug would happen much faster!

CREATE TABLE jiveOffline (
  username              VARCHAR(64)     NOT NULL,
  messageID             INTEGER         NOT NULL,
  creationDate          CHAR(15)        NOT NULL,
  messageSize           INTEGER         NOT NULL,
  stanza                VARCHAR(2000)   NOT NULL,
  CONSTRAINT jiveOffline_pk PRIMARY KEY (username, messageID)

(no, I’am not using DB2…I have MySQL, too…)

Message was edited by: Coolcat