[ejabberd] MySQL schema

Raoul Duke rduke496 at gmail.com
Sat Apr 9 21:56:52 MSK 2016

Hi Edward,

On Sun, Apr 3, 2016 at 9:16 PM, Edward Hibbert <edward at ehibbert.org.uk>

> I'm just getting started with ejabberd, so apologies for any elementary
> mistakes or breach of etiquette.
> I've configured ejabberd to store its DB in MySQL.  I saw a problem which
> I think was because of the way the muc_room table is defined.  The opts
> column seems to be used to store the affiliations, in a rather inefficient
> format (but presumably there's a reason for that). However it's only
> defined as a TEXT, which means that if the number of affiliations becomes
> large, then the column will get truncated, and bad things will happen.
> I've changed it to a LONGTEXT in my schema, but I was wondering:
>    1. Am I correct that this schema is wrong?
> that seems like a valid point to me but I should say I am not a core
developer some someone more knowledable about ejabberd should comment
really.  indeed the maxlength of a TEXT type in MySQL appers to be 64K.
 maybe the mysql schema was based on postgres where TEXT is unlimited.

>    1. What's the reason for the format where the affiliated users are
>    stored in a string-encoded ASCII format?
> do you mean the "opts" field of the muc_room table?

I am not an expert in ejabberd but having looked at the MUC internals a
little I would say that the reason for this is that the MySQL schema mirros
the schema from the default internal format in ejabberd (which uses the
mnesia database) where it would be pretty efficient to store a list of
affiliations in the "opts" field. mnesia can store native erlang
datastructures whereas to map that to a relationsal schema (one to one)
necessitates serializing to (something like) JSON.

>    1. Doesn't this use of the opts field to store all affiliations
>    (rather than a separate table) mean that the load on the DB isn't tenable
>    at scale, when each affiliation change will result in an enormous UPDATE
>    call?
> I think you may have a point if (say) thousands of users had affiliations
for one MUC room.

if this is a concern for you I would suggest that the default relationaal
schema is not necessarily set in stone and if you wanted to make a
muc_room_affiliations table (say) then that shouldn' tbe too hard to do.
 it would (in the first instance) mean forking the default muc

I think this is the place at the opts are currentl serialized to ODBC:
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.jabber.ru/pipermail/ejabberd/attachments/20160409/f864833a/attachment.html>

More information about the ejabberd mailing list