Thomas Gelf schrieb: > Thomas Gelf schrieb: >> db partitioning caused me some headache last days... > > 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. > > GREAT!!! > *grrrrr*
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. 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. 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). Right now I'm pretty sure that there is absolutely no one out there using such a setup in production :-( Nonetheless, I'll keep working on this - half of my email traffic is still flowing through this system and the rest will follow within the next hours. If you are interested in farther details I'll let you know how things are going on. Otherwise just tell me to STFU ;-) Regards, Thomas Gelf NB: Is there someone doing the same job with PostgreSQL? If so: Can you provide me some farther details? Good and bad experiences, replication etc? ------------------------------------------------------------------------- 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 AMaViS-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/amavis-user AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3 AMaViS-HowTos:http://www.amavis.org/howto/
