We're updating the issue view to help you get more done. 

Openfire failes to create tables on MySQL 5.6

Description

Openfire is using varchar(1024) for JID in OfRoster table and it looks this is too big for newer MySQL versions.

Environment

MySQL 5.6, 3.6.2

Acceptance Test - Entry

None

Activity

Show:
Florian Schmaus
March 20, 2013, 10:41 AM

RFC 6120 resource binding XML schema defines a maximum length for fullJID as 3071 (and a minimum of 8). The resource length is defined with a maximum of 1023. I can not find any indication that the JID resource (or the JID) is limited to 255 chars by the XMPP specs.

Surely newer MySQL provide a way to have keys with a greater size. Limiting the JID field length to 255 would break openfire wrt to the XMPP specs.

Florian Schmaus
March 20, 2013, 11:32 AM

From RFC 6122 2.1

RFC 6122 2.1 Fundamentals

Each allowable portion of a JID (localpart, domainpart, and resourcepart) MUST NOT be zero bytes in length and MUST NOT be more than 1023 bytes in length, resulting in a maximum total size (including the '@' and '/' separators) of 3071 bytes

So localport, domainpart and resourcepart max size is 1023. And all together can have a maximum of 3071 bytes (incl. '@' and '/').

Florian Schmaus
March 20, 2013, 4:45 PM

Fix rudimentary verified here.

Dele Olajide
June 24, 2013, 6:59 PM

This is now a blocker for any new Openfire install with mysql 5.6+
Solution is to modify mysql table definitions and limit the JID to 256 for indexing in resources\database\openfire_mysql.sql

CREATE TABLE ofRoster (
rosterID BIGINT NOT NULL,
username VARCHAR(64) NOT NULL,
jid VARCHAR(1024) NOT NULL,
sub TINYINT NOT NULL,
ask TINYINT NOT NULL,
recv TINYINT NOT NULL,
nick VARCHAR(255),
PRIMARY KEY (rosterID),
INDEX ofRoster_unameid_idx (username),
INDEX ofRoster_jid_idx (jid(255))
);

Dele Olajide
December 20, 2013, 6:12 PM

I have fixed this a while back in my customised version of Openfire. I will replicate it her

Assignee

Dele Olajide

Reporter

wroot

Expected Effort

None

Ignite Forum URL

None

Components

Fix versions

Affects versions

Priority

Blocker
Configure