The spool table created by ejabberd on MySQL doesn't have a PK. Is this a bug or a feature?

Question

Is this a bug (I could manually create a PK for it), or is this a feature?

Answer 1

I took a look at the current mysql.sql from the ejabberd 2.1.x branch for additional context at https://github.com/processone/ejabberd/blob/2.1.x/src/odbc/mysql.sql .

    CREATE TABLE spool (
        username varchar(250) NOT NULL,
        xml text NOT NULL,
        seq BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
        created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) CHARACTER SET utf8;

It looks like the "seq" column is a unique identifier that can be used the same way as a primary key except that no foreign key references can be made to it. I expect this is unimportant because there is never a time in ejabberd when you need to reference a particular offline message by its sequence number in another mysql table by way of a foreign key constraint.

I'm sure someone at processone had an opinion about this that resulted in a unique constraint instead of a primary key. Having worked extensively with mod_offline_odbc I can assure you that it won't make a difference to ejabberd if you add "primary_key(seq)" to the spool table definition and remove the redundant unique constraint on the seq column.