Openfire failes to create tables on MySQL 5.6
Openfire is using varchar(1024) for JID in OfRoster table and it looks this is too big for newer MySQL versions.
MySQL 5.6, 3.6.2
I have fixed this a while back in my customised version of Openfire. I will replicate it her
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,
PRIMARY KEY (rosterID),
INDEX ofRoster_unameid_idx (username),
INDEX ofRoster_jid_idx (jid(255))
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 '/').
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.