mysql> mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: hsotname
Master_User: slaverepl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000378
Read_Master_Log_Pos: 157600599
Relay_Log_File: server6-relay-bin.000001
Relay_Log_Pos: 105736
Relay_Master_Log_File: mysql-bin.000378
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: mydatabase
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 ‘1748099’ for key 1’ on query. Default database: ‘mydatabase’. Query: 'INSERT INTO sbbleads_members_extended (sb_uid, sb_short_msg_alert ,sb_referrer,sb_landing_url)
VALUES(1752005, ‘1’ ,‘http://www.google.com’,'http://www.dummyvalue.com/buyoffer_view/id/250989.htm’)’
Skip_Counter: 0
Exec_Master_Log_Pos: 157535878
Relay_Log_Space: 170457
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
1 row in set (0.00 sec)
I did
mysql> STOP SLAVE IO_THREAD;
& deleted the duplicate record from database.Than i did
CHANGE MASTER TO
MASTER_HOST=xx
MASTER_USER=xx
MASTER_PASSWORD=xx
MASTER_LOG_FILE=xx
MASTER_LOG_POS=157600599;
- which i noted from above show slave status (Read_Master_Log_Pos: 157600599),
and than
mysql > START SLAVE;
BUT NOW IT’S GIVING ME ‘DUPLICATE ENTRY’ ERROR AGAIN AND AGAIN.
Please let me know how can i sort this out. Help me out. I believe i need to look into MASTER_LOG_POS parameter
I am also has the same " Slave_SQL_Running: No " problem , the replication stop , Error ‘Duplicate entry …’ in the log error file and in the “show slave status” command, " Slave_SQL_Running: No " and " Last_Errno: 1062 " in the " show slave status " .
Yes I know what caused this, one of the row in the slave already had a Primary Key before the master had it .The replication process goes to a halt when the slave encountered one same primary key from the master log that it already had.
Obviously I did this(read for slave , write for master) for the sake of testing in the slave (Thank god this happen in the development phase not in production ) .
How to solve this ? Obviously I dont want to dump all the master database again and set up again my replication from scratch…
-is it possible for me to edit the master-bin log and remove the problematic INSERT query and then configure the position log in the slave through CHANGE MASTER command to the value of Exec_Master_Log_Pos:1234 in the " show slave status " . Because I notice this Exec_Master_Log_Pos: value is always smaller than the Read_Master_Log_Pos: during this error of replication failure happen. Because when the replication running OK before , the Exec_Master_Log_Pos: value and Read_Master_Log_Pos: value are the same .
Thank you
if you look up that error in the mysql manual, it describes how to change the log position and restart replication.
yup, thank you . after going through the MySQL-Sun website , just to share to some of you guys who might need it, the simpler way than snapshot-drop-slave-db-dump-reposition-master-binlog is :
->stop slave;
->SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n ; (change the letter n to 2 if the replication failure primarily caused by auto-increment primary key or LAST_INSERT_ID(). Other than that , use 1)
->start slave;
the only catch is , this command will skip the problematic INSERT (for auto-increment/duplicate entry primary key problem) query issued by the master(usually) . then , if somehow you still wanna use the entry from master(by getting from master binlog file) , manually you have to update the slave db to match the master db.