Bug Tracker item #2990523, was opened at 2010-04-21 19:47
Message generated for change (Comment added) made by sbajic
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=1126467&aid=2990523&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: contrib/thunderbird
Group: v3.9.0
Status: Closed
Resolution: Fixed
Priority: 5
Private: No
Submitted By: interfaSys ()
Assigned to: Stevan Bajic (sbajic)
Summary: Issue with SQL purge script
Initial Comment:
I tried the following command
/usr/local/bin/dspam_maintenance.sh --logdays=30 --signatures=30 --unused=60
--with-sql-optimization --with-sql-autoupdate --verbose
But it looks like the purge script is having problems
Running MySQL storage driver data cleanup
ERROR 1271 (HY000) at line 59: Illegal mix of collations for operation ' IN '
MySQL purge script returned error code 1
I'm using MySQL 5.1 and the Dspam tables are using InnoDB
----------------------------------------------------------------------
>Comment By: Stevan Bajic (sbajic)
Date: 2010-04-21 21:43
Message:
Hallo Oliver,
> Thank you for working in this.
>
I have to thank you for reporting that issue.
--
Kind Regards from Switzerland,
Stevan Bajić
----------------------------------------------------------------------
Comment By: interfaSys ()
Date: 2010-04-21 21:35
Message:
Congratulations Stevan!
That fixed it. The maintenance script is now working fine.
Thank you for working in this.
Olivier
----------------------------------------------------------------------
Comment By: Stevan Bajic (sbajic)
Date: 2010-04-21 21:22
Message:
Hallo Oliver,
can you try the attached purge-4.1.sql? Does it fix your issue?
btw: You could save some space by using latin1 instead of utf8.
Stevan
----------------------------------------------------------------------
Comment By: interfaSys ()
Date: 2010-04-21 20:16
Message:
Hello Stevan,
I get the same errors with or without the options.
I also tried to run the script in phpmyadmin and got the same result:
Error
SQL query:
DELETE LOW_PRIORITY QUICK FROM t USING dspam_token_data t LEFT JOIN
dspam_preferences p ON ( p.preference = 'trainingMode' AND p.uid = t.uid )
LEFT JOIN dspam_preferences d ON ( d.preference = 'trainingMode' AND d.uid
=0 ) WHERE COALESCE( p.value, d.value, @TrainingMode ) NOT IN (
'TOE',
'TUM',
'NOTRAIN'
) AND from_days( @today - @PurgeUnused ) > last_hit;
MySQL said: Documentation
#1271 - Illegal mix of collations for operation ' IN '
And here are the tables, as requested:
mysql> show create table dspam_preferences\G;
*************************** 1. row ***************************
Table: dspam_preferences
Create Table: CREATE TABLE `dspam_preferences` (
`uid` int(10) unsigned NOT NULL DEFAULT '0',
`preference` varchar(32) COLLATE utf8_swedish_ci NOT NULL DEFAULT '',
`value` varchar(64) COLLATE utf8_swedish_ci NOT NULL DEFAULT '',
UNIQUE KEY `id_preferences_01` (`uid`,`preference`),
CONSTRAINT `dspam_preferences_ibfk_1` FOREIGN KEY (`uid`) REFERENCES
`dspam_virtual_uids` (`uid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci
1 row in set (0.01 sec)
mysql> show create table dspam_signature_data\G;
*************************** 1. row ***************************
Table: dspam_signature_data
Create Table: CREATE TABLE `dspam_signature_data` (
`uid` int(10) unsigned NOT NULL DEFAULT '0',
`signature` varchar(32) COLLATE utf8_swedish_ci NOT NULL DEFAULT '',
`data` longblob NOT NULL,
`length` int(10) unsigned NOT NULL DEFAULT '0',
`created_on` date NOT NULL DEFAULT '0000-00-00',
UNIQUE KEY `id_signature_data_01` (`uid`,`signature`),
KEY `id_signature_data_02` (`created_on`),
CONSTRAINT `dspam_signature_data_ibfk_1` FOREIGN KEY (`uid`) REFERENCES
`dspam_virtual_uids` (`uid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci
MAX_ROWS=2500000 AVG_ROW_LENGTH=8096
1 row in set (0.00 sec)
mysql> show create table dspam_stats\G;
*************************** 1. row ***************************
Table: dspam_stats
Create Table: CREATE TABLE `dspam_stats` (
`uid` int(10) unsigned NOT NULL DEFAULT '0',
`spam_learned` bigint(20) unsigned NOT NULL DEFAULT '0',
`innocent_learned` bigint(20) unsigned NOT NULL DEFAULT '0',
`spam_misclassified` bigint(20) unsigned NOT NULL DEFAULT '0',
`innocent_misclassified` bigint(20) unsigned NOT NULL DEFAULT '0',
`spam_corpusfed` bigint(20) unsigned NOT NULL DEFAULT '0',
`innocent_corpusfed` bigint(20) unsigned NOT NULL DEFAULT '0',
`spam_classified` bigint(20) unsigned NOT NULL DEFAULT '0',
`innocent_classified` bigint(20) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`uid`),
CONSTRAINT `dspam_stats_ibfk_1` FOREIGN KEY (`uid`) REFERENCES
`dspam_virtual_uids` (`uid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci
1 row in set (0.00 sec)
mysql> show create table dspam_token_data\G;
*************************** 1. row ***************************
Table: dspam_token_data
Create Table: CREATE TABLE `dspam_token_data` (
`uid` int(10) unsigned NOT NULL DEFAULT '0',
`token` bigint(20) unsigned NOT NULL DEFAULT '0',
`spam_hits` bigint(20) unsigned NOT NULL DEFAULT '0',
`innocent_hits` bigint(20) unsigned NOT NULL DEFAULT '0',
`last_hit` date NOT NULL DEFAULT '0000-00-00',
UNIQUE KEY `id_token_data_01` (`uid`,`token`),
KEY `spam_hits` (`spam_hits`),
KEY `innocent_hits` (`innocent_hits`),
KEY `last_hit` (`last_hit`),
CONSTRAINT `dspam_token_data_ibfk_1` FOREIGN KEY (`uid`) REFERENCES
`dspam_virtual_uids` (`uid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci PACK_KEYS=1
1 row in set (0.00 sec)
mysql> show create table dspam_virtual_uids\G;
*************************** 1. row ***************************
Table: dspam_virtual_uids
Create Table: CREATE TABLE `dspam_virtual_uids` (
`uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(128) COLLATE utf8_swedish_ci DEFAULT NULL,
PRIMARY KEY (`uid`),
UNIQUE KEY `id_virtual_uids_01` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=2052 DEFAULT CHARSET=utf8
COLLATE=utf8_swedish_ci
1 row in set (0.00 sec)
----------------------------------------------------------------------
Comment By: Stevan Bajic (sbajic)
Date: 2010-04-21 20:01
Message:
Hallo Oliver,
can you post here the output you get when executing the following command
in a MySQL shell:
----------------------------
show create table dspam_preferences\G;
show create table dspam_signature_data\G;
show create table dspam_stats\G;
show create table dspam_token_data\G;
show create table dspam_virtual_uids\G;
----------------------------
Could you as well try to run once without "--with-sql-autoupdate"? Do you
get the error then as well?
And how about without "--with-sql-optimization" and without
"--with-sql-autoupdate"? Do you get the error then as well?
Stevan
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=1126467&aid=2990523&group_id=250683
------------------------------------------------------------------------------
_______________________________________________
Dspam-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/dspam-devel
