bayes in MySQL with replication

[Jason Ede]

Many thanks for that... just what I was looking for...

I also saw a

080222 15:38:55 [ERROR] Slave: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON DUPLICATE KEY UPDATE spam_count = GREATEST(spam_count + '0', 0),
            ' at line 2' on query. Default database: 'sa_bayes'. Query: 'INSERT INTO bayes_token
               (id    ON DUPLICATE KEY UPDATE spam_count = GREATEST(spam_count + '0', 0),
                                       ham_count = GREATEST(ham_count + '1', 0),
                                       atime = GREATEST(atime, '1203694258')Ã', Error_code: 1064
080222 15:38:55 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000011' position 5334725

error. I'm guessing I can add 1064 to that list too although I can't see what the syntax is wrong


Jason
From: mailscanner-bounces at lists.mailscanner.info [mailto:mailscanner-bounces at lists.mailscanner.info] On Behalf Of Rick Cooper
Sent: 22 February 2008 14:55
To: 'MailScanner discussion'
Subject: RE: bayes in MySQL with replication



________________________________
From: mailscanner-bounces at lists.mailscanner.info [mailto:mailscanner-bounces at lists.mailscanner.info] On Behalf Of Jason Ede
Sent: Friday, February 22, 2008 7:00 AM
To: MailScanner discussion
Subject: OT: bayes in MySQL with replication
(sent 2ce as I think it was blocked first time due to random binary characters)

I know this is way off topic, but I'm guessing others have had this and have solved it...

Since our 2 mail servers are on different sites and the VPN between them hasn't been overly reliable I've been trialling the bayes in MySQL with Master - Master replication. (MySQL 5.0)

This seems to work fine for a few hours and I then get...

080222 10:38:07 [ERROR] Slave: Error 'Duplicate entry '<random binary>' for key 1' on query. Default database: 'sa_bayes'. Query: 'INSERT INTO bayes_token
                   (id, token, spam_count, ham_count, atime)
                   VALUES ('1','<random binary>','0','1','1203675610')', Error_code: 1062
080222 10:38:07 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000006' position 8791360

and SQL replication stops. Is there any way of avoiding this or of specifying a default action in case of a clash like this?

Currently each mailserver connects to the local copy of the database.

Jason


[Rick Cooper]

Add

slave-skip-errors = 1062

To your mysql config file under the [mysqld] section. that line says skip duplication errors, you might even want slave-skip-errors = 1053,1062 (skip master shutting down  and data duplication errors)

Look here for other replication config options that might interest you

http://dev.mysql.com/doc/refman/5.1/en/replication-options.html



--
This message has been scanned for viruses and
dangerous content by MailScanner<http://www.mailscanner.info/>, and is
believed to be clean.

--
This message has been scanned for viruses and
dangerous content by MailScanner<http://www.mailscanner.info/>, and is
believed to be clean.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.mailscanner.info/pipermail/mailscanner/attachments/20080222/ef12fe38/attachment.html