1
$Id: mysql_drv.txt,v 1.3 2010/04/26 09:30:23 sbajic Exp $
1
$Id: mysql_drv.txt,v 1.5 2011/06/28 00:13:48 sbajic Exp $
3
Copyright (c) 2002-2010 DSPAM Project
3
COPYRIGHT (C) 2002-2011 DSPAM Project
4
4
http://dspam.sourceforge.net
8
This program is free software; you can redistribute it and/or
9
modify it under the terms of the GNU General Public License
10
as published by the Free Software Foundation; version 2
8
This program is free software: you can redistribute it and/or modify
9
it under the terms of the GNU Affero General Public License as
10
published by the Free Software Foundation, either version 3 of the
11
License, or (at your option) any later version.
13
13
This program is distributed in the hope that it will be useful,
14
14
but WITHOUT ANY WARRANTY; without even the implied warranty of
15
15
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16
GNU General Public License for more details.
16
GNU Affero General Public License for more details.
18
You should have received a copy of the GNU General Public License
19
along with this program; if not, write to the Free Software
20
Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
18
You should have received a copy of the GNU Affero General Public License
19
along with this program. If not, see <http://www.gnu.org/licenses/>.
30
29
- No need for context locking mechanisms
31
30
- Replication and other MySQL features
33
It is STRONGLY RECOMMENDED that you run DSPAM with MySQL v4.1 or greater, as
34
some critical issues were addressed including:
36
- Support for full unsigned 2^64 integer support
37
This will allow DSPAM to store its token keys as numeric (8 bytes) instead
38
of character (20 bytes). On older versions of MySQL, DSPAM is forced to
39
use character fields. Using the mysql_objects-4.1.sql script will
40
result in a database about 30-35% smaller and more optimized.
42
- Support for subqueries
43
This allows purge-4.1.sql to conditionally purge based on the user's
44
training mode, which allows a cleaner and more granular purge.
47
4.1 Supports the use of ON DUPLICATE KEY which allows us to perform all
48
token inserts with a single query. As a result, MySQL 4.1 is about 20%
53
Some versions of 4.1 apparently have a bug with the way bigint's are handled.
54
If you have this bug, you'll need to either upgrade to a better version of
55
MySQL or use the 4.0 (speed, space) object scripts for DSPAM instead of the 4.1
58
To check for this bug, log into MySQL and issue these statements:
60
create table dspam_test (token bigint(20));
62
insert into dspam_test values('3232933621568372736'),('5174632569486238062'),('5860192774298853742'),('8787532532089676142'),('8029914733239132526');
64
select token from dspam_test where token in('3232933621568372736','8787532532089676142','8029914733239132526','5174632569486238062','5860192774298853742');
66
Broken versions of MySQL will return only one row, whereas a working
67
version will return all five rows.
32
DSPAM support MySQL 5.0 and greater.
69
34
1. CONFIGURING DSPAM
93
58
2. CREATING MYSQL OBJECTS
95
Before mysql_drv will function, you must run one of the mysql_objects.sql files
60
Before mysql_drv will function, you must run the mysql_objects-4.1.sql file
96
61
(located in src/tools.mysql_drv) to create the table objects required by the
97
driver. Depending on whether you are looking to optimize for speed or disk
98
space, you should run the appropriately named script. See your MySQL
99
documentation for further information.
62
driver. See your MySQL documentation for further information.
101
64
This script assumes that you have already created a database for DSPAM objects
102
65
and a user with full access to SELECT, INSERT, UPDATE, and DELETE.
111
74
This will extend the query size from 1MB to 8MB; some larger queries may
112
75
cause MySQL to drop connection without this set.
77
NOTE: The mysql_objects-space.sql, mysql_objects-speed.sql and purge,sql files
78
located in src/tools.mysql_drv were originally created for older MySQL versions.
79
They can be used on recent MySQL versions, but do not use MySQL features for
80
improved performance available since MySQL 4.1. For new setups, you should
81
always use the *-4.1.sql files.
114
83
3. UPDATING DSPAM.CONF
116
85
mysql_drv needs to know how to connect to your MySQL database. You will need to
144
113
If you are supporting TOE or TUM-mode users on your system, you will want to
145
114
do one of the following:
147
1. Preferences Extensions + MySQL 4.1 or Greater
116
1. Preferences Extensions
149
If you are running MySQL 4.1 or greater, it is recommended you activate
150
preferences extension support and recompile. You may then use
151
purge-4.1.sql instead of purge.sql, which will skip certain types of
152
purges for users with TOE/TUM specified in their preferences.
118
It is recommended you activate preferences extension support.
119
purge-4.1.sql will skip certain types of purges for users with TOE/TUM
120
specified in their preferences.
154
122
NOTE: You should add a preference for any global users on your system, so
155
123
that their data is purged as if TOE-based (since global user data is
158
126
dspam_admin add pref [username] trainingMode TOE
160
2. Global TOE Support or MySQL < 4.1
162
If you are running an earlier version of MySQL or if you are using
163
TOE globally (and not in individual user preferences), then you should
164
use purge.sql, but remove the following lines:
166
delete from dspam_token_data
167
where from_days(@a-90) > last_hit;
128
2. Global TOE Support
130
If you are using TOE globally (and not in individual user preferences),
131
then you should update purge-4.1.sql, and set the default training mode
134
SET @TrainingMode = 'TOE';
169
136
This will prevent the purging of stale tokens, which could cause serious
170
137
data loss in TOE databases (because tokens are never touched unless
174
141
If you will NOT be supporting TOE users on your system, you may simply run
175
the purge.sql script nightly, as-is.
142
the purge-4,1.sql script nightly, as-is.
245
212
ON DELETE CASCADE;
246
213
SET FOREIGN_KEY_CHECKS=1;
248
If you are using the preference extension then issue the following SQL commands
249
to add the additional constrain to the preference extension table:
215
If you are using the preference extension then issue the following SQL
216
commands to add the additional constrain to the preference extension table:
250
217
SET FOREIGN_KEY_CHECKS=0;
251
218
ALTER TABLE `dspam_preferences`
252
219
ADD CONSTRAINT `dspam_preferences_ibfk_1`