Attachment #166540 for bug #238806

View | Details | Raw Unified | Return to bug 238806
Collapse All | Expand All




  _optin       tinyint(1) unsigned NOT NULL default '1',
  _priority    tinyint(1) unsigned NOT NULL default '0',
  UNIQUE KEY _rcpt (_rcpt,_optin) 
) TYPE=InnoDB;

CREATE TABLE policy_training (
  _rcpt        char(60) NOT NULL default '',
  _expire      int(10) unsigned NOT NULL default '0',
  UNIQUE KEY _rcpt (_rcpt),
  KEY _expire (_expire)
) TYPE=InnoDB;

CREATE TABLE throttle (
  _from        char(60) NOT NULL default '',

  _log_panic   int(10) unsigned NOT NULL default '0',
  _priority    tinyint(1) unsigned NOT NULL default '0',
  UNIQUE KEY _from (_from)
) TYPE=InnoDB;

CREATE TABLE throttle_from_instance (
  _instance    char(60) NOT NULL default '',

  _expire      int(10) unsigned NOT NULL default '0',
  UNIQUE KEY _instance (_instance),
  KEY _expire (_expire)
) TYPE=InnoDB;

CREATE TABLE throttle_rcpt (
  _rcpt        char(60) NOT NULL default '',

  _log_warn    int(10) unsigned NOT NULL default '0',
  _log_panic   int(10) unsigned NOT NULL default '0',
  UNIQUE KEY _rcpt (_rcpt)
) TYPE=InnoDB;

CREATE TABLE triplet (
  _from        char(60) NOT NULL default '',

  UNIQUE KEY _host (_host,_from,_rcpt),
  KEY _datelast (_datelast),
  KEY _datenew (_datenew)
) TYPE=InnoDB;
  
CREATE TABLE whitelist (
  _whitelist   char(15) NOT NULL default '',

  _expire      int(10) unsigned NOT NULL default '0',
  UNIQUE KEY _whitelist (_whitelist),
  KEY _expire (_expire)
) TYPE=InnoDB;

CREATE TABLE whitelist_sender (
  _whitelist   char(60) NOT NULL default '',

  _expire      int(10) unsigned NOT NULL default '0',
  UNIQUE KEY _whitelist (_whitelist),
  KEY _expire (_expire)
) TYPE=InnoDB;

CREATE TABLE whitelist_dnsname (
  _whitelist   char(60) NOT NULL default '',

  _expire      int(10) unsigned NOT NULL default '0',
  UNIQUE KEY _whitelist (_whitelist),
  KEY _expire (_expire)
) TYPE=InnoDB;

CREATE TABLE blacklist (
  _blacklist   char(15) NOT NULL default '',

  _expire      int(10) unsigned NOT NULL default '0',
  UNIQUE KEY _blacklist (_blacklist),
  KEY _expire (_expire)
) TYPE=InnoDB;

CREATE TABLE blacklist_helo (
  _helo        char(60) NOT NULL default '',
  UNIQUE KEY _helo (_helo)
) TYPE=InnoDB;

CREATE TABLE blacklist_sender (
  _blacklist   char(60) NOT NULL default '',

  _expire      int(10) unsigned NOT NULL default '0',
  UNIQUE KEY _blacklist (_blacklist),
  KEY _expire (_expire)
) TYPE=InnoDB;

CREATE TABLE blacklist_dnsname (
  _blacklist   char(60) NOT NULL default '',

  _expire      int(10) unsigned NOT NULL default '0',
  UNIQUE KEY _blacklist (_blacklist),
  KEY _expire (_expire)
) TYPE=InnoDB;

CREATE TABLE spamtrap (
  _rcpt        char(60) NOT NULL default '',
  _active      tinyint(1) unsigned NOT NULL default '1',
  UNIQUE KEY _rcpt (_rcpt,_active)
) TYPE=InnoDB;

CREATE TABLE helo (
  _host        char(15) NOT NULL default '',

  _expire      int(10) unsigned NOT NULL default '0',
  UNIQUE KEY _host (_host,_helo),
  KEY _expire (_expire)
) TYPE=InnoDB;


Lines 511-516 Link Here

(-)policyd-v1.80/README.txt (+9 lines)
511	module=bypass <- mysql failed inside module	511	module=bypass <- mysql failed inside module
512	(module can be whitelist,blacklist,helo,etc..)	512	(module can be whitelist,blacklist,helo,etc..)
513		513
		514	###
		515	# innodb patch
		516	##################
		517
		518	This patch will properly be unsuported for a long time until all can use
		519	policyd v2 with have all tables as innodb, i created that patch here to
		520	backport this in to v1 olso, it should solve most issues that remains
		521	with cleanups
		522
514	#######	523	#######
515	# EOF #	524	# EOF #
516	#######	525	#######




    {
      /* build up query */
      snprintf(mysqlquery_array[fd], 512,
        "INSERT INTO blacklist (_blacklist,_description,_expire) VALUES ('%s.%%','# blacklisted helo: (%s)',%d)",
       host_array[fd][2], triplet_array[fd][5], expire);
    } else { /* blacklist host ip */
      /* build up query */
      snprintf(mysqlquery_array[fd], 512,
        "INSERT INTO blacklist (_blacklist,_description,_expire) VALUES ('%s','# blacklisted helo: (%s)',%d)",
        host_array[fd][2], triplet_array[fd][5], expire);
    }
    /* execute query */




    
    /* build up & execute query */
    snprintf(mysqlquery_array[0], 512,
      "DELETE FROM triplet WHERE _datelast < %d LIMIT 100000",
      timenow - TRIPLET_AUTH_TIMEOUT);
    if(db_deletequery(0) == -1) exit(-1);



    /* build up & execute query */
    snprintf(mysqlquery_array[0], 512,
      "DELETE FROM triplet WHERE _datenew < %d AND _count = 0 LIMIT 100000",
      timenow - TRIPLET_UNAUTH_TIMEOUT);
    if(db_deletequery(0) == -1) exit(-1);
  }


    /* build up & execute query */
    snprintf(mysqlquery_array[0], 512,
      "DELETE FROM blacklist WHERE _expire <= %d \
       AND _expire != 0 LIMIT 100000",
      timenow);
    if(db_deletequery(0) == -1) exit(-1);


    /* build up & execute query */
    snprintf(mysqlquery_array[0], 512,
      "DELETE FROM whitelist WHERE _expire <= %d AND \
       _expire != 0 LIMIT 100000",
      timenow);
    if(db_deletequery(0) == -1) exit(-1);


    /* build up & execute query */
    snprintf(mysqlquery_array[0], 512,
      "DELETE FROM helo WHERE _expire <= %d AND _expire != 0 LIMIT 100000",
      timenow);
    if(db_deletequery(0) == -1) exit(-1);
  }


    /* build up & execute query */
    snprintf(mysqlquery_array[0], 512,
      "DELETE FROM throttle WHERE _date < %d",
      timenow - SENDER_INACTIVE_EXPIRE);
    if(db_deletequery(0) == -1) exit(-1);



    /* build up & execute query */
    snprintf(mysqlquery_array[0], 512,
      "DELETE FROM throttle_from_instance WHERE _expire < %d",
      timenow - 3600);
    if(db_deletequery(0) == -1) exit(-1);
  }


     /* build up & execute query */
    snprintf(mysqlquery_array[0], 512,
      "DELETE FROM throttle_rcpt WHERE _date < %d",
      timenow - RECIPIENT_INACTIVE_EXPIRE);
    if(db_deletequery(0) == -1) exit(-1);
  }


    /* build up & execute query */
    snprintf(mysqlquery_array[0], 512,
      "DELETE FROM policy_training WHERE _expire < %d and _expire != 0",
      timenow - TRAINING_POLICY_TIMEOUT);
    if(db_deletequery(0) == -1) exit(-1);
  }




  {
    /* build up & execute query */
    snprintf(mysqlquery_array[fd], 512,
      "INSERT INTO triplet (_datenew,_datelast,_host,_from,_rcpt) VALUES (%d,%d,'%s','%s','%s')",
      timenow, timenow, triplet_array[fd][0], triplet_array[fd][1], triplet_array[fd][2]);
    if(db_doquery(fd) == -1) return(db_failure(fd, "greylist"));


        if(BLACKLIST_NETBLOCK==1)
        { /* blacklist netblock */
          snprintf(mysqlquery_array[fd], 512,
            "INSERT INTO blacklist (_blacklist,_description,_expire) VALUES ('%s.%%','# autoblacklisted', %d)",
             triplet_array[fd][0], expire);
        } else { /* blacklist host ip */
          snprintf(mysqlquery_array[fd], 512,
            "INSERT INTO blacklist (_blacklist,_description,_expire) VALUES ('%s','# autoblacklisted',%d)",
             host_array[fd][2], expire);
        }
        /* execute query */


        /* build up & execute query */
        snprintf(mysqlquery_array[fd], 512,
          "DELETE from triplet WHERE _host='%s'", triplet_array[fd][0]);
        if(db_doquery(fd) == -1) return(db_failure(fd, "greylist"));

        /* reject */

      {
        /* build up & execute query */
        snprintf(mysqlquery_array[fd], 512,
          "INSERT INTO whitelist (_whitelist,_description,_expire) VALUES ('%s.%%','# autowhitelisted host',%d)",
          triplet_array[fd][0], expire);
      } else {
        /* build up & execute query */
        snprintf(mysqlquery_array[fd], 512,
          "INSERT INTO whitelist (_whitelist,_description,_expire) VALUES ('%s','# autowhitelisted host',%d)",
          host_array[fd][2], expire);
      }
      if(db_doquery(fd) == -1) return(db_failure(fd, "greylist"));

      /* build up & execute query */
      snprintf(mysqlquery_array[fd], 512,
        "DELETE from triplet WHERE _host='%s'", triplet_array[fd][0]);
      if(db_doquery(fd) == -1) return(db_failure(fd, "greylist"));

      logmessage("rcpt=%lu, greylist=awl, host=%s (%s), from=%s, to=%s, size=%s, expire=%d\n",





    /* build up query */
    snprintf(mysqlquery_array[fd], 512,
      "INSERT INTO blacklist (_blacklist,_description,_expire) VALUES ('%s','# helo abuse',%d)",
      host_array[fd][2], expire);
    if(db_doquery(fd) == -1) return(db_failure(fd, "helo"));


    /* clean up helo table entries */
    /* build up query */
    snprintf(mysqlquery_array[fd], 512,
      "DELETE FROM helo WHERE _host='%s'", host_array[fd][2]);
    if(db_doquery(fd) == -1) return(db_failure(fd, "helo"));

    return (1);


    /* build up query & execute */
    snprintf(mysqlquery_array[fd], 512,
      "INSERT INTO helo (_host,_helo,_expire) VALUES ('%s','%s',%d)",
      host_array[fd][2], triplet_array[fd][5], expire);
    if(db_doquery(fd) == -1) return(db_failure(fd, "helo"));
  }




    {
      /* build up query */
      snprintf(mysqlquery_array[fd], 512,
        "INSERT INTO blacklist (_blacklist,_description,_expire) VALUES ('%s','# spamtrap delivery: (%s)',%d)",
       host_array[fd][3], triplet_array[fd][2], expire);
    } else { /* blacklist host ip */
      /* build up query */
      snprintf(mysqlquery_array[fd], 512,
        "INSERT INTO blacklist (_blacklist,_description,_expire) VALUES ('%s','# spamtrap delivery: (%s)',%d)",
      host_array[fd][2], triplet_array[fd][2], expire);
    }
    /* execute query */

Lines 118-124 Link Here

(-)policyd-v1.80/throttle.c (-1 / +1 lines)
118	expire=timenow;	118	expire=timenow;
119		119
120	snprintf(mysqlquery_array[fd], 512,	120	snprintf(mysqlquery_array[fd], 512,
121	"INSERT DELAYED INTO throttle_from_instance (_instance,_expire) VALUES ('%s',%d)",	121	"INSERT INTO throttle_from_instance (_instance,_expire) VALUES ('%s',%d)",
122	triplet_array[fd][6], expire);	122	triplet_array[fd][6], expire);
123	if(db_doquery(fd) == -1) return(db_failure(fd, "throttle"));	123	if(db_doquery(fd) == -1) return(db_failure(fd, "throttle"));
124		124

Lines 63-69 Link Here

(-)policyd-v1.80/throttle_from.c (-2 / +2 lines)
63		63
64	/* build up & execute query */	64	/* build up & execute query */
65	snprintf(mysqlquery_array[fd], 512,	65	snprintf(mysqlquery_array[fd], 512,
66	"INSERT DELAYED INTO throttle "	66	"INSERT INTO throttle "
67	"(_date,_from,_quota_cur,_quota_max,_rcpt_max,_mail_size,_count_max,_time_limit)"	67	"(_date,_from,_quota_cur,_quota_max,_rcpt_max,_mail_size,_count_max,_time_limit)"
68	" VALUES (%d, '%s', %d, %ld, %ld, %ld, %ld, %ld)",	68	" VALUES (%d, '%s', %d, %ld, %ld, %ld, %ld, %ld)",
69	timenow,	69	timenow,
Lines 199-205 Link Here
199	expire=timenow+SENDER_THROTTLE_AUTOBLACKLIST_EXPIRE;	199	expire=timenow+SENDER_THROTTLE_AUTOBLACKLIST_EXPIRE;
200		200
201	snprintf(mysqlquery_array[fd], 512,	201	snprintf(mysqlquery_array[fd], 512,
202	"INSERT DELAYED INTO blacklist_sender (_blacklist,_description,_expire) "	202	"INSERT INTO blacklist_sender (_blacklist,_description,_expire) "
203	"VALUES ('%s','# sender throttle autoblacklisted',%d)",	203	"VALUES ('%s','# sender throttle autoblacklisted',%d)",
204	mysqlchar_array[fd][0], expire);	204	mysqlchar_array[fd][0], expire);
205		205

Lines 63-69 Link Here

(-)policyd-v1.80/throttle_host.c (-2 / +2 lines)
63		63
64	/* build up & execute query */	64	/* build up & execute query */
65	snprintf(mysqlquery_array[fd], 512,	65	snprintf(mysqlquery_array[fd], 512,
66	"INSERT DELAYED INTO throttle "	66	"INSERT INTO throttle "
67	"(_date,_from,_quota_cur,_quota_max,_rcpt_max,_mail_size,_count_max,_time_limit)"	67	"(_date,_from,_quota_cur,_quota_max,_rcpt_max,_mail_size,_count_max,_time_limit)"
68	" VALUES (%d, '%s', %d, %ld, %ld, %ld, %ld, %ld)",	68	" VALUES (%d, '%s', %d, %ld, %ld, %ld, %ld, %ld)",
69	timenow,	69	timenow,
Lines 199-205 Link Here
199	expire=timenow+SENDER_THROTTLE_AUTOBLACKLIST_EXPIRE;	199	expire=timenow+SENDER_THROTTLE_AUTOBLACKLIST_EXPIRE;
200		200
201	snprintf(mysqlquery_array[fd], 512,	201	snprintf(mysqlquery_array[fd], 512,
202	"INSERT DELAYED INTO blacklist (_blacklist,_description,_expire) "	202	"INSERT INTO blacklist (_blacklist,_description,_expire) "
203	"VALUES ('%s','# sender throttle autoblacklisted',%d)",	203	"VALUES ('%s','# sender throttle autoblacklisted',%d)",
204	mysqlchar_array[fd][0], expire);	204	mysqlchar_array[fd][0], expire);
205		205

Lines 85-91 Link Here

(-)policyd-v1.80/throttle_rcpt.c (-1 / +1 lines)
85		85
86	/* build up & execute query */	86	/* build up & execute query */
87	snprintf(mysqlquery_array[fd], 512,	87	snprintf(mysqlquery_array[fd], 512,
88	"INSERT DELAYED INTO throttle_rcpt (_date,_rcpt,_count_max,_time_limit) "	88	"INSERT INTO throttle_rcpt (_date,_rcpt,_count_max,_time_limit) "
89	"VALUES (%d, '%s', %ld, %ld)",	89	"VALUES (%d, '%s', %ld, %ld)",
90	timenow,	90	timenow,
91	triplet_array[fd][2],	91	triplet_array[fd][2],

Lines 64-70 Link Here

(-)policyd-v1.80/throttle_sasl.c (-1 / +1 lines)
64		64
65	/* build up & execute query */	65	/* build up & execute query */
66	snprintf(mysqlquery_array[fd], 512,	66	snprintf(mysqlquery_array[fd], 512,
67	"INSERT DELAYED INTO throttle "	67	"INSERT INTO throttle "
68	"(_date,_from,_quota_cur,_quota_max,_rcpt_max,_mail_size,_count_max, _time_limit)"	68	"(_date,_from,_quota_cur,_quota_max,_rcpt_max,_mail_size,_count_max, _time_limit)"
69	" VALUES (%d, '%s', %d, %ld, %ld, %ld, %ld, %ld)",	69	" VALUES (%d, '%s', %d, %ld, %ld, %ld, %ld, %ld)",
70	timenow,	70	timenow,

Return to bug 238806

Lines 70-81 Link Here

(-)policyd-v1.80/blacklist_helo.c (-2 / +2 lines)
70	{	70	{
71	/* build up query */	71	/* build up query */
72	snprintf(mysqlquery_array[fd], 512,	72	snprintf(mysqlquery_array[fd], 512,
73	"INSERT DELAYED INTO blacklist (_blacklist,_description,_expire) VALUES ('%s.%%','# blacklisted helo: (%s)',%d)",	73	"INSERT INTO blacklist (_blacklist,_description,_expire) VALUES ('%s.%%','# blacklisted helo: (%s)',%d)",
74	host_array[fd][2], triplet_array[fd][5], expire);	74	host_array[fd][2], triplet_array[fd][5], expire);
75	} else { /* blacklist host ip */	75	} else { /* blacklist host ip */
76	/* build up query */	76	/* build up query */
77	snprintf(mysqlquery_array[fd], 512,	77	snprintf(mysqlquery_array[fd], 512,
78	"INSERT DELAYED INTO blacklist (_blacklist,_description,_expire) VALUES ('%s','# blacklisted helo: (%s)',%d)",	78	"INSERT INTO blacklist (_blacklist,_description,_expire) VALUES ('%s','# blacklisted helo: (%s)',%d)",
79	host_array[fd][2], triplet_array[fd][5], expire);	79	host_array[fd][2], triplet_array[fd][5], expire);
80	}	80	}
81	/* execute query */	81	/* execute query */

Lines 6-19 Link Here

(-)policyd-v1.80/DATABASE.mysql (-15 / +15 lines)
6	_optin tinyint(1) unsigned NOT NULL default '1',	6	_optin tinyint(1) unsigned NOT NULL default '1',
7	_priority tinyint(1) unsigned NOT NULL default '0',	7	_priority tinyint(1) unsigned NOT NULL default '0',
8	UNIQUE KEY _rcpt (_rcpt,_optin)	8	UNIQUE KEY _rcpt (_rcpt,_optin)
9	) TYPE=MyISAM;	9	) TYPE=InnoDB;
10		10
11	CREATE TABLE policy_training (	11	CREATE TABLE policy_training (
12	_rcpt char(60) NOT NULL default '',	12	_rcpt char(60) NOT NULL default '',
13	_expire int(10) unsigned NOT NULL default '0',	13	_expire int(10) unsigned NOT NULL default '0',
14	UNIQUE KEY _rcpt (_rcpt),	14	UNIQUE KEY _rcpt (_rcpt),
15	KEY _expire (_expire)	15	KEY _expire (_expire)
16	) TYPE=MyISAM;	16	) TYPE=InnoDB;
17		17
18	CREATE TABLE throttle (	18	CREATE TABLE throttle (
19	_from char(60) NOT NULL default '',	19	_from char(60) NOT NULL default '',
Lines 34-40 Link Here
34	_log_panic int(10) unsigned NOT NULL default '0',	34	_log_panic int(10) unsigned NOT NULL default '0',
35	_priority tinyint(1) unsigned NOT NULL default '0',	35	_priority tinyint(1) unsigned NOT NULL default '0',
36	UNIQUE KEY _from (_from)	36	UNIQUE KEY _from (_from)
37	) TYPE=MyISAM;	37	) TYPE=InnoDB;
38		38
39	CREATE TABLE throttle_from_instance (	39	CREATE TABLE throttle_from_instance (
40	_instance char(60) NOT NULL default '',	40	_instance char(60) NOT NULL default '',
Lines 42-48 Link Here
42	_expire int(10) unsigned NOT NULL default '0',	42	_expire int(10) unsigned NOT NULL default '0',
43	UNIQUE KEY _instance (_instance),	43	UNIQUE KEY _instance (_instance),
44	KEY _expire (_expire)	44	KEY _expire (_expire)
45	) TYPE=MyISAM;	45	) TYPE=InnoDB;
46		46
47	CREATE TABLE throttle_rcpt (	47	CREATE TABLE throttle_rcpt (
48	_rcpt char(60) NOT NULL default '',	48	_rcpt char(60) NOT NULL default '',
Lines 56-62 Link Here
56	_log_warn int(10) unsigned NOT NULL default '0',	56	_log_warn int(10) unsigned NOT NULL default '0',
57	_log_panic int(10) unsigned NOT NULL default '0',	57	_log_panic int(10) unsigned NOT NULL default '0',
58	UNIQUE KEY _rcpt (_rcpt)	58	UNIQUE KEY _rcpt (_rcpt)
59	) TYPE=MyISAM;	59	) TYPE=InnoDB;
60		60
61	CREATE TABLE triplet (	61	CREATE TABLE triplet (
62	_from char(60) NOT NULL default '',	62	_from char(60) NOT NULL default '',
Lines 68-74 Link Here
68	UNIQUE KEY _host (_host,_from,_rcpt),	68	UNIQUE KEY _host (_host,_from,_rcpt),
69	KEY _datelast (_datelast),	69	KEY _datelast (_datelast),
70	KEY _datenew (_datenew)	70	KEY _datenew (_datenew)
71	) TYPE=MyISAM;	71	) TYPE=InnoDB;
72		72
73	CREATE TABLE whitelist (	73	CREATE TABLE whitelist (
74	_whitelist char(15) NOT NULL default '',	74	_whitelist char(15) NOT NULL default '',
Lines 76-82 Link Here
76	_expire int(10) unsigned NOT NULL default '0',	76	_expire int(10) unsigned NOT NULL default '0',
77	UNIQUE KEY _whitelist (_whitelist),	77	UNIQUE KEY _whitelist (_whitelist),
78	KEY _expire (_expire)	78	KEY _expire (_expire)
79	) TYPE=MyISAM;	79	) TYPE=InnoDB;
80		80
81	CREATE TABLE whitelist_sender (	81	CREATE TABLE whitelist_sender (
82	_whitelist char(60) NOT NULL default '',	82	_whitelist char(60) NOT NULL default '',
Lines 84-90 Link Here
84	_expire int(10) unsigned NOT NULL default '0',	84	_expire int(10) unsigned NOT NULL default '0',
85	UNIQUE KEY _whitelist (_whitelist),	85	UNIQUE KEY _whitelist (_whitelist),
86	KEY _expire (_expire)	86	KEY _expire (_expire)
87	) TYPE=MyISAM;	87	) TYPE=InnoDB;
88		88
89	CREATE TABLE whitelist_dnsname (	89	CREATE TABLE whitelist_dnsname (
90	_whitelist char(60) NOT NULL default '',	90	_whitelist char(60) NOT NULL default '',
Lines 92-98 Link Here
92	_expire int(10) unsigned NOT NULL default '0',	92	_expire int(10) unsigned NOT NULL default '0',
93	UNIQUE KEY _whitelist (_whitelist),	93	UNIQUE KEY _whitelist (_whitelist),
94	KEY _expire (_expire)	94	KEY _expire (_expire)
95	) TYPE=MyISAM;	95	) TYPE=InnoDB;
96		96
97	CREATE TABLE blacklist (	97	CREATE TABLE blacklist (
98	_blacklist char(15) NOT NULL default '',	98	_blacklist char(15) NOT NULL default '',
Lines 100-111 Link Here
100	_expire int(10) unsigned NOT NULL default '0',	100	_expire int(10) unsigned NOT NULL default '0',
101	UNIQUE KEY _blacklist (_blacklist),	101	UNIQUE KEY _blacklist (_blacklist),
102	KEY _expire (_expire)	102	KEY _expire (_expire)
103	) TYPE=MyISAM;	103	) TYPE=InnoDB;
104		104
105	CREATE TABLE blacklist_helo (	105	CREATE TABLE blacklist_helo (
106	_helo char(60) NOT NULL default '',	106	_helo char(60) NOT NULL default '',
107	UNIQUE KEY _helo (_helo)	107	UNIQUE KEY _helo (_helo)
108	) TYPE=MyISAM;	108	) TYPE=InnoDB;
109		109
110	CREATE TABLE blacklist_sender (	110	CREATE TABLE blacklist_sender (
111	_blacklist char(60) NOT NULL default '',	111	_blacklist char(60) NOT NULL default '',
Lines 113-119 Link Here
113	_expire int(10) unsigned NOT NULL default '0',	113	_expire int(10) unsigned NOT NULL default '0',
114	UNIQUE KEY _blacklist (_blacklist),	114	UNIQUE KEY _blacklist (_blacklist),
115	KEY _expire (_expire)	115	KEY _expire (_expire)
116	) TYPE=MyISAM;	116	) TYPE=InnoDB;
117		117
118	CREATE TABLE blacklist_dnsname (	118	CREATE TABLE blacklist_dnsname (
119	_blacklist char(60) NOT NULL default '',	119	_blacklist char(60) NOT NULL default '',
Lines 121-133 Link Here
121	_expire int(10) unsigned NOT NULL default '0',	121	_expire int(10) unsigned NOT NULL default '0',
122	UNIQUE KEY _blacklist (_blacklist),	122	UNIQUE KEY _blacklist (_blacklist),
123	KEY _expire (_expire)	123	KEY _expire (_expire)
124	) TYPE=MyISAM;	124	) TYPE=InnoDB;
125		125
126	CREATE TABLE spamtrap (	126	CREATE TABLE spamtrap (
127	_rcpt char(60) NOT NULL default '',	127	_rcpt char(60) NOT NULL default '',
128	_active tinyint(1) unsigned NOT NULL default '1',	128	_active tinyint(1) unsigned NOT NULL default '1',
129	UNIQUE KEY _rcpt (_rcpt,_active)	129	UNIQUE KEY _rcpt (_rcpt,_active)
130	) TYPE=MyISAM;	130	) TYPE=InnoDB;
131		131
132	CREATE TABLE helo (	132	CREATE TABLE helo (
133	_host char(15) NOT NULL default '',	133	_host char(15) NOT NULL default '',
Lines 135-139 Link Here
135	_expire int(10) unsigned NOT NULL default '0',	135	_expire int(10) unsigned NOT NULL default '0',
136	UNIQUE KEY _host (_host,_helo),	136	UNIQUE KEY _host (_host,_helo),
137	KEY _expire (_expire)	137	KEY _expire (_expire)
138	) TYPE=MyISAM;	138	) TYPE=InnoDB;
139		139

Lines 71-77 Link Here

(-)policyd-v1.80/cleanup.c (-9 / +9 lines)
71		71
72	/* build up & execute query */	72	/* build up & execute query */
73	snprintf(mysqlquery_array[0], 512,	73	snprintf(mysqlquery_array[0], 512,
74	"DELETE QUICK FROM triplet WHERE _datelast < %d LIMIT 100000",	74	"DELETE FROM triplet WHERE _datelast < %d LIMIT 100000",
75	timenow - TRIPLET_AUTH_TIMEOUT);	75	timenow - TRIPLET_AUTH_TIMEOUT);
76	if(db_deletequery(0) == -1) exit(-1);	76	if(db_deletequery(0) == -1) exit(-1);
77		77
Lines 81-87 Link Here
81		81
82	/* build up & execute query */	82	/* build up & execute query */
83	snprintf(mysqlquery_array[0], 512,	83	snprintf(mysqlquery_array[0], 512,
84	"DELETE QUICK FROM triplet WHERE _datenew < %d AND _count = 0 LIMIT 100000",	84	"DELETE FROM triplet WHERE _datenew < %d AND _count = 0 LIMIT 100000",
85	timenow - TRIPLET_UNAUTH_TIMEOUT);	85	timenow - TRIPLET_UNAUTH_TIMEOUT);
86	if(db_deletequery(0) == -1) exit(-1);	86	if(db_deletequery(0) == -1) exit(-1);
87	}	87	}
Lines 95-101 Link Here
95		95
96	/* build up & execute query */	96	/* build up & execute query */
97	snprintf(mysqlquery_array[0], 512,	97	snprintf(mysqlquery_array[0], 512,
98	"DELETE QUICK FROM blacklist WHERE _expire <= %d \	98	"DELETE FROM blacklist WHERE _expire <= %d \
99	AND _expire != 0 LIMIT 100000",	99	AND _expire != 0 LIMIT 100000",
100	timenow);	100	timenow);
101	if(db_deletequery(0) == -1) exit(-1);	101	if(db_deletequery(0) == -1) exit(-1);
Lines 111-117 Link Here
111		111
112	/* build up & execute query */	112	/* build up & execute query */
113	snprintf(mysqlquery_array[0], 512,	113	snprintf(mysqlquery_array[0], 512,
114	"DELETE QUICK FROM whitelist WHERE _expire <= %d AND \	114	"DELETE FROM whitelist WHERE _expire <= %d AND \
115	_expire != 0 LIMIT 100000",	115	_expire != 0 LIMIT 100000",
116	timenow);	116	timenow);
117	if(db_deletequery(0) == -1) exit(-1);	117	if(db_deletequery(0) == -1) exit(-1);
Lines 127-133 Link Here
127		127
128	/* build up & execute query */	128	/* build up & execute query */
129	snprintf(mysqlquery_array[0], 512,	129	snprintf(mysqlquery_array[0], 512,
130	"DELETE QUICK FROM helo WHERE _expire <= %d AND _expire != 0 LIMIT 100000",	130	"DELETE FROM helo WHERE _expire <= %d AND _expire != 0 LIMIT 100000",
131	timenow);	131	timenow);
132	if(db_deletequery(0) == -1) exit(-1);	132	if(db_deletequery(0) == -1) exit(-1);
133	}	133	}
Lines 141-147 Link Here
141		141
142	/* build up & execute query */	142	/* build up & execute query */
143	snprintf(mysqlquery_array[0], 512,	143	snprintf(mysqlquery_array[0], 512,
144	"DELETE QUICK FROM throttle WHERE _date < %d",	144	"DELETE FROM throttle WHERE _date < %d",
145	timenow - SENDER_INACTIVE_EXPIRE);	145	timenow - SENDER_INACTIVE_EXPIRE);
146	if(db_deletequery(0) == -1) exit(-1);	146	if(db_deletequery(0) == -1) exit(-1);
147		147
Lines 151-157 Link Here
151		151
152	/* build up & execute query */	152	/* build up & execute query */
153	snprintf(mysqlquery_array[0], 512,	153	snprintf(mysqlquery_array[0], 512,
154	"DELETE QUICK FROM throttle_from_instance WHERE _expire < %d",	154	"DELETE FROM throttle_from_instance WHERE _expire < %d",
155	timenow - 3600);	155	timenow - 3600);
156	if(db_deletequery(0) == -1) exit(-1);	156	if(db_deletequery(0) == -1) exit(-1);
157	}	157	}
Lines 165-171 Link Here
165		165
166	/* build up & execute query */	166	/* build up & execute query */
167	snprintf(mysqlquery_array[0], 512,	167	snprintf(mysqlquery_array[0], 512,
168	"DELETE QUICK FROM throttle_rcpt WHERE _date < %d",	168	"DELETE FROM throttle_rcpt WHERE _date < %d",
169	timenow - RECIPIENT_INACTIVE_EXPIRE);	169	timenow - RECIPIENT_INACTIVE_EXPIRE);
170	if(db_deletequery(0) == -1) exit(-1);	170	if(db_deletequery(0) == -1) exit(-1);
171	}	171	}
Lines 180-186 Link Here
180		180
181	/* build up & execute query */	181	/* build up & execute query */
182	snprintf(mysqlquery_array[0], 512,	182	snprintf(mysqlquery_array[0], 512,
183	"DELETE QUICK FROM policy_training WHERE _expire < %d and _expire != 0",	183	"DELETE FROM policy_training WHERE _expire < %d and _expire != 0",
184	timenow - TRAINING_POLICY_TIMEOUT);	184	timenow - TRAINING_POLICY_TIMEOUT);
185	if(db_deletequery(0) == -1) exit(-1);	185	if(db_deletequery(0) == -1) exit(-1);
186	}	186	}

Lines 104-110 Link Here

(-)policyd-v1.80/greylist.c (-7 / +7 lines)
104	{	104	{
105	/* build up & execute query */	105	/* build up & execute query */
106	snprintf(mysqlquery_array[fd], 512,	106	snprintf(mysqlquery_array[fd], 512,
107	"INSERT DELAYED INTO triplet (_datenew,_datelast,_host,_from,_rcpt) VALUES (%d,%d,'%s','%s','%s')",	107	"INSERT INTO triplet (_datenew,_datelast,_host,_from,_rcpt) VALUES (%d,%d,'%s','%s','%s')",
108	timenow, timenow, triplet_array[fd][0], triplet_array[fd][1], triplet_array[fd][2]);	108	timenow, timenow, triplet_array[fd][0], triplet_array[fd][1], triplet_array[fd][2]);
109	if(db_doquery(fd) == -1) return(db_failure(fd, "greylist"));	109	if(db_doquery(fd) == -1) return(db_failure(fd, "greylist"));
110		110
Lines 134-144 Link Here
134	if(BLACKLIST_NETBLOCK==1)	134	if(BLACKLIST_NETBLOCK==1)
135	{ /* blacklist netblock */	135	{ /* blacklist netblock */
136	snprintf(mysqlquery_array[fd], 512,	136	snprintf(mysqlquery_array[fd], 512,
137	"INSERT DELAYED INTO blacklist (_blacklist,_description,_expire) VALUES ('%s.%%','# autoblacklisted', %d)",	137	"INSERT INTO blacklist (_blacklist,_description,_expire) VALUES ('%s.%%','# autoblacklisted', %d)",
138	triplet_array[fd][0], expire);	138	triplet_array[fd][0], expire);
139	} else { /* blacklist host ip */	139	} else { /* blacklist host ip */
140	snprintf(mysqlquery_array[fd], 512,	140	snprintf(mysqlquery_array[fd], 512,
141	"INSERT DELAYED INTO blacklist (_blacklist,_description,_expire) VALUES ('%s','# autoblacklisted',%d)",	141	"INSERT INTO blacklist (_blacklist,_description,_expire) VALUES ('%s','# autoblacklisted',%d)",
142	host_array[fd][2], expire);	142	host_array[fd][2], expire);
143	}	143	}
144	/* execute query */	144	/* execute query */
Lines 155-161 Link Here
155		155
156	/* build up & execute query */	156	/* build up & execute query */
157	snprintf(mysqlquery_array[fd], 512,	157	snprintf(mysqlquery_array[fd], 512,
158	"DELETE QUICK from triplet WHERE _host='%s'", triplet_array[fd][0]);	158	"DELETE from triplet WHERE _host='%s'", triplet_array[fd][0]);
159	if(db_doquery(fd) == -1) return(db_failure(fd, "greylist"));	159	if(db_doquery(fd) == -1) return(db_failure(fd, "greylist"));
160		160
161	/* reject */	161	/* reject */
Lines 249-267 Link Here
249	{	249	{
250	/* build up & execute query */	250	/* build up & execute query */
251	snprintf(mysqlquery_array[fd], 512,	251	snprintf(mysqlquery_array[fd], 512,
252	"INSERT DELAYED INTO whitelist (_whitelist,_description,_expire) VALUES ('%s.%%','# autowhitelisted host',%d)",	252	"INSERT INTO whitelist (_whitelist,_description,_expire) VALUES ('%s.%%','# autowhitelisted host',%d)",
253	triplet_array[fd][0], expire);	253	triplet_array[fd][0], expire);
254	} else {	254	} else {
255	/* build up & execute query */	255	/* build up & execute query */
256	snprintf(mysqlquery_array[fd], 512,	256	snprintf(mysqlquery_array[fd], 512,
257	"INSERT DELAYED INTO whitelist (_whitelist,_description,_expire) VALUES ('%s','# autowhitelisted host',%d)",	257	"INSERT INTO whitelist (_whitelist,_description,_expire) VALUES ('%s','# autowhitelisted host',%d)",
258	host_array[fd][2], expire);	258	host_array[fd][2], expire);
259	}	259	}
260	if(db_doquery(fd) == -1) return(db_failure(fd, "greylist"));	260	if(db_doquery(fd) == -1) return(db_failure(fd, "greylist"));
261		261
262	/* build up & execute query */	262	/* build up & execute query */
263	snprintf(mysqlquery_array[fd], 512,	263	snprintf(mysqlquery_array[fd], 512,
264	"DELETE QUICK from triplet WHERE _host='%s'", triplet_array[fd][0]);	264	"DELETE from triplet WHERE _host='%s'", triplet_array[fd][0]);
265	if(db_doquery(fd) == -1) return(db_failure(fd, "greylist"));	265	if(db_doquery(fd) == -1) return(db_failure(fd, "greylist"));
266		266
267	logmessage("rcpt=%lu, greylist=awl, host=%s (%s), from=%s, to=%s, size=%s, expire=%d\n",	267	logmessage("rcpt=%lu, greylist=awl, host=%s (%s), from=%s, to=%s, size=%s, expire=%d\n",

Lines 71-77 Link Here

(-)policyd-v1.80/helo.c (-3 / +3 lines)
71		71
72	/* build up query */	72	/* build up query */
73	snprintf(mysqlquery_array[fd], 512,	73	snprintf(mysqlquery_array[fd], 512,
74	"INSERT DELAYED INTO blacklist (_blacklist,_description,_expire) VALUES ('%s','# helo abuse',%d)",	74	"INSERT INTO blacklist (_blacklist,_description,_expire) VALUES ('%s','# helo abuse',%d)",
75	host_array[fd][2], expire);	75	host_array[fd][2], expire);
76	if(db_doquery(fd) == -1) return(db_failure(fd, "helo"));	76	if(db_doquery(fd) == -1) return(db_failure(fd, "helo"));
77		77
Lines 88-94 Link Here
88	/* clean up helo table entries */	88	/* clean up helo table entries */
89	/* build up query */	89	/* build up query */
90	snprintf(mysqlquery_array[fd], 512,	90	snprintf(mysqlquery_array[fd], 512,
91	"DELETE QUICK FROM helo WHERE _host='%s'", host_array[fd][2]);	91	"DELETE FROM helo WHERE _host='%s'", host_array[fd][2]);
92	if(db_doquery(fd) == -1) return(db_failure(fd, "helo"));	92	if(db_doquery(fd) == -1) return(db_failure(fd, "helo"));
93		93
94	return (1);	94	return (1);
Lines 118-124 Link Here
118		118
119	/* build up query & execute */	119	/* build up query & execute */
120	snprintf(mysqlquery_array[fd], 512,	120	snprintf(mysqlquery_array[fd], 512,
121	"INSERT DELAYED INTO helo (_host,_helo,_expire) VALUES ('%s','%s',%d)",	121	"INSERT INTO helo (_host,_helo,_expire) VALUES ('%s','%s',%d)",
122	host_array[fd][2], triplet_array[fd][5], expire);	122	host_array[fd][2], triplet_array[fd][5], expire);
123	if(db_doquery(fd) == -1) return(db_failure(fd, "helo"));	123	if(db_doquery(fd) == -1) return(db_failure(fd, "helo"));
124	}	124	}

Lines 68-79 Link Here

(-)policyd-v1.80/spamtrap.c (-2 / +2 lines)
68	{	68	{
69	/* build up query */	69	/* build up query */
70	snprintf(mysqlquery_array[fd], 512,	70	snprintf(mysqlquery_array[fd], 512,
71	"INSERT DELAYED INTO blacklist (_blacklist,_description,_expire) VALUES ('%s','# spamtrap delivery: (%s)',%d)",	71	"INSERT INTO blacklist (_blacklist,_description,_expire) VALUES ('%s','# spamtrap delivery: (%s)',%d)",
72	host_array[fd][3], triplet_array[fd][2], expire);	72	host_array[fd][3], triplet_array[fd][2], expire);
73	} else { /* blacklist host ip */	73	} else { /* blacklist host ip */
74	/* build up query */	74	/* build up query */
75	snprintf(mysqlquery_array[fd], 512,	75	snprintf(mysqlquery_array[fd], 512,
76	"INSERT DELAYED INTO blacklist (_blacklist,_description,_expire) VALUES ('%s','# spamtrap delivery: (%s)',%d)",	76	"INSERT INTO blacklist (_blacklist,_description,_expire) VALUES ('%s','# spamtrap delivery: (%s)',%d)",
77	host_array[fd][2], triplet_array[fd][2], expire);	77	host_array[fd][2], triplet_array[fd][2], expire);
78	}	78	}
79	/* execute query */	79	/* execute query */