Hello,
I have been working on moving ejabberd to a MySQL ndbcluster based database. Unfortunately there is a specific problem with getting this to work properly.
The mysql.sql the comes from the source create the rosterusers table as such:
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);
In order to use cluster the TYPE has to be set to NDBCLUSTER instead of InnoDB.
The first UNIQUE INDEX "i_rosteru_user_jid" throws an error.
This is the given reason from the MySQL dev team on a very similar situation.
Hi,
As the error message indicates and as [dev.mysql.com] also mentions, Cluster does not support indexes on prefixes of columns. So you can not create a unique on the first 25 characters. As you indicated the 32 works because that is the entire column width (not a prefix).
The reason why this doesn't matter is because the UNIQUE is implemented as a hashed value. That means it will take up the same width in the index regardless of how many characters you use in it. Even non-unique indexes contain a direct pointer to the data rather than a copy since it is a t-tree.
Harrison Fisk, Trainer and Consultant
MySQL AB,
So in order to work around this I have tried playing around with the UNIQUE and prefixing, but unfortunately it does not work properly. Eventually the rosters will stop updating. And adding users to rosterusers will stop working.
So given the following situation I am stuck at a point where i am forced to use a table as InnoDB, and i have to make all ejabberd nodes connect to one specific database instead of connecting to their own mysql cluster node.
If anyone could help out, it would be greatly appreciated.
Thanks,
Jeffrey R.
