I just installed wildfire using a sql2005 database and got the following warning:
Warning! The maximum key length is 900 bytes. The index ‘‘jiveSASLAuthoirzed_pk’’ has maximum length of 4128 bytes. For some combination of large values, the insert/update operation will fail.
so I went on and tested it and the inserts will fail when the row exceeds 450 characters (length of username and principal)
with the following sql error:
Msg 1946, Level 16, State 3, Line 1
Operation failed. The index entry of length 2008 bytes for the index ‘‘jiveSASLAuthoirzed_pk’’ exceeds the maximum length of 900 bytes.
oracle has basicly the same problem, i asked our dba-er about it and he replyed with:
The key length is limited by the db block size as a key value can’‘t span multiple blocks, so it’‘s the varchar(2000) column that’'s the main problem. I suspect that your new db has a 2K block size, but the db from which it was exported was using a larger block size. Keep in mind that the key value must be smaller than the db block size for overhead.
Unfortunately the only way to create this index is to recreate the db with a larger blocksize as you can’'t decrease the column size unless the column is empty.
so even on a default oracle installation the inserts will fail if the username length + principal lengt is bigger then 2000 chars (i have not looked into the unicode storage of oracle so it could be 1000 chars.
I guess thats what I get for developing a database without being a DBA or talking to one. The idea was to both enforce the uniqueness of a user/principal combination and provide faster lookups. But I gess that was a bad idea. Im guessing the correct solution now is to add an AUTO INCREMENT id column as the primary key. Bummer. Unless perhaps your DBA has a better suggestion for the table? Im no DBA so I would yield to someone with more experience in this matter.
as you may know Jivesoftware does not like auto increment or other similar database features. I really wonder why they are using primary keys and unique constraints but no foreign keys and no auto increment values / sequences.
the problem with auto-increment is that its not “ANSI SQL95” or whatever standard compliant. Most databases use different strategies for incrementation. eg ms sql200x uses a “identity” statement on a column and oracle uses a sequence where you select the “nextval()” from.
All in all the usage of a sequence table with “high_low” algorithm is mostly used when devewloping cross database applications.
But back to this problem, I think a session_id or something liek that is the best way to go (together with username the PK. I have no idea how SASL behaves (is the full jid a unique identifier ??, then its easely changed ) .
just a note:
the usage of foreign keys has a lot of pre’'s, like data integrity and usage of the index when doing selects, the problem now is that the inserts/deletes need to be checked if they are done “in order”.