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 [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. 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 ------------------------------------------------------------------------- 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/
