Ejabberd: muc_room not getting saved in sql

Created on 24 Jun 2021 · 14Comments · Source: processone/ejabberd

Environment

ejabberd version: 21.4.0
Erlang version: erl +V
OS: BigSur 11.4
Installed from: https://hub.docker.com/r/ejabberd/ecs

Configuration (only if needed): grep -Ev '^$|^\s*#' ejabberd.yml

###
###              ejabberd configuration file
###
### The parameters used in this configuration file are explained at
###
###       https://docs.ejabberd.im/admin/configuration
###
### The configuration file is written in YAML.
### *******************************************************
### *******           !!! WARNING !!!               *******
### *******     YAML IS INDENTATION SENSITIVE       *******
### ******* MAKE SURE YOU INDENT SECTIONS CORRECTLY *******
### *******************************************************
### Refer to http://en.wikipedia.org/wiki/YAML for the brief description.
###
sql_type: mysql
sql_server: "sypore-db"
sql_database: "ejabberd"
sql_username: "ejabberd"
sql_password: "password"
## If you want to specify the port:
sql_port: 3306

auth_method: sql
default_db: sql

hosts:
  - localhost

loglevel: 4
log_rotate_size: 10485760
log_rotate_date: ""
log_rotate_count: 1
log_rate_limit: 100

certfiles:
  - /home/ejabberd/conf/server.pem

ca_file: "/home/ejabberd/conf/cacert.pem"

## When using let's encrypt to generate certificates
## certfiles:
##  - /etc/letsencrypt/live/fullchain.pem
##  - /etc/letsencrypt/live/privkey.pem
##
## ca_file: "/etc/letsencrypt/live/hardstoneenterprises.com/fullchain.pem"

listen:
  -
    port: 5222
    ip: "::"
    module: ejabberd_c2s
    max_stanza_size: 262144
    shaper: c2s_shaper
    access: c2s
    starttls_required: true
  -
    port: 5269
    ip: "::"
    module: ejabberd_s2s_in
    max_stanza_size: 524288
  -
    port: 5443
    ip: "::"
    module: ejabberd_http
    tls: false
    request_handlers:
      "/admin": ejabberd_web_admin
      "/api": mod_http_api
      "/bosh": mod_bosh
      "/captcha": ejabberd_captcha
      "/upload": mod_http_upload
      "/ws": ejabberd_http_ws
      "/oauth": ejabberd_oauth
  -
    port: 5280
    ip: "::"
    module: ejabberd_http
    request_handlers:
      "/admin": ejabberd_web_admin
  -
    port: 1883
    ip: "::"
    module: mod_mqtt
    backlog: 1000
  ##
  ## https://docs.ejabberd.im/admin/configuration/#stun-and-turn
  ## ejabberd_stun: Handles STUN Binding requests
  ##
  ##-
  ##  port: 3478
  ##  ip: "0.0.0.0"
  ##  transport: udp
  ##  module: ejabberd_stun
  ##  use_turn: true
  ##  turn_ip: "{{ IP }}"
  ##  auth_type: user
  ##  auth_realm: "example.com"
  ##-
  ##  port: 3478
  ##  ip: "0.0.0.0"
  ##  module: ejabberd_stun
  ##  use_turn: true
  ##  turn_ip: "{{ IP }}"
  ##  auth_type: user
  ##  auth_realm: "example.com"
  ##- 
  ##  port: 5349
  ##  ip: "0.0.0.0"
  ##  module: ejabberd_stun
  ##  certfile: "/home/ejabberd/conf/server.pem"
  ##  tls: true
  ##  use_turn: true
  ##  turn_ip: "{{ IP }}"
  ##  auth_type: user
  ##  auth_realm: "example.com"
  ##
  ## https://docs.ejabberd.im/admin/configuration/#sip
  ## To handle SIP (VOIP) requests:
  ##
  ##-
  ##  port: 5060
  ##  ip: "0.0.0.0"
  ##  transport: udp
  ##  module: ejabberd_sip
  ##-
  ##  port: 5060
  ##  ip: "0.0.0.0"
  ##  module: ejabberd_sip
  ##-
  ##  port: 5061
  ##  ip: "0.0.0.0"
  ##  module: ejabberd_sip
  ##  tls: true

s2s_use_starttls: optional

acl:
  local:
    user_regexp: ""
  loopback:
    ip:
      - 127.0.0.0/8
      - ::1/128
      - ::FFFF:127.0.0.1/128
  admin:
    user:
      - "admin@localhost"

access_rules:
  local:
    allow: local
  c2s:
    deny: blocked
    allow: all
  announce:
    allow: admin
  configure:
    allow: admin
  muc_create:
    allow: local
  pubsub_createnode:
    allow: local
  trusted_network:
    allow: loopback

api_permissions:
  "console commands":
    from:
      - ejabberd_ctl
    who: all
    what: "*"
  "admin access":
    who:
      access:
        allow:
          acl: loopback
          acl: admin
      oauth:
        scope: "ejabberd:admin"
        access:
          allow:
            acl: loopback
            acl: admin
    what:
      - "*"
      - "!stop"
      - "!start"
  "public commands":
    who:
      ip: 0.0.0.0/0
    what:
      - "*"

shaper:
  normal: 1000
  fast: 50000

shaper_rules:
  max_user_sessions: 10
  max_user_offline_messages:
    5000: admin
    100: all
  c2s_shaper:
    none: admin
    normal: all
  s2s_shaper: fast

max_fsm_queue: 10000

acme:
   contact: "mailto:[email protected]"
   ca_url: "https://acme-v01.api.letsencrypt.org"

modules:
  mod_adhoc: {}
  mod_admin_extra: {}
  mod_announce:
    access: announce
  mod_avatar: {}
  mod_blocking: {}
  mod_bosh: {}
  mod_caps: {}
  mod_carboncopy: {}
  mod_client_state: {}
  mod_configure: {}
  mod_disco: {}
  mod_fail2ban: {}
  mod_http_api: {}
  mod_http_upload:
    put_url: https://@HOST@:5443/upload
  mod_last: {}
  mod_mam:
    ## Mnesia is limited to 2GB, better to use an SQL backend
    ## For small servers SQLite is a good fit and is very easy
    ## to configure. Uncomment this when you have SQL configured:
    db_type: sql
    clear_archive_on_room_destroy: false
    assume_mam_usage: true
    default: always
  mod_mqtt: {}
  mod_muc:
    db_type: sql
    access:
      - allow
    access_admin:
      - allow: admin
    access_create: muc_create
    access_persistent: muc_create
    access_mam:
      - allow
    default_room_options:
      allow_subscription: true  # enable MucSub
      mam: true
      members_only: true
      persistent: true
  mod_muc_admin: {}
  mod_offline:
    access_max_user_messages: max_user_offline_messages
  mod_ping: {}
  mod_privacy: {}
  mod_private: {}
  mod_proxy65:
    access: local
    max_connections: 5
  mod_pubsub:
    access_createnode: pubsub_createnode
    plugins:
      - flat
      - pep
    force_node_config:
      ## Avoid buggy clients to make their bookmarks public
      storage:bookmarks:
        access_model: whitelist
  mod_push: {}
  mod_push_keepalive: {}
  mod_register:
    ## Only accept registration requests from the "trusted"
    ## network (see access_rules section above).
    ## Think twice before enabling registration from any
    ## address. See the Jabber SPAM Manifesto for details:
    ## https://github.com/ge0rg/jabber-spam-fighting-manifesto
    ip_access: trusted_network
  mod_roster:
    versioning: true
  mod_sip: {}
  mod_s2s_dialback: {}
  mod_shared_roster: {}
  mod_stream_mgmt:
    resend_on_timeout: if_offline
  mod_vcard: {}
  mod_vcard_xupdate: {}
  mod_version:
    show_os: false

### Local Variables:
### mode: yaml
### End:
### vim: set filetype=yaml tabstop=8

Errors from error.log/crash.log

No errors

Hi
i have started exploring ejabberd. I was able to run via Docker and able to setup mysql. I created first user and it got saved in mysql database. so database connection is working fine. When i created a new room either from rest api or using ejabberdctl. Room are not getting in mysql. I can see room list in browser, so no errors while creating room. Room disappear on server restart. Looks like it is still getting in Mnesia. Please help.

Start up logs

2021-06-24 11:39:05.964969+00:00 [info] Loading configuration from /home/ejabberd/conf/ejabberd.yml

2021-06-24 11:39:06.032972+00:00 [warning] ACME directory URL https://acme-v01.api.letsencrypt.org defined in option acme->ca_url is deprecated and was automatically replaced with https://acme-v02.api.letsencrypt.org/directory. Please adjust your configuration file accordingly. Hint: run `ejabberdctl dump-config` command to view current configuration as it is seen by ejabberd.

2021-06-24 11:39:06.033412+00:00 [warning] Option 'log_rotate_date' is deprecated and has no effect anymore. Please remove it from the configuration.

2021-06-24 11:39:06.033552+00:00 [warning] Option 'log_rate_limit' is deprecated and has no effect anymore. Please remove it from the configuration.

2021-06-24 11:39:06.777679+00:00 [info] Configuration loaded successfully

2021-06-24 11:39:07.189437+00:00 [info] Got no NOTIFY_SOCKET, notifications disabled

2021-06-24 11:39:07.211965+00:00 [info] Building language translation cache

2021-06-24 11:39:07.540609+00:00 [info] Creating Mnesia ram table 'ejabberd_commands'

2021-06-24 11:39:07.638624+00:00 [info] Creating Mnesia ram table 'route'

2021-06-24 11:39:07.657917+00:00 [info] Creating Mnesia ram table 'route_multicast'

2021-06-24 11:39:07.695490+00:00 [info] Creating Mnesia ram table 'session'

2021-06-24 11:39:07.710452+00:00 [info] Creating Mnesia ram table 'session_counter'

2021-06-24 11:39:07.733268+00:00 [info] Creating Mnesia ram table 's2s'

2021-06-24 11:39:07.748820+00:00 [info] Creating Mnesia ram table 'temporarily_blocked'

2021-06-24 11:39:07.782196+00:00 [info] Loading modules for localhost

2021-06-24 11:39:07.782933+00:00 [info] Creating Mnesia ram table 'mod_register_ip'

2021-06-24 11:39:07.934711+00:00 [info] Creating Mnesia ram table 'muc_online_room'

2021-06-24 11:39:08.042214+00:00 [info] Creating Mnesia ram table 'bosh'

2021-06-24 11:39:08.264783+00:00 [info] Creating Mnesia ram table 'sip_session'

2021-06-24 11:39:08.337419+00:00 [info] Creating Mnesia ram table 'pubsub_last_item'

2021-06-24 11:39:08.405055+00:00 [info] Creating Mnesia ram table 'mqtt_session'

2021-06-24 11:39:08.420807+00:00 [info] Creating Mnesia ram table 'mqtt_sub'

2021-06-24 11:39:08.462355+00:00 [info] Building MQTT cache for localhost, this may take a while

2021-06-24 11:39:08.485861+00:00 [info] Creating Mnesia ram table 'bytestream'

2021-06-24 11:39:08.607631+00:00 [info] Waiting for Mnesia synchronization to complete

2021-06-24 11:39:08.770635+00:00 [warning] Invalid certificate in /home/ejabberd/conf/server.pem: at line 53: self-signed certificate

2021-06-24 11:39:08.777518+00:00 [info] ejabberd 21.4.0 is started in the node ejabberd@0fe01424af3a in 3.19s

2021-06-24 11:39:08.778196+00:00 [info] Start accepting TCP connections at [::]:5269 for ejabberd_s2s_in

2021-06-24 11:39:08.778042+00:00 [info] Start accepting TCP connections at [::]:5222 for ejabberd_c2s

2021-06-24 11:39:08.778597+00:00 [info] Start accepting TCP connections at [::]:5443 for ejabberd_http

2021-06-24 11:39:08.778597+00:00 [info] Start accepting TCP connections at [::]:5280 for ejabberd_http

2021-06-24 11:39:08.778813+00:00 [info] Start accepting TCP connections at [::]:1883 for mod_mqtt

2021-06-24 11:39:08.778863+00:00 [info] Start accepting TCP connections at 172.20.0.3:7777 for mod_proxy65_stream

2021-06-24 11:39:34.616975+00:00 [info] (<0.749.0>) Accepted connection [::ffff:172.20.0.1]:60274 -> [::ffff:172.20.0.3]:5443

MUC

Source

hassanrazakhalid

👀1

All 14 comments

Hi, did you set room config to make room persistent? We only store info about rooms that have this flag set (as rooms without it are ephemeral and live only as long as there are users in them).

prefiks on 24 Jun 2021

Yes. Please see the following logs. This is when i create a new room. Please also see the screenshot
Room Screenshot. I am using mysql 8.0.23

2021-06-24 13:13:58.802128+00:00 [debug] Executing command mod_muc_admin:create_room_with_opts with Args=[<<"1e1cf3d0-9030-40d7-ac63-8e55694b18e5">>,

<<"conference.localhost">>,

<<"localhost">>,

[{<<"mam">>,

<<"true">>},

{<<"persistent">>,

<<"true">>},

{<<"allow_user_invites">>,

<<"true">>}]]

hassanrazakhalid on 24 Jun 2021

When i created a new room either from rest api or using ejabberdctl. Room are not getting in mysql. I can see room list in browser, so no errors while creating room. Room disappear on server restart. Looks like it is still getting in Mnesia.

Can you try those ideas?

A) When you create a new room, check if the room is stored in the muc_room table in the mnesia database. Some ways to check that: in the ejabberd WebAdmin -> Nodes -> your node -> Database. Or in an erlang shell: mnesia:info().
B) If you create the room with a desktop XMPP client (like Gajim, Psi, ...), is it stored in any database?

badlop on 26 Jun 2021

@hassanrazakhalid: Have you seen the last comment of @badlop?

Neustradamus on 4 Jul 2021

Yes i have seen it. Really sorry i was unable to reply. I will respond in a day or two. Thank you for patience.

hassanrazakhalid on 5 Jul 2021

❤1

Hi really sorry for late response. Yes i can see the room in mnesia but in muc_online_room please see the logs.

test:traverse_table_and_show(muc_online_room).  
{muc_online_room,{<<"04b71211-131d-4419-b69a-3170c31aae98">>,
                  <<"conference.dev.ejabber.hardstoneenterprises.com">>},
                 <9182.831.0>}
[]

muc_room doesn't exist in Mnesia

test:traverse_table_and_show(muc_room).       
** exception exit: {aborted,{no_exists,muc_room}}
     in function  mnesia:wrap_trans/6 (mnesia.erl, line 497)
(ejabberd@4d7169ad023f)22> test:traverse_table_and_show(muc_room).

"muc_online_room" is being created in Mysql and and Mnesia. Please let me know if there anything else required.

hassanrazakhalid on 7 Jul 2021

❤1

Ok, and what about the second question I made?

B) If you create the room with a desktop XMPP client (like Gajim, Psi, ...), is it stored in any database?

badlop on 7 Jul 2021

Yes are right i tried connecting with Spark which Openfire client. And created Following points to consider

muc_online_room in MYSQL is still empty
muc_online_room in Mnesia has room
muc_room in MYSQL got an entry

hassanrazakhalid on 8 Jul 2021

Following packets exchanged after creating muc room. from spark

<iq xmlns="jabber:client" to="[email protected]" id="OMhte-102" type="get">
  <query xmlns="http://jabber.org/protocol/disco#info"/>
</iq>

<iq xmlns="jabber:client" to="[email protected]/Spark 2.9.4" from="[email protected]" id="OMhte-102" type="error">
  <error xmlns="jabber:client" type="cancel">
    <item-not-found xmlns="urn:ietf:params:xml:ns:xmpp-stanzas"/>
    <text xmlns="urn:ietf:params:xml:ns:xmpp-stanzas" xml:lang="en">Conference room does not exist</text>
  </error>
</iq>

<presence xmlns="jabber:client" to="[email protected]/admin" id="OMhte-104">
  <x xmlns="http://jabber.org/protocol/muc"/>
  <c xmlns="http://jabber.org/protocol/caps" hash="sha-1" node="http://www.igniterealtime.org/projects/smack" ver="9LJego/jm+LdNGOFm5gPTMPapl0="/>
</presence>

<presence xmlns="jabber:client" to="[email protected]/Spark 2.9.4" from="[email protected]" id="6755267713050837794">
  <c xmlns="http://jabber.org/protocol/caps" hash="sha-1" node="http://www.process-one.net/en/ejabberd/" ver="kqCmNqtSR3cvjboN+/HL3539N/M="/>
</presence>

<presence xmlns="jabber:client" to="[email protected]/Spark 2.9.4" from="[email protected]/admin" id="OMhte-104" xml:lang="en">
  <c xmlns="http://jabber.org/protocol/caps" hash="sha-1" node="http://www.igniterealtime.org/projects/smack" ver="9LJego/jm+LdNGOFm5gPTMPapl0="/>
  <x xmlns="vcard-temp:x:update">
    <photo>null</photo>
  </x>
  <x xmlns="http://jabber.org/protocol/muc#user">
    <item affiliation="owner" jid="[email protected]/Spark 2.9.4" role="moderator"/>
    <status code="201"/>
    <status code="110"/>
  </x>
</presence>


<message xmlns="jabber:client" to="[email protected]/Spark 2.9.4" from="[email protected]" type="groupchat">
  <subject/>
</message>

hassanrazakhalid on 8 Jul 2021

Ok, with those tests I finally got a clue what is going on. You found two independent problems:

A) Why muc_online_room is saved in Mnesia instead of SQL?

Because that table is saved according to this specific mod_muc option:

ram_db_type: mnesia
Define the type of volatile (in-memory) storage where the module will store room information. The only available value for this module is mnesia.

And looking at the source code, that ram_db_type options supports sql value since ejabberd 17.04: 12e01a51196a656955d79869a05ec3db1fc2f9bb

So, that documentation is incorrect. It has been fixed in 2e2667bbd7f00e465f9f58a2699069fdb2c60a4e

You can really configure like this:

modules:
  mod_muc:
    db_type: sql # This stores muc_room in SQL
    ram_db_type: sql # This stores muc_online_room and muc_online_users in SQL

B) Why a room created using "ejabberdctl create_room" is not stored in muc_room table (neither in Mnesia or SQL)?

Because that create_room API only takes care to create the room process. It doesn't take care to store the room, even if it's configured to be persistent.

One simple workaround: when creating a persistent room, use also "change_room_option": that API takes care to store the room in the database!

ejabberdctl create_room room444 conf.mysql.localhost mysql.localhost
ejabberdctl change_room_option room444 conf.mysql.localhost persistent true

That has been fixed in 271a9f097d251fb76912f935f83b29bae833799b and will be included in the next ejabberd release.

badlop on 8 Jul 2021

❤1

Thank you for all the support. When new release is expected.

hassanrazakhalid on 8 Jul 2021

❤1

@hassanrazakhalid please try to build from HEAD and test in your setup in the meantime.

licaon-kter on 8 Jul 2021

❤1

@badlop: How many "features" work only with mnesia?

Neustradamus on 8 Jul 2021

When new release is expected.

It would be nice to release before August, but it isn't 100% sure.

How many "features" work only with mnesia?

badlop on 8 Jul 2021

Was this page helpful?

0 / 5 - 0 ratings

Related issues

ERR_EMPTY_RESPONSE instead of admin panel

lgg · 4Comments

AccessRules: restrict who can view local vCards

cromain · 3Comments

Possibility to disable in-band account unregister?

haegar · 4Comments

API error: Account does not have the right to perform the operation

jjdp · 3Comments

cross domain http image upload

shazvan275 · 3Comments