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
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.
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 '/').
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))
I have fixed this a while back in my customised version of Openfire. I will replicate it her