powered by Jive Software

MySQL install error with ofRoster jid INDEX

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:

MySQL said:

#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,
nick VARCHAR(255),
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.