Problems upgrading amavisd-new-2.6.6 -> 2.7.0

Mikael Bak mikael at t-online.hu
Thu Sep 22 13:55:21 CEST 2011


Hi list,

>From the release notes:

- SQL fields msgs.mail_id, msgs.secret_id, msgrcpt.mail_id and
  quarantine.mail_id must be treated case-insensitively. A suitable data
  type for these fields in PostgreSQL is bytea, and varbinary in MySQL
  (of size 12 or 16 characters). In order not to lose entropy in mail_id,
  and not to increase a probability of collisions, please check existing
  database schema and adjust as necessary, either a data type, or chose
  a case-sensitive collation setting. See README.sql-pg and README.sql-mysql
  for an ALTER command to change data type of these fields.


The README.sql-mysql says this:

Similarly, semantics of some other fields is strings of octets too, with
no associated character set (which is a characteristic of char and varchar
data types), so the following alternations to pre-2.6.2 schema may be
beneficial to prevent SQL server from checking validity of octets data
against non-applicable arbitrary character set constraints:
  ALTER table msgs       CHANGE mail_id mail_id varbinary(12);
  ALTER table msgs       CHANGE secret_id secret_id varbinary(12);
  ALTER table msgs       CHANGE content content binary(1);
  ALTER table msgs       CHANGE quar_type quar_type binary(1);
  ALTER table msgs       CHANGE quar_loc quar_loc binary(255);
  ALTER table msgrcpt    CHANGE mail_id mail_id varbinary(12);
  ALTER table quarantine CHANGE mail_id mail_id varbinary(12);
and for good measure:
  ALTER table msgrcpt    CHANGE rid rid bigint unsigned;
  ALTER table msgs       CHANGE sid sid bigint unsigned;


But when I execute the first alter table statement I get this in the
mysql console:

mysql> ALTER table msgs       CHANGE mail_id mail_id varbinary(12);
ERROR 1025 (HY000): Error on rename of './quarantine/#sql-4ad_9f7f' to
'./quarantine/msgs' (errno: 150)

mysql> SHOW INNODB STATUS ;
[snip]
------------------------
LATEST FOREIGN KEY ERROR
------------------------
110922 11:50:55 Error in foreign key constraint of table quarantine/msgrcpt:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match the ones in table. Constraint:
,
  CONSTRAINT "msgrcpt_ibfk_2" FOREIGN KEY ("mail_id") REFERENCES "msgs"
("mail_id") ON DELETE CASCADE
The index in the foreign key in table is "msgrcpt_idx_mail_id"
See
http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
[snip]

Anybody knows how to fix this?
TIA,
Mikael


More information about the amavis-users mailing list