amavis-user

[AMaViS-user] Duplicate entry with @storage_sql_dsn

[Zhang H. <zhb...@gm...>]

Hi, all.

I enabled '@storage_sql_dsn' in Amavisd-new-2.6.4, used to store
incoming/outgoing mail info, and quarantine SPAM into MySQL database.

But i got many "Duplicate entry" log like below, just wondering how to solve
it.

#--<--
Feb  9 12:10:16 mail amavis[31806]: (31806-06) (!)WARN save_info_final: sql exec: err=1062, 23000, DBD::mysql::st execute failed: Duplicate entry '0-9R6CJuPSz7Ar-0' for key 1 at (eval 102) line 166, <GEN15> line 7126.
Feb  9 12:10:18 mail amavis[31806]: (31806-06) (!)WARN save_info_final: sql exec: err=1062, 23000, DBD::mysql::st execute failed: Duplicate entry '0-9R6CJuPSz7Ar-0' for key 1 at (eval 102) line 166, <GEN15> line 7126.
Feb  9 12:10:33 mail amavis[32041]: (32041-03) (!)WARN save_info_final: sql exec: err=1062, 23000, DBD::mysql::st execute failed: Duplicate entry '0-7-BWtkkAeqdC-0' for key 1 at (eval 102) line 166, <GEN15> line 3484.
Feb  9 12:10:34 mail amavis[32041]: (32041-03) (!)WARN save_info_final: sql exec: err=1062, 23000, DBD::mysql::st execute failed: Duplicate entry '0-7-BWtkkAeqdC-0' for key 1 at (eval 102) line 166, <GEN15> line 3484.
#--<--

I try to increase MySQL "max_connections" setting, but it doesn't work at
all.

Any suggestions? Thanks. :)

Re: [AMaViS-user] Duplicate entry with @storage_sql_dsn

[Mark M. <Mar...@ij...>]

Huangbin,

> I enabled '@storage_sql_dsn' in Amavisd-new-2.6.4, used to store
> incoming/outgoing mail info, and quarantine SPAM into MySQL database.
> 
> But i got many "Duplicate entry" log like below, just wondering how to
> solve it.
> 
> #--<--
> Feb  9 12:10:16 mail amavis[31806]: (31806-06) (!)WARN save_info_final: sql
> exec: err=1062, 23000, DBD::mysql::st execute failed: Duplicate entry
> '0-9R6CJuPSz7Ar-0' for key 1 at (eval 102) line 166, <GEN15> line 7126.
> Feb  9 12:10:18 mail amavis[31806]: (31806-06) (!)WARN save_info_final:
> sql exec: err=1062, 23000, DBD::mysql::st execute failed: Duplicate entry
> '0-9R6CJuPSz7Ar-0' for key 1 at (eval 102) line 166, <GEN15> line 7126.
> Feb  9 12:10:33 mail amavis[32041]: (32041-03) (!)WARN save_info_final:
> sql exec: err=1062, 23000, DBD::mysql::st execute failed: Duplicate entry
> '0-7-BWtkkAeqdC-0' for key 1 at (eval 102) line 166, <GEN15> line 3484.
> Feb  9 12:10:34 mail amavis[32041]: (32041-03) (!)WARN save_info_final:
> sql exec: err=1062, 23000, DBD::mysql::st execute failed: Duplicate entry
> '0-7-BWtkkAeqdC-0' for key 1 at (eval 102) line 166, <GEN15> line 3484.
> #--<--
> 
> I try to increase MySQL "max_connections" setting, but it doesn't work at
> all.

I'm guessing here, but according to the reported key '0-9R6CJuPSz7Ar-0'
it has three components, like in a schema suggested for 2.7.0.
With the third component added (a rseqnum field) in 2.7.0,
this composite key became suitable as a primary key.

With 2.6.4 or earlier the suggested schema does not have a
field msgrcpt.rseqnum. The key in table msgrcpt is either mail_id
or a composite key (partition_tag,mail_id). In any case, this is
*not* a unique key (a message can have more than one recipient),
and as such it must not be declared a primary key.

So, either use the 2.7.0 schema with 2.7.0, or remove the
'unique' constraint on a 2.6.4 msgrcpt.mail_id key.

  Mark

Re: [AMaViS-user] Duplicate entry with @storage_sql_dsn

[Zhang H. <zhb...@gm...>]

On 2/17/11 12:47 AM, Mark Martinec wrote:
> So, either use the 2.7.0 schema with 2.7.0, or remove the
> 'unique' constraint on a 2.6.4 msgrcpt.mail_id key.
Thanks Mark. :)

Since v2.7.0 is not released yet, i will stay on v2.6.4.

SQL commands used to fix it:

mysql> ALTER TABLE msgrcpt DROP PRIMARY KEY;
mysql> CREATE INDEX idx_mail_id ON msgrcpt (mail_id);