Skip to content

# Upgrade to ejabberd 19.02

19.02 adds support of latest MIX specification and MQTT protocol. You may improve your current configuration or SQL schema depending on your needs.

## Configuration

To enable MQTT, you need to add a new listener and enable mod_mqtt. Here is example of configuration:

    listen:
      -
        port: 1883
        ip: "::"
        module: mod_mqtt
        backlog: 1000

    modules:
      mod_mqtt: {}

## Database

If you want to use latest MIX (XEP-0369) with an SQL backend then you have to create new tables.

If you have issues using PEP and MySQL, you should update your schema.

### MySQL

When using PEP with MySQL, due to limitation in size of indexes, some long JIDs could have broken support. Index size on pubsub_node was modified to fix use of PEP. This change is optional and required only if you have issues when using PEP.

    mysql -h host -u user database -p << EOF
    DROP INDEX i_pubsub_node_tuple ON pubsub_node;
    CREATE UNIQUE INDEX i_pubsub_node_tuple ON pubsub_node(host(71), node(120));
    EOF

If you want to use new MIX implementation, you must create some tables.

    CREATE TABLE mix_channel (
        channel text NOT NULL,
        service text NOT NULL,
        username text NOT NULL,
        domain text NOT NULL,
        jid text NOT NULL,
        hidden boolean NOT NULL,
        hmac_key text NOT NULL,
        created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

    CREATE UNIQUE INDEX i_mix_channel ON mix_channel (channel(191), service(191));
    CREATE INDEX i_mix_channel_serv ON mix_channel (service(191));

    CREATE TABLE mix_participant (
        channel text NOT NULL,
        service text NOT NULL,
        username text NOT NULL,
        domain text NOT NULL,
        jid text NOT NULL,
        id text NOT NULL,
        nick text NOT NULL,
        created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

    CREATE UNIQUE INDEX i_mix_participant ON mix_participant (channel(191), service(191), username(191), domain(191));
    CREATE INDEX i_mix_participant_chan_serv ON mix_participant (channel(191), service(191));

    CREATE TABLE mix_subscription (
        channel text NOT NULL,
        service text NOT NULL,
        username text NOT NULL,
        domain text NOT NULL,
        node text NOT NULL,
        jid text NOT NULL
    ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

    CREATE UNIQUE INDEX i_mix_subscription ON mix_subscription (channel(153), service(153), username(153), domain(153), node(153));
    CREATE INDEX i_mix_subscription_chan_serv_ud ON mix_subscription (channel(191), service(191), username(191), domain(191));
    CREATE INDEX i_mix_subscription_chan_serv_node ON mix_subscription (channel(191), service(191), node(191));
    CREATE INDEX i_mix_subscription_chan_serv ON mix_subscription (channel(191), service(191));

    CREATE TABLE mix_pam (
        username text NOT NULL,
        channel text NOT NULL,
        service text NOT NULL,
        id text NOT NULL,
        created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

    CREATE UNIQUE INDEX i_mix_pam ON mix_pam (username(191), channel(191), service(191));
    CREATE INDEX i_mix_pam_u ON mix_pam (username(191));

If you want to use new MQTT feature, you need to create a table.

If you're using the new schema (`new_sql_schema`):

    CREATE TABLE mqtt_pub (
        username varchar(191) NOT NULL,
        server_host varchar(191) NOT NULL,
        resource varchar(191) NOT NULL,
        topic text NOT NULL,
        qos tinyint NOT NULL,
        payload blob NOT NULL,
        payload_format tinyint NOT NULL,
        content_type text NOT NULL,
        response_topic text NOT NULL,
        correlation_data blob NOT NULL,
        user_properties blob NOT NULL,
        expiry int unsigned NOT NULL,
        UNIQUE KEY i_mqtt_topic_server (topic(191))
    );

If you're using the old schema:

    CREATE TABLE mqtt_pub (
        username varchar(191) NOT NULL,
        resource varchar(191) NOT NULL,
        topic text NOT NULL,
        qos tinyint NOT NULL,
        payload blob NOT NULL,
        payload_format tinyint NOT NULL,
        content_type text NOT NULL,
        response_topic text NOT NULL,
        correlation_data blob NOT NULL,
        user_properties blob NOT NULL,
        expiry int unsigned NOT NULL,
        UNIQUE KEY i_mqtt_topic (topic(191))
    );

### PostgreSQL

If you want to use new MIX implementation, you must create some tables.

    CREATE TABLE mix_channel (
        channel text NOT NULL,
        service text NOT NULL,
        username text NOT NULL,
        domain text NOT NULL,
        jid text NOT NULL,
        hidden boolean NOT NULL,
        hmac_key text NOT NULL,
        created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    );

    CREATE UNIQUE INDEX i_mix_channel ON mix_channel (channel, service);
    CREATE INDEX i_mix_channel_serv ON mix_channel (service);

    CREATE TABLE mix_participant (
        channel text NOT NULL,
        service text NOT NULL,
        username text NOT NULL,
        domain text NOT NULL,
        jid text NOT NULL,
        id text NOT NULL,
        nick text NOT NULL,
        created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    );

    CREATE UNIQUE INDEX i_mix_participant ON mix_participant (channel, service, username, domain);
    CREATE INDEX i_mix_participant_chan_serv ON mix_participant (channel, service);

    CREATE TABLE mix_subscription (
        channel text NOT NULL,
        service text NOT NULL,
        username text NOT NULL,
        domain text NOT NULL,
        node text NOT NULL,
        jid text NOT NULL
    );

    CREATE UNIQUE INDEX i_mix_subscription ON mix_subscription (channel, service, username, domain, node);
    CREATE INDEX i_mix_subscription_chan_serv_ud ON mix_subscription (channel, service, username, domain);
    CREATE INDEX i_mix_subscription_chan_serv_node ON mix_subscription (channel, service, node);
    CREATE INDEX i_mix_subscription_chan_serv ON mix_subscription (channel, service);

    CREATE TABLE mix_pam (
        username text NOT NULL,
        channel text NOT NULL,
        service text NOT NULL,
        id text NOT NULL,
        created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    );

    CREATE UNIQUE INDEX i_mix_pam ON mix_pam (username, channel, service);
    CREATE INDEX i_mix_pam_us ON mix_pam (username);

If you want to use new MQTT feature, you need to create a table.

If you're using the new schema (`new_sql_schema`):

    CREATE TABLE mqtt_pub (
        username text NOT NULL,
        server_host text NOT NULL,
        resource text NOT NULL,
        topic text NOT NULL,
        qos smallint NOT NULL,
        payload bytea NOT NULL,
        payload_format smallint NOT NULL,
        content_type text NOT NULL,
        response_topic text NOT NULL,
        correlation_data bytea NOT NULL,
        user_properties bytea NOT NULL,
        expiry bigint NOT NULL
    );

    CREATE UNIQUE INDEX i_mqtt_topic_server ON mqtt_pub (topic, server_host);

If you're using the old schema:

    CREATE TABLE mqtt_pub (
        username text NOT NULL,
        resource text NOT NULL,
        topic text NOT NULL,
        qos smallint NOT NULL,
        payload bytea NOT NULL,
        payload_format smallint NOT NULL,
        content_type text NOT NULL,
        response_topic text NOT NULL,
        correlation_data bytea NOT NULL,
        user_properties bytea NOT NULL,
        expiry bigint NOT NULL
    );

    CREATE UNIQUE INDEX i_mqtt_topic ON mqtt_pub (topic, server_host);

### SQLite

If you want to use new MIX implementation, you must create some tables.

    CREATE TABLE mix_channel (
        channel text NOT NULL,
        service text NOT NULL,
        username text NOT NULL,
        domain text NOT NULL,
        jid text NOT NULL,
        hidden boolean NOT NULL,
        hmac_key text NOT NULL,
        created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    );

    CREATE UNIQUE INDEX i_mix_channel ON mix_channel (channel, service);
    CREATE INDEX i_mix_channel_serv ON mix_channel (service);

    CREATE TABLE mix_participant (
        channel text NOT NULL,
        service text NOT NULL,
        username text NOT NULL,
        domain text NOT NULL,
        jid text NOT NULL,
        id text NOT NULL,
        nick text NOT NULL,
        created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    );

    CREATE UNIQUE INDEX i_mix_participant ON mix_participant (channel, service, username, domain);
    CREATE INDEX i_mix_participant_chan_serv ON mix_participant (channel, service);

    CREATE TABLE mix_subscription (
        channel text NOT NULL,
        service text NOT NULL,
        username text NOT NULL,
        domain text NOT NULL,
        node text NOT NULL,
        jid text NOT NULL
    );

    CREATE UNIQUE INDEX i_mix_subscription ON mix_subscription (channel, service, username, domain, node);
    CREATE INDEX i_mix_subscription_chan_serv_ud ON mix_subscription (channel, service, username, domain);
    CREATE INDEX i_mix_subscription_chan_serv_node ON mix_subscription (channel, service, node);
    CREATE INDEX i_mix_subscription_chan_serv ON mix_subscription (channel, service);

    CREATE TABLE mix_pam (
        username text NOT NULL,
        channel text NOT NULL,
        service text NOT NULL,
        id text NOT NULL,
        created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    );

    CREATE UNIQUE INDEX i_mix_pam ON mix_pam (username, channel, service);
    CREATE INDEX i_mix_pam_us ON mix_pam (username);

If you want to use new MQTT feature, you need to create a table.

If you're using the new schema (`new_sql_schema`):

    CREATE TABLE mqtt_pub (
        username text NOT NULL,
        server_host text NOT NULL,
        resource text NOT NULL,
        topic text NOT NULL,
        qos smallint NOT NULL,
        payload blob NOT NULL,
        payload_format smallint NOT NULL,
        content_type text NOT NULL,
        response_topic text NOT NULL,
        correlation_data blob NOT NULL,
        user_properties blob NOT NULL,
        expiry bigint NOT NULL
    );

    CREATE UNIQUE INDEX i_mqtt_topic_server ON mqtt_pub (topic);

If you're using the old schema:

    CREATE TABLE mqtt_pub (
        username text NOT NULL,
        resource text NOT NULL,
        topic text NOT NULL,
        qos smallint NOT NULL,
        payload blob NOT NULL,
        payload_format smallint NOT NULL,
        content_type text NOT NULL,
        response_topic text NOT NULL,
        correlation_data blob NOT NULL,
        user_properties blob NOT NULL,
        expiry bigint NOT NULL
    );

    CREATE UNIQUE INDEX i_mqtt_topic ON mqtt_pub (topic);