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

GTID(GLOBAL TRANSACTION ID) FOR MYSQL 5.6 REPLICATION

Replication: Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves). Replication is asynchronous - slaves need not be connected permanently to receive updates from the master. This means that updates can occur over long-distance connections and even over temporary or intermittent connections such as a dial-up service. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database. 

In MySQL version 5.6 Oracle released one of the enhancement is GTID for Replication.

Early we have to set master_log_position and master_log_file in slave instead of these both,we can use this GTID for master slave replication in MySQL 5.6.

The Global Transaction IDentifier (GTID) is a unique identifier created and associated with each transaction when it is committed on the server of origin (master). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication setup.

We need to add and enable some variables in cnf file. The gtid_mode and enforce-gtid-consistency.Starting the server with gtid_mode=ON requires that the server also be started with the log_bin and log_slave_updates options as well.

gtid_mode  : It is the main option that needs to be enabled for global transaction IDs.

enforce-gtid-consistency : It allows execution of only those statements that can be logged in a transactionally safe manner.

For master slave replication we need to enable gtid_mode basically.

Master cnf:

Slave cnf:

Now create the replication user in Master:

 

When we see the master status we see the Executed_Gtid_Set (set of all transaction logged in the binary log).

 Now do the changes in slave server but no need to give master_log_position and master_log_file anymore. Start the slave nowCheck the replication is running or not  by using slave status.

 

MASTER_AUTO_POSITION = 1 is used when the slave attempts to connect to the master using the GTID-based replication protocol. In this case, the coordinates represented by MASTER_LOG_FILE and MASTER_LOG_POS are not used, and global transaction identifiers are used instead. Thus the use of either or both of these options together with MASTER_AUTO_POSITION causes an error.

gtid_mode must also be enabled before issuing CHANGE MASTER TO ... MASTER_AUTO_POSITION = 1. Otherwise, the statement fails with an error.

Retrieved_Gtid_set : It displays the transaction that we read from master.
Executed_Gtid_set : It displays the transaction that we already executed.

Now we check the replication is works fine if the command executed in master that is replicate on slave or not.
In Master: Check it in Slave: Got successfully replicated by GTID.

So here after no need to worry about log file and position in slave while doing change master to statement.