Migrate ejabberd DB schema (mysql) from 2.1.x

Hiya! Long time no see. So I was upgrading my beloved ejabberd a while back and when I looked closer I found that most of the new functionality just wouldn’t work. Mostly because my ejabberd’s DB schema dating back to some 2.1.x install didn’t fit anymore. So but how to upgrade? After playing weird ideas back and forth I came across a script called ‘php-mysql-diff‘. According to its description it sounded very promising to what I wanted to achieve. Just that it wouldn’t work right of the box. After tearing it apart and putting it back together again I found that this was because my dump files as created by mysqldump sport a ‘COLLATE’ keyword which wasn’t supported by the built-in regexp used by its parser. After I fixed this I got the desired result, a migration script that I could use to upgrade my schemas. Hooray!


# Disable Foreign Keys Check
SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = '';

# Deleted Tables

# Changed Tables

-- changed table `last`

ALTER TABLE `last`
CHANGE COLUMN `username` `username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL FIRST,
CHANGE COLUMN `seconds` `seconds` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `username`,
CHANGE COLUMN `state` `state` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `seconds`;

-- changed table `privacy_default_list`

ALTER TABLE `privacy_default_list`
CHANGE COLUMN `username` `username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL FIRST,
CHANGE COLUMN `name` `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `username`;

-- changed table `privacy_list`

ALTER TABLE `privacy_list`
CHANGE COLUMN `username` `username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL FIRST,
CHANGE COLUMN `name` `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `username`,
ADD COLUMN `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `id`;

-- changed table `privacy_list_data`

ALTER TABLE `privacy_list_data`
CHANGE COLUMN `t` `t` char(1) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `id`,
CHANGE COLUMN `value` `value` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `t`,
CHANGE COLUMN `action` `action` char(1) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `value`,
ADD KEY `i_privacy_list_data_id` (`id`);

-- changed table `private_storage`

ALTER TABLE `private_storage`
CHANGE COLUMN `username` `username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL FIRST,
CHANGE COLUMN `namespace` `namespace` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `username`,
CHANGE COLUMN `data` `data` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `namespace`,
ADD COLUMN `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `data`;

-- changed table `rostergroups`

ALTER TABLE `rostergroups`
CHANGE COLUMN `username` `username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL FIRST,
CHANGE COLUMN `jid` `jid` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `username`,
CHANGE COLUMN `grp` `grp` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `jid`;

-- changed table `rosterusers`

ALTER TABLE `rosterusers`
CHANGE COLUMN `username` `username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL FIRST,
CHANGE COLUMN `jid` `jid` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `username`,
CHANGE COLUMN `nick` `nick` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `jid`,
CHANGE COLUMN `subscription` `subscription` char(1) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `nick`,
CHANGE COLUMN `ask` `ask` char(1) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `subscription`,
CHANGE COLUMN `askmessage` `askmessage` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `ask`,
CHANGE COLUMN `server` `server` char(1) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `askmessage`,
CHANGE COLUMN `subscribe` `subscribe` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `server`,
CHANGE COLUMN `type` `type` text COLLATE utf8mb4_unicode_ci AFTER `subscribe`,
ADD COLUMN `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `type`;

-- changed table `spool`

ALTER TABLE `spool`
CHANGE COLUMN `username` `username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL FIRST,
CHANGE COLUMN `xml` `xml` blob NOT NULL AFTER `username`,
ADD COLUMN `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `seq`,
ADD KEY `i_spool_created_at` (`created_at`) USING BTREE;

-- changed table `users`

ALTER TABLE `users`
CHANGE COLUMN `username` `username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL FIRST,
CHANGE COLUMN `password` `password` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `username`,
ADD COLUMN `serverkey` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' AFTER `password`,
ADD COLUMN `salt` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' AFTER `serverkey`,
ADD COLUMN `iterationcount` int(11) NOT NULL DEFAULT '0' AFTER `salt`,
ADD COLUMN `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `iterationcount`;

-- changed table `vcard`

ALTER TABLE `vcard`
CHANGE COLUMN `username` `username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL FIRST,
CHANGE COLUMN `vcard` `vcard` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL AFTER `username`,
ADD COLUMN `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `vcard`;

-- changed table `vcard_search`

ALTER TABLE `vcard_search`
CHANGE COLUMN `username` `username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL FIRST,
CHANGE COLUMN `lusername` `lusername` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `username`,
CHANGE COLUMN `fn` `fn` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `lusername`,
CHANGE COLUMN `lfn` `lfn` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `fn`,
CHANGE COLUMN `family` `family` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `lfn`,
CHANGE COLUMN `lfamily` `lfamily` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `family`,
CHANGE COLUMN `given` `given` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `lfamily`,
CHANGE COLUMN `lgiven` `lgiven` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `given`,
CHANGE COLUMN `middle` `middle` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `lgiven`,
CHANGE COLUMN `lmiddle` `lmiddle` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `middle`,
CHANGE COLUMN `nickname` `nickname` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `lmiddle`,
CHANGE COLUMN `lnickname` `lnickname` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `nickname`,
CHANGE COLUMN `bday` `bday` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `lnickname`,
CHANGE COLUMN `lbday` `lbday` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `bday`,
CHANGE COLUMN `ctry` `ctry` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `lbday`,
CHANGE COLUMN `lctry` `lctry` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `ctry`,
CHANGE COLUMN `locality` `locality` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `lctry`,
CHANGE COLUMN `llocality` `llocality` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `locality`,
CHANGE COLUMN `email` `email` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `llocality`,
CHANGE COLUMN `lemail` `lemail` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `email`,
CHANGE COLUMN `orgname` `orgname` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `lemail`,
CHANGE COLUMN `lorgname` `lorgname` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `orgname`,
CHANGE COLUMN `orgunit` `orgunit` text COLLATE utf8mb4_unicode_ci NOT NULL AFTER `lorgname`,
CHANGE COLUMN `lorgunit` `lorgunit` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL AFTER `orgunit`;

# New Tables

-- new table `archive`

CREATE TABLE `archive` (
`username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`timestamp` bigint(20) unsigned NOT NULL,
`peer` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`bare_peer` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`xml` text COLLATE utf8mb4_unicode_ci NOT NULL,
`txt` text COLLATE utf8mb4_unicode_ci,
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`kind` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`nick` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY `i_bare_peer` (`bare_peer`) USING BTREE,
KEY `i_peer` (`peer`) USING BTREE,
FULLTEXT KEY `i_text` (`txt`),
KEY `i_timestamp` (`timestamp`) USING BTREE,
KEY `i_username` (`username`) USING BTREE,
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `archive_prefs`

CREATE TABLE `archive_prefs` (
`username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`def` text COLLATE utf8mb4_unicode_ci NOT NULL,
`always` text COLLATE utf8mb4_unicode_ci NOT NULL,
`never` text COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `caps_features`

CREATE TABLE `caps_features` (
`node` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`subnode` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`feature` text COLLATE utf8mb4_unicode_ci,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY `i_caps_features_node_subnode` (`node`(75),`subnode`(75))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `irc_custom`

CREATE TABLE `irc_custom` (
`jid` text COLLATE utf8mb4_unicode_ci NOT NULL,
`host` text COLLATE utf8mb4_unicode_ci NOT NULL,
`data` text COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY `i_irc_custom_jid_host` (`jid`(75),`host`(75)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `motd`

CREATE TABLE `motd` (
`username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`xml` text COLLATE utf8mb4_unicode_ci,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `muc_registered`

CREATE TABLE `muc_registered` (
`jid` text COLLATE utf8mb4_unicode_ci NOT NULL,
`host` text COLLATE utf8mb4_unicode_ci NOT NULL,
`nick` text COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY `i_muc_registered_jid_host` (`jid`(75),`host`(75)) USING BTREE,
KEY `i_muc_registered_nick` (`nick`(75)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `muc_room`

CREATE TABLE `muc_room` (
`name` text COLLATE utf8mb4_unicode_ci NOT NULL,
`host` text COLLATE utf8mb4_unicode_ci NOT NULL,
`opts` text COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY `i_muc_room_name_host` (`name`(75),`host`(75)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `pubsub_item`

CREATE TABLE `pubsub_item` (
`nodeid` bigint(20) DEFAULT NULL,
`itemid` text COLLATE utf8mb4_unicode_ci,
`publisher` text COLLATE utf8mb4_unicode_ci,
`creation` text COLLATE utf8mb4_unicode_ci,
`modification` text COLLATE utf8mb4_unicode_ci,
`payload` text COLLATE utf8mb4_unicode_ci,
KEY `i_pubsub_item_itemid` (`itemid`(36)),
UNIQUE KEY `i_pubsub_item_tuple` (`nodeid`,`itemid`(36)),
CONSTRAINT `pubsub_item_ibfk_1` FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (`nodeid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `pubsub_node`

CREATE TABLE `pubsub_node` (
`host` text COLLATE utf8mb4_unicode_ci,
`node` text COLLATE utf8mb4_unicode_ci,
`parent` text COLLATE utf8mb4_unicode_ci,
`type` text COLLATE utf8mb4_unicode_ci,
`nodeid` bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`nodeid`),
KEY `i_pubsub_node_parent` (`parent`(120)),
UNIQUE KEY `i_pubsub_node_tuple` (`host`(20),`node`(120))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `pubsub_node_option`

CREATE TABLE `pubsub_node_option` (
`nodeid` bigint(20) DEFAULT NULL,
`name` text COLLATE utf8mb4_unicode_ci,
`val` text COLLATE utf8mb4_unicode_ci,
KEY `i_pubsub_node_option_nodeid` (`nodeid`),
CONSTRAINT `pubsub_node_option_ibfk_1` FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (`nodeid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `pubsub_node_owner`

CREATE TABLE `pubsub_node_owner` (
`nodeid` bigint(20) DEFAULT NULL,
`owner` text COLLATE utf8mb4_unicode_ci,
KEY `i_pubsub_node_owner_nodeid` (`nodeid`),
CONSTRAINT `pubsub_node_owner_ibfk_1` FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (`nodeid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `pubsub_state`

CREATE TABLE `pubsub_state` (
`nodeid` bigint(20) DEFAULT NULL,
`jid` text COLLATE utf8mb4_unicode_ci,
`affiliation` char(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`subscriptions` text COLLATE utf8mb4_unicode_ci,
`stateid` bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`stateid`),
KEY `i_pubsub_state_jid` (`jid`(60)),
UNIQUE KEY `i_pubsub_state_tuple` (`nodeid`,`jid`(60)),
CONSTRAINT `pubsub_state_ibfk_1` FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (`nodeid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `pubsub_subscription_opt`

CREATE TABLE `pubsub_subscription_opt` (
`subid` text COLLATE utf8mb4_unicode_ci,
`opt_name` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`opt_value` text COLLATE utf8mb4_unicode_ci,
UNIQUE KEY `i_pubsub_subscription_opt` (`subid`(32),`opt_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `roster_version`

CREATE TABLE `roster_version` (
`username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`version` text COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `sm`

CREATE TABLE `sm` (
`usec` bigint(20) NOT NULL,
`pid` text COLLATE utf8mb4_unicode_ci NOT NULL,
`node` text COLLATE utf8mb4_unicode_ci NOT NULL,
`username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`resource` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`priority` text COLLATE utf8mb4_unicode_ci NOT NULL,
`info` text COLLATE utf8mb4_unicode_ci NOT NULL,
KEY `i_node` (`node`(75)),
UNIQUE KEY `i_sid` (`usec`,`pid`(75)),
KEY `i_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `sr_group`

CREATE TABLE `sr_group` (
`name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`opts` text COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `sr_user`

CREATE TABLE `sr_user` (
`jid` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`grp` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY `i_sr_user_grp` (`grp`),
KEY `i_sr_user_jid` (`jid`),
UNIQUE KEY `i_sr_user_jid_group` (`jid`(75),`grp`(75))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- new table `vcard_xupdate`

CREATE TABLE `vcard_xupdate` (
`username` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`hash` text COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# Disable Foreign Keys Check
SET FOREIGN_KEY_CHECKS = 1;

For convenience you can also download the file created: ejabberd_migrate2.sql.

PS: The fixed version of php-mysql-diff can be found at https://github.com/sstrigler/php-mysql-diff, a PR has been submitted.

Update: First version was working with an outdated schema for the users table. This is fixed at ejabberd_migrate2.sql now. Apparently my ejabberd-16.01 with shipped with an incorrect schema. :-/

Comments are closed