Running MySQL 4.1.20, we got a database install error in creating ofRoster. We manually tool the openfire_myaql.sal file from the install directory and run the SQL from the MySQL admin tool.
The error was:
#1170 - BLOB/TEXT column ‘jid’ used in key specification without a key length
After investigating similar issues, we modified the script to:
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(70))
We defined the length if the ‘jid’ INDEX to 70 characters. We used 70 because in table ofMucMember, the jid as a primery key was defined to 70 characters since the field was a TEXT.
Could some please let me know that this is OK? The installation of the database worked, but want to make certain the length of 70 is ok for this index.