[ejabberd] Has anyone successfully used ejabberd with a MySQL cluster?

Tom tom.valdes at gmail.com
Wed Aug 25 06:06:34 MSD 2010


I have ejabberd working fine against a single MySQL database.
I'm trying to increase the availability of my database and have MySQL
clustering working.

Before working with ejabberd on the new systems, I set up clustering and it
works correctly.
I can create a table on SystemA and the table shows up correctly on SystemB

I found an old post which describes the same issue I'm seeing, but the
thread seems to have died without any resolution
http://www.ejabberd.im/node/1186

To use clustering, TYPE=NDBCLUSTER needs to be added to the end of each
CREATE TABLE section

ie:
---

CREATE TABLE rosterusers (
username varchar(250) NOT NULL,
jid varchar(250) NOT NULL,
nick text,
subscription character(1) NOT NULL,
ask character(1) NOT NULL,
server character(1) NOT NULL,
subscribe text,
type text
) TYPE=NDBCLUSTER CHARACTER SET utf8;

CREATE UNIQUE INDEX i_rosteru_user_jid USING HASH ON
rosterusers(username(75), jid(75));
CREATE INDEX i_rosteru_username USING HASH ON rosterusers(username);
CREATE INDEX i_rosteru_jid USING HASH ON rosterusers(jid);

---

The first UNIQUE INDEX "i_rosteru_user_jid" throws an error.

ERROR 1089 (HY000) at line 50: Incorrect prefix key; the used key part isn't
a string, the used length is longer than the key part, or the storage engine
doesn't support unique prefix keys
If I change the following type of lines..
CREATE UNIQUE INDEX i_rosteru_user_jid ON rosterusers(username(75),
jid(75));
to
CREATE UNIQUE INDEX i_rosteru_user_jid ON rosterusers(username, jid);

It gets closer to completion (more tables get created)

but still gives errors like
ERROR 1118 (42000) at line 80: Row size too large. The maximum row size for
the used table type, not counting BLOBs, is 8052. You have to change some
columns to TEXT or BLOBs

for the following section

CREATE TABLE vcard_search (
    username varchar(250) NOT NULL,
    lusername varchar(250) PRIMARY KEY,
    fn text NOT NULL,
    lfn varchar(250) NOT NULL,
    family text NOT NULL,
    lfamily varchar(250) NOT NULL,
    given text NOT NULL,
    lgiven varchar(250) NOT NULL,
    middle text NOT NULL,
    lmiddle varchar(250) NOT NULL,
    nickname text NOT NULL,
    lnickname varchar(250) NOT NULL,
    bday text NOT NULL,
    lbday varchar(250) NOT NULL,
    ctry text NOT NULL,
    lctry varchar(250) NOT NULL,
    locality text NOT NULL,
    llocality varchar(250) NOT NULL,
    email text NOT NULL,
    lemail varchar(250) NOT NULL,
    orgname text NOT NULL,
    lorgname varchar(250) NOT NULL,
    orgunit text NOT NULL,
    lorgunit varchar(250) NOT NULL
    lorgunit text NOT NULL
) CHARACTER SET utf8 ENGINE=NDBCLUSTER;

CREATE INDEX i_vcard_search_lfn       ON vcard_search(lfn);
CREATE INDEX i_vcard_search_lfamily   ON vcard_search(lfamily);
CREATE INDEX i_vcard_search_lgiven    ON vcard_search(lgiven);
CREATE INDEX i_vcard_search_lmiddle   ON vcard_search(lmiddle);
CREATE INDEX i_vcard_search_lnickname ON vcard_search(lnickname);
CREATE INDEX i_vcard_search_lbday     ON vcard_search(lbday);
CREATE INDEX i_vcard_search_lctry     ON vcard_search(lctry);
CREATE INDEX i_vcard_search_llocality ON vcard_search(llocality);
CREATE INDEX i_vcard_search_lemail    ON vcard_search(lemail);
CREATE INDEX i_vcard_search_lorgname  ON vcard_search(lorgname);
CREATE INDEX i_vcard_search_lorgunit  ON vcard_search(lorgunit);


Has anyone successfully used ejabberd with a MySQL cluster?



thanks,

tom
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.jabber.ru/pipermail/ejabberd/attachments/20100824/57e7eb19/attachment.html>


More information about the ejabberd mailing list