Creating tables for mysql 5.6 fails on openfire 3.8

Openfire Openfire Support
1

I Guys,

I’m using MySQL 5.6 on Windows Server 2008.

Before configuring openfire 3.8, when trying to import the MySQL tables from the resources file I get this:

“Error 1071 (42000) at line 57: Specified key was too long; max key lenght is 767 bytes”

It seems to be a problem with the ofRoster table creation… probably due to the JID being a varchar(1024)…

Can I change it to a varchar(255) without problems? Why does it have such a high size?

Cheers

Flávio

2

Thanks for this

I just had the same problem on a new Openfire install with mySQL 3.6.2 latest

3

Glad I could help…

Altough since no one replied I really don’t know the impact of the fix… But it seems to worl ok…

Cheers

4

I think your fix is alright. 1024 characters is an overkill for an XMPP JID. The resource part of a JID is limited to 255 by the spec, so somthing larger than 255 may be required.

I’ll try and raise a fault on JIRA as soon as I get a chance.

5

No worry, Dele, i have filed this for you OF-654

6

I can not find any indication in the XMPP specs that the resource part is limited to 255 chars. See also http://issues.igniterealtime.org/browse/OF-654?focusedCommentId=20146&page=com.a tlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-20146

7

wroot, thank you so much . You are a star

-dele

8

It was in the old XEP which is superceeded by

by RFC 3920, which in turn has been superseded by RFC 6122.

http://xmpp.org/extensions/xep-0029.html

2.4 Resource Identifier

Resources identifiers are case-sensitive and are limited to 256 bytes. They may include any Unicode character greater than #x20, except #xFFFE and #xFFFF.

2.5 Limited Resources

To date, resource identifiers have not had a fixed limit on their length. This document seeks to limit it to 256 bytes for the following reasons:

  1. In order to perform JID manipulations safely, one cannot use stack space if there is no limit. This forces temporary calculations onto the heap which is unnecessarily costly.
  2. As a fixed length character field, a resource identifier is more easily stored in, searched on, and retrieved from a database. If an end user may store an encyclopedia’s worth of information in their resource, then the only way it can be stored without truncating it is to store it as a large object (BLOB or CLOB). Depending on the database used, that makes it either grossly inefficient or impossible to search on.
  3. There exist denial of service attacks stemming from an unlimited resource length.
9

XEP-0029 is retracted. RFC 6122 defines the max size for resource part to be 1023 bytes.

10

The issue for me is is not with the spec max size. It is the limitation in the implementation

Right now, we need to fix the install script for Openfire that is broken with mySQL 5.6.2, but works fine with HSQLDB (embedded database) and probably the others (which I could not test)

Any DB (mySQL expert) who can through some light on why strings with 1024 characters are no more acceptable in mySQL 5.6.2, but were alright in 5.2 and what is the alternative?

How do we recode this?

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)
);
11

It seems to be possible to reduce the prefix used as index

INDEX of Roster_unamed_idx (username(256)),

INDEX ofRoster_jid_idx (jid(256)),

may do the trick. Maybe somebody can test this. But I am not 100% sure and no mysql expert.

See also http://stackoverflow.com/a/1814594/194894

12

Thanks Flow. I can confirm this works and is a better fix that truncating JID to 255

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(255))
);

It did not like 256 key length for JID using default character set.

1 Like
13

so for us n00bs that depend on commands … what did you do to fix this, I am having to migrate my old openfire server to a new serverhost. As such the old DB is toast (that is why I am moving it) I am switching from the embeded DB to a MySQL db for a couple of reasons. But I have encountered this error on the new install.

Hope that made sense …

P

14

  1. stop openfire

  2. make the change above to OPENFIRE_HOME\resources\database\openfire_mysql.sql

  3. restart openfire

15

that was perfect - thank you fixed the problem straight away!

16

I have linked OF-654 with OF-697 thinking maybe newer mysql jdbc driver can fix this issue too, but now i doubt. Dele, Flow can you clarify? New driver is already in the trunk.

17

I first changed driver myself and it had no effect. The script has to be modified. I still have to deal with this issue on every new deployment of Openfire with SQL