[Dspam-devel] [ dspam-Bug Tracker-3290708 ] MySQL error while learning

[SourceForge.net <no...@so...>]

Bug Tracker item #3290708, was opened at 2011-04-21 03:46
Message generated for change (Tracker Item Submitted) made by raizel
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126467&aid=3290708&group_id=250683

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: None
Group: v3.9.0
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Raizel (raizel)
Assigned to: Nobody/Anonymous (nobody)
Summary: MySQL error while learning

Initial Comment:
Hello,

I'm running dspam on an Arch Linux and I just noticed that with the last update to version 3.9.0-4 the learning routine sometimes fails with an MySQL error.
The error may also just have arised due to an MySQL update to version 5.5.11-1 installed on the same day.
In any case, it seems that MySQL now evaluates fields and operations on field strictly abiding the field type.
Hence GREATEST(0,innocent_hits-1) will fail when innocent_hits is already 0, as it was defined unsigned.

I replicated the issue by using

SELECT innocent_hits-1 FROM dspam_token_data;

and

SELECT GREATEST(0,innocent_hits-1) FROM dspam_token_data;

Both resulting in the same out of range error.

The table layout I used is the one provided with the installation of dspam by the way.
mysql> describe dspam_token_data;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| uid           | int(10) unsigned    | NO   | PRI | NULL    |       |
| token         | varchar(20)         | NO   | PRI | NULL    |       |
| spam_hits     | bigint(20) unsigned | NO   |     | NULL    |       |
| innocent_hits | bigint(20) unsigned | NO   |     | NULL    |       |
| last_hit      | date                | NO   |     | NULL    |       |
+---------------+---------------------+------+-----+---------+-------+

The affected places in the code might be two different, one for learning spam (-> innocent-1) and one for innocent (->spam-1).

[04/07/2011 20:52:02] 12192: BIGINT UNSIGNED value is out of range in '(`crm_mail_dspam`.`dspam_token_data`.`spam_hits` - 1)': UPDATE dspam_token_data SET last_hit=CURRENT_DATE(),spam_hits=GREATEST(0,spam_hits-1),innocent_hits=GREATEST(0,innocent_hits+1) WHERE uid=1 AND token IN ('919723413030374694','13443240891866447616','16665632978184174093','6671638468243386368','9307258077686163
[...]
);
[04/08/2011 20:52:01] 12591: BIGINT UNSIGNED value is out of range in '(`crm_mail_dspam`.`dspam_token_data`.`innocent_hits` - 1)': UPDATE dspam_token_data SET last_hit=CURRENT_DATE(),spam_hits=GREATEST(0,spam_hits+1),innocent_hits=GREATEST(0,innocent_hits-1) WHERE uid=1 AND token IN ('11954504243112204174','208962648082639053','7327233996930628601','5813000335823732736','8588670824882
[...]
);

As we want to keep updating the other respective values of the record, I suggest using IF([spam|innocent]_hits,[spam|innocent]_hits-1,0) instead of using GREATEST().

If this issue is already fixed in 3.9.1, I'll patiently wait for the release. Thanks in advance.

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126467&aid=3290708&group_id=250683