Idiot's guide to xdb_sql on Redhat
by Tim Klem
For anyone trying to get the xdb_sql interface up and running, and who is having the same agonizing time I've had recently, here is one man's success story.
Part of the problem is that many of the tips you find, scouring through postings on the jabber newsgroups and elsewhere, may now be out of date due to changes in how the various mysql rpm's are grouped, or where they place files. Or something.
Here's everything I did, soup to nuts, to get jabber and mysql talking to each other on a single machine:
1. Install Redhat
Install Redhat 7.3, using "Workstation" installation type. (I didn't add or remove any packages from the workstation default settings.) You must log in as root user for the remaining tasks.
2. Install Jabber
Unpack/compile/make jabber-1.4.2.tar.gz. Test that it's working in xdb_file mode.
3. Expand xdb_sql tarball
Unpack xdb_sql-1.2.tar.gz into the jabber-1.4.2 directory.
4. Install mySQL
Download and nstall the following rpm's from mysql.org
MySQL-3.23.52-1.i386.rpm MySQL-client-3.23.52-1.i386.rpm MySQL-devel-3.23.52-1.i386.rpm MySQL-shared-3.23.52-1.i386.rpm
I didn't change default paths for any of these files. Just basic: "rpm -ivv MySQL-3.23.52-1.i386.rpm" etc. for each file
Note: I didn't need to install or compile the mysql source code, neither MySQL-3.23.52-1.src.rpm nor the source tar.gz file.
5. Create Databases
Run the sample_database.sql script from the mysql command line. (This also tests that the mysql server and client are both working.) E.g.: mysql> source /path/to/xdb_sql/sample_database.sql
6. Build xdb_sql
Make xdb_sql.so, using Raditha Dissanayake's modified makefile, posted in the jdev news-group a few days back. I changed only the following 2 lines, to point to the correct path of my mysql install:
Change this line:
And change this line:
$(CC) $(CFLAGS) $(MCFLAGS) -o xdb_sql.so $(OBJECTS) $(LDFLAGS) \ $(LIBS) -static -L/usr/local/mysql/lib/mysql -lmysqlclient
$(CC) $(CFLAGS) $(MCFLAGS) -o xdb_sql.so $(OBJECTS) $(LDFLAGS) \ $(LIBS) -static -L/usr/lib -L/usr/lib/mysql -lmysqlclient
(I don't think I need that "-L/usr/lib" in there, but it doesn't hurt.)
7. Update jabber.xml
In jabber.xml, replace the default xdb section with the following:
<xdb id="xdb"> <host/> <ns>jabber:iq:roster</ns> <ns>jabber:x:offline</ns> <ns>jabber:iq:filter</ns> <ns>jabber:iq:last</ns> <ns>jabber:iq:auth</ns> <ns>jabber:iq:auth:0k</ns> <ns>jabber:iq:register</ns> <ns>vcard-temp</ns> <load> <xdb_sql>./xdb_sql/xdb_sql.so</xdb_sql> </load> <jabberd:include>./xdb_sql/xdb_sql.xml</jabberd:include> <load> <xdb_file>./xdb_file/xdb_file.so</xdb_file> </load> <xdb_file xmlns="jabber:config:xdb_file"> <spool><jabberd:cmdline flag='s'>./spool</jabberd:cmdline></spool> </xdb_file> </xdb>
8. Update xdb_sql.xml
Update your xdb_sql.xml file with the mysql user info you created in step 5.
9. restart jabberd
Restart and confirm that the db, not the spool files, are being used. Hope this helps, for someone who's in the same place I was a week or two back!
This installation guide works for mysql 4.0.x as well and should work for most versions of Redhat as well as other distributions.