ScanMail MYSQL Support Page

For additional help with MySQL including installation, setup, troubleshooting and more, contact us by email or phone: [email protected] or (410) 838 5100. We do offer support on a per incident basis for $199 per incident.

On this page you will find the details necessary to help setup and use MYSQL with ScanMail Utilities. These are the actual sql statements you would use. Below the examples are the queries in a format suitable for a script or use in the Mysql Query Browser. Just cut and paste and create the ScanMailmail Database. You can also use the mysql_utilities program.

Create ptsmail Database:
mysql>create database ptsmail;
mysql>use ptsmail  
mysql> create table BlackList (IPAddress VARCHAR(20), 
     ->BlackListName VARCHAR(30),datecreated date,Primary Key(IPAddress)); 

create table BlackList (IPAddress VARCHAR(20), 
BlackListName VARCHAR(30),datecreated date,Primary Key(IPAddress)); 

This is the local IP Blacklist table.

mysql> create table DomainList (DomainAddress VARCHAR(100), 
     ->DomainListName VARCHAR(50),datecreated date,
     ->Primary Key(DomainAddress)); 

create table DomainList (DomainAddress VARCHAR(100), 
DomainListName VARCHAR(50),datecreated date,
Primary Key(DomainAddress)); 

This is the local Domain Blacklist table.

mysql> create table tblEmailFilter (RejectReason Varchar(200),
     ->FromAddress varchar(125), ToAddress varchar(50),DateEntered datetime,
     ->Bytes varchar(100),DNS varchar(100),Col007 varchar(150),
     ->EmailText varchar(75), Col009 varchar(65),Col010 varchar(50),
     ->RejectReason2 varchar(150), RejectReason3 varchar(35), 
     ->RejectReason4 varchar(35), RejectReason5 varchar(35), 
     ->RejectReason6 varchar(35), RejectReason7 text);

create table tblEmailFilter (RejectReason Varchar(200),
FromAddress varchar(125), ToAddress varchar(50),DateEntered datetime,
Bytes varchar(100),DNS varchar(100),Col007 varchar(150),
EmailText varchar(75), Col009 varchar(65),Col010 varchar(50),
RejectReason2 varchar(150), RejectReason3 varchar(35), 
RejectReason4 varchar(35), RejectReason5 varchar(35), 
RejectReason6 varchar(35), RejectReason7 text);

This is the local Statistics and history table.

mysql> create table tblEmailAddress (EmailAddress VARCHAR(100), 
     ->Primary Key(EmailAddress)); 

create table tblEmailAddress (EmailAddress VARCHAR(100), 
Primary Key(EmailAddress)); 

This is a table of users you will accept mail for.  Used primarily 
for systems with multiple incoming mail servers feeding to one pop server.

mysql> create table emailalert (fromaddress VARCHAR(1024),
     ->toaddress varchar(1000),
     ->alertaddress varchar (1000)); 

create table emailalert (fromaddress VARCHAR(1024),toaddress varchar(1000),
alertaddrress varchar(1000)); 

This is a table of email alert addresses;

mysql> create table tblUserList (ToAddress VARCHAR(50), 
     ->FromAddress varchar(125),Action tinyint); 

create table tblUserList (ToAddress VARCHAR(50), 
FromAddress varchar(125),Action tinyint); 

This is a table of email addresses with an action of deny or allow.

mysql> create table tblmessage (msgid VARCHAR(35), 
     ->themessage text,msgdate datetime,subject varchar(150),
     ->Primary Key(msgid)); 

create table tblmessage (msgid VARCHAR(35), 
themessage text,msgdate datetime,subject varchar(150),
Primary Key(msgid)); 

This is a table of stored messages.

mysql> create table tblgreylist(ipaddress varchar(20),
     ->FromAddress varchar(200),
     ->ToAddress varchar(50),firstseen datetime,expiretime datetime,
     ->recordexpire datetime,blockedmessages int, passedmessages int, 
     ->firstseennumber bigint,expirenumber bigint,recordexpirenumber bigint,
     ->Key (ipaddress));

create table tblgreylist(ipaddress varchar(20),
FromAddress varchar(200),
ToAddress varchar(50),firstseen datetime,expiretime datetime,
recordexpire datetime,blockedmessages int, passedmessages int, 
firstseennumber bigint,expirenumber bigint,recordexpirenumber bigint,
Key (ipaddress));

This is the greylisting table.

mysql> create table sendmailinfo(pid varchar(10),
     ->queueid varchar(30),
     ->thedate varchar(20),
     ->status varchar(300),
     ->relay varchar(300),
     ->reject varchar(300),
     ->nrcpts varchar(10),
     ->DateEntered datetime,
     ->messageinfo text(30),
     ->thesystem varchar(40),
     ->fromaddress varchar(1024),
     ->toaddress varchar(1024),
     ->delay varchar(45),
     ->xdelay varchar(45),
     ->ctladdr varchar(45),
     ->dsn varchar(45),
     ->Primary Key(queueid)); 

create table sendmailinfo(pid varchar(10),
queueid varchar(30),
thedate varchar(20),
status varchar(300),
relay varchar(300),
reject varchar(300),
nrcpts varchar(10),
DateEntered datetime,
messageinfo text(30),
thesystem varchar(40),
fromaddress varchar(1024),
toaddress varchar(1024),
delay varchar(45),
xdelay varchar(45),
ctladdr varchar(45),
dsn varchar(45),
subject varchar(200),
Primary Key(queueid)); 

This is the sendmail information table.  This table has information 
for all sendmail processes not just ptsfilter processes.

Substitute your host,username and password;
mysql>use mysql  
mysql> insert into user (Host,User,Password) 
mysql> insert into user (Host,User,Password) 
mysql> grant all privileges on *.* to 'username'@'%';
mysql> grant all privileges on *.* to 'username'@'localhost';
mysql> flush privileges;
mysql> use ptsmail
mysql> create table restricted(emailaddress varchar(512), password char(45));
mysql> quit

mysql file: 
create a file /usr/local/etc/ptsfilter/mysql 
change permissions (chmod 600 /usr/local/etc/ptsfilter/mysql) 
change owner to root (chown root /usr/local/etc/ptsfilter/mysql) 
Add the following: 
where host is the hostname you want to connect to 
username is the mysql user you want to log in as
password is the password for the user
port is the tcp/ip port to use (usually 3306)
Your fully qualified domain name.
Add "use_mysql" to /usr/local/etc/ptsfilter.conf
Restart ptsfilter

On a very busy system substitute the ip address instead of the hostname

For more information about our software please contact us:
[email protected] .

Copyright © 2013 ScanMail. All rights reserved.
last modified on: 04/28/10 07:13:51 AM

Customer Feedback    
Free Installation    
Real time stats     
Test ScanMail Utilities    

Free 15 Day Demo    
Upgrade Your License    
ScanMail Firewall Server