mod_archive_odbc release


This article is about mod_archive_odbc: ejabberd implementation of server-side instant messaging history support, also known as XEP-136.

Update: development of this module has stopped, all further development is done in mod_archive2 project.

Development history

Original version of module for XEP-136 support in ejabberd, mod_archive was written by Olivier Goffart in 2006, see his post.

Without this original great work I do not think I would even start working on this subject at all, as I didn’t know Erlang and had no idea on where to start from.

When trying to experiment with this original mod_archive implementation I soon discovered some bugs in RSM handling, as well as missing features here and there.

I’ve performed some fixing, added replication support and submitted patch in ejabberd bugtracking system.

However, the patch was not applied as at this time there was already another implementation of mod_archive: mod_archive_sql, which is basically the port of original, mnesia-based mod_archive to PostgreSQL RDBMS, and ejabberd developers didn’t want these versions to differ.

To be true, I find the approach of maintaining two separate versions of the same module that differ only by storage engine pretty ridiculous, and for sure I wasn’t going to participate in that “road to hell” - I worked once for one big project that had very high rate of duplicate code (hello to those my ex co-workers who’re reading this! , and I know how bad it is …

Therefore, I’ve stopped at that time with further mod_archive fixes and switched to the other task - writing client library for XEP-136 support, to make it easy for clients to adopt this protocol. The library is subject for separate post, but what is important is that when I started to test it against server part, it quickly appeared that there were some severe limitations in mod_archive architecture, which were also inherited by mod_archive_sql version:

  • RSM filtering, which is basically used to limit the number of queries and to page through results, was applied after fetching items satisfying the main request.
  • When answering requests related to collections, they were fully fetched from the database, including all messages they contained.

These two points effectively mean that if client performs the request as simple as “show last 10 collections” the entire database is picked up in memory, and only after that filtering is performed to select those 10 collections the user asked for.

With about 3,000 collections recorded in mnesia database ejabberd easily took about 1Gb of RAM answering these queries, the speed was quite bad also - I do not think it would be useable at all with even larger database.

Just to put it to the right scale: currently I have the messaging archive with about 28,000 collections and around 290,000 messages, I cannot imagine processing these volumes of data with architecture explained above.

Therefore I had to return to the task of mod_archive development again.

First I just wanted to further patch original, mnesia-based mod_archive version to make it more efficient - but soon I discovered that mnesia just has not enough power to perform the tasks I needed. The most severe limiting factor was that I found no ways to efficiently limit queries size and page through results. Switching to SQL-based version seemd to be the only viable solution.

So I took mnesia-based mod_archive version, mod_archive_sql and tried to produce resulting version which would have the correct architecture & implementation to be efficient enough.

This appeared to be not that easy task - in the end it looks like I’ve rewritten most important parts of the module almost completely.

However, the current implementation I have seems to be quite effective, it easily proceeds the volumes of data I mentioned above and also much more conformant to XEP-136 standard.

I will submit this version to ejabberd bug-tracking system once more - I hope it can be included now into ejabberd trunk, replacing both mod_archive and mod_archive_sql, as I believe there’s no point in further development of mnesia-based mod_archive.

Current status

Current version is called mod_archive_odbc, as it’s designed (in theory) to work with any ODBC-compliant RDBMS. On practice there are quite some incompatibilities between RDBMS implementations, see below for details.

To the best of my knowledge and understanding of the standard, this version is fully conformant to XEP-136 except of the following:

  • Encryption is not supported.
  • Setting “auto” preference is persistent, not per-session as it should be according to XEP-136 (thanks to Olivier Goffart for spotting this!)
  • When auto-archiving is enforced by module options, warning message, that is required according to XEP-136, is not sent to legacy clients.
  • Remove collection” command with attribute “open=true” is not supported - I do not think it’s very important to have at current stage, though.

All examples from XEP-136 standard, excluding those related to features mentioned above, seems to work correctly with current version.

Please note though that the standard is not yet finalized, so it’s still possible there will be some further changes in it.

Also there were revealed some problems in standard during implementation, I will submit the report to XMPP guys about them - hopefully, these still can be fixed before standard approval. mod_archive_odbc contains fixes to these problems - as these fixes do not break backward compatibility, it should be completely safe to have them there.

If you run mod_archive_odbc and encounter any standard-breaking behavior, or just any bug with it - I’d like to hear about it, please consider either contacting me or posting the comment to this article.

Please note that this module is still in experimental state! You’re strongly adviced to avoid using it on production servers, or at least make sure that frequent database backups are performed.

Change History

Revision 476

  • Fixed error when requests without RSM tag were not processed, by Olivier Goffart.
  • Added auto-archiving for groupchats.
  • Fixes to support native MySQL driver.

Revision 465

Replaced string:to_lower by jlib:tolower everywhere in sources, as it appeated that string:to_lower was introduced as late as in erlang R11-B4 so not everybody have it.

Revision 434

Fixed bug with crashes on empty RSM responses.

Version 1.0.1

Fixed serious bug which might cause wiping out extra collections in the case user uploads preferences which specify expire interval for full or bare JID, but there is no prefs entry for domain of this JID - expire interval of this JID was applied on the whole domain, possibly causing removal of unwanted collections.

All users are adviced to upgrade or, if upgrade is not possible, the workaround for version 1.0.0 is to set wipeout_interval option to infinity.

Upgrading instructions: DB schemas were not changed, so just copy new version of mod_archive_odbc.erl to ejabberd sources and recompile.

Version 1.0.0

Initial release.

Downloading and Installation

The most up-to-date version is located at ejabberd-modules SVN repository. The files you need are with mod_archive_odbc* prefix.

Just put mod_archive_odbc to ejabberd source tree before ejabberd compilation, then compile ejabberd as usual. Do not forget to turn on ODBC support using --enable-odbc.

mod_archive_odbc was tested with ejabberd SVN trunk and ejabberd-1.1.4 versions and works with both of them. However, for ejabberd-1.x versions the following needs to be done:

  1. Supporting module ejabberd_odbc_sup has to be started - if you use auth_odbc it is already started there, if not - you should start it explicitly from mod_archive_odbc, just uncomment lines “ejabberd-1.x compatibility code”. In ejabberd SVN this is done always automatically by ejabberd_rdbms, so you do not have to worry about it.
  2. Connection to RDBMS can be closed by timeout if not used for a long time, causing the module to fail. In ejabberd SVN version there’s odbc_keepalive_interval parameter which can be used to periodically issue dummy query to keep connection up, but there’s no such thing in ejabberd-1.x, so you’ll have to deal with it on your own either switching timeout off in RDBMS configuration, issuing periodically the command to mod_archive_odbc or in any other way you find suitable for your configuration.

Please note that odbcserver, which is part of ODBC support package for erlang, has limit 8001 bytes on the maximal size of column data returned - this may become a problem if you have large messages in your collections, as they will be truncated on retrieval from database.

You can use this patch for erlang-11.2.5 to lift the restriction up to 65535 bytes. You’ll need to recompile odbcserver, either separately on its own - or just as part of whole erlang compilation.


The following steps need to be done to configure mod_archive_odbc to use ODBC database connection:

  1. Create database using either one of schema files provided with mod_archive_odbc or your own, making sure, of course, that it’s compatible to the schema mod_archive_odbc expects.
  2. Create DSN to your database: for Windows use Administrative Tools → Data Sources (ODBC), for *nixes - configs for your unixODBC installation. It’s a good idea to check at this point DSN works correctly before proceeding further, preferrably - using erlang + odbc, see erlang odbc docs for an example, or even better - using ejabberd ODBC wrapper, see section “Debugging the ODBC configuration”, using ejabberd_odbc:sql_query. For example, smth like ejabberd_odbc:sql_query("localhost", "select * from archive_messages").
  3. Specify {odbc_server, “Your ejabberd database DSN”} in ejabberd.cfg.
  4. Add mod_archive_odbc to the list of modules ejabberd loads, parameters are described below.

For native connections, such as native MySQL driver, after creating database you should just add the appropriate option to ejabberd.cfg specifying database to connect and that’s should be it, see ejabberd site articles for general discussion, as well as native MySQL driver tutorial.

Parameters mod_archive_odbc understands:

  • database_type - selects RDBMS type, currently “mysql”, “sqlite”, and “pgsql” are supported. This parameter should always be specified, all subsequent parameters are optional.
  • default_auto_save (true or false, defaults to false) - whether to perform auto-saving by default or not; please note that XEP-136 requires it to be off by default.
  • enforce_default_auto_save (true or false, defaults to false) - whether to enforce default_auto_save option by prohibiting users changing it individually in their preferences or not.
  • session_duration (time in seconds) - the timeout interval the server uses to decide that conversation is over when performing automated archiving.
  • default_expire (time in seconds or infinity atom, defaults to infinity) - all collections with their start time satisfying now() - start() >= default_expire will be removed, unless expire time is overridden by users in their preferences.
  • enforce_min_expire (time in seconds, defaults to 0) - minimal expire time users are allowed to set in their preferences, useful if you’d like to enforce some message saving policy. NOTE: this parameter is not currently checked in modification commands, such as “upload collection” or “remove collection”, so you cannot enforce the policy completely yet!
  • enforce_max_expire (time in seconds or infinity atom, defaults to infinity)
    • maximum expire time user is allowed to set, useful for limiting the amount of collections users may keep on server.
  • replication_expire (time in seconds or infinity atom, defaults to 31536000, which is one year) - all collections with their removal time satisfying now() - removal_time() >= replication_expire will be removed completely from the database, thus the server completely forgets they were present once.
  • wipeout_interval (time in seconds or infinity atom, defaults to 86400, which is one day) - interval between executions of expire routine.

Please note that according to XEP-136 only the following auto_save combinations are valid:

  1. default_auto_save = true, enforce_default_auto_save = true
  2. default_auto_save = false, enforce_default_auto_save = false

Implementation will happily work with any combination of these, though - for example, for personal ejabberd server, until all clients support XEP-136, combination default_auto_save = true, enforce_default_auto_save = false is quite logical, while for some public ejabberd server with lots of users and shortage of disk space default_auto_save = false, enforce_default_auto_save = true might be desirable, so that only manual uploads are supported.

Here’s the example configuration when using ODBC MySQL driver:


% Add to modules section:
{mod_archive_odbc, [{database_type, "mysql"},
                      {default_auto_save, true},
                      {enforce_default_auto_save, false},
                      {default_expire, infinity},
                      {enforce_min_expire, 0},
                      {enforce_max_expire, infinity},
                      {replication_expire, 31536000},
                      {session_duration, 1800},
                      {wipeout_interval, 86400}]},


% Add somewhere at top level, if you do not have it yet:
{odbc_server, "DSN=ejabberd;UID=ejabberd;PWD=passwd"}.

% Make sure the connection isn't closed,
% this option is supported in ejabberd-2.x only.
{odbc_keepalive_interval, 3600}.


For native MySQL driver replace “odbc_server” connection string by smth like this:


{odbc_server, {mysql, "localhost", "ejabberd", "ejabberd", "password"}}.


Migrating history

There’s no easy way to fully import history for all users from previous versions of mod_archive or other server-side storages to mod_archive_odbc - however, for XEP-136 servers implementations you can do it per user basis using wswutil by downloading history first to local file and then uploading it to server which runs mod_archive_odbc.

Please note, though, that previous versions of mod_archive had some of the features required by wswutil broken, so I cannot guarantee that downloading will succeed: I’ve tested this only with patched version of mnesia-based mod_archive. Also be warned that downloading can take large amounts of memory and be quite slow for the reasons explained above.

In wswutil distribution there are also some Perl scripts for history convertion to XEP-136 XML file format for the following clients:

  • Miranda (msg_export and dbeditorpp plugins should be used first to export history and database to text files)
  • Psi
  • vicq

However, these scripts are quite hacky and may require some finishing of resulting XML files before uploading.

I strongly suggest to test obtained XML files on some test server running mod_archive_odbc before uploading them on production one, as if it’s determined in the middle of uploading that XML is broken you’ll stay with half-way imported history and will have to find the way to remove all uploaded collections to start from scratch, which may be problematic if you have already some other collections stored in database which you’d like to keep.

Client support

So far nothing encouraging can be written here - I know no Jabber clients supporting current version of XEP-136. There’s JWChat which claims to support XEP-136, but it uses old version of specs so I’m almost sure it will not work with mod_archive_odbc.

One of the possibilities to get access to the history stored by mod_archive_odbc is to use Olivier’s Jabber archive web reader - it provides web interface to history on Jabber server that supports XEP-136, such as ejabberd + mod_archive_odbc.

Other RDBMS support

mod_archive_odbc utilizes ejabberd wrappers over ODBC - this means that in theory it should work with all RDBMS’es which have ODBC driver, as well as through native drivers to MySQL and PostgreSQL, as they are included in ejabberd and supported through the same interface as ODBC queries.

On practice, though, due to SQL standard being … well … just like any other standard, i.e. missing important features and not fully supported by any of the RDBMS in any of standard / RDBMS versions, there are some either non-standard or incompatible features used.

My tests covered the following setups:

  1. Windows XP + SQLite3 through ODBC driver.
  2. Linux/MySQL through ODBC driver.
  3. Linux/MySQL through native driver (only partially tested).

Therefore, for those configurations I can be pretty sure they’re working. There’s also support for PostgreSQL in sources, but it was never tested - so, most likely, it will not work out of the box, though should be easy to fix.

Update: it was reported that it works also with PostgreSQL, the appropriate schema file is now included in mod_archive_odbc, but I haven’t tried it myself.

Update2: it was reported that collections expiration is broken on PostgreSQL, see comments to this post for details.

For other RDBMS’es like MS SQL, Oracle, Firebird or whatever, you will need to read on to figure out what should be changed to support them.

This comparison of different SQL implementations, which lists incompatibilities between different RDBMS’es, helped me very much during development, if you’re thinking about adding support to mod_archive_odbc for other RDBMS’es - it can be useful for you also.

Below I list all the features I know in current implementation to behave not according to standard or that are likely to be a problem for new RDBMS support.

Almost all differences are localized to the appropriate case construction, so you just need to search the source for all database_type instances, and add your RDBMS into the appropriate case statement.


Triggers are still new to some of RDBMS’es, so there may be some deviations here and there. In my tests they worked perfectly for SQLite3, but required quite some tuning for MySQL. Specifically, MySQL doesn’t support triggers updating the same table that invoked these triggers - therefore, for MySQL I had to put some actions directly in mod_archive_odbc code instead of triggers.

Depending on situation with your RDBMS you can consider either going SQLite3 direction, so just using triggers (preferred way) or use the same approach as for MySQL.

Paging through results and limiting result size

I have no clues how could that happen that this is not in the standard and implementations are incompatible with respect to this feature. To be more precise, there IS functionality in the standard that allows to implement this, albeit in quite awkward way, but it’s not supported by majority of RDBMS’es anyway, so there’s not that much sense in it.

Due to RDBMS’es selection used for my tests, I’ve used "OFFSET ... LIMIT ..." construction to acomplish the goal - it should be portable at least between SQLite, MySQL and PostgreSQL, and is quite easy to understand.

This one is important to fix for your RDBMS, as it provides the essential functionality, but it’s also one of the most difficult ones - if your RDBMS requires completely different way of query construction you may need to make quite some changes. They’re localized to generic query constructions functions, though, so at least you do not have to do this separately for collections, messages and changes queries.

Last Inserted Auto ID

When auto incremented column, used as primary key, is updated on new record insertion, I need in some places to know its value that was just inserted. Again, surprisingly there’s quite some deviation with that: almost every RDBMS has this function, but it’s called differently everywhere.

This one is easy - just find out the right way to do it for your RDBMS and insert it into the appropriate case.

In principle, you can even skip this completely, if this function is not available - implementation will just perform normal SELECT statement, that should also work in most cases, although being slightly slower and, theoretically, subject to problems if duplicate records are inserted - which should not be the case on practice, as last inserted id functionality is currently used for collections insertions only.

Strings escaping

The standard specifies that strings are specified as ‘string’, and one ’ symbol is escaped as ”, so the string can't should become 'can''t'. Besides that, \ symbol does not have special meaning according to the standard.

However, some RDBMS’es allow \ to be used for escaping, which creates the mess, as strings have to be escaped differently: for those that strictly follow the standard a\b'c should be escaped as 'a\b''c', while for others 'a\\\\b\'c'.

Again, this one is simple to handle: just add the case for your RDBMS in escape_str() function.

Multiple insert

For optimum performance it’s better to insert messages in database not one-by-one, but in bulk requests, this makes quite some difference - with single inserts uploading about 3000 collections into MySQL over local network takes more than an hour, while with bulk inserts - less than 10 minutes. The problem is that appropriate INSERT operation is specified as optional in SQL standard, and is not implemented by some RDBMS’es.

Just change case in store_messages() function to cover your RDBMS correctly.

Date/Time functions

For expiration support there’s a need to perform some calculations with date/time in query - again, everyone is different here.

Change case in get_expired_str() to cover your RDBMS, if you need expiration support.

Database creation and types

I tried to stick to very basic types in database schema to make it easier to port implementation to other RDBMS’es, as well as to work-around odbc problems in erlang implementation. Therefore, for example, BOOLEAN type is not used and is discouraged - use INTEGER or TINYINT instead. For timestamps everything that is able to store the date in format ‘YYYY-MM-DD HH:MM’ is OK, use CHAR(16) or anything similar if you cannot find any suitable type, provided that date/time functions of your RDBMS will work OK with that type. Everything other is just stored as VARCHAR.

I recommend taking types schema from mod_archive_odbc_mysql.sql and triggers creation code from mod_archive_odbc_sqlite3.sql and then adjusting it to suit your RDBMS.


Erlang + ODBC knows nothing about encodings, so they just pass the text further to native driver without changes. As ejabberd and XMPP protocol in general use UTF-8, you should make sure that the native driver expects to receive UTF-8 from you. For most *nixes this is easy to accomplish: just configure unixODBC with charset=utf-8 or, if using native driver directly, specify encoding in your DBMS config.

For Windows, there’s one important complication: Windows treats erlang + ODBC as ANSI client, so when mod_archive_odbc connects to the database which has Unicode ODBC driver, Windows performs ANSI codepage→Unicode conversion, completely messing your UTF-8 input.

I’m not really sure what is the best & universal solution for it - at least I haven’t found the way to solve this problem for SQLite3 Unicode ODBC driver. What I’ve finally done for that was patching the driver to expose ANSI interface, so that input is passed to driver without conversion, and later treat it as UTF-8 - hacky and wrong, but works OK. Just in case, if anyone needs these, here is the patch for sqliteodbc-0.76 sources and here is the compiled binary.

For more complex / powerful RDBMS’es, such as MS SQL, as far as I understood there are the ways in ODBC driver itself to specify what encoding to assume for particular DSN when clients connect to it via ANSI interface.

Most likely, you will also want to specify UTF-8 as encoding of the database when creating it, or make sure that it’s created by default with UTF-8 encoding.

Closing thoughts

I hope that releasing mod_archive_odbc should improve server-side message archiving situation as it provides mostly standard-conformant and full-featured implementation, thus making it easier to develop clients supporting server-side message archiving.

My other project, libwsw, once brought up to fully useable state, should make it easier to add XEP-136 support to clients, once it is finished, and can already be used for simple maintenance operations like importing / exporting history to / from XEP-136 compliant servers.

Please, do not hesitate to comment!

Comment viewing options

ndl@home is currently in maintenance. During this maintenance it is not possible to change site content (like comments, pages and users).
Select your preferred way to display the comments and click "Save settings" to activate your changes.

Thank you for your detailed

Thank you for your detailed and prompt reply. It's good to have sure knowledge of what one can expect even if this is not exactly what was anticipated.

In fact, I believe this behavior is strictly in accordance with XEP-136, see “7.2 Retrieving a Collection” section - “The ‘with’ and ‘start’ attributes specify the participating full JID and the start time”.

Yes, I was mislead by reading the Implementation Notes
10.1 Exact JID Matching
. However these speaks in generic terms on the basis of what is precisely specified among other places in the chapter you quote. So, yes, I fully agree with you.

To come to what we want to achieve: we wanted to use history management to load part of the last 1-to-1 chat into the client at start up, but I see that there are too many troubles with using xep-136 to this end (in the end involving too much traffic over the net), so we will most probably resort to retrieving the chat (or parts of it) directly from the DB with PHP. This gives us a much more fine grained control with what and how much we want to send to the client as a "primer" for the user.

The need of disregarding the resource identifier was instead prompted by some work around we are implementing, this is too ugly to be describe here, at least until it is fully proved that it is needed, and it works


XEP-136 inconsistency

NDL's picture

Yes, I was mislead by reading the Implementation Notes 10.1 Exact JID Matching.

Ok, I see - it indeed looks like XEP-136 is inconsistent in that aspect, I’d suggest you write your feedback on that to XMPP standards list, so that they can fix it by excluding “retrieve” from 10.1 paragraph, until spec goes to the final stage.

To come to what we want to achieve: we wanted to use history management to load part of the last 1-to-1 chat into the client at start up, but I see that there are too many troubles with using xep-136 to this end (in the end involving too much traffic over the net)

Well, it’s probably true that XEP-136 is not the easiest standard to implement However, for your case I believe you need just two queries: one - “list” with RSM items “before” and “max=1” (which essentially would mean “get the last conversation”) and the other one - “retrieve” for the collection returned in “list” with RSM params “before” and “max=N” (where N is the number of messages you’d like to show) - it doesn’t look like two such queries would be much of a hassle in terms of implementation/network load - but of course I do not know exactly what your situation is … indeed looks like indeed looks like XEP-136 is inconsistent in that aspect, I’d suggest you write your feedback on that to XMPP standards list...

OK, done.

Having removed our ugly hack with the resource identifier everything's easier and are now using the two queries as you suggest. It's quick, painless and allows a fine control of how much we want to retrieve.
I suppress ejabberd from sending the last messages from the muc chat with history_size: 0 and retrive the muc messages like the other chats with xep-0136.

We are still having some troubles in getting the muc messages right at startup, I can't tell right now if it's a javascript timing problem or something specific with the client-server communication. I suspect it is a timing issue, it would be a typical problem of starting a javascript at load time, but not knowing exactly how the timing should be makes it difficult to debug.
I have two types of issues:
1. In the log as a response to a retrieve query I get "wrong key", the client gets items-not-found and the connection closes. Why the key should be wrong is not clear to me yet. I've found an incomplete thread on the ejabberd forum about the same issue, I will post to it.
2. Other times I just receive an empty packet as a response to a query that looks perfectly identical to a query that normally would receive a collection in response.
Especially this second kind of failure puzzles me.

If you have experiences of hints to share about these it would be very much appreciated. However in any case thank you for your help so far.

Queries failures

NDL's picture

Hm, I’m afraid I’ve never encountered any of these issues

You might consider enabling debug output in mod_archive_odbc (just uncomment all MYDEBUG lines in mod_archive_odbc.erl) and post here logs for problematic queries. I do not promise immediate reply/fix, but at least I’ll try to have a look at it.

Log in mod_archive_odbc

hmm, should I be able to see the log messages inside the ordinary ejabberd log?
Running with
{loglevel, 5}.

I don't see any particular message from mod_archive_odbc.

Further by compiling mod_archive_odbc I get:

./mod_archive_odbc.erl:71: Warning: behaviour gen_mod undefined

but don't know if this may have an impact.

Having examined the log files I tend to believe now that there must be something wrong with my ejabberd as I can't find any explanation for why sometimes ejabberd just stops in the middle of processing the query like so:

=INFO REPORT==== 2008-12-12 22:16:30 ===
D(<0.802.0>:ejabberd_http_bind:957) : --- incoming data ---
--- END ---

=INFO REPORT==== 2008-12-12 22:16:30 ===
D(<0.802.0>:ejabberd_http_bind:704) : http-put

=INFO REPORT==== 2008-12-12 22:16:30 ===
D(<0.794.0>:ejabberd_http_bind:342) : state.rid/cur rid: 386066/386067

=INFO REPORT==== 2008-12-12 22:16:30 ===
D(<0.794.0>:ejabberd_http_bind:378) : Key/OldKey/NextKey: 1a40711660050b7ae5abfc990a32ed6c1da8b78b/9b6d08d5aea51a6b56640a002c2eec82bfbcb15e/9b6d08d5aea51a6b56640a002c2eec82bfbcb15e

=INFO REPORT==== 2008-12-12 22:16:30 ===
D(<0.794.0>:ejabberd_http_bind:428) : -- SaveKey: 1a40711660050b7ae5abfc990a32ed6c1da8b78b

=INFO REPORT==== 2008-12-12 22:16:30 ===
D(<0.801.0>:ejabberd_http_bind:772) : OutPacket:


NDL's picture

hmm, should I be able to see the log messages inside the ordinary ejabberd log?

No, you should get output in the console. Just run ejabberd in the way that allows you to see its console output. You might need to remove -detached option (if you have it) and make sure you do not suppress its output in any other way, such as putting ejabberd in background and redirecting its output to /dev/null or whatever

./mod_archive_odbc.erl:71: Warning: behaviour gen_mod undefined

Hm, I think I haven’t seen this one in my environment, but I doubt it causes any problems here.

I can’t find any explanation for why sometimes ejabberd just stops in the middle of processing the query

I have no experience with using HTTP binding in ejabberd, so it’s unlikely I can help with that, sorry Unless you’re 100% sure in implementation at the client side, I would also try to debug it too …

why SET table_type=InnoDB;

No, you should get output in the console. Just run ejabberd in the way that allows you to see its console output.

Ok, found that, I see that when it fails it never arrives at this module.
I'll have to put up a reproduceable test case for the ejabberd people to look at it.

(btw, how do you make the quote format on this blog, I keep using cite, which is not so clear)

One more question unrelated to this is as per the subject line of this post: I don't see in the code of the module any reason why tables should be InnoDB, is there a specific reason for using InnoDB in this module? I ask because if I want to implement fulltext search in the messages (which I want) at least the archive_messages table must be MyISAM (and in general InnoDB is slower).

why SET table_type=InnoDB;

NDL's picture

btw, how do you make the quote format on this blog, I keep using cite, which is not so clear

Sorry, it’s fault from my side The most appropriate thing to use for such kind of quotes seems to be <blockquote> (and that’s what I use) - but due to some reason it’s turned off by default in Drupal “Filtered HTML” input filter, and I didn’t notice that … Anyway, I’ve fixed it, and also applied similar formatting to <cite> so that previous posts look prettier

As for the forced InnoDB table type - that’s actually quite funny (?), as more or less exactly a year ago another user of mod_archive_odbc convinced me to put it there

Basically, I’m all in favor of omitting it, so that every user depending on his requirements decides on his own default table type policy, but the problem is that ejabberd uses fixed InnoDB table type in all its other SQL schemes Thus, leaving it out in mod_archive_odbc would mean that ejabberd installation may end up with tables of different table types, which seems to be even worse than forcing particular (but consistent) table type across all tables …

Answering your other questions:

is there a specific reason for using InnoDB in this module

I’m not expert in MySQL, but from what I’ve read and for my particular usage scenario, InnoDB looks better due to the following reasons:

  • Proper transactions support: I value data correctness more than possible (and questionable - see below) speed increase.
  • Due to the nature of XEP-136 and mod_archive_odbc design there are a lot of small inserts/updates, especially when running in autosave mode: as far as I know, InnoDB might well be faster than myISAM for such kinds of scenarios.

I ask because if I want to implement fulltext search in the messages (which I want) at least the archive_messages table must be MyISAM

Again: I’m not an expert in MySQL and depending on your performance requirements things may vary, but for my personal purposes I regularly use search over entire history database stored in InnoDB tables with quite acceptable results using mod_archive_webview module, which just goes through the entire database with “WHEN body LIKE” clause - it’s not instant, but still quite manageable.

Thnaks for your overview


thanks a lot for the great tutorial, it works like a charm. I am wondering if you would have any clue as to how one could set up a second node for clustering.

I have tried to replicate the configuration on the second node but it still starts as an independent node.


NDL's picture

In fact I’ve never tried to setup clustering for ejabberd and, hence, have no idea as to how mod_archive_odbc would work with it.

Moreover, for mod_archive_odbc things might be even more complicated than simple clustering setup, as RDBMS will either have to be re-used across the whole cluster of nodes (which would, most likely, result in too low performance and make sense only for fault tolerance) or you will need to do some kind of RDBMS replication also.

Actually, if main goal for clustering setup is mod_archive_odbc performance - I’d better think about RDBMS replication/load balancing in the first place, as it should be the main limiting factor.

Internal Server Error when attempting to list a collection

Firstly, thanks for contributing this excellent library.

I'm seeing an issue when attempting to list a collection. The XMPP stanzas that fail are something like this:

<iq type='get' id='page1'>
  <retrieve xmlns=''
    <set xmlns=''>

And I'm receiving an Internal Server Error response back. I'm still very new to XMPP so I'm not certain this is actually a bug, but it did seem to be a pretty serious error message if the xmpp stanza was just badly formed.

I also have the Ejabberd error report from the Ejabberd log if that's needed?


Internal Server Error when attempting to list a collection

Hi, I've got the same Problem and it occurs every day. A restart of the ejabberd Server always solve this problem but I don't want to restart the Server every day. What is a possible reason for a "query time out" I gues the following "internal-server-error" is just a sequence error, isn't it?

I'm not sure whether this is a bug or just a wrong configuration of my ejabberd Server, any issues?

My config is:
- ejabberd version ejabberd-2.1.3
- mod_archive_odbc at revision 1057 from
- RDBMS MySQL 5.0.26


{mod_archive_odbc, [{database_type, "mysql"},
{default_auto_save, true},
{enforce_default_auto_save, true},
{default_expire, infinity},
{enforce_min_expire, 0},
{enforce_max_expire, infinity},
{replication_expire, 31536000},
{session_duration, 1800},
{wipeout_interval, 86400}]},

ejabberd.log returns:

=ERROR REPORT==== 2010-09-07 08:39:23 ===
E(<0.2977.0>:mod_archive_odbc:2428) : failed transaction: {aborted,
"query timed out"}},
init_p,5}]}}, stack: {backtrace,
<<"Program counter: 0xb7c234e4 (unknown function)\nCP: 0xb30dfb00 (mod_archive_odbc:run_sql_transaction/2 + 232)\n\n0xb3effe24 Return addr 0xb30d3da4 (mod_archive_odbc:do_log/12 + 256)\ny(0) {aborted,{{badmatch,{aborted,\"query timed out\"}},[{mod_archive_odbc,get_last_inserted_id,2},{mod_archive_odbc,get_collection_id,1},{mod_archive_odbc,new_dict_answer,5},{mod_archive_odbc,'-do_log/12-fun-0-',13},{ejabberd_odbc,outer_transaction,3},{ejabberd_odbc,session_established,3},{p1_fsm,handle_msg,10},{proc_lib,init_p,5}]}}\n\n0xb3effe2c Return addr 0xb30d2a78 (mod_archive_odbc:handle_cast/2 + 460)\ny(0) []\ny(1) []\ny(2) []\ny(3) {dict,0,16,16,8,80,48,{[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[]},{{[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[]}}}\ny(4) []\ny(5) []\ny(6) []\ny(7) []\ny( []\ny(9) []\ny(10) []\n\n0xb3effe5c Return addr 0xb6a59d14 (gen_server:handle_msg/5 + 932)\ny(0) []\ny(1) []\ny(2) []\ny(3) []\ny(4) []\ny(5) []\ny(6) []\ny(7) []\ny( []\ny(9) []\ny(10) []\ny(11) []\ny(12) {state,\"\",{dict,0,16,16,8,80,48,{[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[]},{{[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[]}}},1800}\n\n0xb3effe94 Return addr 0xb6a637ec (proc_lib:init_p/5 + 200)\ny(0) mod_archive_odbc\ny(1) {state,\"\",{dict,0,16,16,8,80,48,{[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[]},{{[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[]}}},1800}\ny(2) ''\ny(3) <0.251.0>\ny(4) {'$gen_cast',{addlog,\"chat\",to,\"vmuth\",\"\",\"40554349641283841483603324\",{jid,\"mttest\",\"\",[],\"mttest\",\"\",[]},[],[],[],\"wird das auf der db hinterlegt?\"}}\ny(5) Catch 0xb6a59d14 (gen_server:handle_msg/5 + 932)\n\n0xb3effeb0 Return addr 0x081dbe40 ()\ny(0) Catch 0xb6a637fc (proc_lib:init_p/5 + 216)\ny(1) gen\ny(2) init_it\ny(3) [gen_server,<0.251.0>,<0.251.0>,{local,''},mod_archive_odbc,[\"\",[{database_type,\"mysql\"},{default_auto_save,true},{enforce_default_auto_save,true},{default_expire,infinity},{enforce_min_expire,0},{enforce_max_expire,infinity},{replication_expire,31536000},{session_duration,1800},{wipeout_interval,86400}]],[]]\n">>}

=ERROR REPORT==== 2010-09-07 08:39:23 ===
E(<0.2977.0>:mod_archive_odbc:489) : error when performing automated archiving: {xmlelement,


Internal Server Error when attempting to list a collection

NDL's picture

I’m still very new to XMPP so I’m not certain this is actually a bug, but it did seem to be a pretty serious error message if the xmpp stanza was just badly formed.

Yes, it’s not normal for the module to give “internal server error” message on such stanza - it looks valid and works fine on my installation (returning “item-not-found”, which is to be expected  )

What is your configuration? (ejabberd version, mod_archive_odbc version, RDBMS used). Have you verified ejabberd → RDBMS link actually works? Are there any other error log entries in sasl.log related to mod_archive_odbc?

I also have the Ejabberd error report from the Ejabberd log if that’s needed?

Yes, please - it typically allows to understand where the problem is right from the report message.

What is your configuration?

What is your configuration? (ejabberd version, mod_archive_odbc version, RDBMS used). Have you verified ejabberd -> RDBMS link actually works? Are there any other error log entries in sasl.log related to mod_archive_odbc?

- ejabberd version 2.0.2_2
- I checked out mod_archive_odbc from a day or two ago (revision 720).
- PosgreSQL 8.3.3 as my RDBMS
- Yup, ejabbered -> RDBMS is working. When I send messages from a XMPP client I can see them going into archive_messages.
- No error log entries in sasl.log.

I've put part of the ejabberd log up at

PostgreSQL support

NDL's picture

Sorry for delay with the answer …

In fact, PostgreSQL support was never previously tested by me - I received one successful report about using PostgreSQL and mod_archive_odbc together with schema file attached that was used for tests, so I was able to include that schema in distribution, but that’s it.

To investigate your problem I’ve installed PostgreSQL & run mod_archive_odbc against it - and I was able to reproduce your problem, assuming that you use the native erlang pgsql driver. That was the good thing about it.

The bad thing is that either I do not understand something very essential about RDBMS in general / erlang in particular, or erlang pgsql driver seems to be plain buggy. As far as I understood the reason for the problem, the driver stops producing the output as soon as it encounters the first null value in the result.

Here’s the result of debugging session from erlang shell running ejabberd:

(ejabberd@localhost)1> ejabberd_odbc:sql_query("ndl-home", "select * from archive_messages"). 
          [{"1","1","2008-09-20 19:17:25","0",
            "Welcome to this Jabber server.",[]}]}
(ejabberd@localhost)2> ejabberd_odbc:sql_query("ndl-home", "select * from archive_collections").
(ejabberd@localhost)3> ejabberd_odbc:sql_query("ndl-home", "select id,us from archive_collections").

Note that for archive_messages table (where all fields are non-null) result is completely OK, while for archive_collections, where some elements are null, results seem to be returned up to the first null - third query shows that there are some non-null items that are indeed present in the record, but they are just not returned in the second query. Moreover, even if the complete record consisted from nulls only, I would expect to get precisely the number of items that are specified in table schema, whether they are null or not null - I’m not supposed to guess which items were silently discarded and which are not, am I?

I do not really have the time (and desire) to investigate why pgsql driver is so hopelessly broken in ejabberd, but this link seems to suggest that due to some compatibility problems they use the older version of driver, which seems to have problems with null values.

I would suggest two things:

  • Contact ejabberd team regarding the pgsql driver version they use and its issues with null values.
  • Until then, switch to ODBC interface to your PostgreSQL database. If everything goes well it should just require having working ODBC support on your system and changing one line in ejabberd.cfg to switch from native pgsql driver settings to ODBC ones.

Thanks for your all your help

Thanks for your all your help and your time. It's good to know that I'm not going crazy.

I may have to use the native MySQL driver temporally until I can sort out the setup for ODBC on the test server. But this should get me moving again

Thanks again.


change_utc must not be null.

change_utc must not be null.

I had similar problems and

I had similar problems and had to increase the thread_stack parameter of mysql

Is this alive?

I would like to know if this module is still being developed? I'm going to use it in a production environment (didn't find any better solution) and it is important to know if it is still maintained and any chances for the improvements to come in future.
Also please give your opinion on scalability and performance of this solution.
Anyway thanks for a great module to a great jabber server!

... no, it's inorganic ;-)

NDL's picture

I would like to know if this module is still being developed?

As it’s currently almost feature-complete (except for the minor things mentioned in documentation) and I’m overall satisfied with its stability and performance - there’s basically nothing to be really “developed” there.

The only thing I’m still going to do when I have time is to synchronize it with the latest XEP-136 draft: after some discussion on XMPP standards list some of my suggestions were incorporated to XEP-136, so now I need to change implementation to reflect that.

Those things are more or less “internal details”, though, so unless you’re going to use it as “reference implementation” of XEP-136 - they shouldn’t really matter.

I’m going to use it in a production environment (didn’t find any better solution)

… well, a kind of controversial compliment, but thanks anyway

it is important to know if it is still maintained and any chances for the improvements to come in future.

I use this module for my personal needs, so it is likely to remain maintained in foreseeable future. I have no personal interest in making any improvements for “large-scale deployment”, though - at least if nobody sponsors this

Also please give your opinion on scalability and performance of this solution.

Both scalability and performance are mainly limited by RDBMS: for example, during auto-archiving there are lots of small SQL update statements executed, each one is a separate transaction, so RDBMS is better to handle it efficiently. Also note that ejabberd itself seems to be not that good at RDBMS handling: as far as I know even 2.x versions still do not reconnect to RDBMS if it’s disconnected for whatever reason.

I use it successfully for personal needs with messages number in the database about 300,000 - 400,000 and CPU consumption being unnoticeable, but I have no numbers for other setups, sorry.

P.S.Взагалі кажучи, можна й українською, якщо Вам так зручніше

User sent messages aren't logged.

Thanks for putting together this mod. I'm using mod_archive_odbc, and I noticed on messages received by users are being logged. I've been digging around the code, and it seems like there's a long standing bug with ejabberd (there was a bug report about this in 2006 for mod_service_log) in that the user_send_packet event is not trigger attached event handlers. Have you figured out what's going wrong with this?


It looks like the event isn't

It looks like the event isn't triggered for mucs. And that's what I need to log without duplication.

Do you mean presence packets?

NDL's picture

Do you mean “normal” messages or presence packets? Looking at the link you’ve posted it seems the discussion is about presence, but you should note that XEP-139 doesn’t support presence logging, it’s designed only to log “normal” messages - you can extend it to perform presence logging also, but that extension would be incompartible with XEP-139 and thus will not be understood by other server implementations or clients.

As for the “normal” messages - the version available in SVN thus far worked OK for me for MUC messages logging, so if it doesn’t work for you - I will need the additional information to debug it, such as exact revision of mod_archive_odbc you use, information on your RDBMS configuration, exact version of ejabberd you use, preferrably - also ejabberd logs with debugging enabled in mod_archive_odbc.

For my purposes, I need to

For my purposes, I need to log information in the same way that mod_muc_log does it except to MySQL. Looking at the data in the tables for mod_archive_odbc, it looks like the message received event is not firing from ejabberd.

I needed to record presence changes and messages so I modified ejabberd's mod_muc_room.erl to dispatch new events and mod_archive_odbc to handle them. Everything's been working out fine so far. I haven't read XEP-139, so I don't know if my changes are compatible with the standard. Thanks for the reply.

Post new comment

ndl@home is currently in maintenance. During this maintenance it is not possible to change site content (like comments, pages and users).
Syndicate content