I have replication from master server to slave. The slave server data is not 100% “clean” and sometimes I get a replication error like duplicate key (see following log entry):
070928 20:28:08 [ERROR] Slave: Error ‘Duplicate entry ‘478519012’ for key 1’ on query. Default database: ‘community’. Query: 'INSERT INTO user_access (ID,SESSION_ID,IP,AID,ANONYMOUS_FLAG,NEW_VISIT_FLAG,DATE_CRE ATED) VALUES (478519012,‘55567510deb4776a3acbc057990bf83e’,'89.178.91.165 ‘,‘akter’,1,0,1191000486)’, Error_code: 1062
070928 20:28:08 [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.000001’ position 685190145
0
… the big problem is that after this error the replication stops to work and I must start it manually after deleting bad entry.
Is it posible to ignore errors like this? (with any config option or something like that)
Normally, replication stops when an error occurs on the slave. This gives you the opportunity to resolve the inconsistency in the data manually. This option tells the slave SQL thread to continue replication when a statement returns any of the errors listed in the option value.
Do not use this option unless you fully understand why you are getting errors. If there are no bugs in your replication setup and client programs, and no bugs in MySQL itself, an error that stops replication should never occur. Indiscriminate use of this option results in slaves becoming hopelessly out of synchrony with the master, with you having no idea why this has occurred.
For error codes, you should use the numbers provided by the error message in your slave error log and in the output of SHOW SLAVE STATUS. Appendix B, Errors, Error Codes, and Common Problems, lists server error codes.
You can also (but should not) use the very non-recommended value of all to cause the slave to ignore all error messages and keeps going regardless of what happens. Needless to say, if you use all, there are no guarantees regarding the integrity of your data. Please do not complain (or file bug reports) in this case if the slave’s data is not anywhere close to what it is on the master. You have been warned.