Re: [AMaViS-user] mysql-SQL changes in amavisd-new-2.6.1

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

Peter,

> I had to make a few changes to my initial mail. While everything looks
> (is) right, it's not as simple as it seems.
> In order to change the field type from varchar to varbinary I had to first
> drop the foreign key and only then could do the changes. [...]

Thanks for details.

Before we scare everyone away, I'd just like to point out that
as far as varbinary/binary change is concerned, there was no change
in amavisd code between 2.6.0 and 2.6.1. The topic is only about
documentation change, i.e. best practices. If one wants to distinguish
between 'S' and 's' as a value of msgs.content (e.g. as may be seen by
a management application), then binary is a more appropriate data type,
as pointed out by Thomas Gelf. Amavisd does not care about the value
of existing records, it only sets it.


> I also had to change the maddr(id) to integer, in order to make it work at
> all. I realise it says somwhere in the README that they would have to be
> the same. However a diff to the two versions of READMEs did not show that
> change.

But it does, sid and rid are changed to the same data type as maddr.id :

@@ -180,3 +180,3 @@
   time_iso   char(16)      NOT NULL,    -- rx_time: ISO8601 UTC ascii time
-  sid        integer unsigned NOT NULL, -- sender: maddr.id
+  sid        bigint unsigned NOT NULL, -- sender: maddr.id
   policy     varchar(255)  DEFAULT '',  -- policy bank path (like macro %p)
@@ -211,4 +211,4 @@
   partition_tag integer    DEFAULT 0,    -- see $sql_partition_tag
-  mail_id    varchar(12)   NOT NULL,     -- (must allow duplicates)
-  rid        integer unsigned NOT NULL,  -- recipient: maddr.id (dupl. allowed)
+  mail_id    varbinary(12) NOT NULL,     -- (must allow duplicates)
+  rid        bigint unsigned NOT NULL,   -- recipient: maddr.id (dupl. allowed)
   ds         char(1)       NOT NULL,     -- delivery status: P/R/B/D/T

> I also had a problem with changing from bigint to integer. While the field
> type change worked fine in mysql. I received an error after restarting
> amavisd:
>
> Jun 29 16:59:24 george.eliot.priv.at /usr/sbin/amavisd[21307]: (21307)
> (!!)TROUBLE in check_mail: sql-enter FAILED: find_or_save_addr: failed to
> insert addr int...@ha...: sql exec: err=1062, S1000,
> DBD::mysql::st execute failed: Duplicate entry '0' for key 1 at (eval 87)
> line 166, <GEN19> line 21. at (eval 88) line 111, <GEN19> line 21.

Looks like the AUTO_INCREMENT counter value was reset after the change
of the table msgs, and started counting from zero again. Not good.

Note that the diff on README.sql-mysql between 2.6.0 and 2.6.1 did not
change table maddr, but changed the other two tables which referenced
the maddr. If it worked before, there is not need to change the data
type of maddr.id, msgs.sid and msgrcpt.rid. I was only fixing the issue
when creating a dataset from scratch, cut/pasting from README.sql-mysql,
was reported not to work because of a data type mismatch in foregn keys,
so I fixed the docs. Again, no change in amavisd code took place between
2.6.0 and 2.6.1 in this matter.

> Maybe this is normal behaviour, when one changes a previous type of bigint
> into integer. I am not that familiar with mysql to say.
>
> In the end I left the fields as they were, and only changed the varchar to
> varbinary (after dropping the foreign keys)

Good. No need to change anything if it worked.

> Sorry to bother you again, with this, I thought it might safe other people
> the frustration of trying to apply SQL commands I posted earlier.

Yes, thanks, it is valuable to clarify things and to share experience.

  Mark