Error Duplicate Entry For Key Replication
Contents |
This happens in order for us to be able to identify the problem and fix it, and keep the data mysql replication duplicate entry for key primary consistent with the mater that has sent the query. You can skip mysql replication error 1062 duplicate entry such errors, even if this is not recommended, as long as you know really well what are duplicate entry for key 1 those queries and why they are failing, etc. For example you can skip just one query that is hanging the slave using: 1 mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START duplicate entry for key primary auto increment SLAVE; There might be cases where you will want to skip more queries. For example you might want to skip all duplicate errors you might be getting (output from show slave status;): 1 "1062 | Error 'Duplicate entry 'xyz' for key 1' on query. Default database: 'db'. Query: 'INSERT INTO ..." If you are sure that skipping
Duplicate Entry For Key 'primary' Sql=insert Into
those errors will not bring your slave inconsistent and you want to skip them ALL, you would add to your my.cnf: 1 slave-skip-errors = 1062 As shown above in my example 1062 is the error you would want to skip, and from here we have: _ Error: 1062 SQLSTATE: 23000 (ER_DUP_ENTRY) Message: Duplicate entry ‘%s’ for key %d_ You can skip also other type of errors, but again don’t do this unless you understand very well what those queries are and what impact they have on your data: 1 slave-skip-errors=[err_code1,err_code2,...|all] and for the error codes you can see them all here … (you will see them in your _show slave statu_s; also). Posted by Marius Ducea Feb 13th, 2008 posted in: tips & tricks tagged with: mysql, tips Tweet « Linux Tips: find all files of a particular size Increase PHP memory limit » Comments Please enable JavaScript to view the comments powered by Disqus. About Me My name is Marius Ducea. I am an experienced Infrastructure Devel
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any
Duplicate Entry For Key 'group_key'
questions you might have Meta Discuss the workings and policies of duplicate entry for key unique this site About Us Learn more about Stack Overflow the company Business Learn more about hiring duplicate entry for key 2 developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site http://www.ducea.com/2008/02/13/mysql-skip-duplicate-replication-errors/ for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Master-Master Replication Duplicate Key http://dba.stackexchange.com/questions/68317/master-master-replication-duplicate-key up vote 1 down vote favorite 1 I have set two servers master-master replication mysql , i manage to set successfully but the problem is the duplicate key i got MASTER 1 Configuration /etc/my.cnf [mysqld] server-id=1 bind-address = 0.0.0.0 log_bin = /var/log/mysql/bin.log log-slave-updates log_bin-index = /var/log/mysql/bin.index log_error = /var/log/mysql/error.log relay_log = /var/log/mysql/relay.log relay-log-info-file = /var/log/mysql/relay.info relay-log-index = /var/log/mysql/relay.index binlog_do_db=database_name replicate-do-db = database_name auto-increment-increment = 10 auto-increment-offset = 1 innodb_file_per_table=1 open_files_limit=7306 mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.1 Master_User: database_name Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000021 Read_Master_Log_Pos: 8385119 Relay_Log_File: relay.000019 Relay_Log_Pos: 79533 Relay_Master_Log_File: bin.000021 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: database_name Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '19882' for key 'PRIMARY'' on query. Default database: 'database_name'. Query: 'INSERT INTO dle_comments (post_id, date, autor, email, text, ip, is_register, approve) values ('353', '2014-06-16 13:04:43', 'ofadoruo', 'ebuxaje@asdfooff.org', 'Peritoneal Accutanea> emerge subjective, section state; pattern; Start here for a quick overview of the site Help Center Detailed answers to http://serverfault.com/questions/349307/why-does-replication-fail-with-error-duplicate-entry-for-key any questions you might have Meta Discuss the workings and http://www.pinoytux.com/linux/tip-mysql-replication-error-duplicate-entry-for-key policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Server Fault Questions Tags Users Badges Unanswered Ask Question _ Server Fault is a question and answer duplicate entry site for system and network administrators. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Why does replication fail with error Duplicate entry for key? up vote 2 down duplicate entry for vote favorite We have been replicating for a year or two now with little problem. Sometimes we get an erroneous SQL query which stops replications, and we use the following commands to get it going again: STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE; SHOW SLAVE STATUS \G; This is usually fine, however today we've started getting errors while trying to replicate a (FYI Invision Power Board) database: Last_SQL_Error: Error 'Duplicate entry 'forums-pid-994' for key ' app'' on query. Default database: 'forum_db'. Query: 'INSERT INTO ibf_rep utation_cache (`app`,`type`,`type_id`,`rep_like_cache`) VALUES('forums','pid',99 4,'a:2:{s:10:\"cache_data\";a:0:{}s:12:\"cache_expire\";i:1326339370;}') ON DUPL ICATE KEY UPDATE app=VALUES(app),type=VALUES(type),type_id=VALUES(type_id),rep_l ike_cache=VALUES(rep_like_cache)' There are many queries like this it seems as part of the Invision Power Board software so getting rid of it isn't a choice. The weird thing is, when I run that exact query on the same MySQL server it goes through without problem. Note: We upgraded from MySQL 5.1.36 to MySQL 5.5.16 yesterday to my surprise occurred on a slave database. error 'Duplicate entry '2355476' for key 1' on query ' INSERT INTO logs (DateStamp) VALUES (NOW()) You might be wondering how a slave database could have a duplicate entry while the master and other slaves are running just fine? Some say that there could be an entry where it was written in the slave and then written again so the duplicate error occurred. Others say that if myisamchk was run in the master, it removed some entries and tries to create those entries again. The slave then attempts to write the same entry, but since myisamchk was not executed on the slave, the entry exists hence the duplication error. But luckily, found myself a solution to fix the replication error: mysql> set sql_slave_skip_counter = 1;
Query OK, 0 rows affected (0.01 sec) mysql> slave start;
Query OK, 0 rows affected (0.00 sec) This will skip the error and continue on with the replication. Do not forget to start the slave process afterwards. Related PostsStrict Standards: date() [function.date] Error (2)Sudo error: Sorry, you must have a tty to run sudo (1)How to Fix PECL PHP Error: /bin/sh: bad interpreter: Permission denied (2)John Dvorak: The Sun-MySQL Deal Stinks (1)Sun Microsystems To Acquire MySQL for $1B (0) 3 Comments to "Tip: MySQL Replication Error: Duplicate Entry for Key" Ratheesh January 7, 2010 at 6:57 pm I'm having the similar problem but its very often. I was more curios what would be root cause and how to resolve it once in for all. A stable solution that can solve the problem forever any ideas or anybody ?? Uma July 27, 2010 at 2:54 pm But any idea why in first place does this occur?? We face this error many times, we know the solution to skip the tran. But we need the reason as to why do we face this on slave, when all is fine on the master? mandm August 3, 2010 at 10:38 pm i had to do this set global sql_slave_skip_counter = 1; since it is a global variable but what does this setting actually do internally to the entire replication process Post comment Click here to cancel reply. Name (required) Mail (will not be published) (required) Website Comment Notify me of followup comments via e-mail Follow me on Twitter! Got Plurk? Follow on Plurk instead. Search PinoyTux Subscribe to Email Feeds Enter Email Address: Blog Lounge Contact Me Disclosure Policy PinoyTux Archives Popular Posts Tips for Keeping Your Cloud Secure Cebu Pacific Sucks Howto: Install yum On RHEL 4 Recent Posts Vim: How to Copy Multiple Lin