Skip to content

# Upgrade to ejabberd 17.03

If you are upgrading from ejabberd 16.08, 16.09, 16.12 or 17.01, and are using an SQL backend, you need to alter tables for better PubSub support before starting ejabberd 17.03.

## MySQL database upgrade

If you're running MySQL, this change in not mandatory but highly recommended

    mysql -h host -u user database -p << EOF
    ALTER TABLE rosterusers MODIFY subscribe text NOT NULL;

    UPDATE pubsub_node SET parent='' WHERE parent=NULL;

    ALTER TABLE pubsub_node
     MODIFY host TEXT NOT NULL,
     MODIFY node TEXT NOT NULL,
     MODIFY parent VARCHAR(191) NOT NULL DEFAULT '',
     MODIFY type TEXT NOT NULL;

    ALTER TABLE pubsub_node_option
     MODIFY name text NOT NULL,
     MODIFY val text NOT NULL;

    ALTER TABLE pubsub_node_owner
     MODIFY owner text NOT NULL;

    UPDATE pubsub_state SET subscriptions='' WHERE subscriptions=NULL;

    ALTER TABLE pubsub_state
     MODIFY jid text NOT NULL,
     MODIFY subscriptions VARCHAR(191) NOT NULL DEFAULT '';

    ALTER TABLE pubsub_item
     MODIFY itemid text NOT NULL,
     MODIFY publisher text NOT NULL,
     MODIFY creation text NOT NULL,
     MODIFY modification text NOT NULL,
     MODIFY payload text NOT NULL;

    ALTER TABLE pubsub_subscription_opt
     MODIFY subid text NOT NULL,
     MODIFY opt_value text NOT NULL;
    EOF

## PostgreSQL database upgrade

If you're running PostgreSQL, this change is mandatory.

    psql -W -h host database user << EOF
    ALTER TABLE rosterusers ALTER COLUMN subscribe SET NOT NULL;

    UPDATE pubsub_node SET parent='' WHERE parent=NULL;

    ALTER TABLE pubsub_node
    ALTER COLUMN host SET NOT NULL,
    ALTER COLUMN node SET NOT NULL,
    ALTER COLUMN parent SET NOT NULL,
    ALTER COLUMN parent SET DEFAULT '',
    ALTER COLUMN type SET NOT NULL;

    ALTER TABLE pubsub_node_option
    ALTER COLUMN name SET NOT NULL,
    ALTER COLUMN val SET NOT NULL;

    ALTER TABLE pubsub_node_owner
    ALTER COLUMN  owner SET NOT NULL;

    UPDATE pubsub_state SET subscriptions='' WHERE subscriptions=NULL;

    ALTER TABLE pubsub_state
    ALTER COLUMN jid SET NOT NULL,
    ALTER COLUMN subscriptions SET NOT NULL,
    ALTER COLUMN subscriptions SET DEFAULT '';

    ALTER TABLE pubsub_item
    ALTER COLUMN itemid SET NOT NULL,
    ALTER COLUMN publisher SET NOT NULL,
    ALTER COLUMN creation SET NOT NULL,
    ALTER COLUMN modification SET NOT NULL,
    ALTER COLUMN payload SET NOT NULL;

    ALTER TABLE pubsub_subscription_opt
    ALTER COLUMN subid SET NOT NULL,
    ALTER COLUMN opt_value SET NOT NULL;
    EOF

## SQLite database upgrade

If you're running SQLite, you have to create a new database with schema file provided in ejabberd 17.03 sources. Then you have to export all data from your current database and import into the newly created database.

## MsSQL database upgrade

We do not provide tested upgrade procedure on MsSQL Server. The upgrade may not be mandatory (this was not tested) but highly recommended. You have to create a new database with schema file provided in ejabberd 17.03 sources. Then you have to export all data from your current database and import into the newly created database.