Idiot's guide to xdb_sql on Redhat

Articles

Open Exam

The case of the missing errors.

FTP security issues.

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:

		CFLAGS:=$(CFLAGS) -I/usr/local/mysql/include
	

to:

		CFLAGS:=$(CFLAGS) -I/usr/include/mysql
	

And change this line:

		$(CC) $(CFLAGS) $(MCFLAGS) -o xdb_sql.so $(OBJECTS) $(LDFLAGS) \
		$(LIBS) -static -L/usr/local/mysql/lib/mysql -lmysqlclient
	

to:

		$(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!

Editors Note:

This installation guide works for mysql 4.0.x as well and should work for most versions of Redhat as well as other distributions.