Ejabberd roster with mysql

1
votes

I am using exauth with ejabberd to authenticate my users, but I want to use mysql to store user information, such as GROUPS, rosters etc.

Here is my configuration.

{auth_method, external}.
{extauth_program, "/home/hitesh/ejabberd_auth.php"}.

{host_config, "subdomain.nodyssey.com", [{auth_method, [external, anonymous]}]}.

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

the mysql database is EMPTY! users still have roster and chat history on group chat,so looks like mnesia database is being used! How do I switch to mysql for this?

what am I doing wrong?

2
erlangxmppejabberd
did you create the mysql database? were you previously using mnesia for roster? if so did you migrate the data? last thing, did you enable in ejabberd.cfg mod_roster_odbc? – user601836
@user601836 how do I do that? {mod_roster_odbc,[]} like this? – Hitesh Joshi
yes, plus disable mod_odbc...of course you need all the modules needed for mysql connection – user601836
ok, mod_odbc is disabled, mysql connection is working, mysql modules are uploaded and working fine. But still its using mynesia database! here is my config, gist.github.com/hiteshjoshi/5117477 , – Hitesh Joshi
sorry. i meant you have to disable mod_roster..not mod_odbc...mod_roster enabled means roster support with mnesia. – user601836

2 Answers

5
votes

user601836's correct. I misunderstood the question initially. Assuming that odbc and mysql driver are properly installed, here is the updated answer:

  1. Have you created the mysql tables to store the roster data, etc? Also are all of your ejabberd tables created using innodb engine? You can get the schema in github: https://github.com/processone/ejabberd/blob/2.1.x/src/odbc/mysql.sql. Also you can find it in your ejabberd installation folder. Mine is in this location: ejabberd_installation_root/lib/ejabberd-2.1.11/priv/sql/mysql.sql.
  2. Replace related module names using the *_odbc one in your ejabberd.cfg. For example, replace mod_roster to mod_roster_odbc, replace mod_muc to mod_muc_odbc. A number of odbc supported modules are listed here: 
    ...
{mod_offline_odbc,  []},
{mod_last_odbc,     []},
{mod_roster_odbc,   []},
{mod_shared_roster_odbc,[]},
{mod_vcard_odbc,    []},
{mod_muc_odbc, [
  ...]},
{mod_pubsub_odbc, [
  ...]},
...

For the detailed module information, check the official doc:

https://git.process-one.net/ejabberd/mainline/blobs/raw/v2.1.11/doc/guide.html#modoverview
0
votes

It has been some time since this question was asked. Ejabberd configuration file has changed in meantime. To enable external roster storage, assuming that ejabberd and mysql DB and ejabberd schema are configured, all you have to do in ejabberd.yml is this:

sql_type: mysql
sql_server: "localhost"
sql_database: "some_database_name"
sql_username: "some_username"
sql_password: "some_password"

and to change mod_roster: {}

to 

mod_roster:
   db_type: sql