Nate a écrit : > At 12:00 PM 10/9/2007, Olivier Smedts wrote: > >> I agree to the idee that replication should not happen in policyd, but >> in MySQL, cause MySQL already supports replication and redundancy. And >> it works nearly like a charm. But here is the problem : >> policyd doesn't use an AUTO_INCREMENT id in its primary keys (in >> "triplet" table mostly), so if two mail servers receive the same mail in >> the same time (between the time at which policyd checks in the entry >> already exists and the time at which it inserts a new one), the first >> server will insert the new entry, and the second server won't be able to >> insert it (primary key constraint violation) so the replication will stop. >> >> > >> > I personnaly like this since I have already replicated system like >> > that with severals slaves... and a loadbalancer have lb read slaves. >> >> I set up 3 mail servers (MX) with policyd and MySQL multi-master chain >> replication (each node is slave of the previous one and master of the >> next one). >> I think a solution would be to insert "IF EXIST" in DELETE and UPDATE >> queries, and "IF NOT EXIST" in INSERT queries of policyd (greylist.c >> source file), so that the same query could be executed two times without >> stopping replication. What do you think of this solution ? Could it be >> done upstream, to make policyd a perfect greylisting daemon for mail >> server clusters ? >> > > We have a similar setup and it is pretty rare that this type of > duplicate key item would occur. A easy fix within mysql is to just > tell mysql to ignore duplicate key errors on replication. It'll > create an extremely slight data inconsistency; however, it will not > affect anything from my experience. Just add the following to my.cnf > [mysqld] section. > > slave-skip-errors=126,1062 > I appreciate your feedback, I haven't checked slave-skip-errors. But what is error 126 ? I don't see it in http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html
In this setup with three nodes, do you know what happens in the case that the two first servers receive the same mail at the same time ? The first that receives the mail inserts a new entry in the "triplet" table. The second one will try to insert it to but will fail cause it already exists. It won't stop replication thanks to slave-skip-errors=1062. But will the second server log the insert request in its master log, so that the entry can be replicated on the third server ? Or will it "skip" the request and won't log it because of skip-slave-errors ? I checked policyd source again and made little tests in the MySQL database, with no slave-skip-errors : - when trying to delete a non-existent triplet : DELETE QUICK from triplet WHERE _host='999.999.999'; Query OK, 0 rows affected (0.00 sec) => no errors - when updating a non-existent triplet : UPDATE triplet SET _datelast='1191502133',_count=_count+1 WHERE _host='999.999.999' AND _from='[EMAIL PROTECTED]' AND _rcpt='[EMAIL PROTECTED]'; Query OK, 0 rows affected (0.00 sec) => no errors too, only "0 rows affected" - when trying to insert an already-existing triplet : INSERT DELAYED INTO triplet (_datenew,_datelast,_host,_from,_rcpt) VALUES (1190984813,1191502135,'127.0.0','[EMAIL PROTECTED]','[EMAIL PROTECTED]'); Query OK, 1 row affected (0.00 sec) INSERT DELAYED INTO triplet (_datenew,_datelast,_host,_from,_rcpt) VALUES (1190984813,1191502135,'127.0.0','[EMAIL PROTECTED]','[EMAIL PROTECTED]'); Query OK, 1 row affected (0.00 sec) => the DELAYED keyword make the request work. If you try without it, it will return "ERROR 1062 (23000): Duplicate entry '[EMAIL PROTECTED]@domain.com' for key 1". With INSERT DELAYED, there's no error. The fields aren't updated though. I now think that policyd doesn't need even a little modification to make it work perfectly with MySQL replication. It seems that there is no need for "IF EXIST" or "IF NOT EXIST" because UPDATE and DELETE can be made on non-existent keys, and INSERT DELAYED doesn't return an error in case of duplicate key. -- Olivier > We use this reliably with policyd as well as dozens of other mysql > apps with zero impact on data integrity. > > - Nate > > > > > ------------------------------------------------------------------------- > This SF.net email is sponsored by: Splunk Inc. > Still grepping through log files to find problems? Stop. > Now Search log events and configuration files using AJAX and a browser. > Download your FREE copy of Splunk now >> http://get.splunk.com/ > _______________________________________________ > policyd-users mailing list > policyd-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/policyd-users > > ------------------------------------------------------------------------- This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now >> http://get.splunk.com/ _______________________________________________ policyd-users mailing list policyd-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/policyd-users
