Thesql replication deadlock slave stopped

deadlockMySQLreplication

I've a master and slave and got the slave stopped with the following error log in slave. I want to fix this and get the two servers on synch and get going again.

111128 8:42:24 [Note] Slave I/O thread: connected to master 'repl@masterIP:3306', replication started in log 'mysql-bin.000006' at position 169

111128 15:46:50 [ERROR] Slave: Query caused different errors on master and slave. Error on master: 'Deadlock found when trying to get lock; try restarting transaction' (1213), Error on slave: 'no error' (0). Default database: 'Dbase'. Query: 'insert into archieved(eid,at,rtime,scode) select id,at,avg(rtime),scode from tstatus where date(at)="2011-10-29" group by id', Error_code: 0
111128 15:46:50 [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.000006' position 1277768

I'm not sure how to do the following:

try restarting transaction' (1213)

Thanks.

Best Answer

This query fails on slave: 'insert into archieved(eid,at,rtime,scode) select id,at,avg(rtime),scode from tstatus where date(at)="2011-10-29" group by id'

You report a strange Error_code: 0, but try to run this query on slave? When it run succesfully then run as root:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
SLAVE START;

Related Solutions

Mysql – Broken thesql replication on slave server (error 1236)

I believe the log file and position need to be specified with the host.

Try:

CHANGE MASTER TO MASTER_HOST='192.168.1.105', MASTER_USER='slaveuser', MASTER_PASSWORD='mypassword', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;

Mysql – How to fix Replication event checksum verification failed while reading from network. Error_code: 1743

Try resetting the relay logs as follows:

STOP SLAVE;
CHANGE MASTER TO master_log_file='mysql-bin.000030',master_log_pos=196820914;
START SLAVE;

This will erase all relay logs in the Slave, starting with a fresh one. It should start retrieving binlog events from the Master. If it fails again, then mysql-bin.000030 on the Master may be corrupt. In that event, you would have to set up replication from scratch by doing this:

Stop the DB Application
On the Master, RESET MASTER;
mysqldump the data from the Master
Load the mysqldump on the Slave
On the Slave, run STOP SLAVE;
On the Slave, run CHANGE MASTER TO master_log_file='mysql-bin.000001',master_log_pos=4;
On the Slave, run START SLAVE;

CAVEAT

Make sure the network connection is clear (with no dropped packets) between Master and Slave.

Best Answer

Related Solutions

Mysql – Broken thesql replication on slave server (error 1236)

Mysql – How to fix Replication event checksum verification failed while reading from network. Error_code: 1743

CAVEAT

Give it a Try !!!

Related Question