Replication_Error: Error_code : 1205; handler error HA_ERR_LOCK_WAIT_TIMEOUT;

Hi,

Recently I have faced an issue in Master Slave replication.Unfortunately slave stops it replication and it throws an error as,

[ERROR] Slave SQL: Could not execute Update_rows event on table DBname.tablename; Lock wait timeout exceeded; try restarting transaction,

Error_code: 1205; handler error HA_ERR_LOCK_WAIT_TIMEOUT; the event's master log mysql-bin.******, end_log_pos *******, Error_code: 1205

To clear the error. I did,

show slave status\G
stop slave;
start slave;

Now error has gone and replication runs successfully.

It happens at sometime when a query takes long time to execute.


Thank you...

MySQL Until_log_file and Until_log_pos

How to use until option in MySQL Replication.
 
Some times we need updates in replication for until binlog file and position, during that time we can use this option in Slave server.

Here I gave how to do it.



 
The above fig. shows there is no until condition in slave status.

1.Give the change master query.

Change master to
     master_user='user name',
     master_host='host name',
     master_password='******',
     master_log_file='mysql-bin.000142',
     master_log_pos='562483'
     master_port='3306';

2.Dont use START SLAVE;
   first start the IO thread. START SLAVE IO_THREAD;

3.Now give the until condition while start the SQL thread,

   START SLAVE SQL_THREAD UNTIL MASTER_LOG_FILE='mysql-bin.000146',
   MASTER_LOG_POS=178741931;

   or


   START SLAVE SQL_THREAD UNTIL RELAY_LOG_FILE='mysql-bin.******',
   RELAY_LOG_POS=*****;


4.Now See, SLAVE STATUS\G



 Here we can see the until condition is MASTER and see the until_log_file and until_log_pos that what are the values we gave when we started the SQL thread.The replication will be happen until the given values.

Until_Condition has these values:

• None if no UNTIL clause was specified
• Master if the slave is reading until a given position in the master's binary log
• Relay if the slave is reading until a given position in its relay log

http://docs.oracle.com/cd/E17952_01/refman-5.1-en/start-slave.html