Hi guys,
There seems to be an issue with the file - /opt/openfire/resources/database/openfire_mysql.sql , still present in 3.6.0a
The MySQL maximum size for a varchar type is 256, when you try to create a varchar field larger than this it gets converted to a ‘text’ type. MySQL will not create an index on a BLOB or TEXT field due to the variable size.
See here for a better explanation : http://www.mydigitallife.info/2007/07/09/mysql-error-1170-42000-blobtext-column- used-in-key-specification-without-a-key-length/
CREATE TABLE jiveRoster (
rosterID BIGINT NOT NULL,
username VARCHAR(64) NOT NULL,
jid VARCHAR(255) NOT NULL, <-- had to change to 255 from 1024
sub TINYINT NOT NULL,
ask TINYINT NOT NULL,
recv TINYINT NOT NULL,
nick VARCHAR(255),
PRIMARY KEY (rosterID),
INDEX jiveRoster_unameid_idx (username),
INDEX jiveRoster_jid_idx (jid)
);
cheers,
kbp