FreeBSD jabberd port mysql bug

As a quick post (esp since I have not been posting much lately) I recently ran into another issue with jabberd on freebsd. I say another, if you will remember a previous post concerning sasl - http://global-security.blogspot.com/2007/08/pidgin-on-linux-w-jabberd2-on.html.

This has more to do with cleaning up some of the errors that seem to exist in the mysql schema. Specifically, if you install jabberd2 from the ports tree "/usr/ports/net-im/jabberd" and configure it to use mysql as it's storage engine, you will receive several errors in your stdout our log files (depending on your configuration). These errors are generated when a users status changes, i.e. login, logout, away etc... I have included a quick snapshot of the errors below.

Nov 26 14:48:48 secure2 jabberd/sm[1629]: mysql: sql delete failed: Table 'jabberd2.status' doesn't exist
Nov 26 14:50:26 secure2 jabberd/sm[1629]: mysql: sql delete failed: Unknown column 'collection-owner' in 'where clause'
Nov 26 14:51:10 secure2 jabberd/sm[1629]: mysql: sql select failed: Unknown column 'object-sequence' in 'order clause'
Nov 26 14:51:10 secure2 jabberd/sm[1629]: mysql: sql insert failed: Unknown column 'status' in 'field list'
Nov 26 14:52:17 secure2 jabberd/sm[1629]: mysql: sql insert failed: Unknown column 'show' in 'field list'
Nov 26 14:52:58 secure2 jabberd/sm[1629]: mysql: sql insert failed: Unknown column 'last-login' in 'field list'
Nov 26 14:55:46 secure2 jabberd/sm[1629]: mysql: sql insert failed: Unknown column 'last-logout' in 'field list'
Nov 26 14:59:46 secure2 jabberd/c2s[1631]: [7] [192.168.1.2, port=3746] disconnect jid=user@test.com/Home, packets: 15
Nov 26 14:59:46 secure2 jabberd/sm[1629]: session ended: jid=user@test.com/Home
Nov 26 15:00:05 secure2 jabberd/c2s[1631]: [7] [192.168.1.2, port=3932] connect
Nov 26 15:00:05 secure2 jabberd/c2s[1631]: [7] SASL authentication succeeded: mechanism=DIGEST-MD5; authzid=user@test.com
Nov 26 15:00:05 secure2 jabberd/c2s[1631]: [7] bound: jid=user@test.com/Home
Nov 26 15:00:05 secure2 jabberd/c2s[1631]: [7] requesting session: jid=user@test.com/Home
Nov 26 15:00:05 secure2 jabberd/sm[1629]: session started: jid=user@test.com/Home

To remediate this, simply run the following against your jabberd2 mysql database:

CREATE TABLE `status` (
`collection-owner` varchar(256),
`object-sequence` bigint,
`status` text NOT NULL,
`show` text,
`last-login` int DEFAULT '0',
`last-logout` int DEFAULT '0',
PRIMARY KEY (`collection-owner`));

This will get ya going, I'm not gonna go into what's wrong with the script that is included in the jabberd2 install, I think that it's pretty straight forward.

Also note, I will try to post more regularly now but it's been a hectic few weeks for me (new job, family visiting etc...)

Cheers,
JJC