Integration DSPAM and MySQL

Setting up an existing MySQL database for use with Dspam is very simple. If you need help getting MySQL up for the first time, check MySQL Setup.

MySQL 4.1 and later

# mysqladmin create dspam
# mysql dspam < ./src/tools.mysql_drv/mysql_objects-4.1.sql
# mysql
mysql> grant all on dspam.* to dspam@localhost identified by 'ThisIsMyPassword';
# ./configure --with-mysql-includes=DIR --with-mysql-libraries=DIR --with-storage-driver=mysql_drv --enable-mysql4-initialization

Do not use the same password everywhere. Using a different password in each place makes it so that if one password is compromised, access is limited to only what uses that password.

To keep your database nice and clean you will want to run this command nightly:

mysql -udspam -pThisIsMyPassword dspam < purge-4.1.sql

MySQL 4.0 and earlier

First of all, you should consider upgrading MySQL, since 4.1 will run faster /and/ have a smaller database.

# mysqladmin create dspam
# mysql dspam < ./src/tools.mysql_drv/mysql_objects-space.sql
# mysql
mysql> grant all on dspam.* to dspam@localhost identified by 'ThisIsMyPassword';
# ./configure --with-mysql-includes=DIR --with-mysql-libraries=DIR --with-storage-driver=mysql_drv --disable-mysql4-initialization

Do not use the same password everywhere. Using a different password in each place makes it so that if one password is compromised, access is limited to only what uses that password.

To keep your database nice and clean you will want to run this command nightly:

mysql -udspam -pThisIsMyPassword dspam < purge.sql

Backing up the dspam database

# mysqldump -q -r BackupFileName dspamdb

Destroying the dspam database

If for some reason you need to wipe out the dspam database, you can do so with a single command. This will erase all your training data and make dspam unusable!

# mysqladmin drop dspam

InnoDB Specific Setup

InnoDB provides row-level locking and many other performance boosting features. Something I use is the foreign-key constraints to simply the cleanup of the dspam tables. I use vpopmail with MySQL based user tables, so I set the following constraints:

-- 
-- Constraints for table `dspam_neural_data`
-- 
ALTER TABLE `dspam_neural_data`
  ADD CONSTRAINT `dspam_neural_data_ibfk_1` FOREIGN KEY (`uid`) 
  REFERENCES `dspam_virtual_uids` (`uid`) ON DELETE CASCADE;

-- 
-- Constraints for table `dspam_neural_decisions`
-- 
ALTER TABLE `dspam_neural_decisions`
  ADD CONSTRAINT `dspam_neural_decisions_ibfk_1` FOREIGN KEY (`uid`) 
  REFERENCES `dspam_virtual_uids` (`uid`) ON DELETE CASCADE;

-- 
-- Constraints for table `dspam_preferences`
-- 
ALTER TABLE `dspam_preferences`
  ADD CONSTRAINT `dspam_preferences_ibfk_1` FOREIGN KEY (`uid`) 
  REFERENCES `dspam_virtual_uids` (`uid`) ON DELETE CASCADE;

-- 
-- Constraints for table `dspam_signature_data`
-- 
ALTER TABLE `dspam_signature_data`
  ADD CONSTRAINT `dspam_signature_data_ibfk_1` FOREIGN KEY (`uid`) 
  REFERENCES `dspam_virtual_uids` (`uid`) ON DELETE CASCADE;

-- 
-- Constraints for table `dspam_stats`
-- 
ALTER TABLE `dspam_stats`
  ADD CONSTRAINT `dspam_stats_ibfk_1` FOREIGN KEY (`uid`) 
  REFERENCES `dspam_virtual_uids` (`uid`) ON DELETE CASCADE;

-- 
-- Constraints for table `dspam_token_data`
-- 
ALTER TABLE `dspam_token_data`
  ADD CONSTRAINT `dspam_token_data_ibfk_1` FOREIGN KEY (`uid`) 
  REFERENCES `dspam_virtual_uids` (`uid`) ON DELETE CASCADE;

Basically, now if I delete a uid from the dspam_virtual_uids table, all their data in the other tables also gets deleted. Now I can run a query like this:

DELETE FROM `dspam`.`dspam_virtual_uids` 
  WHERE `dspam`.`dspam_virtual_uids`.`username` NOT IN (
    SELECT CONCAT( `hosting`.`vpopmail`.`pw_name`, '@', `hosting`.`vpopmail`.`pw_domain` ) AS email 
      FROM `hosting`.`vpopmail`
  );

This deletes all dspam virtual users that aren't in my vpopmail users table, to clean up deleted accounts, and dictionary attack spams, since I don't have the vchkuser patch working with qmail yet.

I simply added this query to the top of my mysql_purge.sql file, and gave the dspam user the rights to see the pw_name and pw_domain columns in the vpopmail table. Works like a charm.

MySQL - Compile dspam under Fedora Core (64 bit)

Compiling under the 64 bit version of Fedora Core returns this:

required version of libmysqlclient not found

Here's the solution. The 64 bit version uses the 64 bit libraries. You have to use the /usr/lib64 directory.

--with-mysql-libraries=/usr/lib64/mysql  
--with-mysql-includes=/usr/include/mysql