Thomas, > > It's getting even worse. The following SQL will not work... > > > CREATE TABLE IF NOT EXISTS `msgs` ( > > > ... > > > PRIMARY KEY (`mail_id`,`partition_tag`), > > > FOREIGN KEY (`sid`, `partition_tag`) > > > REFERENCES maddr(`id`, `partition_tag`) ON DELETE RESTRICT, > > ...as MySQL 5.1 doesn't support foreign keys on partitioned tables. > > It would be nice to have these foreign key constraints, but as they > are IMO solely needed for garbage collection (please correct me, if > I'm wrong) I can live without them - DROP PARTITION does a better > job and none of my other applications are relaying on these keys.
True. The FOREIGN KEY is only used for purging (ON DELETE RESTRICT) and can be dropped if other purging methods are used, like dropping a partition, or even just deleting records, individually for each table (without referencing other tables) based on partition_tag only. > That's the good news. Bad news is, that there is one (more?) really > ugly MySQL bug that manages it to produce duplicate auto_increment > keys when using partitions: > http://bugs.mysql.com/bug.php?id=27405 > http://lists.mysql.com/commits/25285 > Bug should have been fixed with 5.1.22 (at least with 5.1.23) - and > I'm using 5.1.24rc from Debian's experimental tree. Thanks for a research. > Nonetheless it > happened that immediately after switching one of my front MX servers > to the new amavisd-new instances there have been a lot of mails in > my queue, temporarily rejected by amavisd-new with the following > > error message: > > 451 4.5.0 Error in processing, id=31462-06, sql-enter FAILED: > > find_or_save_addr: failed to insert addr [EMAIL PROTECTED]: sql exec: > > err=1022, 23000, DBD::mysql::st execute failed: Can't write; > > duplicate key in table 'maddr' at (eval 96) line 166, <GEN16> line > > 2157. at (eval 97) line 112, <GEN16> line 2157. (in reply to end of > > DATA command) > > While investigating the problem I stumbled over innodb_autoinc_lock > _mode, a global server variable introduced with 5.1.22. Setting it > to 0 (default is 1) seems to make things better, but there are still > temporarily rejected mails from time to time (with the same error > as reported above). Is it still happening? Amavisd uses the following logic when finding/inserting e-mail addresses in table maddr: 1. First an attempt to find an existing address is done, using 'sel_adr' clause: SELECT id FROM maddr WHERE partition_tag=? AND email=? Even though there may be a race condition with other amavisd child processes it is worthwhile to do this step first as it is quite likely the address is already in a database, especially for recipient addresses. 2. If SELECT did not find an existing record, the INSERT is done as a second step, using 'ins_adr' clause: INSERT INTO maddr (partition_tag, email, domain) VALUES (?,?,?) This INSERT may fail if more than amavisd process is dealing with the same new address (in different messages) and they are all trying to trying to INSERT it at about the same time. Because the INSERT is expected to fail at this point once in a while, the error is not yet reported at this point, but just remembered and reported after step 3 if necessary. 3. after the INSERT was done (regardless of its outcome), a 'sel_adr' is done a second time. If there were a race condition during INSERT it is now expected that the second SELECT attempt will find a record just inserted by the same or some other process. Even if INSERT succeeded by this same process, a subsequent SELECT provides the needed auto-generated maddr.id field, eliminating a need for a working (nonstandard) last_insert_id method. Now, if this second SELECT failed to find a record, only then the failure of step 2 is reported - which is what you are seeing. I'm not sure what sequence of events would lead to this in a partitioned database. > If you are interested in farther details I'll let you know how things > are going on. Otherwise just tell me to STFU ;-) I'm grateful that you are willing to invest time and effort to pave the path for others to follow. Mark ------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ 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/
