MS SQL Warnings - The maximum key length is 900 bytes. (Bug: Openfire 3.x.x)

Openfire Openfire Support
1

Deploying a new Openfire server that will use Microsoft SQL Server for the database.

Openfire: 3.8.2

Operating System: Windows Server 2008 R2 Datacenter (x64)

SQL Server: Microsoft SQL Server 2008 R2

When running the openfire_sqlserver.sql script SQL server returns 2 warnings:

Warning! The maximum key length is 900 bytes.
The index ‘ofRoster_jid_idx’ has maximum length of 2048 bytes. For some combination of large values, the insert/update operation will fail.

Warning! The maximum key length is 900 bytes.
The index ‘ofSASLAuthorized_pk’ has maximum length of 4128 bytes. For some combination of large values, the insert/update operation will fail.

This seems to be caused by defining the columns that have lengths longer than 900 bytes in either an Index or as a constraint.

I was able to find a previous bug report in the archive (JM-899) that was marked as closed and issue being fixed in version 3.x. I have confirmed this is still a problem and confirmed to impact v3.8.2 and v3.9.1. I suspect this will affect any version using the MS SQL with the supplied database sql.

File: openfire_sqlserver.sql

Revision: 1650

Date: 2005-07-20 00:18:17 -0300 (Wed, 20 Jul 2005)

Can these warning be ignored or is there a workaround available? (That doesn’t require using a different database server)

2

Hey Mike,

I ran into the same problem. Don’t run the openfire_sqlserver.sql directly. Allow openfire to do it for you and you shouldn’t have any problems.

Start with an empty database and remove any database connection info from your /conf/openfire.xml

Make sure the db user have the rights to create tables.

3

Hey Calvin,

This doesn’t correct the error it simply suppresses the warning messages. I feel like the dev guys should look at fixing this… im not a real DBA but there must be a way to design the DB to support this.

4

Hey Mike,

I agree! That SQL haven’t been updated for awhile. I’m sure people can change the SQL to address the issue but we can’t be sure if there will be a negative impact on the system.

They should fix it since they know why it was designed this way. =)

5

I am also encountering this issue and have also posted, here:

openfire_sqlserver.sql script: Warning! The maximum key length is 900 bytes

6

its unlikely this warning will cause an issue, but it looks like to fix it you would change the tables

CREATE TABLE ofRoster (

rosterID INTEGER NOT NULL,

username NVARCHAR(64) NOT NULL,

jid NVARCHAR(1024) NOT NULL,

sub INTEGER NOT NULL,

ask INTEGER NOT NULL,

recv INTEGER NOT NULL,

nick NVARCHAR(255),

CONSTRAINT ofRoster_pk PRIMARY KEY (rosterID)

);

CREATE INDEX ofRoster_username_idx ON ofRoster (username ASC);

CREATE INDEX ofRoster_jid_idx ON ofRoster (jid ASC);

This tells us an entry a JID to be 1024 character saved in unicode (2 bytes per character) causing the size to be 2048. Since is unlikely anyone will have a jid that size, you could change this value down to something like 448. Then when the index is created using the jid, it will be under 900 bytes

Same theory goes for the

CREATE TABLE ofSASLAuthorized (

username NVARCHAR(64) NOT NULL,

principal NVARCHAR(2000) NOT NULL,

CONSTRAINT ofSASLAuthorized_pk PRIMARY KEY (username, principal)

This primary key is created by using the both the username and principal. Again, its unlikely that the principal will be 2000 characters, so you could set this to something like 320.

another option may be to use the INCLUDE

for example…

CONSTRAINT ofSASLAuthorized_pk PRIMARY KEY (username)

INCLUDE(principal)

I haven’t test any of this…so use at your own risk!!

7

Hey speedy, thanks! I’ll have a look at your fix and will give it a try. Thanks!

8

Ok, So I’ve incorporated your changes, and the scripts run fine now. I hope this all works fine also when under operation. Still, I think this script should be fixed so that the correct script is included in the distribution. Anyway, thanks Speedy!