Troubleshooting wrong hostname in chat DB

Archived from the Ethora community forum. First posted on 15 April 2026; product details may have changed since. For current setup see Get Ethora and GitHub.

Taras Filatov (Ethora team) · 15 April 2026

Note: In this example, old host = ethoradev.com, new host = chat.ethora.com

Check which tables and records contain old hosts

sudo bash -lc '
source "/home/ubuntu/ethora-install-shared/deploy/.deploy.env"
docker-compose -f "/home/ubuntu/ethora-install-shared/deploy/docker-compose.enterprise.yml" exec -T mysql \
  mysql -uroot -p"$MYSQL_ROOT_PASSWORD" ejabberd_db <<'"'"'SQL'"'"'
SELECT "muc_room hosts" AS check_name;
SELECT host, COUNT(*) AS cnt
FROM muc_room
GROUP BY host
ORDER BY cnt DESC;

SELECT "muc_room_subscribers hosts" AS check_name;
SELECT host, COUNT(*) AS cnt
FROM muc_room_subscribers
GROUP BY host
ORDER BY cnt DESC;

SELECT "user_rooms hosts" AS check_name;
SELECT host, COUNT(*) AS cnt
FROM user_rooms
GROUP BY host
ORDER BY cnt DESC;

SELECT "legacy archive refs" AS check_name;
SELECT COUNT(*) AS cnt
FROM archive
WHERE username LIKE "%ethoradev.com%"
   OR peer LIKE "%ethoradev.com%"
   OR bare_peer LIKE "%ethoradev.com%"
   OR xml LIKE "%ethoradev.com%";

SELECT "legacy conference host rows" AS check_name;
SELECT COUNT(*) AS cnt FROM muc_room WHERE host = "conference.xmpp.ethoradev.com";
SELECT COUNT(*) AS cnt FROM muc_room_subscribers WHERE host = "conference.xmpp.ethoradev.com";
SELECT COUNT(*) AS cnt FROM user_rooms WHERE host = "conference.xmpp.ethoradev.com";
SQL
'

Repair old hosts entries

sudo bash -lc '
source "/home/ubuntu/ethora-install-shared/deploy/.deploy.env"
docker-compose -f "/home/ubuntu/ethora-install-shared/deploy/docker-compose.enterprise.yml" exec -T mysql \
  mysql -uroot -p"$MYSQL_ROOT_PASSWORD" ejabberd_db <<'"'"'SQL'"'"'
SET sql_safe_updates = 0;
START TRANSACTION;

UPDATE archive
SET
  username = REPLACE(REPLACE(username, "conference.xmpp.ethoradev.com", "conference.xmpp.chat.ethora.com"), "xmpp.ethoradev.com", "xmpp.chat.ethora.com"),
  peer = REPLACE(REPLACE(peer, "conference.xmpp.ethoradev.com", "conference.xmpp.chat.ethora.com"), "xmpp.ethoradev.com", "xmpp.chat.ethora.com"),
  bare_peer = REPLACE(REPLACE(bare_peer, "conference.xmpp.ethoradev.com", "conference.xmpp.chat.ethora.com"), "xmpp.ethoradev.com", "xmpp.chat.ethora.com"),
  xml = REPLACE(REPLACE(xml, "conference.xmpp.ethoradev.com", "conference.xmpp.chat.ethora.com"), "xmpp.ethoradev.com", "xmpp.chat.ethora.com")
WHERE
  username LIKE "%ethoradev.com%"
  OR peer LIKE "%ethoradev.com%"
  OR bare_peer LIKE "%ethoradev.com%"
  OR xml LIKE "%ethoradev.com%";

UPDATE muc_room
SET
  host = REPLACE(REPLACE(host, "conference.xmpp.ethoradev.com", "conference.xmpp.chat.ethora.com"), "xmpp.ethoradev.com", "xmpp.chat.ethora.com"),
  opts = REPLACE(REPLACE(opts, "conference.xmpp.ethoradev.com", "conference.xmpp.chat.ethora.com"), "xmpp.ethoradev.com", "xmpp.chat.ethora.com")
WHERE
  host LIKE "%ethoradev.com%"
  OR opts LIKE "%ethoradev.com%";

UPDATE muc_room_subscribers
SET
  host = REPLACE(REPLACE(host, "conference.xmpp.ethoradev.com", "conference.xmpp.chat.ethora.com"), "xmpp.ethoradev.com", "xmpp.chat.ethora.com"),
  jid = REPLACE(REPLACE(jid, "conference.xmpp.ethoradev.com", "conference.xmpp.chat.ethora.com"), "xmpp.ethoradev.com", "xmpp.chat.ethora.com")
WHERE
  host LIKE "%ethoradev.com%"
  OR jid LIKE "%ethoradev.com%";

UPDATE user_rooms
SET
  `user` = REPLACE(REPLACE(`user`, "conference.xmpp.ethoradev.com", "conference.xmpp.chat.ethora.com"), "xmpp.ethoradev.com", "xmpp.chat.ethora.com"),
  host = REPLACE(REPLACE(host, "conference.xmpp.ethoradev.com", "conference.xmpp.chat.ethora.com"), "xmpp.ethoradev.com", "xmpp.chat.ethora.com")
WHERE
  `user` LIKE "%ethoradev.com%"
  OR host LIKE "%ethoradev.com%";

COMMIT;
SQL
'

Verify after repair

sudo bash -lc '
source "/home/ubuntu/ethora-install-shared/deploy/.deploy.env"
docker-compose -f "/home/ubuntu/ethora-install-shared/deploy/docker-compose.enterprise.yml" exec -T mysql \
  mysql -uroot -p"$MYSQL_ROOT_PASSWORD" ejabberd_db <<'"'"'SQL'"'"'
SELECT host, COUNT(*) AS cnt FROM muc_room GROUP BY host ORDER BY cnt DESC;
SELECT host, COUNT(*) AS cnt FROM muc_room_subscribers GROUP BY host ORDER BY cnt DESC;
SELECT host, COUNT(*) AS cnt FROM user_rooms GROUP BY host ORDER BY cnt DESC;
SELECT COUNT(*) AS archive_legacy_refs
FROM archive
WHERE username LIKE "%ethoradev.com%"
   OR peer LIKE "%ethoradev.com%"
   OR bare_peer LIKE "%ethoradev.com%"
   OR xml LIKE "%ethoradev.com%";
SQL
'

Restart XMPP server

sudo bash -lc '
source "/home/ubuntu/ethora-install-shared/deploy/.deploy.env"
docker-compose -f "/home/ubuntu/ethora-install-shared/deploy/docker-compose.enterprise.yml" restart xmpp
'