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
