We have problem with mod_roster_odbc module, because its table implementation
it not equivalent to mod_roster. Let's see:
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| username | varchar(250) | NO | MUL | | |
| jid | varchar(250) | NO | MUL | | |
| nick | text | NO | | | |
| subscription | char(1) | NO | | | |
| ask | char(1) | NO | | | |
| askmessage | text | NO | | | |
| server | char(1) | NO | | | |
| subscribe | text | NO | | | |
| type | text | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
-record(roster, {usj,
us,
jid,
name = "",
subscription = none,
ask = none,
groups = [],
askmessage = [],
xs = []}).
As we can see odbc version have not field `server` (only `username`) and as result
we can have not correct work with some users with same name on different vhosts
(an example and )
Maybe I am not good describe trouble, if so I can explain.
mysql> select * from rosterusers; +------------+-----------------------------+------------+--------------+-----+------------+--------+-----------+------+ | username | jid | nick | subscription | ask | askmessage | server | subscribe | type | +------------+-----------------------------+------------+--------------+-----+------------+--------+-----------+------+ | ivan | | First | B | N | | N | | item | | ivan | | Second | B | N | | N | | item | +------------+-----------------------------+------------+--------------+-----+------------+--------+-----------+------+
for example this table can explain what i mean (if have in roster and vice-versa)
ejabberd.cfg
...
% Host name:
{hosts, ["my_one_host.com", "my_two_host.com"]}.
...
But the key of the table
But the key of the table includes the Username and the JID, right? Since the JID includes the server, the problem should not exist.
This is also described in the database schema:
CREATE TABLE rosterusers (
username varchar(250) NOT NULL,
jid varchar(250) NOT NULL,
nick text NOT NULL,
subscription character(1) NOT NULL,
ask character(1) NOT NULL,
askmessage text NOT NULL,
server character(1) NOT NULL,
subscribe text NOT NULL,
type text
) CHARACTER SET utf8;
CREATE UNIQUE INDEX i_rosteru_user_jid ON rosterusers(username(75), jid(75));
CREATE INDEX i_rosteru_username ON rosterusers(username);
CREATE INDEX i_rosteru_jid ON rosterusers(jid);
username not include server
username not include server name
and JID it is member jid of roster for user with given username
so, trouble exists
Ah, I didn't think about
Ah, I didn't think about that. You are right, each ODBC database can only be used for a single vhost.
If you have several vhosts, the solution is to use several databases. Create them using the same method than the first one. And then configure ejabberd to use a database for each host. For example:
{host_config, "example.org", [ {odbc_server, {mysql, "localhost", "ejabberd_org", "usernameee", "passworddd"}}. ]}. {host_config, "example.com", [ {odbc_server, {mysql, "localhost", "ejabberd_com", "usernameee", "passworddd"}}. ]}.