SOLVED: Gentoo: Cannot create users when using mysql

The nature of the problem seems to have made itself somewhat more clear, so I redid this post entirely.

ejabberd appears not to be able to use MySQL, at least not when set up as per the Official MySQL HowTo. I have enabled the mysql logs, and I see that ejabberd isnt communicating with the DB at any point.

Below is a pretty comprehensive (if I do say so myself) account of what I did to set everything up.

Step 1: Backup the /etc/jabber/directory and compile/install ejabberd

After install Gentoo says:

* For configuration instructions, please see /usr/share/doc/ejabberd-1.1.4-r1/html/guide.html
* or the online version at
* Please edit /etc/jabber/ssl.cnf and run /etc/jabber/
* Ejabberd may refuse to start without an SSL certificate

...this appears to be sound advice, so i restore my backup ssl.cnf, and build new .pems.

Step 2: Reinstall ssl certs...

SHA1 Fingerprint=BLAH:B3:34:B3:83:A7:BLAH

..also, make I make sure to:

thedude jabber # chmod +r /etc/jabber/ssl.pem

so that ejabberd can read it.

Step 3: What is the minimum it takes to get Authentication working in ejabberd.cfg?

  1. Replace this line, {hosts, ["localhost"]}. with {hosts, [""]}.
  2. Replace every reference to './ssl.pem' file with '/etc/jabber/ssl.pem' or it wont work

Start ejabberd... Testing...

I can Add Users
Users can log in
Users can authorize with each other
[borat]Great Success![/borat]

Step 4: Attempt to use Native MySQL support.

  1. Compile the native Mysql driver as per the Using ejabberd with MySQL native driver tutorial, and copy them to where they need to go. which is /usr/lib/erlang/lib/ejabberd-1.1.4/ebin/.
  2. Setup the ejabberd table schema:
    # echo "show tables;" | mysql -D FQDN_XMPP -h localhost -p -u ejabberd
    Enter password:
  3. Make changes to ejabberd.cfg
    %%% Commented-out when seting up for mysql auth:
    %%% {auth_method, internal}.


    {auth_method, odbc}.
    {odbc_server, {mysql, "localhost", "FQDN_XMPP", "ejabberd", "password"}}.

  4. Make following changes as per Native MySQL HowTo
    * Change mod_last to mod_last_odbc to store the last seen date in MySQL.
    * Change mod_offline to mod_offline_odbc to store offline messages in MySQL.
    * Change mod_roster to mod_roster_odbc to store contact lists in MySQL.
    * Change mod_vcard to mod_vcard_odbc to store user description in MySQL.
  5. restart ejabbed

Attempting to register existing user in the db's users table via pidgin: Not Authorized
Attempting to register a new user not in db's users table: 409: Conflict
Attempting to add a user via ejabberdctl: User "" already registered at node ejabberd@thedude

...So now it appears beyond much doubt that I know how to reproduce the problem.

Logs are not useful:

=INFO REPORT==== 2008-01-18 14:52:52 ===
I(<0.593.0>:ejabberd_listener:90): (#Port<0.771>) Accepted connection {{67,84,29,140},58248} -> {{67,18,17,245},5222}

Here's the difference between my ejabberd.cfg and the default

thedude jabber # diff -i ejabberd.cfg.example ejabberd.cfg
< {auth_method, internal}.
> % {auth_method, internal}.

< %{auth_method, odbc}.
< %{odbc_server, "DSN=ejabberd;UID=ejabberd;PWD=ejabberd"}.
> {auth_method, odbc}.
> {odbc_server, {mysql, "localhost", "FQDN_XMPP", "ejabberd", "secret"}}.

< {hosts, ["localhost"]}.
> {hosts, [""]}.

<                           starttls, {certfile, "./ssl.pem"}]},

>                           starttls, {certfile, "/etc/jabber/ssl.pem"}]},

<                           tls, {certfile, "./ssl.pem"}]},
>                           tls, {certfile, "/etc/jabber/ssl.pem"}]},

< {s2s_certfile, "./ssl.pem"}.
> {s2s_certfile, "/etc/jabber/ssl.pem"}.

<   {mod_roster,     []},
>   {mod_roster_odbc,     []},

<   {mod_vcard,      []},
<   {mod_offline,    []},

>   {mod_vcard_odbc,      []},
>   {mod_offline_odbc,    []},

<   {mod_last,       []},
>   {mod_last_odbc,       []}, whats the diff between what I've got and a setup that works with MySQL?

The ejabberd pid:

# ps ax|grep jabber
21717 pts/1    S+     0:00 tail -fn 100 /var/log/jabber/ejabberd.log
26617 pts/2    Sl     0:01 /usr/lib/erlang/erts-5.5.5/bin/beam -- -root /usr/lib/erlang -progname erl -- -home /var/run/jabber -pa /usr/lib/erlang/lib/ejabberd-1.1.4/ebin -sname ejabberd -s ejabberd -ejabberd config "/etc/jabber/ejabberd.cfg" log_path "/var/log/jabber/ejabberd.log" -kernel inetrc "/etc/jabber/inetrc" -sasl sasl_error_logger {file,"/var/log/jabber/sasl.log"} -mnesia dir "/var/spool/jabber" -noshell -noshell -noinput

[UPDATE]: In a related problem I had a few weeks ago, someone suggested i modify part of ejabberd_auth_odbc.erl, to look like this:

try_register(User, Server, Password) ->
    ?INFO_MSG("Lets start with the function try_register with ~n User: ~p, Server: ~p, Password: ~p", [User, Server, Password]),
    case jlib:nodeprep(User) of
error ->
            ?INFO_MSG("Error: invalid JID; with user: ~p", [User]),
    {error, invalid_jid};
LUser ->
            ?INFO_MSG("OK, nodeprep returns LUser: ~p", [LUser]),
    Username = ejabberd_odbc:escape(LUser),
    Pass = ejabberd_odbc:escape(Password),
    LServer = jlib:nameprep(Server),
            ?INFO_MSG("So, let's call add_user with ~n LServer: ~p, Username: ~p, Pass: ~p", [LServer, Username, Pass]),
    case catch odbc_queries:add_user(LServer, Username, Pass) of
{updated, 1} ->
                    ?INFO_MSG("Success creating the account!!", []),
    {atomic, ok};
E ->
                    ?INFO_MSG("Error creating the account :(  Maybe the account already exists ~n Result: ~p", []),
    {atomic, exists}

I tried that and my logs are still useless. I know the code was compiled because it failed until I removed a "'" that was in the wrong place. Where should I see this output?

[UPDATE]: I gave the mailing list a shot, and a fellow recommended do this:

ebin # erl -sname node1 ejabberd@thedude
Erlang (BEAM) emulator version 5.5.5 [source] [async-threads:0] [kernel-poll:false]

Eshell V5.5.5  (abort with ^G)
1> ejabberd_odbc:sql_query("thedude", "select 1").
** exited: {noproc,{gen_server,call,
                                infinity]}} **

...seems like that confirms ejabberd is not communicating with MySQL as suspected, but I speak no erlang, so I'm just guessing.


This was the result of an improperly configured hosts file, that prevented ejabberd from locating the SQL server

Syndicate content