Hi Mark, et al
Thank you for your mail. I am glad to help a little, if I can.
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.
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.
So I did the following to make it work (no changes to sid, rid, id):
--------------------------------
#### Drop foreign keys first:
ALTER table msgrcpt DROP foreign key msgrcpt_ibfk_2;
ALTER table quarantine DROP foreign key quarantine_ibfk_1;
ALTER table msgs CHANGE mail_id mail_id varbinary(12);
ALTER table msgrcpt CHANGE mail_id mail_id varbinary(12);
ALTER table quarantine CHANGE mail_id mail_id varbinary(12);
##### Add the foreign keys again
ALTER TABLE quarantine ADD FOREIGN KEY (mail_id) REFERENCES msgs(mail_id)
ON DELETE CASCADE;
ALTER TABLE msgrcpt ADD FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON
DELETE CASCADE;
#### Change the rest
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);
-----------------------------------------------------------
Now, I don't know if the order of things is the same for everyone (I.e.
the sequence of the foreign keys. This can be checked FIRST! with
SHOW CREATE table msgs; (or any other table)
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:
in the maillog:
Jun 29 16:59:24 george sendmail[21471]: m5TExK8l021471: Milter: data,
reject=451 4.5.0 Error in processing, id=21307, sql-enter FAILED:
find_or_save_addr: failed to insert addr [EMAIL PROTECTED]: 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.
Jun 29 16:59:24 george sendmail[21471]: m5TExK8l021471:
to=<[EMAIL PROTECTED]>, delay=00:00:02, pri=31256, stat=Error in
processing, id=21307, sql-enter FAILED: find_or_save_addr: failed to
insert addr interpreti... entry '0' for key 1 at (eval 87) line 166,
<GEN19> line 21. at (eval 88) line 111, <GEN19> line 21.
in the amavisd.log:
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 [EMAIL PROTECTED]: 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.
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)
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.
Enjoy your evening
.peter
On Sun, 29 Jun 2008, Mark Martinec wrote:
Peter,
I painstakingly followed the changes from previous amavisd-new
installations to amavisd-new-2.6.0 a while ago.
I couldn't find any list of changes between 2.6.0 and 2.6.1 except what
was mentioned in the mails on this list. So I sat down and created a diff
between the READMEs of 2.6.0 and 2.6.1.
The few changes I found were (listed as mysql commands):
ALTER table msgs CHANGE sid sid integer;
ALTER table msgrcpt CHANGE rid rid integer;
This should probably be a 'bigint unsigned', or whatever
makes SQL happy (satisfies data type match in FOREIGN KEY).
It reflects the following entry from release notes
(documentation update):
- README.sql-mysql: fixed a SQL data type mismatch between maddr.id (used as
a foreign key) and msgs.sid & msgrcpt.rid; they all should be of the same
type, either integer unsigned or bigint unsigned; a schema as published
in README.sql-mysql could not be built because of a conflict in a data
type; reported by Leonardo Rodrigues Magalhães and Zhang Huangbin;
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);
This looks right, thanks for providing the ALTER commands
for changing varchar into varbinary and char into binary.
This change to a MySQL database is optional and at the moment
not applicable to a more picky PostgreSQL. It refers to this entry
in release notes:
- README.sql-mysql: changed SQL datatype VARCHAR into VARBINARY for
data fields mail_id, secret_id and quar_loc, and CHAR into BINARY for
msgs.content and msgs.quar_type to preserve case sensitivity on string
comparison operators; suggested by Thomas Gelf;
The same change should eventually be done on README.sql-pg too, but as
PostgreSQL is more picky than MySQL on matching a field data type to a
supplied data value, the change of a data type would need to be reflected
in SQL calls in amavisd. This will have to wait until some future version
of amavisd-new, having to undergo more testing than I have available
before the 2.6.1 release.
If anyone has more experience with mysql, or anyone finds a mistake in
the list above, please let me know.
I hope this is useful for more people than just me.
Useful, thanks.
Mark
-------------------------------------------------------------------------
Check out the new SourceForge.net Marketplace.
It's the best place to buy or sell services for
just about anything Open Source.
http://sourceforge.net/services/buy/index.php
_______________________________________________
AMaViS-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/
-------------------------------------------------------------------------
Check out the new SourceForge.net Marketplace.
It's the best place to buy or sell services for
just about anything Open Source.
http://sourceforge.net/services/buy/index.php
_______________________________________________
AMaViS-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/
